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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I would prefer not to revoke login privileges since that will break things
that mine pg_users and pg_shadow.
Melvin Davidson-5 wrotePossibly,
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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.
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.
--
View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836990.html
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 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. [01]
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] <
ml-node+s1045698n5836992h76@n5.nabble.com> 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: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836994.html
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 pastThis 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.consulting@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
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 pastThis 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';
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] <
ml-node+s1045698n5837006h10@n5.nabble.com> 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: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5837007.html
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 6, 2015 at 3:55 PM, Felipe Gasper [via PostgreSQL] <
ml-node+s1045698n5837008h72@n5.nabble.com> 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: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5837009.html
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general