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

On Mon, Sep 12, 2022 at 3:51 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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.
>
> 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.

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

You gotta learn to accept that life involves risk. OTOH, resilient code
should already be doing this kind of stuff since this kind of interruption
in more probable scenarios than this. Either way, this doesn't seem to
meaningfully change the risk profile. Those who feel differently have
options.

> (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?

No, having a login privilege for the database comes with power and
responsibility. In the continuum between usability and locked-down this
seems reasonable.

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, though, and I suspect most setups
would rather rely on user roles holding pg_signal_backend to deal with any
misbehaving process (which leans me toward not wanting to introduce yet
another predefined role). Likely combined with pg_read_all_stats so
viewing pg_stat_activity gives them a complete picture. It seems
reasonable, though, to remove the default function execute grant from
PUBLIC for these; or if that doesn't work consider a documentation patch if
you feel the typical DBA would remain under-informed even after reviewing
the documentation (though without a dedicate section discussing such best
practices I suspect such material would go unread by those who would most
need it).

David J.