Database Reference
In-Depth Information
To let the user connect again, run the following:
pguser=# alter user bob login;
ALTER ROLE
How it works...
This sets a flag in the system catalog telling PostgreSQL not to let the user to log in. It does
not kick out already connected users.
Limiting number of concurrent connections by a user
The same result can be achieved by setting
connection
limit
for that user to
0
:
pguser=# alter user bob connection limit 0;
ALTER ROLE
To allow bob 10 concurrent connections, run the following:
pguser=# alter user bob connection limit 10;
ALTER ROLE
To allow an unlimited number of connections by this user, run the following:
pguser=# alter user bob connection limit -1;
ALTER ROLE
Forcing NOLOGIN users to disconnect
In order to make sure that all users whose login privilege has been revoked are disconnected
right away, run the following SQL statement as a superuser:
SELECT pg_terminate_backend(procpid)
FROM from pg_stat_activity a
JOIN pg_roles r ON a.usename = r.rolname AND not rolcanlogin;
On older versions of postgresql, where
pg_terminate_backend()
function does not
exist, you can get the same effect from shell by running the following as user postgres on
the database server:
postgres@hvost:~$ psql -t -c "\
select 'kill ' || procpid from pg_stat_activity a \
join pg_roles r on a.usename = r.rolname and not rolcanlogin;"\
| bash
This incantation constructs proper
kill
commands from a query, and then feeds them
to the shell for execution.
