Re: Temporarily suspend a user account?

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