Re: [GENERAL] Temporarily suspend a user account?

[Jerry Sievers]

David G Johnston <david.g.johns...@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';


>
> 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.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general