Обсуждение: Is it possible to stop sessions killing eachother when they all authorize as the same role?

I'll use "kill" here a shorthand for using the "pg_terminate_backend()" built-in function. I read about it in the
"ServerSignaling 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
concurrentsessions that are also authorized as "r1"—and kill them all without restriction. (The doc does say "Use of
thesefunctions is usually restricted to superusers, with noted exceptions." So I s'pose that I'm talking about one of
thesenoted exceptions.) 

It's common to design a three tier app so that the middle tier always authorizes as just a single role—say,
"client"—andwhere the operations that "client" can perform are limited as the overall design specifies. The maximal
exampleof this paradigm defines the API to the database functionality by granting "execute" to just the designed set of
subprograms.Here, the subprograms and the tables that they access all have owners other than "client". (The
authorizationof external principals, and ow their identity is mapped to a unique key for use within that database, is
outsidethe scope of what I write about here.) 

It seems far-fetched to think that the requirements spec for every such design would deliberately specify:

— Must be possible for any "client" session to kill all other concurrent "client" sessions.

Yet the paradigm is that the database API expresses exactly and only what the design says that it should. Ergo, the
paradigmis, in general, unimplementable. 

I appreciate that (while the privileges that "client" has are unchanged) a just-killed session can easily reconnect by
tryingwhat they had just tried again. But not before suffering the fatal "57P01: terminating connection due to
administratorcommand" error. 

The implication is that every client program must follow every database call with defensive code to detect error
"57P01"and programmatically re-try. (Maybe some drivers can do this automatically. But I haven't found out if whatever
psqluses 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? And, if so, might a remedy be possible? Maybe something like
this:

— Define a new privilege as a cousin to "pg_signal_backend". I'll call it "pg_signal_backend_for_self_role" here. This
wouldgovern the possibility that a session can kill another session that authorized as the same role as itself. 

— Document the fact that "pg_signal_backend_for_self_role" is implicitly granted to a newly-created role (just as it's
documentedthat "execute… to public" is implicitly granted to a newly created subprogram). 

— Allow "revoke pg_signal_backend_for_self_role from…"—by all means with extra rules like only a superuser can do this.

> 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
networkfailure (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
causesit 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-worldrisk 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
authorizedto the same role, so it would be unwise to introduce that feature and have it be revoked from non-superusers
bydefault.  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…

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

> 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

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...

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
version11) 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
thepoint that disgruntled employees (current or very recently former) who know the code in question do sometimes wreak
havoc—sometimesjust for sport. The general risk that the unrestricted ability to use "pg_terminate_backend()" to kill
sessionsstarted by one's peers is ordinary denial of service—notwithstanding the possibility for automatic re-connect.
Itstill 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.

> 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'sdon'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.

> 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

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!

tgl@sss.pgh.pa.us wrote:

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

Very possible!

jeremy@musicsmith.net wrote:

bryn@yugabyte.com wrote:

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...

Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane:

> (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.)

Surely, in the light of security a no-op revoke is
potentially more dangerous than a no-op grant.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Am Wed, Sep 14, 2022 at 10:10:30AM +0200 schrieb Karsten Hilbert:

> Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane:
>
> > (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.)
>
> Surely, in the light of security a no-op revoke is
> potentially more dangerous than a no-op grant.

In the sense where no-op means "despite being revoked it is
still granted by another grant" rather than "the revoke is a
no-op because it is already revoked", that is.

(although the latter can be used to inform on the first if
the latter extends to all "levels" of revokage ... :-)

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

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.)

It's common to design a three tier app so that the middle tier always authorizes as just a single role—say, "client"—and where the operations that "client" can perform are limited as the overall design specifies. The maximal example of this paradigm defines the API to the database functionality by granting "execute" to just the designed set of subprograms. Here, the subprograms and the tables that they access all have owners other than "client". (The authorization of external principals, and ow their identity is mapped to a unique key for use within that database, is outside the scope of what I write about here.)

It seems far-fetched to think that the requirements spec for every such design would deliberately specify:

— Must be possible for any "client" session to kill all other concurrent "client" sessions.

Yet the paradigm is that the database API expresses exactly and only what the design says that it should. Ergo, the paradigm is, in general, unimplementable.

