Re: Temporarily suspend a user account?

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