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

On Mon, Sep 12, 2022 at 3:51 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

I'll use "kill" here a shorthand for using the "pg_terminate_backend()"
built-in function. I read about it in the "Server Signaling Functions"
section of the enclosing "System Administration Functions" section of the
current doc:

www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

And I tried a few tests. All of the outcomes were just as the doc promised.

I'm troubled by the notion that (as it seems) one session that authorizes
as the role "r1" can easily list all other concurrent sessions that are
also authorized as "r1"—and kill them all without restriction. (The doc
does say "Use of these functions is usually restricted to superusers, with
noted exceptions." So I s'pose that I'm talking about one of these noted
exceptions.)

You can always choose to limit that function to explicitly granted roles if
you wish. And write security definer functions if you desire some
different rules.

The implication is that every client program must follow every database
call with defensive code to detect error "57P01" and programmatically
re-try.

You gotta learn to accept that life involves risk. OTOH, resilient code
should already be doing this kind of stuff since this kind of interruption
in more probable scenarios than this. Either way, this doesn't seem to
meaningfully change the risk profile. Those who feel differently have
options.

(Maybe some drivers can do this automatically. But I haven't found out if
whatever psql uses can do this. Nor have I found out how to write re-try
code in psql.)

Does anybody else find all this as troubling as I do?

No, having a login privilege for the database comes with power and
responsibility. In the continuum between usability and locked-down this
seems reasonable.

There are only a couple of relevant functions so revoking default
privileges and granting them explicitly gives you the same outcome as
adding the pg_signal_backend predefined role.

I'll agree it is an insecure default, though, and I suspect most setups
would rather rely on user roles holding pg_signal_backend to deal with any
misbehaving process (which leans me toward not wanting to introduce yet
another predefined role). Likely combined with pg_read_all_stats so
viewing pg_stat_activity gives them a complete picture. It seems
reasonable, though, to remove the default function execute grant from
PUBLIC for these; or if that doesn't work consider a documentation patch if
you feel the typical DBA would remain under-informed even after reviewing
the documentation (though without a dedicate section discussing such best
practices I suspect such material would go unread by those who would most
need it).

David J.

On Sep 12, 2022, at 15:51, Bryn Llewellyn <bryn@yugabyte.com> wrote:
The implication is that every client program must follow every database call with defensive code to detect error "57P01" and programmatically re-try.

That situation exists even without the ability for a role to kill other sessions authorized to the same role. A superuser (or role granted pg_signal_backend) could have terminated it, the connection could have broken due to a network failure (which caused the backend to roll back and terminate), or the server could have crashed.

Pragmatically, the only real additional risk cases here are:

(a) An intrusion using that role,
(b) A client program that for some reason can issue a legitimate pg_terminate_backend() call, but that has a bug that causes it to use it inappropriately.

In the case of (a), pg_terminate_backend() is the least of your worries, and I have a hard time seeing (b) as a real-world risk that requires a new PostgreSQL feature to defending again.

Also pragmatically, it would be a *very* significant behavior shift if roles could not by default signal other sessions authorized to the same role, so it would be unwise to introduce that feature and have it be revoked from non-superusers by default. And, if it's not revoked by default, it's not going be very widely used except for ultra-locked-down environments. I don't think it would hurt anything to introduce it, but I'm not sure the utility is there.

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

…I'm troubled by the notion that (as it seems) one session that authorizes as the role "r1" can easily list all other concurrent sessions that are also authorized as "r1"—and kill them all without restriction. (The doc does say "Use of these functions is usually restricted to superusers, with noted exceptions." So I s'pose that I'm talking about one of these noted exceptions.)

You can always choose to limit that function to explicitly granted roles if you wish. And write security definer functions if you desire some different rules… There are only a couple of relevant functions so revoking default privileges and granting them explicitly gives you the same outcome as adding the pg_signal_backend predefined role… I'll agree it is an insecure default…

This sounds like exactly what I want—and what I was asking about. But how do I "limit pg_terminate_backend() to explicitly granted roles?" Out of the box, any role can execute it. So yes… "revoking default privileges and granting them explicitly" is what I want. But how? I'd already tried authorizing as a superuser and doing this:

revoke execute on function pg_terminate_backend(int, bigint) from public;

It ran without error. But even so, a freshly created non-super role was still able to kill sessions that had authorized as the same role. So I assumed that there was something hard-wired about the accessibility of "pg_terminate_backend()".

In other words, where can I read about this and learn how to do what you propose? (Of course I see that with this achieved, "security definer" subprograms would then bring their usual value.)

having a login privilege for the database comes with power and responsibility

I can't accept that having a login privilege will give any session that logs on unlimited power to act. The notions of object ownership, privileges, "security definer" subprograms, and so on support my stance.

On Mon, Sep 12, 2022 at 6:08 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

*revoke execute on function pg_terminate_backend(int, bigint) from public;*

I just did this very thing in v16 (head-ish) and it worked as expected,
preventing the non-superuser role from executing the function:

Session 1 - superuser
postgres=# revoke execute on function pg_terminate_backend from public;
REVOKE

Session 2 - non-superuser (normalrole with direct login)
postgres=> select pid, usename, query, state from pg_stat_activity;
pid | usename | query
| state
--------+------------+----------------------------------------------------------+--------
466663 | | <insufficient privilege>
|
466664 | vagrant | <insufficient privilege>
|
470387 | normalrole | select pid, usename, query, state from
pg_stat_activity; | active
470391 | normalrole | select pg_sleep(1000);
| active
470412 | vagrant | <insufficient privilege>
|
466660 | | <insufficient privilege>
|
466659 | | <insufficient privilege>
|
466662 | | <insufficient privilege>
|
(8 rows)

postgres=> select pg_terminate_backend(470391);
ERROR: permission denied for function pg_terminate_backend

David J.

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

revoke execute on function pg_terminate_backend(int, bigint) from public;

I just did this very thing in v16 (head-ish) and it worked as expected, preventing the non-superuser role from executing the function:

Session 1 - superuser
postgres=# revoke execute on function pg_terminate_backend from public;
REVOKE

Session 2 - non-superuser (normalrole with direct login)
postgres=> select pid, usename, query, state from pg_stat_activity;
pid | usename | query | state
--------+------------+----------------------------------------------------------+--------
466663 | | <insufficient privilege> |
466664 | vagrant | <insufficient privilege> |
470387 | normalrole | select pid, usename, query, state from pg_stat_activity; | active
470391 | normalrole | select pg_sleep(1000); | active
470412 | vagrant | <insufficient privilege> |
466660 | | <insufficient privilege> |
466659 | | <insufficient privilege> |
466662 | | <insufficient privilege> |
(8 rows)

postgres=> select pg_terminate_backend(470391);
ERROR: permission denied for function pg_terminate_backend

Version 16? Thus might be the clue, then. Here's the result of "select version()" with my macOS PG :

PostgreSQL 14.5 (Homebrew) on x86_64-apple-darwin20.6.0, compiled by Apple ...

The current PG doc says "PostgreSQL 14.5 Documentation". And it does seem to be a reasonable policy for me, an ordinary end user, to arrange always to use the current non-Beta software as the doc advertises it to be. I repeated my test to be doubly sure. 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):

revoke execute on function pg_terminate_backend from public;
revoke execute on function pg_terminate_backend from u1;

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!

Can you (or anybody) please confirm this? And if this is confirmed, then obviously I'll shut up just wait patiently until Version 16 is supported version.

On Sep 12, 2022, at 20:44, Bryn Llewellyn <bryn@yugabyte.com> wrote:
Version 16? Thus might be the clue, then.

It behaves as David describes on:

PostgreSQL 14.5 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit

xof@thebuild.com wrote:

bryn@yugabyte.com wrote:

The implication is that every client program must follow every database call with defensive code to detect error "57P01" and programmatically re-try.

That situation exists even without the ability for a role to kill other sessions authorized to the same role. A superuser (or role granted pg_signal_backend) could have terminated it, the connection could have broken due to a network failure (which caused the backend to roll back and terminate), or the server could have crashed.

Pragmatically, the only real additional risk cases here are...

