Is it possible to stop sessions killing eachother when they all authorize as the same role?
От | Bryn Llewellyn |
---|---|
Тема | Is it possible to stop sessions killing eachother when they all authorize as the same role? |
Дата | |
Msg-id | 10F360BB-3149-45E6-BFFE-10B9AE31F1A6@yugabyte.com обсуждение исходный текст |
Ответы |
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role? Re: Is it possible to stop sessions killing eachother when they all authorize as the same role? |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: