Thread: Is it possible to stop sessions killing eachother when they all authorize as the same role?
Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
"David G. Johnston"
Date:
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Christophe Pettus
Date:
> 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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
"David G. Johnston"
Date:
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
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
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Christophe Pettus
Date:
> 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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
xof@thebuild.com wrote:
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.
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...
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".
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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_backendIt 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';
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=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';
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}
-----------------------
{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".
--------------------------------------------------------------------------------
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
-------
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;
{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;
------------------------------------------------------------
-- 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;
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Christophe Pettus
Date:
> 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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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;
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;
from pg_roles
where rolname !~ '^pg_'
order by rolname;
It produced this:
rolname | rolsuper | rolcanlogin
----------+----------+-------------
Bllewell | true | false
postgres | true | true
r1 | false | true
----------+----------+-------------
Bllewell | true | false
postgres | true | true
r1 | false | true
What are you seeing that I'm failing to?
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Christophe Pettus
Date:
> 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=>
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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
\c postgres postgres
drop user if exists joe;
create user joe;
\c - joe
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;
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Guillaume Lelarge
Date:
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 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$;
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();
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid();
As it is less code, it's quicker to understand what it does.
--
Guillaume.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Jeremy Smith
Date:
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.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Christophe Pettus
Date:
> 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!
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role? >> CASE CLOSED
From
Bryn Llewellyn
Date:
xof@thebuild.com wrote:
/*
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;
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...
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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...
Guilty as charged. Sorry. I was too focuses on what I wanted to achieve: to stop sessions connected as "r1" killing other sessions connected as "r1".
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.
Yes… that's exactly it. I just replied to that effect here:
before I read this from you. Thanks for your interest in this!
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
guillaume@lelarge.info wrote:
This won't answer your question
It has been answered now. See my "case closed" email here:
…but still… I usually really like your scripts, it's nicely written, but this part seems really weird to me:bryn@yugabyte.com wrote:
-- 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$;
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.
Well, yes… I have often been accused of being anally fixated on details—and of verbosity. This is just the old chestnut that a "select" statement shouldn't have side effects. "pg_terminate_backend(p)" ought, by the book, to be a procedure. But I suppose that it dates from the time when PG had only user-defined functions (and no shipped procedures). And "perform" makes a function feel to me to be a bit more like a procedure than just selecting it feels. Others might well disagree…
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Guillaume Lelarge
Date:
Le mer. 14 sept. 2022 à 00:35, Bryn Llewellyn <bryn@yugabyte.com> a écrit :
guillaume@lelarge.info wrote:
This won't answer your questionIt has been answered now. See my "case closed" email here:…but still… I usually really like your scripts, it's nicely written, but this part seems really weird to me:bryn@yugabyte.com wrote:
-- 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$;
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.
Well, yes… I have often been accused of being anally fixated on details—and of verbosity. This is just the old chestnut that a "select" statement shouldn't have side effects.
Oh, OK, looks like a good reason to me. I will probably still do the quick SELECT, but I understand your view on it.
"pg_terminate_backend(p)" ought, by the book, to be a procedure. But I suppose that it dates from the time when PG had only user-defined functions (and no shipped procedures). And "perform" makes a function feel to me to be a bit more like a procedure than just selecting it feels. Others might well disagree…
--
Guillaume.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Karsten Hilbert
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Karsten Hilbert
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Mladen Gogala
Date:
On 9/12/22 18:51, Bryn Llewellyn 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.) 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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Mladen Gogala
Date:
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_backendIt 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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Bryn Llewellyn
Date:
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-SIGNALAnd 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…
Thanks. Yes, all is clear now. It had never occurred to me as what I think of as “built-in” PG functions (probably a bad term) were like ordinary user-defined functions in that they are subject to the same privilege notions. At the same time, it never occurred to anybody else that I could think this. When this was finally made clear to me, even then it wasn’t emphasized that I had missed a general principle.
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.
My excuse is that my thinking is still conditioned by many years of using Oracle Database. I just tried this there as their "SYS" user:
revoke execute on length from public;
It caused the error "procedure, function, package, or package body does not exist"—in other words, "length" and its built-in cousins are so deeply hard-wired that they are outside the privileges domain of discourse.
Anyway… I'm wiser now—at least on this point.
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Tom Lane
Date:
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
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
From
Mladen Gogala
Date:
On 9/14/22 23:27, Tom Lane wrote:
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