0

Can you please tell me how to lock a user in Postgres for temporarily. I have tried different commands but nothing seems to work.

  1. REVOKE connect from john.
  2. alter user john ACCOUNT lock;

please help me. my ph_hba conf file

# IPv4 local & remote connections:

host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
host    all             john             0.0.0.0/0               reject

# IPv6 local connections:

host    all             all             ::1/128                 trust
host    all             john             0.0.0.0/0               reject
user10801913
  • 11
  • 5

1 Answers1

0

REVOKE CONNECT ON DATABASE won't have an effect because CONNECT is granted to PUBLIC by default. You'd first have to REVOKE CONNECT on the database FROM PUBLIC and grant it to the users that need it.

There is no way to lock a user, but you can ALTER ROLE ... VALID UNTIL 'yesterday' to make the password expire.

The easiest way is typically to add a reject entry to pg_hba.conf. Make sure, however, that you add the reject entry above the original entry that allows the user in.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Thanks.I have the added the entry like this in pg_hba.conf but the user is still able to connect. can you please suggest. host all john 0.0.0.0/0 reject – user10801913 Dec 21 '18 at 13:59
  • Did you add it at the top and run `SELECT pg_reload_conf();`? – Laurenz Albe Dec 21 '18 at 14:21
  • Laurenz Albe .yeah i have restarted the postgres database also.my entries are, # IPv4 local host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 host all john 0.0.0.0/0 reject # IPv6 local connections: host all all ::1/128 trust host all john 0.0.0.0/0 reject – user10801913 Dec 21 '18 at 14:24
  • Maybe it is a different database server? You should add more details to your question, like your `pg_hba.conf`. – Laurenz Albe Dec 21 '18 at 14:26
  • I have tried alter role also.This is also not working. ALTER ROLE JOHN with VALID UNTIL 'May 4 12:00:00 2015 +1'; – user10801913 Dec 21 '18 at 14:27
  • You have to [edit the question](https://stackoverflow.com/posts/53872610/edit) and add the information there. It is not readable in a comment. – Laurenz Albe Dec 21 '18 at 14:40
  • Laurenz Albe .the question is edited..please check once. – user10801913 Dec 21 '18 at 14:57
  • You didn't add the `reject` entries on top like I said. – Laurenz Albe Dec 21 '18 at 15:10
  • The entry is good, but **it has to be at the beginning of the file**. You might want to add another entry for IPv6 addresses. – Laurenz Albe Dec 21 '18 at 15:33
  • Yeah..my purpose of this is i want to track this user executed how many queries per day.for example if it exceeds the query limit 100 then i want to disable the user for the day and enable him again for next day.what could be the best approach for this.can you please suggest something. – user10801913 Dec 21 '18 at 15:41
  • That's a totally different question, so you might ask it as such. There is no way to do that though, as far as I know. – Laurenz Albe Dec 21 '18 at 15:43
  • I think that we can disable the password and enable him the very next day using alter role.can we do that .when i try with this ALTER ROLE john with VALID UNTIL '2016-06-22 19:10:25-07'; it is not working. – user10801913 Dec 21 '18 at 15:46