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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Sep 12, 2022 at 6:08 PM Bryn Llewellyn <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

David J.