Re: pg_terminate_backend and pg_cancel_backend by not administrator user

On Fri, Jul 01, 2011 at 07:31:30PM +0200, Torello Querci wrote:
> 2011/6/2 Noah Misch <noah(at)leadboat(dot)com>:

> > Having thought about this some more, I do now see a risk. ?Currently, a SECURITY
> > DEFINER function (actually any function, but that's where it matters) can trap
> > query_canceled. ?By doing so, the author can ensure that only superusers and
> > crashes may halt the function during a section protected in this way. ?One might
> > use it to guard a series of updates made over dblink. ?pg_terminate_backend()
> > breaks this protection. ?I've never designed something this way; it only
> > suffices when you merely sort-of-care about transactional integrity. ?Perhaps
> > it's an acceptable loss for this feature?
> >
> >> And if so, is this patch a good first step on that path?
> >
>
> Understand that the pg_terminate_backend() is able to kill process
> that need not to be killed.
> I suppose that looking inside the internal postgreql table in order to
> not allow a normal db owner to kill a superuser connection can avoid
> this problem?

Checking whether a session is authenticated to a superuser is not necessary or
sufficient to close the hazard I described above. My inclination is to just say
that the hazard is acceptable, and we should not worry about it.

No database owner should be allowed to kill processes like the bgwriter or the
stats collector. Since they do not connect to databases or operate as an
authenticated user, none of the proposed tests would open up ways to kill them.

> If I am the database owner I need to be able to manage my DB. Ok for
> superuser connection (and internal administrative process like
> autovacuum)
> I am the developer, not the DBA, so sometimes, when I wrong something,
> I need to kill my session if I wrong something ....
>
> Can we suppose, in a more generic case, that an user can kill
> connection only from the same user even if this is not the database
> owner?

Yes. Modulo concerns I described above, database owners should be allowed to
cancel or terminate any backend connected to their databases, and any user
should be able to cancel or terminate backends authenticated to themselves.