Обсуждение: Temporarily suspend a user account?
Hello,
Is there a way to temporarily suspend a user account?
I would prefer not to revoke login privileges since that will break
things that mine pg_users and pg_shadow.
I also am trying to find something that is completely reversible, so
something like setting connection limit to 0, which would lose a
potentially customized connection limit, doesn’t work.
We do this in MySQL by reversing the password hash then running FLUSH
PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid
as some sort of cache prevents this from taking effect.
Has anyone else solved this issue? Thank you!
-Felipe Gasper
Houston, TX
Possibly,
ALTER USER xname RENAME TO name;
???
To disble:To enable
ALTER USER name RENAME TO xname;
ALTER USER xname RENAME TO name;
???
On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper <felipe@felipegasper.com> wrote:
Hello,
Is there a way to temporarily suspend a user account?
I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow.
I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work.
We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect.
Has anyone else solved this issue? Thank you!
-Felipe Gasper
Houston, TX
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Felipe Gasper wrote > Hello, > > Is there a way to temporarily suspend a user account? > > I would prefer not to revoke login privileges since that will break > things that mine pg_users and pg_shadow. > > I also am trying to find something that is completely reversible, so > something like setting connection limit to 0, which would lose a > potentially customized connection limit, doesn’t work. > > We do this in MySQL by reversing the password hash then running FLUSH > PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid > as some sort of cache prevents this from taking effect. > > Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past http://www.postgresql.org/docs/9.4/interactive/sql-alterrole.html David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836982.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> I would prefer not to revoke login privileges since that will break things > that mine pg_users and pg_shadow. > Melvin Davidson-5 wrote >> Possibly, >> >> To disble: >> ALTER USER name RENAME TO xname; >> >> To enable >> ALTER USER xname RENAME TO name; Given that removing login privileges is a no-go this doesn't seem like an acceptable solution for the OP. Its unclear exactly what catalog data is being used but likely the role name is an important one. David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836987.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Might not do what you want, but I just change the password.
--
Mike Nolan--
On Fri, Feb 6, 2015 at 4:11 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
Possibly,To disble:To enable
ALTER USER name RENAME TO xname;
ALTER USER xname RENAME TO name;
???--On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper <felipe@felipegasper.com> wrote:Hello,
Is there a way to temporarily suspend a user account?
I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow.
I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work.
We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect.
Has anyone else solved this issue? Thank you!
-Felipe Gasper
Houston, TX
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalMelvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Might not do what you want, but I just change the password.
How do you do that and re-enable using the previous password?
David J.
View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Melvin Davidson <melvin6925@gmail.com> writes: > Possibly, > > To disble: > ALTER USER name RENAME TO xname; Fine if you don't care about losing password :-) yomamadb/postgres =# create user foo password 'foowow'; CREATE ROLE yomamadb/postgres =# alter user foo rename to fooxxx; NOTICE: MD5 password cleared because of role rename <<<--- ALTER ROLE yomamadb/postgres =# > > To enable > ALTER USER xname RENAME TO name; > > ??? > > On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper <felipe@felipegasper.com> wrote: > > Hello, > >     Is there a way to temporarily suspend a user account? > >     I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. > >     I also am trying to find something that is completely reversible, so something like setting connectionlimit to 0, which would lose a potentially customized > connection limit, doesnât work. > >     We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesnâtseem to work in PostgreSQL/pg_authid as some sort of > cache prevents this from taking effect. > >     Has anyone else solved this issue? Thank you! > > -Felipe Gasper > Houston, TX > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- > Melvin Davidson > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. [01] > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 6 Feb 2015 3:15 PM, David G Johnston wrote: > Felipe Gasper wrote >> Hello, >> >> Is there a way to temporarily suspend a user account? >> >> I would prefer not to revoke login privileges since that will break >> things that mine pg_users and pg_shadow. >> >> I also am trying to find something that is completely reversible, so >> something like setting connection limit to 0, which would lose a >> potentially customized connection limit, doesn’t work. >> >> We do this in MySQL by reversing the password hash then running FLUSH >> PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid >> as some sort of cache prevents this from taking effect. >> >> Has anyone else solved this issue? Thank you! > > Personally untested: > > ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in > the past > This doesn’t work, either, because it will clobber any custom expiration time for the role … -FG
On 6 Feb 2015 3:24 PM, David G Johnston wrote: > On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] <[hidden > email] </user/SendEmail.jtp?type=node&node=5836990&i=0>>wrote: > > Might not do what you want, but I just change the password. > > > How do you do that and re-enable using the previous password? > Is there no way to “sync up” from a custom MD5 hash in pg_authid? -FG
On 6 Feb 2015 3:15 PM, David G Johnston wrote:
> Felipe Gasper wrote
>> Hello,
>>
>> Is there a way to temporarily suspend a user account?
>>
>> I would prefer not to revoke login privileges since that will break
>> things that mine pg_users and pg_shadow.
>>
>> I also am trying to find something that is completely reversible, so
>> something like setting connection limit to 0, which would lose a
>> potentially customized connection limit, doesn’t work.
>>
>> We do this in MySQL by reversing the password hash then running FLUSH
>> PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid
>> as some sort of cache prevents this from taking effect.
>>
>> Has anyone else solved this issue? Thank you!
>
> Personally untested:
>
> ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in
> the past
>
This doesn’t work, either, because it will clobber any custom expiration
time for the role …
-FG
Since everything about a role can be customized, and there is no simple "enabled" boolean, you need to take a known value, cache it somewhere, make your change, then restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question.
David J.
View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2/6/15, David G Johnston <david.g.johnston@gmail.com> wrote: > On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] < > ml-node+s1045698n5836989h83@n5.nabble.com> wrote: > >> Might not do what you want, but I just change the password. >> >> > How do you do that and re-enable using the previous password? > > David J. Encrypted passwords are kept in the pg_shadow file and should start with 'md5'. Just save a copy of the encrypted password for that user and when you want to re-enable that user do: alter user xxx encrypted password 'md5xxxxxxxx'; I have tested this on 9.3.5. -- Mike Nolan
On 6 Feb 2015 4:04 PM, Michael Nolan wrote: > > Encrypted passwords are kept in the pg_shadow file and should start with 'md5'. > > Just save a copy of the encrypted password for that user and when you > want to re-enable that user do: > > alter user xxx encrypted password 'md5xxxxxxxx'; > > I have tested this on 9.3.5. That’s basically what I tried before, though I just string-reversed the MD5 hash so that I could un-reverse it. I also prefixed “md5” with '-' so I could tell which passwords were scrambled. What I found was that a “suspended” user could still log in, though. I looked for some control to “reload” the passwords from that datastore but couldn’t find any. I also did this on pg_authid; would that have made a difference? -FG
David G Johnston <david.g.johnston@gmail.com> writes: > On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] <[hidden email]> wrote: > > On 6 Feb 2015 3:15 PM, David G Johnston wrote: > > > Felipe Gasper wrote > >> Hello, > >> > >> Is there a way to temporarily suspend a user account? > >> > >> I would prefer not to revoke login privileges since that will break > >> things that mine pg_users and pg_shadow. > >> > >> I also am trying to find something that is completely reversible, so > >> something like setting connection limit to 0, which would lose a > >> potentially customized connection limit, doesnât work. > >> > >> We do this in MySQL by reversing the password hash then running FLUSH > >> PRIVILEGES; however, that doesnât seem to work in PostgreSQL/pg_authid > >> as some sort of cache prevents this from taking effect. > >> > >> Has anyone else solved this issue? Thank you! > > > > Personally untested: > > > > ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in > > the past > > > > This doesnât work, either, because it will clobber any custom expiration > time for the role ⦠> > -FG > > âSince everything about a role can be customized, and there is no simple "enabled" boolean, you need to take a knownvalue, cache it somewhere, make your change, then > restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. Here we go... disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; > > David J. > â >  > > ------------------------------------------ > View this message in context: Re: Temporarily suspend a user account? > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 6 Feb 2015 4:21 PM, Jerry Sievers wrote: > David G Johnston <david.g.johnston@gmail.com> writes: > >> On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] <[hidden email]> wrote: >> >> On 6 Feb 2015 3:15 PM, David G Johnston wrote: >> >> > Felipe Gasper wrote >> >> Hello, >> >> >> >> Is there a way to temporarily suspend a user account? >> >> >> >> I would prefer not to revoke login privileges since that will break >> >> things that mine pg_users and pg_shadow. >> >> >> >> I also am trying to find something that is completely reversible, so >> >> something like setting connection limit to 0, which would lose a >> >> potentially customized connection limit, doesn’t work. >> >> >> >> We do this in MySQL by reversing the password hash then running FLUSH >> >> PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid >> >> as some sort of cache prevents this from taking effect. >> >> >> >> Has anyone else solved this issue? Thank you! >> > >> > Personally untested: >> > >> > ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in >> > the past >> > >> >> This doesn’t work, either, because it will clobber any custom expiration >> time for the role … >> >> -FG >> >> ​Since everything about a role can be customized, and there is no simple "enabled" boolean, you need to take a knownvalue, cache it somewhere, make your change, then >> restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. > > Here we go... > > disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; > > enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; > This does appear to work. It didn’t work earlier when I mangled the format such that it no longer began with “md5”, though. Weird. Anyway, thank you! :) -FG
On 6 Feb 2015 4:31 PM, Felipe Gasper wrote: > On 6 Feb 2015 4:21 PM, Jerry Sievers wrote: >> David G Johnston <david.g.johnston@gmail.com> writes: >> >>> On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] >>> <[hidden email]> wrote: >>> >>> On 6 Feb 2015 3:15 PM, David G Johnston wrote: >>> >>> > Felipe Gasper wrote >>> >> Hello, >>> >> >>> >> Is there a way to temporarily suspend a user account? >>> >> >>> >> I would prefer not to revoke login privileges since that will >>> break >>> >> things that mine pg_users and pg_shadow. >>> >> >>> >> I also am trying to find something that is completely >>> reversible, so >>> >> something like setting connection limit to 0, which would lose a >>> >> potentially customized connection limit, doesn’t work. >>> >> >>> >> We do this in MySQL by reversing the password hash then >>> running FLUSH >>> >> PRIVILEGES; however, that doesn’t seem to work in >>> PostgreSQL/pg_authid >>> >> as some sort of cache prevents this from taking effect. >>> >> >>> >> Has anyone else solved this issue? Thank you! >>> > >>> > Personally untested: >>> > >>> > ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that >>> to sometime in >>> > the past >>> > >>> >>> This doesn’t work, either, because it will clobber any custom >>> expiration >>> time for the role … >>> >>> -FG >>> >>> ​Since everything about a role can be customized, and there is no >>> simple "enabled" boolean, you need to take a known value, cache it >>> somewhere, make your change, then >>> restore the cached value; or just edit pg_hba.conf and add reject >>> entries for the role in question. >> >> Here we go... >> >> disable: update pg_authid set rolpassword = rolpassword || '.disabled' >> where rolname = 'foo'; >> >> enable: update pg_authid set rolpassword = rtrim(rolpassword, >> 'disabled') where rolname = 'foo'; >> > So, this works when I do it manually, but not when I script it. Is it possible that this change doesn’t take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? -FG
So, this works when I do it manually, but not when I script it.
Is it possible that this change doesn’t take effect immediately? Is
there any way to tell when it does (besides just waiting until login
attempts fail)?
It should take effect when you commit the transaction in which you perform the update...
The active sessions would remain logged in but future attempts to login would fail.
David J.
View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 6 Feb 2015 4:51 PM, David G Johnston wrote: > On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] <[hidden > email] </user/SendEmail.jtp?type=node&node=5837007&i=0>>wrote: > > > So, this works when I do it manually, but not when I script it. > > Is it possible that this change doesn’t take effect immediately? Is > there any way to tell when it does (besides just waiting until login > attempts fail)? > > > It should take effect when you commit the transaction in which you > perform the update... > > The active sessions would remain logged in but future attempts to login > would fail. > Yeah now I’m wondering if DBD::Pg is doing some weird caching. Anyhow, I’ll figure it out. Thank you! :) -FG
On 6 Feb 2015 4:51 PM, David G Johnston wrote:
> On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] <[hidden
> email] </user/SendEmail.jtp?type=node&node=5837007&i=0>>wrote:
>
>
> So, this works when I do it manually, but not when I script it.
>
> Is it possible that this change doesn’t take effect immediately? Is
> there any way to tell when it does (besides just waiting until login
> attempts fail)?
>
>
> It should take effect when you commit the transaction in which you
> perform the update...
>
> The active sessions would remain logged in but future attempts to login
> would fail.
>
Anyhow, I’ll figure it out. Thank you! :)
Connection pool sessions are likely remaining connected to the database longer than your application...
Dave
View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David G Johnston <david.g.johnston@gmail.com> writes: > On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] <[hidden email]> wrote: > > So, this works when I do it manually, but not when I script it. > > Is it possible that this change doesnât take effect immediately? Is > there any way to tell when it does (besides just waiting until login > attempts fail)? > > âIt should take effect when you commit the transaction in which you perform the update... > > The active sessions would remain logged in but future attempts to login would fail. Right. Nothing about disabling an account causes existing sessions to close. The OP should do... mangle password and commit; pg_terminate_backend(disabled user); WAiting a few seconds between those steps probably not a bad idea to help avoid a race if any between pw authentication and a session registering in pg_stat_activity. > âDavid J. > > ------------------------------------------ > View this message in context: Re: Temporarily suspend a user account? > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800