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