How to terminate all DB usage of a PostgreSQL user

Question

How to terminate all DB activities of a particular PostgreSQL user?

I know how to select them: SELECT * FROM pg_stat_activity WHERE usename='foo_user'?

And I found this here: https://stackoverflow.com/a/35319598/633961

What I did is first check what are the running processes by

SELECT * FROM pg_stat_activity WHERE state = 'active';

Find the process you want to kill, then type:

SELECT pg_cancel_backend(<pid of the process>)

If the process cannot be killed, try:

SELECT pg_terminate_backend(<pid of the process>)

But how to apply this to several processes?

Use Case

Sometimes a test in CI takes too long and the system should be destroyed.

Answer 1

Use it with your first select:

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity 
WHERE usename='foo_user'

Answer 2

pg_cancel_backend takes a process id as its argument.
It doesn't care if that comes from you typing it in, or a from a query.

That makes it possible, albeit quite risky, to combine these two into one:

SELECT pg_cancel_backend( pid )
FROM pg_stat_activity 
WHERE usename = 'foo_user'
AND state = 'active' ;

OK, this doesn't give you option to escalate the "cancel" to a "terminate", but it might be enough.

Obviously, inappropriate use of this could cause huge amounts of Trouble. Use with caution.

A better solution might be to not let them into the database in the first place, but that's a little Draconian and rarely a Real World option (no matter how satisfying it is to "slam the door" on a troublemaker).

The best solution would be to find out why the User is doing what they're doing and improve their process/query to "play" better with everything else that your database is doing.

Answer 3

Even though the latest versions of PostgreSQL have a configuration parameter for killing "idle in transaction" connections, here is the way to do it manually for idle in transaction connections older than 10 minutes:

select pg_terminate_backend(pid) 
from pg_stat_activity 
where state = 'idle in transaction' 
and date_part('minute',(now() - xact_start)) > 10;

Answer 4

I use this all the time on my development machines:

SELECT
    pg_terminate_backend ( pg_stat_activity.pid )
FROM
    pg_stat_activity
WHERE
    pg_stat_activity.datname = 'your_database_name'
    AND pid <> pg_backend_pid ( ); -- Don't kill this query.