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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

tgl@xxxxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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:

revoke execute on function pg_terminate_backend from public;
select proacl from pg_proc where proname = 'pg_terminate_backend';

       proacl         
-----------------------
{postgres=X/postgres}

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

create user joe;
\c - joe
select pg_terminate_backend(42);

ERROR:  permission denied for function pg_terminate_backend

It very much looks as if what I have described 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.

I'll be delighted to learn what error I've been making and how to avoid it in future. I copied my testcase at the end. I also tried it in an old PG Version 11 that I have in an Ubuntu VM. I got the same outcome there as I do in Version 14.5 on macOS. (I had to make s small change because, back then, the signature was just "pg_terminate_backend(int)"—without the second "bigint" formal argument.)

There must be some-or-other non-standard setting in my environment that results in the behavior that I see and that other's don't. Notice that following this:

-- Self-document what seems to be the default.
grant execute on function pg_terminate_backend(int, bigint) to public;
select proacl from pg_proc where proname = 'pg_terminate_backend';

I see this:

              proacl               
-----------------------------------
 {Bllewell=X/Bllewell,=X/Bllewell}

"Bllewell" owns the "postgres" database, the templates, and all the schemas like "pg_catalog" and "information_schema" that come with a freshly-created database. Then, later, following this:

-- Hardening attempt.
revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from public;
revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from r1;
select proacl from pg_proc where proname = 'pg_terminate_backend';

I see this:

        proacl         
-----------------------
 {Bllewell=X/Bllewell}

It seems to be a strange way to report the fact that *any* superuser inevitably is unstoppable while I have two of these: "Bllewell" and "postgres"—both of which came with the installation.

This anyway annoys me. Is it inevitable on macOS? If not, would it help to remove my present installation without trace and to make a new one from scratch? Notice, though, that my Ubuntu installation has no superuser that matches the OS owner of the installation. There, it's just "postgres".

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

\c postgres postgres
set client_min_messages = warning;
------------------------------------------------------------
-- Setup
drop schema if exists pulic;
drop database if exists play;
drop role if exists r1;

-- Self-document what seems to be the default.
grant execute on function pg_terminate_backend(int, bigint) to public;

-- Check the starting state.

select proacl from pg_proc where proname = 'pg_terminate_backend';

select rolname, rolsuper, rolcanlogin::text
from pg_roles
where rolname !~ '^pg_'
order by rolname;

select datname
from pg_database
where not datistemplate;

select count(*) as "nof. ordinary schemas"
from pg_namespace
where not (
  nspname ~ '^pg_' or
  nspname = 'information_schema');

/*
 RESULTS
 -------

              proacl               

-----------------------------------
 {Bllewell=X/Bllewell,=X/Bllewell}

 rolname  | rolsuper | rolcanlogin 
----------+----------+-------------
 Bllewell | t        | false
 postgres | t        | true

 datname  
----------
 postgres

 nof. ordinary schemas 
-----------------------
                     0
*/;

-- Setup
create database play owner postgres;
revoke all on database play from public;
create role r1 with login password 'p';
grant connect on database play to r1;
------------------------------------------------------------
-- RED SESSION
\c play r1

-- Reports one row.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;
------------------------------------------------------------
-- BLUE SESSION
\c play r1

-- Reports two rows.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;

-- No error
do $body$
declare
  p int not null := 0;
begin
  for p in (
    select pid
    from pg_stat_activity
    where backend_type =  'client backend'
    and   pid          <> pg_backend_pid())
  loop
    perform pg_terminate_backend(p);
  end loop;
end;
$body$;

-- Now reports just one row.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;
------------------------------------------------------------
-- RED SESSION (don't re-connect)

-- Causes FATAL: terminating connection due to administrator command
select 1;
------------------------------------------------------------
\c postgres postgres

-- Hardening attempt.
revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from public;
revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from r1;

— Shows "{Bllewell=X/Bllewell}"

select proacl from pg_proc where proname = 'pg_terminate_backend';
------------------------------------------------------------
-- BLUE SESSION
\c play r1

-- Reports one row.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;
------------------------------------------------------------
-- RED SESSION
\c play r1

-- Reports two rows.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;

-- No error
do $body$
declare
  p int not null := 0;
begin
  for p in (
    select pid
    from pg_stat_activity
    where backend_type =  'client backend'
    and   pid          <> pg_backend_pid())
  loop
    perform pg_terminate_backend(p);
  end loop;
end;
$body$;

-- Now reports just one row.
select datname, usename, pid
from pg_stat_activity
where backend_type = 'client backend'
order by 1, 2, 3;
------------------------------------------------------------
-- BLUE SESSION (don't re-connect)

-- Causes FATAL: terminating connection due to administrator command
select 1;