From: Josh Kupershmidt <schmiddy@gmail.com>
To: pgsql-admin@postgresql.org
Subject: [patch] pg_cancel_backend for unprivileged users
Date: Fri, 5 Feb 2010 17:55:33 -0500
Message-ID: <4ec1cf761002051455i6e702999y7cf4699b4eb48242@mail.gmail.com> (raw)
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.phpAttachments:pg_cancel_backend.patch (application/octet-stream, 2.9K) downloaddiff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.cindex 30199b4..f19c2e5 100644
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 33,38 ****
--- 33,39 ----
#include "storage/procarray.h"
#include "utils/builtins.h"
#include "tcop/tcopprot.h"
+ #include "storage/proc.h"
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
*************** current_query(PG_FUNCTION_ARGS)
*** 68,83 ****
PG_RETURN_NULL();
}
/*
* Functions to send signals to other backends.
*/
static bool
pg_signal_backend(int pid, int sig)
{
- if (!superuser())- ereport(ERROR,- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),- (errmsg("must be superuser to signal other server processes"))));
if (!IsBackendPid(pid))
{
--- 69,81 ----
PG_RETURN_NULL();
}
+
/*
* Functions to send signals to other backends.
*/
static bool
pg_signal_backend(int pid, int sig)
{
if (!IsBackendPid(pid))
{
*************** pg_signal_backend(int pid, int sig)
*** 105,120 ****
return true;
}
Datum
pg_cancel_backend(PG_FUNCTION_ARGS)
{
! PG_RETURN_BOOL(pg_signal_backend(PG_GETARG_INT32(0), SIGINT));
}
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
! PG_RETURN_BOOL(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
}
Datum
--- 103,160 ----
return true;
}
+ + /* Allow users to cancel their own queries running under the given backend PID.+ * Superusers may cancel anyone's queries.+ */
Datum
pg_cancel_backend(PG_FUNCTION_ARGS)
{
! int BackendPid = PG_GETARG_INT32(0);
! Oid SessionUserId = GetUserId();
! PGPROC *backend_proc = BackendPidGetProc(BackendPid);
! Oid TargetRoleId;
!
! if (backend_proc == NULL)
! {
! ereport(WARNING, (errmsg("Unable to find backend with PID: %u", BackendPid)));
! PG_RETURN_BOOL(false);
! }
! TargetRoleId = backend_proc->roleId;
!
! ereport(NOTICE, (errmsg("Your session ID: %u. Desired backend to kill: %u. roleId which is using this backend: %u", (int) SessionUserId, (int) BackendPid, (int) TargetRoleId)));
!
! if (superuser())
! {
! ereport(NOTICE, (errmsg("superuser() check passed!")));
! PG_RETURN_BOOL(pg_signal_backend(BackendPid, SIGINT));
! }
! else if (TargetRoleId == SessionUserId)
! {
! ereport(NOTICE, (errmsg("roleIds match, so you may kill.")));
! PG_RETURN_BOOL(pg_signal_backend(BackendPid, SIGINT));
! }
! else
! {
! ereport(WARNING, (errmsg("You may not kill the desired backend, since you are not superuser and your roleId differs.")));
! PG_RETURN_BOOL(false);
! }
}
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
! if (!superuser())
! {
! ereport(ERROR,
! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser to terminate other server processes"))));
! PG_RETURN_BOOL(false);
! }
! else
! {
! PG_RETURN_BOOL(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
! }
}
Datum
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: schmiddy@gmail.com
Subject: Re: [patch] pg_cancel_backend for unprivileged users
In-Reply-To: <4ec1cf761002051455i6e702999y7cf4699b4eb48242@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox