Database Reference
In-Depth Information
Pushing users off the system
Sometimes we may need to remove groups of users from the database server for various
operational reasons. Here's how.
How to do it...
You can terminate a user's session with the
pg_terminate_backend()
function included
with PostgreSQL. That function takes the
pid
, or the process ID, of the user's session on the
server. This is known as the backend, and is a different system process from the program that
runs the client.
To find out the
pid
of a user, we can look at the view
pg_stat_activity
. We can use that
in a query like the following:
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE ...
There's a couple of things to note if you run that query, which are as follows:
F
If the
WHERE
clause matches no sessions, then you won't get anything back from the
query. Similarly, if it matches multiple rows, you will also get a fairly useless result.
F
If you are not careful enough to include your own session in the query, then you will
disconnect yourself! What's even funnier is that you'll disconnect yourself halfway
through disconnecting the other users, as the query will run
pg_terminate_
backend()
in the order that sessions are returned from the outer query.
So as a more useful query, I suggest a safer query that gives a useful response in all cases,
which is as follows:
postgres=# SELECT count(pg_terminate_backend(procpid))
FROM pg_stat_activity
WHERE usename NOT IN
(SELECT usename
FROM pg_user
WHERE usesuper);
count
-------
1
assuming that superusers are performing administrative tasks.
