[patch] pg_cancel_backend for unprivileged users

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: [patch] pg_cancel_backend for unprivileged users
Date: 2010-02-05 22:55:33
Message-ID: 4ec1cf761002051455i6e702999y7cf4699b4eb48242@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I'd like a way to allow non-superusers to cancel their own queries.

Wearing a DBA hat sometimes, I get requests from users asking that their
long-running or erroneous queries be killed. These users have typically
executed their queries through phpPgAdmin or some other method that doesn't
allow them to cancel their own query, as you can in a psql session by
hitting Control-C. Letting users kill their own queries would be quite
useful[1], as it would cut down on work for DBAs.

I couldn't find a way to implement such a function in a PL, so I hacked up
backend/utils/adt/misc.c so that:
* pg_signal_backend() no longer restricts calls to users passing the
superuser() check
* callers of pg_signal_backend(), which are just pg_cancel_backend() and
pg_terminate_backend() right now, must perform their own permissions checks
* pg_cancel_backend() lets users kill their own queries, or superusers kill
any query

Patch should be attached. This was my first attempt at modifying PG, so I
suspect there are some problems I'm not seeing in my simple approach, or
else someone would have already done this. Some questions:
* Is there anyone else who's attempted[2] to implement something similar to
what I'm looking for, or are others interested in having something like
this?
* Is there a fundamental reason why it's unsafe for non-superusers to be
sending SIGTERM to their own backends (in other words, is this idea fatally
flawed)?

Thanks for any help,
Josh

--
[1] MySQL offers similar functionality with the "KILL <thread_id>" command,
which lets unprivileged users kill queries running under their own usernames
(actually, it looks like it disconnects the killed session entirely). Oracle
also has ALTER SYSTEM KILL SESSION -- I'm not sure if unprivileged users are
allowed to use this.

[2] I found a thread from 2006 where Craig James was after similar
functionality, but he implemented a rather ugly solution, namely a C
function which would let any user send a SIGTERM to any backend PID. I'm not
sure why he didn't just use a PL/pgSQL wrapper around
pg_terminate_backend().
http://archives.postgresql.org/pgsql-performance/2006-07/msg00092.php

Attachment Content-Type Size
pg_cancel_backend.patch application/octet-stream 2.9 KB

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-02-06 06:11:36 Re: vacuum deadlock
Previous Message Kevin Grittner 2010-02-05 18:47:30 Re: vacuum deadlock