Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

gogala.mladen@xxxxxxxxx wrote:

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.

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…

Thanks. Yes, all is clear now. It had never occurred to me as what I think of as “built-in” PG functions (probably a bad term) were like ordinary user-defined functions in that they are subject to the same privilege notions. At the same time, it never occurred to anybody else that I could think this. When this was finally made clear to me, even then it wasn’t emphasized that I had missed a general principle.

I just confirmed that, if it suits me, I can revoke "execute" from "public" on all overloads of the humble length() function. Maybe I should refer to it as "pg_catalog.length()" to emphasize another point that had escaped me.

My excuse is that my thinking is still conditioned by many years of using Oracle Database. I just tried this there as their "SYS" user:

revoke execute on length from public;

It caused the error "procedure, function, package, or package body does not exist"—in other words, "length" and its built-in cousins are so deeply hard-wired that they are outside the privileges domain of discourse.

Anyway… I'm wiser now—at least on this point.