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

Mladen Gogala <gogala.mladen@xxxxxxxxx> · Wed, 14 Sep 2022 22:27:04 -0400

    On 9/13/22 00:49, Tom Lane wrote:

      Bryn Llewellyn <bryn@xxxxxxxxxxxx> writes:

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

      Really?

I did this in 14.5:

regression=# revoke execute on function pg_terminate_backend from public;
REVOKE
regression=# select proacl from pg_proc where proname = 'pg_terminate_backend';
        proacl         
-----------------------
 {postgres=X/postgres}
(1 row)

(as expected, the superuser's own execute permission is all that remains)

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> select pg_terminate_backend(42);
ERROR:  permission denied for function pg_terminate_backend

        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!

      No, it very much looks like pilot error.  But you've not shown
us exactly what your test consisted of, so it's hard to say just
where it went off the rails.

			regards, tom lane

    Tom, I did the same thing on 14.5, and it behaves as Bryn
      alleges: 

    postgres=# select proacl from
      pg_proc where proname = 'pg_terminate_backend';

              proacl         

      -----------------------

       {postgres=X/postgres}

      (1 row)

    So, the only user who should be able
        to execute pg_terminate_backend is "postgres". Let's try with
        user "scott".

    mgogala@umajor ~]$ psql -U scott
Password for user scott: 
psql (14.5)
Type "help" for help.

scott=> select pid from pg_stat_activity where usename='scott';
 pid 
-----
  66
  79
(2 rows)

scott=> select pg_terminate_backend(66);
 pg_terminate_backend 
----------------------
 t
(1 row)

    User scott has no special privileges:

    postgres=# select
      usesuper,usecreatedb,usebypassrls from pg_user where
      usename='scott';

      usesuper | usecreatedb | usebypassrls 

      ----------+-------------+--------------

      f | f | f

      (1 row)

    Yet, it is still able to execute the function in question. My
      version is the following:
    scott=> select version();

        version                        

--------------------------------------------------------------------------------

        -------------------------

         PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
        8.5.0 20210514 (R

        ed Hat 8.5.0-10), 64-bit

        (1 row)

    Regards

    -- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com