Deactivate postgres user account

Question

Is there a way to deactivate a postgres user account via an SQL statement?

I want to prevent a user from using their database, but without deleting the user or their databases.

Answer 1

You might also consider ALTER USER someone WITH NOLOGIN; which is probably the right solution if you have multiple databases in the same back-end and want to block the user entirely.

To lock an account:

# Lock an account
ALTER USER someone WITH NOLOGIN;

To unlock an account:

# Un-lock an account
ALTER USER someone WITH LOGIN;

Check account is locked or not:

select rolcanlogin from pg_roles where rolname='database_user';

OUTPUT:
rolcanlogin
-------------
 f
(1 row)

f = false # Can not login
t = true # Can login

Answer 2

Take a look at the REVOKE command.

In order for a user to connect to a database he has been granted the CONNECT privilege. Just REVOKE this privilege and he won't be able to use this database.