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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

…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.)

You can always choose to limit that function to explicitly granted roles if you wish. And write security definer functions if you desire some different rules… There are only a couple of relevant functions so revoking default privileges and granting them explicitly gives you the same outcome as adding the pg_signal_backend predefined role… I'll agree it is an insecure default…

This sounds like exactly what I want—and what I was asking about. But how do I "limit pg_terminate_backend() to explicitly granted roles?" Out of the box, any role can execute it. So yes… "revoking default privileges and granting them explicitly" is what I want. But how? I'd already tried authorizing as a superuser and doing this:

revoke execute on function pg_terminate_backend(int, bigint) from public;

It ran without error. But even so, a freshly created non-super role was still able to kill sessions that had authorized as the same role. So I assumed that there was something hard-wired about the accessibility of "pg_terminate_backend()".

In other words, where can I read about this and learn how to do what you propose? (Of course I see that with this achieved, "security definer" subprograms would then bring their usual value.)

having a login privilege for the database comes with power and responsibility

I can't accept that having a login privilege will give any session that logs on unlimited power to act. The notions of object ownership, privileges, "security definer" subprograms, and so on support my stance.