DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:from:date:message-id:subject:to:content-type;
	b=vQGy5URwsvvXiD3xKQBlHLmXGKuallHN5isLlw2aU8hKD2H6lTWWUtRc0TcNMr2NPc
	AF4n7mozthUM+Jge8nX9Vuyv5tYc9rWb7B3CDIVg0J0Xkq7XtAgTodtM5n6jupcP3LoF
	P+rh6Wt0YZ32uANt8oWdd1b08h4qlQDRNxP0k=
MIME-Version: 1.0
From: Josh Kupershmidt <schmiddy@gmail.com>
Date: Fri, 5 Feb 2010 17:55:33 -0500
Message-ID: <4ec1cf761002051455i6e702999y7cf4699b4eb48242@mail.gmail.com>
Subject: [patch] pg_cancel_backend for unprivileged users
To: pgsql-admin@postgresql.org
Content-Type: multipart/mixed; boundary=001636c5971b841392047ee25aea

--001636c5971b841392047ee25aea
Content-Type: multipart/alternative; boundary=001636c5971b84138b047ee25ae8

--001636c5971b84138b047ee25ae8
Content-Type: text/plain; charset=ISO-8859-1

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

--001636c5971b84138b047ee25ae8
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I&#39;d like a way to allow non-superusers to cancel their own queries.<br>=
<br>Wearing a DBA hat sometimes, I get requests from users asking that thei=
r long-running or erroneous queries be killed. These users have typically e=
xecuted their queries through phpPgAdmin or some other method that doesn=
9;t allow them to cancel their own query, as you can in a psql session by h=
itting Control-C. Letting users kill their own queries would be quite usefu=
l[1], as it would cut down on work for DBAs.<br>

<br>I couldn&#39;t find a way to implement such a function in a PL, so I ha=
cked up backend/utils/adt/misc.c so that:<br>=A0* pg_signal_backend() no lo=
nger restricts calls to users passing the superuser() check<br>=A0* callers=
 of pg_signal_backend(), which are just pg_cancel_backend() and pg_terminat=
e_backend() right now, must perform their own permissions checks<br>

=A0* pg_cancel_backend() lets users kill their own queries, or superusers k=
ill any query<br><br>Patch should be attached. This was my first attempt at=
 modifying PG, so I suspect there are some problems I&#39;m not seeing in m=
y simple approach, or else someone would have already done this. Some quest=
ions:<br>

=A0* Is there anyone else who&#39;s attempted[2] to implement something sim=
ilar to what I&#39;m looking for, or are others interested in having someth=
ing like this?<br>=A0* Is there a fundamental reason why it&#39;s unsafe fo=
r non-superusers to be sending SIGTERM to their own backends (in other word=
s, is this idea fatally flawed)?<br>

<br>Thanks for any help,<br>Josh<br><br>--<br>[1] MySQL offers similar func=
tionality with the &quot;KILL &lt;thread_id&gt;&quot; command, which lets u=
nprivileged 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&#39;m not sure if unprivileged users are al=
lowed to use this.<br>

<br>[2] I found a thread from 2006 where Craig James was after similar func=
tionality, but he implemented a rather ugly solution, namely a C function w=
hich would let any user send a SIGTERM to any backend PID. I&#39;m not sure=
 why he didn&#39;t just use a PL/pgSQL wrapper around pg_terminate_backend(=
). <br>

=A0=A0=A0 <a href=3D"http://archives.postgresql.org/pgsql-performance/2006-=
07/msg00092.php">http://archives.postgresql.org/pgsql-performance/2006-07/m=
sg00092.php</a><br><br>

--001636c5971b84138b047ee25ae8--

