Thread: Functions To Let Users Cancel/Terminate own Back Ends

PostgreSQLers,

I have a need at my $dayjob to let users cancel their own back ends. See any issues with this function to allow them to
dothat? Any security gotchas or anything? 

CREATE OR REPLACE FUNCTION iov_cancel_user_backend(
    pid INTEGER
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
   username NAME;
BEGIN
    SELECT usename INTO username FROM iov_catalog.iov_stat_activity WHERE procpid = pid;
    IF username IS NULL THEN RETURN FALSE; END IF;

    IF username <> session_user THEN
        RAISE EXCEPTION 'You do not own back end %', pid;
    END IF;

    RETURN iov_catalog.pg_cancel_backend(pid);
END;
$$;

I plan to have one that calls pg_terminate_backend(), as well.

Thanks,

David

On Thu, Feb 2, 2012 at 23:38, David E. Wheeler <david@kineticode.com> wrote:
> PostgreSQLers,
>
> I have a need at my $dayjob to let users cancel their own back ends. See any issues with this function to allow them
todo that? Any security gotchas or anything? 

You mean something like this?
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0495aaad8b337642830a4d4e82f8b8c02b27b1be

(So yes, the principle was agreed to be safe)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Feb 2, 2012, at 2:51 PM, Magnus Hagander wrote:

>> I have a need at my $dayjob to let users cancel their own back ends. See any issues with this function to allow them
todo that? Any security gotchas or anything? 
>
> You mean something like this?
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0495aaad8b337642830a4d4e82f8b8c02b27b1be
>
> (So yes, the principle was agreed to be safe)

Oh, it *was* committed? Excellent. Yeah, looks pretty similar in principal. Thanks!

David