Thanks, Christophe. David Johnston said something similar. I'll happily concede that my thinking about the tedium of writing client code to detect a disconnected backend and then to retry was blinkered. Yes, of course that's a general risk—and so that code is needed anyway.

I can't agree with you about risks and probability, though. The general literature of security threats often makes the point that disgruntled employees (current or very recently former) who know the code in question do sometimes wreak havoc—sometimes just for sport. The general risk that the unrestricted ability to use "pg_terminate_backend()" to kill sessions started by one's peers is ordinary denial of service—notwithstanding the possibility for automatic re-connect. It still steals time and resources.

Anyway... David (separately) just said that "revoke execute on function pg_terminate_backend(int, bigint) from public" has the effect that reading the statement leads you to expect—in version 16. But my tests show that it does *not* have this effect in version 14.5.

This indicates that the regime that I complained about was deemed to be a bug—and that I can simply say "case closed".

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

Bryn Llewellyn <bryn@yugabyte.com> writes:

I can't agree with you about risks and probability, though. The general literature of security threats often makes the point that disgruntled employees (current or very recently former) who know the code in question do sometimes wreak havoc—sometimes just for sport. The general risk that the unrestricted ability to use "pg_terminate_backend()" to kill sessions started by one's peers is ordinary denial of service—notwithstanding the possibility for automatic re-connect. It still steals time and resources.

I'm not sure that this argument has much to recommend it. If you
are able to issue arbitrary SQL commands, you can cause effective
denials-of-service in many ways. A couple of random examples:

* launch a query that will eat indefinite amounts of CPU and/or disk.

* "LOCK TABLE some-important-table" and leave for lunch.

* leave a transaction open and go on vacation.

Yeah, you can progressively lock down a system against all of these
sorts of hazards, but it will get progressively less useful for
anyone who wants to do actual work on it.

In the end, the default settings have to represent a compromise
that we think is generally useful. You're welcome to lock your
own installation down more than that, but it doesn't follow that
everybody else should too.

regards, tom lane

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com 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;

On Sep 13, 2022, at 11:23, Bryn Llewellyn <bryn@yugabyte.com> wrote:

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.

From the documentation:

superuser status
A database superuser bypasses all permission checks, except the right to log in.

If you do the test with a non-superuser, you'll get the results you expect. This isn't related to MacOS.

xof@thebuild.com wrote:

bryn@yugabyte.com wrote:

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.

From the documentation:

superuser status: A database superuser bypasses all permission checks, except the right to log in.

If you do the test with a non-superuser, you'll get the results you expect. This isn't related to MacOS.

I didn't do the test with a superuser. I did it with a freshly-created role called "r1" created thus:

create role r1 with login password 'p';
grant connect on database play to r1;

The code that I copeid in my previous mail showed this. I double-checked thus:

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

It produced this:

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

What are you seeing that I'm failing to?

On Sep 13, 2022, at 11:39, Bryn Llewellyn <bryn@yugabyte.com> wrote:

What are you seeing that I'm failing to?

It works correctly for me, on MacOS:

swift-239:~ xof$ psql
psql (14.5)
Type "help" for help.

xof=# create user r1;
CREATE ROLE
xof=# revoke execute on function pg_terminate_backend from r1;
REVOKE
xof=#
\q
swift-239:~ xof$ psql -U r1 xof
psql (14.5)
Type "help" for help.

xof=> select pg_terminate_backend(123);
ERROR: permission denied for function pg_terminate_backend
xof=>

xof@thebuild.com wrote:

bryn@yugabyte.com wrote:

What are you seeing that I'm failing to?

It works correctly for me, on MacOS:

create user r1;
revoke execute on function pg_terminate_backend from r1;

(reconnect as r1)

select pg_terminate_backend(123);

ERROR: permission denied for function pg_terminate_backend

Yes—this is what you all say. I suspect some-or-other setting that I’m unaware of. I tried (part of) Tom's test, thus:

\c postgres postgres
drop user if exists joe;
create user joe;
\c - joe

At first, the attempt to connect as "joe" failed for me because I had hardened my "postgres" database thus:

