Thread: Temporarily suspend a user account?

Temporarily suspend a user account?

From
Felipe Gasper
Date:
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


Re: Temporarily suspend a user account?

From
Melvin Davidson
Date:
Possibly,
To disble:
ALTER USER name RENAME TO xname;
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 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.

Re: Temporarily suspend a user account?

From
David G Johnston
Date:
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.


Re: Temporarily suspend a user account?

From
David G Johnston
Date:
> 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.


Re: Temporarily suspend a user account?

From
Michael Nolan
Date:
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:
ALTER USER name RENAME TO xname;
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 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.


Re: Temporarily suspend a user account?

From
David G Johnston
Date:
On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] <[hidden email]> 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.
 



View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Temporarily suspend a user account?

From
Jerry Sievers
Date:
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


Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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


Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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


Re: Temporarily suspend a user account?

From
David G Johnston
Date:
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.

David J.
 


View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Temporarily suspend a user account?

From
Michael Nolan
Date:
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


Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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


Re: Temporarily suspend a user account?

From
Jerry Sievers
Date:
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


Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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



Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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




Re: Temporarily suspend a user account?

From
David G Johnston
Date:
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.

​David J.


View this message in context: Re: Temporarily suspend a user account?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Temporarily suspend a user account?

From
Felipe Gasper
Date:
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



Re: Temporarily suspend a user account?

From
David G Johnston
Date:
On Fri, Feb 6, 2015 at 3:55 PM, Felipe Gasper [via PostgreSQL] <[hidden email]> wrote:
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! :)

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.

Re: Temporarily suspend a user account?

From
Jerry Sievers
Date:
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