Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux