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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

xof@xxxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

The implication is that every client program must follow every database call with defensive code to detect error "57P01" and programmatically re-try.

That situation exists even without the ability for a role to kill other sessions authorized to the same role.  A superuser (or role granted pg_signal_backend) could have terminated it, the connection could have broken due to a network failure (which caused the backend to roll back and terminate), or the server could have crashed.

Pragmatically, the only real additional risk cases here are...

Thanks, Christophe. David Johnston said something similar. I'll happily concede that my thinking about the tedium of writing client code to detect a disconnected backend and then to retry was blinkered. Yes, of course that's a general risk—and so that code is needed anyway.

I can't agree with you about risks and probability, though. The general literature of security threats often makes the point that disgruntled employees (current or very recently former) who know the code in question do sometimes wreak havoc—sometimes just for sport. The general risk that the unrestricted ability to use "pg_terminate_backend()" to kill sessions started by one's peers is ordinary denial of service—notwithstanding the possibility for automatic re-connect. It still steals time and resources.

Anyway... David (separately) just said that "revoke execute on function pg_terminate_backend(int, bigint) from public" has the effect that reading the statement leads you to expect—in version 16. But my tests show that it does *not* have this effect in version 14.5.

This indicates that the regime that I complained about was deemed to be a bug—and that I can simply say "case closed".