MIME-Version: 1.0
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Mon, 26 Feb 2024 12:38:40 +0500
Message-ID: <CALdSSPhC4GGmbnugHfB9G0=fAxjCSug_-rmL9oUh0LTxsyBfsg@mail.gmail.com>
Subject: Allow non-superuser to cancel superuser tasks.
To: pgsql-hackers@postgresql.org
Content-Type: multipart/mixed; boundary="00000000000046f32b061244005d"
Archived-At: <https://www.postgresql.org/message-id/CALdSSPhC4GGmbnugHfB9G0%3DfAxjCSug_-rmL9oUh0LTxsyBfsg%40mail.gmail.com>
Precedence: bulk

--00000000000046f32b061244005d
Content-Type: multipart/alternative; boundary="00000000000046f328061244005b"

--00000000000046f328061244005b
Content-Type: text/plain; charset="UTF-8"

Hi hackers!

In our Cloud we have a patch, which allows non-superuser role ('mdb_admin')
to do some superuser things.
In particular, we have a patch that allows mdb admin to cancel the
autovacuum process and some other processes (processes with
application_name = 'MDB'), see the attachment.
This is needed to allow non-superuser roles to run pg_repack and to cancel
pg_repack.
We need to cancel running autovac to run pg_repack (because of locks), and
we need to cancel pg_repack sometimes also.

I want to reduce our internal patch size and transfer this logic to
extension or to core.
I have found similar threads [1] and [2], but, as far as I understand, they
do not solve this particular case.
I see 2 possible ways to implement this. The first one is to have hool in
pg_signal_backend, and define a hook in extension which can do the thing.
The second one is to have a predefined role. Something like a
`pg_signal_autovacuum` role which can signal running autovac to cancel. But
I don't see how we can handle specific `application_name` with this
solution.

[1]
https://www.postgresql.org/message-id/flat/F9408A5A-B20B-42D2-9E7F-49CD3D1547BC%40enterprisedb.com
[2]
https://www.postgresql.org/message-id/flat/20220722203735.GB3996698%40nathanxps13

