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 to do it.

How to do it…

You can terminate a user's session with the pg_terminate_backend() function included with PostgreSQL. This function takes the PID, or the process ID, of the user's session on the server. This process is known as the backend, and it is a different system process from the program that runs the client.

To find the PID of a user, we can look at the pg_stat_activity view. We can use it in a query, like this:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE ...

There are a couple of things to note if you run this query. If the WHERE clause doesn't match any sessions, then you won't get any output from the query. Similarly, if it matches multiple rows, you will get a fairly useless result, that is, a list of Boolean true values. Unless you are careful enough to exclude your own session from the query, you will disconnect...