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.