--001636c5971b841392047ee25aea
Content-Type: application/octet-stream; name="pg_cancel_backend.patch"
Content-Disposition: attachment; filename="pg_cancel_backend.patch"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_g5bknyxo0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9taXNjLmMgYi9zcmMvYmFja2VuZC91
dGlscy9hZHQvbWlzYy5jCmluZGV4IDMwMTk5YjQuLmYxOWMyZTUgMTAwNjQ0CioqKiBhL3NyYy9i
YWNrZW5kL3V0aWxzL2FkdC9taXNjLmMKLS0tIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L21pc2Mu
YwoqKioqKioqKioqKioqKioKKioqIDMzLDM4ICoqKioKLS0tIDMzLDM5IC0tLS0KICAjaW5jbHVk
ZSAic3RvcmFnZS9wcm9jYXJyYXkuaCIKICAjaW5jbHVkZSAidXRpbHMvYnVpbHRpbnMuaCIKICAj
aW5jbHVkZSAidGNvcC90Y29wcHJvdC5oIgorICNpbmNsdWRlICJzdG9yYWdlL3Byb2MuaCIKICAK
ICAjZGVmaW5lIGF0b29pZCh4KSAgKChPaWQpIHN0cnRvdWwoKHgpLCBOVUxMLCAxMCkpCiAgCioq
KioqKioqKioqKioqKiBjdXJyZW50X3F1ZXJ5KFBHX0ZVTkNUSU9OX0FSR1MpCioqKiA2OCw4MyAq
KioqCiAgCQlQR19SRVRVUk5fTlVMTCgpOwogIH0KICAKICAvKgogICAqIEZ1bmN0aW9ucyB0byBz
ZW5kIHNpZ25hbHMgdG8gb3RoZXIgYmFja2VuZHMuCiAgICovCiAgc3RhdGljIGJvb2wKICBwZ19z
aWduYWxfYmFja2VuZChpbnQgcGlkLCBpbnQgc2lnKQogIHsKLSAJaWYgKCFzdXBlcnVzZXIoKSkK
LSAJCWVyZXBvcnQoRVJST1IsCi0gCQkJCShlcnJjb2RlKEVSUkNPREVfSU5TVUZGSUNJRU5UX1BS
SVZJTEVHRSksCi0gCQkJKGVycm1zZygibXVzdCBiZSBzdXBlcnVzZXIgdG8gc2lnbmFsIG90aGVy
IHNlcnZlciBwcm9jZXNzZXMiKSkpKTsKICAKICAJaWYgKCFJc0JhY2tlbmRQaWQocGlkKSkKICAJ
ewotLS0gNjksODEgLS0tLQogIAkJUEdfUkVUVVJOX05VTEwoKTsKICB9CiAgCisgCiAgLyoKICAg
KiBGdW5jdGlvbnMgdG8gc2VuZCBzaWduYWxzIHRvIG90aGVyIGJhY2tlbmRzLgogICAqLwogIHN0
YXRpYyBib29sCiAgcGdfc2lnbmFsX2JhY2tlbmQoaW50IHBpZCwgaW50IHNpZykKICB7CiAgCiAg
CWlmICghSXNCYWNrZW5kUGlkKHBpZCkpCiAgCXsKKioqKioqKioqKioqKioqIHBnX3NpZ25hbF9i
YWNrZW5kKGludCBwaWQsIGludCBzaWcpCioqKiAxMDUsMTIwICoqKioKICAJcmV0dXJuIHRydWU7
CiAgfQogIAogIERhdHVtCiAgcGdfY2FuY2VsX2JhY2tlbmQoUEdfRlVOQ1RJT05fQVJHUykKICB7
CiEgCVBHX1JFVFVSTl9CT09MKHBnX3NpZ25hbF9iYWNrZW5kKFBHX0dFVEFSR19JTlQzMigwKSwg
U0lHSU5UKSk7CiAgfQogIAogIERhdHVtCiAgcGdfdGVybWluYXRlX2JhY2tlbmQoUEdfRlVOQ1RJ
T05fQVJHUykKICB7CiEgCVBHX1JFVFVSTl9CT09MKHBnX3NpZ25hbF9iYWNrZW5kKFBHX0dFVEFS
R19JTlQzMigwKSwgU0lHVEVSTSkpOwogIH0KICAKICBEYXR1bQotLS0gMTAzLDE2MCAtLS0tCiAg
CXJldHVybiB0cnVlOwogIH0KICAKKyAKKyAvKiBBbGxvdyB1c2VycyB0byBjYW5jZWwgdGhlaXIg
b3duIHF1ZXJpZXMgcnVubmluZyB1bmRlciB0aGUgZ2l2ZW4gYmFja2VuZCBQSUQuCisgICogU3Vw
ZXJ1c2VycyBtYXkgY2FuY2VsIGFueW9uZSdzIHF1ZXJpZXMuCisgICovCiAgRGF0dW0KICBwZ19j
YW5jZWxfYmFja2VuZChQR19GVU5DVElPTl9BUkdTKQogIHsKISAJaW50CQkJQmFja2VuZFBpZCA9
IFBHX0dFVEFSR19JTlQzMigwKTsKISAJT2lkCQkJU2Vzc2lvblVzZXJJZCA9IEdldFVzZXJJZCgp
OwohIAlQR1BST0MJICAgKmJhY2tlbmRfcHJvYyA9IEJhY2tlbmRQaWRHZXRQcm9jKEJhY2tlbmRQ
aWQpOwohIAlPaWQJCQlUYXJnZXRSb2xlSWQ7CiEgCiEgCWlmIChiYWNrZW5kX3Byb2MgPT0gTlVM
TCkKISAJewohIAkJZXJlcG9ydChXQVJOSU5HLCAoZXJybXNnKCJVbmFibGUgdG8gZmluZCBiYWNr
ZW5kIHdpdGggUElEOiAldSIsIEJhY2tlbmRQaWQpKSk7CiEgCQlQR19SRVRVUk5fQk9PTChmYWxz
ZSk7CiEgCX0KISAJVGFyZ2V0Um9sZUlkID0gYmFja2VuZF9wcm9jLT5yb2xlSWQ7CiEgCiEgCWVy
ZXBvcnQoTk9USUNFLCAoZXJybXNnKCJZb3VyIHNlc3Npb24gSUQ6ICV1LiBEZXNpcmVkIGJhY2tl
bmQgdG8ga2lsbDogJXUuIHJvbGVJZCB3aGljaCBpcyB1c2luZyB0aGlzIGJhY2tlbmQ6ICV1Iiwg
KGludCkgU2Vzc2lvblVzZXJJZCwgKGludCkgQmFja2VuZFBpZCwgKGludCkgVGFyZ2V0Um9sZUlk
KSkpOwohIAohIAlpZiAoc3VwZXJ1c2VyKCkpCiEgCXsKISAJCWVyZXBvcnQoTk9USUNFLCAoZXJy
bXNnKCJzdXBlcnVzZXIoKSBjaGVjayBwYXNzZWQhIikpKTsKISAJCVBHX1JFVFVSTl9CT09MKHBn
X3NpZ25hbF9iYWNrZW5kKEJhY2tlbmRQaWQsIFNJR0lOVCkpOwohIAl9CiEgCWVsc2UgaWYgKFRh
cmdldFJvbGVJZCA9PSBTZXNzaW9uVXNlcklkKQohIAl7CiEgCQllcmVwb3J0KE5PVElDRSwgKGVy
cm1zZygicm9sZUlkcyBtYXRjaCwgc28geW91IG1heSBraWxsLiIpKSk7CiEgCQlQR19SRVRVUk5f
Qk9PTChwZ19zaWduYWxfYmFja2VuZChCYWNrZW5kUGlkLCBTSUdJTlQpKTsKISAJfQohIAllbHNl
CiEgCXsKISAJCWVyZXBvcnQoV0FSTklORywgKGVycm1zZygiWW91IG1heSBub3Qga2lsbCB0aGUg
ZGVzaXJlZCBiYWNrZW5kLCBzaW5jZSB5b3UgYXJlIG5vdCBzdXBlcnVzZXIgYW5kIHlvdXIgcm9s
ZUlkIGRpZmZlcnMuIikpKTsKISAJCVBHX1JFVFVSTl9CT09MKGZhbHNlKTsKISAJfQogIH0KICAK
ICBEYXR1bQogIHBnX3Rlcm1pbmF0ZV9iYWNrZW5kKFBHX0ZVTkNUSU9OX0FSR1MpCiAgewohIAlp
ZiAoIXN1cGVydXNlcigpKQohIAl7CiEgCQllcmVwb3J0KEVSUk9SLAohIAkJCQkoZXJyY29kZShF
UlJDT0RFX0lOU1VGRklDSUVOVF9QUklWSUxFR0UpLAohIAkJIChlcnJtc2coIm11c3QgYmUgc3Vw
ZXJ1c2VyIHRvIHRlcm1pbmF0ZSBvdGhlciBzZXJ2ZXIgcHJvY2Vzc2VzIikpKSk7CiEgCQlQR19S
RVRVUk5fQk9PTChmYWxzZSk7CiEgCX0KISAJZWxzZQohIAl7CiEgCQlQR19SRVRVUk5fQk9PTChw
Z19zaWduYWxfYmFja2VuZChQR19HRVRBUkdfSU5UMzIoMCksIFNJR1RFUk0pKTsKISAJfQogIH0K
ICAKICBEYXR1bQo=
--001636c5971b841392047ee25aea--