On Mon, Sep 12, 2022 at 3:51 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.
