How to terminate all DB usage of a PostgreSQL user

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()

If the process cannot be killed, try:

SELECT pg_terminate_backend()

But how to apply this to several processes?

Use Case

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

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.

StackExchange Database Administrators Q#250831, answer score: 3