I appreciate that (while the privileges that "client" has are unchanged) a just-killed session can easily reconnect by trying what they had just tried again. But not before suffering the fatal "57P01: terminating connection due to administrator command" error.

The implication is that every client program must follow every database call with defensive code to detect error "57P01" and programmatically re-try. (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? And, if so, might a remedy be possible? Maybe something like this:

— Define a new privilege as a cousin to "pg_signal_backend". I'll call it "pg_signal_backend_for_self_role" here. This would govern the possibility that a session can kill another session that authorized as the same role as itself.

— Document the fact that "pg_signal_backend_for_self_role" is implicitly granted to a newly-created role (just as it's documented that "execute… to public" is implicitly granted to a newly created subprogram).

— Allow "revoke pg_signal_backend_for_self_role from…"—by all means with extra rules like only a superuser can do this.

Bryn, you can revoke execute on pg_terminate_backend from public and that will, by extension, revoke it from all users who do not have DBA privilege or have not been explicitly granted the "execute" privilege on pg_terminate_backend. This doesn't look like a big problem because applications usually don't contain code for killing other user's sessions. I am not sure that GTA is running on top of Postgres database.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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)

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

Mladen Gogala <gogala.mladen@gmail.com> writes:
> Tom, I did the same thing on 14.5, and it behaves as Bryn alleges:

Looks to me like you made the same mistake as Bryn.  You
revoked the permission in the postgres database:

> postgres=# select proacl from pg_proc where proname = 
  ^^^^^^^^
> 'pg_terminate_backend';
>          proacl
> -----------------------
>   {postgres=X/postgres}
> (1 row)

> mgogala@umajor ~]$ psql -U scott
> Password for user scott: psql (14.5) 
> Type "help" for help.
> scott=> select pid from pg_stat_activity where 
  ^^^^^

... and here you're testing the permission in the "scott" database.

            regards, tom lane

Mladen Gogala <gogala.mladen@gmail.com> writes:
> ... This doesn't look like a big problem because
> applications usually don't contain code for killing other user's
> sessions. I am not sure that GTA is running on top of Postgres database.

Yeah, I meant to comment on that further but forgot.  I don't
particularly buy the premise that it's useful to prohibit a session
belonging to user X from killing another session belonging to user X.
In the end, the main point of a database is to store your data.
Therefore, if you don't trust another session that is running as
your userID, you have already lost.  That session can drop your
tables, or corrupt the data in those tables to an arbitrary extent,
and the SQL permissions system will not squawk even feebly.  Under
any reasonable understanding of the goals of a DB, those consequences
are far worse than killing a session.  So if you're not happy with
this hazard, you should not be accepting the idea that actors you
don't trust are allowed to submit queries under the same userID
as you.  And if you're using a client-side software stack that
forces that situation on you, it's time to look for another one.

Or in other words, I flatly reject the claim that this:

>>> It's common to design a three tier app so that the middle tier always authorizes as just a single role—say,
"client"—andwhere the operations that "client" can perform are limited as the overall design specifies. 

is in any way sane or secure.  There is not very much that the
database server can do to clean up after insecure client-side stacks.

            regards, tom lane

gogala.mladen@gmail.com wrote:

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.

Bryn, you can revoke execute on pg_terminate_backend from public and that will, by extension, revoke it from all users who do not have DBA privilege or have not been explicitly granted the "execute" privilege on pg_terminate_backend. This doesn't look like a big problem because applications usually don't contain code for killing other user's sessions…

Bryn Llewellyn <bryn@yugabyte.com> writes:
> I just confirmed that, if it suits me, I can revoke "execute" from "public" on all overloads of the humble length()
function.Maybe I should refer to it as "pg_catalog.length()" to emphasize another point that had escaped me. 

Yup.  For even more fun, try revoking privileges on a function that
underlies an operator.

regression=# revoke execute on function int4pl from public;
REVOKE
regression=# select 2+2;  -- still works, for a superuser
 ?column?
----------
        4
(1 row)

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> select 2+2;  -- not so much for anybody else
ERROR:  permission denied for function int4pl

            regards, tom lane

Looks to me like you made the same mistake as Bryn.  You
revoked the permission in the postgres database:

You are right. When I do it correctly, "revoke execute" works. Thanks for taking time to show me the errors of my wicked ways.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com