Re: 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 "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