--00000000000046f328061244005b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi hackers!</div><div><br></div><div>In our Cloud we =
have a patch, which allows non-superuser role (&#39;mdb_admin&#39;) to do s=
ome superuser things.=C2=A0 <br></div><div>In particular, we have a patch t=
hat allows mdb admin to cancel the autovacuum process and some other proces=
ses (processes with application_name =3D &#39;MDB&#39;), see the attachment=
.</div><div>This is needed to allow non-superuser roles to run pg_repack an=
d to cancel pg_repack. <br></div><div>We need to cancel running autovac to =
run pg_repack (because of locks), and we need to cancel pg_repack sometimes=
 also.</div><div><br></div><div>I want to reduce our internal patch size an=
d transfer this logic to extension or to core. <br></div><div>I have found =
similar threads [1] and [2], but, as far as I understand, they do not solve=
 this particular case. <br></div><div>I see 2 possible ways to implement th=
is. The first one is to have hool in pg_signal_backend, and define a hook i=
n extension which can do the thing. <br></div><div>The second one is to hav=
e a predefined role. Something like a `pg_signal_autovacuum` role which can=
 signal running autovac to cancel. But I don&#39;t see how we can handle sp=
ecific `application_name` with this solution.=C2=A0 </div><div><br></div><d=
iv>[1] <a href=3D"https://www.postgresql.org/message-id/flat/F9408A5A-B20B-=
42D2-9E7F-49CD3D1547BC%40enterprisedb.com">https://www.postgresql.org/messa=
ge-id/flat/F9408A5A-B20B-42D2-9E7F-49CD3D1547BC%40enterprisedb.com</a></div=
><div>[2] <a href=3D"https://www.postgresql.org/message-id/flat/20220722203=
735.GB3996698%40nathanxps13">https://www.postgresql.org/message-id/flat/202=
20722203735.GB3996698%40nathanxps13</a></div></div>

--00000000000046f328061244005b--
--00000000000046f32b061244005d
Content-Type: application/octet-stream; 
	name="v1-0001-cloud-mdb_admin-patch-part-to-illustrate.patch"
Content-Disposition: attachment; 
	filename="v1-0001-cloud-mdb_admin-patch-part-to-illustrate.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lt2mf64k0>
X-Attachment-Id: f_lt2mf64k0

RnJvbSAyM2IyODA1Y2FmNzNmNDQxNjc0NzJiNjY4NzAyZmI4NzQ2NjgxYmEzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBLaXJpbGwgUmVzaGtlIDxyZXNoa2VAcWF2bS1hYzk2NzA3Zi5x
ZW11PgpEYXRlOiBNb24sIDI2IEZlYiAyMDI0IDA5OjU5OjEzICswMzAwClN1YmplY3Q6IFtQQVRD
SCB2MV0gY2xvdWQgbWRiX2FkbWluIHBhdGNoIHBhcnQgdG8gaWxsdXN0cmF0ZS4KCi0tLQogc3Jj
L2JhY2tlbmQvc3RvcmFnZS9pcGMvc2lnbmFsZnVuY3MuYyB8IDMwICsrKysrKysrKysrKysrKysr
KysrKysrKystLQogMSBmaWxlIGNoYW5nZWQsIDI4IGluc2VydGlvbnMoKyksIDIgZGVsZXRpb25z
KC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvc3RvcmFnZS9pcGMvc2lnbmFsZnVuY3MuYyBi
L3NyYy9iYWNrZW5kL3N0b3JhZ2UvaXBjL3NpZ25hbGZ1bmNzLmMKaW5kZXggODFkMWE1OTY1OS4u
MTg4NWUzMWU3ZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvc3RvcmFnZS9pcGMvc2lnbmFsZnVu
Y3MuYworKysgYi9zcmMvYmFja2VuZC9zdG9yYWdlL2lwYy9zaWduYWxmdW5jcy5jCkBAIC03NCwx
NCArNzQsNDAgQEAgcGdfc2lnbmFsX2JhY2tlbmQoaW50IHBpZCwgaW50IHNpZykKIAkJcmV0dXJu
IFNJR05BTF9CQUNLRU5EX0VSUk9SOwogCX0KIAorCWxvY2FsX2JlZW50cnkgPSBwZ3N0YXRfZ2V0
X2xvY2FsX2JlZW50cnlfYnlfYmFja2VuZF9pZChwcm9jLT5iYWNrZW5kSWQpOworCWlmIChsb2Nh
bF9iZWVudHJ5ICE9IE5VTEwpCisJCWJlZW50cnkgPSAmbG9jYWxfYmVlbnRyeS0+YmFja2VuZFN0
YXR1czsKIAkvKgogCSAqIE9ubHkgYWxsb3cgc3VwZXJ1c2VycyB0byBzaWduYWwgc3VwZXJ1c2Vy
LW93bmVkIGJhY2tlbmRzLiAgQW55IHByb2Nlc3MKIAkgKiBub3QgYWR2ZXJ0aXNpbmcgYSByb2xl
IG1pZ2h0IGhhdmUgdGhlIGltcG9ydGFuY2Ugb2YgYSBzdXBlcnVzZXItb3duZWQKIAkgKiBiYWNr
ZW5kLCBzbyB0cmVhdCBpdCB0aGF0IHdheS4KIAkgKi8KIAlpZiAoKCFPaWRJc1ZhbGlkKHByb2Mt
PnJvbGVJZCkgfHwgc3VwZXJ1c2VyX2FyZyhwcm9jLT5yb2xlSWQpKSAmJgotCQkhc3VwZXJ1c2Vy
KCkpCi0JCXJldHVybiBTSUdOQUxfQkFDS0VORF9OT1NVUEVSVVNFUjsKKwkJIXN1cGVydXNlcigp
KSB7CisJCU9pZCByb2xlOworCQljaGFyICogYXBwbmFtZTsKKworCQlpZiAobG9jYWxfYmVlbnRy
eSA9PSBOVUxMKSB7CisJCQlyZXR1cm4gU0lHTkFMX0JBQ0tFTkRfTk9TVVBFUlVTRVI7CisJCX0K
KworCQlyb2xlID0gZ2V0X3JvbGVfb2lkKCJtZGJfYWRtaW4iLCB0cnVlIC8qaWYgbm9kb2J5IGNy
ZWF0ZWQgbWRiX2FkbWluIHJvbGUgaW4gdGhpcyBkYXRhYmFzZSovKTsKKwkJYXBwbmFtZSA9IGxv
Y2FsX2JlZW50cnktPmJhY2tlbmRTdGF0dXMuc3RfYXBwbmFtZTsKKworCQkvLyBvbmx5IGFsbG93
IG1kYl9hZG1pbiB0byBraWxsIHN1IHF1ZXJpZXMKKwkJaWYgKCFpc19tZW1iZXJfb2Zfcm9sZShH
ZXRVc2VySWQoKSwgcm9sZSkpIHsKKwkJCXJldHVybiBTSUdOQUxfQkFDS0VORF9OT1NVUEVSVVNF
UjsKKwkJfQorCisJCS8qIG1kYiBhZG1pbiBhbGxvd2VkIHRvIGtpbGwgcHJvYyB3aXRoIGFwcGxp
Y2F0aW9uIG5hbWUgJ01EQicgb3IgYXV0b3ZhY3V1bSAqLworCQlpZiAobG9jYWxfYmVlbnRyeS0+
YmFja2VuZFN0YXR1cy5zdF9iYWNrZW5kVHlwZSA9PSBCX0FVVE9WQUNfV09SS0VSKSB7CisJCQkv
LyBvaworCQl9IGVsc2UgaWYgKGFwcG5hbWUgIT0gTlVMTCAmJiBzdHJjbXAoYXBwbmFtZSwgIk1E
QiIpID09IDApIHsKKwkJCS8vIG9rCisJCX0gZWxzZSB7CisJCQlyZXR1cm4gU0lHTkFMX0JBQ0tF
TkRfTk9TVVBFUlVTRVI7CisJCX0KKwl9CiAKIAkvKiBVc2VycyBjYW4gc2lnbmFsIGJhY2tlbmRz
IHRoZXkgaGF2ZSByb2xlIG1lbWJlcnNoaXAgaW4uICovCiAJaWYgKCFoYXNfcHJpdnNfb2Zfcm9s
ZShHZXRVc2VySWQoKSwgcHJvYy0+cm9sZUlkKSAmJgotLSAKMi40My4xCgo=
--00000000000046f32b061244005d--