Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

revoke execute on function pg_terminate_backend(int, bigint) from public;

I just did this very thing in v16 (head-ish) and it worked as expected, preventing the non-superuser role from executing the function:

Session 1 - superuser
postgres=# revoke execute on function pg_terminate_backend from public;
REVOKE

Session 2 - non-superuser (normalrole with direct login)
postgres=> select pid, usename, query, state from pg_stat_activity;
  pid   |  usename   |                          query                           | state
--------+------------+----------------------------------------------------------+--------
 466663 |            | <insufficient privilege>                                 |
 466664 | vagrant    | <insufficient privilege>                                 |
 470387 | normalrole | select pid, usename, query, state from pg_stat_activity; | active
 470391 | normalrole | select pg_sleep(1000);                                   | active
 470412 | vagrant    | <insufficient privilege>                                 |
 466660 |            | <insufficient privilege>                                 |
 466659 |            | <insufficient privilege>                                 |
 466662 |            | <insufficient privilege>                                 |
(8 rows)

postgres=> select pg_terminate_backend(470391);
ERROR:  permission denied for function pg_terminate_backend

Version 16? Thus might be the clue, then. Here's the result of "select version()" with my macOS PG :

PostgreSQL 14.5 (Homebrew) on x86_64-apple-darwin20.6.0, compiled by Apple ...

The current PG doc says "PostgreSQL 14.5 Documentation". And it does seem to be a reasonable policy for me, an ordinary end user, to arrange always to use the current non-Beta software as the doc advertises it to be. I repeated my test to be doubly sure. My  non-superuser normalrole with direct login, "u1", is *still* able to invoke pg_terminate_backend() and kill other "u1" sessions—even after this (as a super-user):

revoke execute on function pg_terminate_backend from public;

revoke execute on function pg_terminate_backend from u1;

It very much looks as if what I have describe was deemed to be a bug (after that behavior had survived from at least version 11) and that it's now been fixed!

Can you (or anybody) please confirm this? And if this is confirmed, then obviously I'll shut up just wait patiently until Version 16 is supported version.