Re: pg_terminate_backend for same-role

On Fri, Mar 16, 2012 at 3:42 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Thu, Mar 15, 2012 at 04:14:03PM -0700, Daniel Farina wrote:
>> Parallel to pg_cancel_backend, it'd be nice to allow the user to just
>> outright kill a backend that they own (politely, with a SIGTERM),
>> aborting any transactions in progress, including the idle transaction,
>> and closing the socket.
>
> +1
>
>> I imagine the problem is a race condition whereby a pid might be
>> reused by another process owned by another user (doesn't that also
>> affect pg_cancel_backend?).  Shall we just do everything using the
>> MyCancelKey (which I think could just be called "SessionKey",
>> "SessionSecret", or even just "Session") as to ensure we have no case
>> of mistaken identity? Or does that end up being problematic?
>
> No, I think the hazard you identify here is orthogonal to the question of when
> to authorize pg_terminate_backend().  As you note downthread, protocol-level
> cancellations available in released versions already exhibit this hazard.  I
> wouldn't mind a clean fix for this, but it's an independent subject.

Hmm. Well, here's a patch that implements exactly that, I think,
similar to the one posted to this thread, but not using BackendIds,
but rather the newly-introduced "SessionId". Would appreciate
comments. Because an out-of-band signaling method has been integrated
more complex behaviors -- such as closing the
TERM-against-SECURITY-DEFINER-FUNCTION hazard -- can be addressed.
For now I've only attempted to solve the problem of backend ambiguity,
which basically necessitated out-of-line information transfer as per
the usual means.

> Here I discussed a hazard specific to allowing pg_terminate_backend():
> http://archives.postgresql.org/message-id/20110602045955.GC8246@tornado.gateway.2wire.net
>
> To summarize, user code can trap SIGINT cancellations, but it cannot trap
> SIGTERM terminations.  If a backend is executing a SECURITY DEFINER function
> when another backend of the same role calls pg_terminate_backend() thereon,
> the pg_terminate_backend() caller could achieve something he cannot achieve in
> PostgreSQL 9.1.  I vote that this is an acceptable loss.

I'll throw out a patch that just lets this hazard exist and see what
happens, although it is obsoleted/incompatible with the one already
attached.

--
fdr