On 9/13/22 00:49, Tom Lane wrote:
Bryn Llewellyn <bryn@yugabyte.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@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