revoke all on database postgres from public;
alter database postgres connection limit = 0;

I say this only to emphasize that there are always things that are critical that are elided in a testcase that tries to be minimal.

So it seems that there's something critical about my env that I'm failing to tell you all. But what can it be?

I just recruited a colleague to try Tom's minimal test. He saw what I did. That "joe" *is* able to invoke "pg_terminate_backend()" even after revoking execute on it from public. So he's doing the same pilot error as me.

Hi,

This won't answer your question but still... I usually really like your
scripts, it's nicely written, but this part seems really weird to me:

Le mar. 13 sept. 2022 à 20:23, Bryn Llewellyn <bryn@yugabyte.com> a écrit :

*-- No errordo $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$;*

While your script works great, I'm wondering why you don't write it this
way:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid();

As it is less code, it's quicker to understand what it does.

--
Guillaume.

I say this only to emphasize that there are always things that are critical

that are elided in a testcase that tries to be minimal.

So it seems that there's something critical about my env that I'm failing
to tell you all. But what can it be?

Removing permissions also works for me. In my case, I created a brand new
PG14.5 cluster in docker and ran Tom's test case.

Your test code seems quite complex to test this simple case, but looking
through it, it appears that you revoked permissions to pg_terminate_backend
in the postgres database, but then connected as r1 to the play database,
where the permissions weren't revoked. You'll have to revoke the
permissions in all databases or change it in template1 before creating the
new database.

Christophe Pettus <xof@thebuild.com> writes:

It works correctly for me, on MacOS:

swift-239:~ xof$ psql
psql (14.5)
Type "help" for help.

xof=# create user r1;
CREATE ROLE
xof=# revoke execute on function pg_terminate_backend from r1;
REVOKE
xof=#
\q
swift-239:~ xof$ psql -U r1 xof
psql (14.5)
Type "help" for help.

xof=> select pg_terminate_backend(123);
ERROR: permission denied for function pg_terminate_backend
xof=>

Hmm ... that should actually *not* have worked. pg_terminate_backend
has the default ACL for functions, namely GRANT EXECUTE TO PUBLIC.
If you revoke from a specific user, nothing will change because
the PUBLIC grant is still there and they can still use it.
Perhaps you'd already revoked from public in this database?

(I recall that somewhere we have some code that warns about no-op
grants. I wonder if issuing a warning for no-op revokes would be
helpful.)

Jeremy's nearby theory that the REVOKE was done in a different
database seems like a pretty good explanation of Bryn's issue.

regards, tom lane

On Sep 13, 2022, at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Perhaps you'd already revoked from public in this database?

Very possible!

xof@thebuild.com wrote:

tgl@sss.pgh.pa.us wrote:

Perhaps you'd already revoked from public in this database?

Very possible!

You all forgot to tell me to put this aside and go out for a walk. I just told myself to do that. And it struck me then. Tom just said it here—albeit parenthetically with his "in this database".

I had suppressed what I did understand well enough: that the whole suite of infrastructure objects—the catalog tables and views and the built-in functions (or at least as covers for whatever lies beneath them in C) is installed afresh in each newly-created database.

So when I did "revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from public", my current database was "postgres". But when I invoked "pg_catalog.pg_terminate_backend()", my current database was "play".

So with a trivial typing effort to re-order things, it all works fine now:

/*
Good for the lifetime of the "psql" CLI session.
*/;
\set ECHO None
\set QUIET On
\set VERBOSITY Default
--------------------------------------------------
/*
Global actions for the entire cluster.
*/;
\c postgres postgres
set client_min_messages = warning;
drop database if exists play;
drop role if exists r1;
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;
--------------------------------------------------
/*
Local actions - limited in scope to the "play" database.
*/;
\c play postgres
set client_min_messages = warning;
revoke execute on function pg_terminate_backend from public;

\c play r1
set client_min_messages = warning;
select pg_terminate_backend(42);

The final "select" now gets the "permission denied for function pg_terminate_backend" error that you all have been seeing all along.

Thanks to all for keeping me honest here. I feel rather embarrassed—but not enough to hold me back from asking the next stupid question...