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

On 9/13/22 00:49, Tom Lane wrote:
> Bryn Llewellyn<bryn(at)yugabyte(dot)com> 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(at)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