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
