Feedback-ID: ic6394509:Fastmail
User-Agent: Cyrus-JMAP/3.11.0-alpha0-522-ga39cca1d5-fm-20240610.002-ga39cca1d
MIME-Version: 1.0
Message-Id: <80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com>
Date: Wed, 19 Jun 2024 13:34:31 +0200
From: "Joel Jacobson" <joel@compiler.org>
To: pgsql-hackers@postgresql.org
Subject: Add pg_get_acl() function get the ACL for a database object
Content-Type: multipart/mixed;
 boundary=928850a6996e4f019140df59379ac887
Archived-At: <https://www.postgresql.org/message-id/80b16434-b9b1-4c3d-8f28-569f21c2c102%40app.fastmail.com>
Precedence: bulk

--928850a6996e4f019140df59379ac887
Content-Type: text/plain

Hello hackers,

Currently, obtaining the Access Control List (ACL) for a database object
requires querying specific pg_catalog tables directly, where the user
needs to know the name of the ACL column for the object.

Consider:

```
CREATE USER test_user;
CREATE USER test_owner;
CREATE SCHEMA test_schema AUTHORIZATION test_owner;
SET ROLE TO test_owner;
CREATE TABLE test_schema.test_table ();
GRANT SELECT ON TABLE test_schema.test_table TO test_user;
```

To get the ACL we can do:

```
SELECT relacl FROM pg_class WHERE oid = 'test_schema.test_table'::regclass::oid;

                         relacl
---------------------------------------------------------
 {test_owner=arwdDxtm/test_owner,test_user=r/test_owner}
```

Attached patch adds a new SQL-callable functoin `pg_get_acl()`, so we can do:

```
SELECT pg_get_acl('pg_class'::regclass, 'test_schema.test_table'::regclass::oid);
                       pg_get_acl
---------------------------------------------------------
 {test_owner=arwdDxtm/test_owner,test_user=r/test_owner}
```

The original idea for this function came from Alvaro Herrera,
in this related discussion:
https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com

On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
> On 2021-Mar-25, Joel Jacobson wrote:
>
>> pg_shdepend doesn't contain the aclitem info though,
>> so it won't work for pg_permissions if we want to expose
>> privilege_type, is_grantable and grantor.
>
> Ah, of course -- the only way to obtain the acl columns is by going
> through the catalogs individually, so it won't be possible.  I think
> this could be fixed with some very simple, quick function pg_get_acl()
> that takes a catalog OID and object OID and returns the ACL; then
> use aclexplode() to obtain all those details.

The pg_get_acl() function has been implemented by following
the guidance from Alvaro in the related dicussion:

On Fri, Mar 26, 2021, at 13:43, Alvaro Herrera wrote:
> AFAICS the way to do it is like AlterObjectOwner_internal obtains data
> -- first do get_catalog_object_by_oid (gives you the HeapTuple that
> represents the object), then
> heap_getattr( ..., get_object_attnum_acl(), ..), and there you have the
> ACL which you can "explode" (or maybe just return as-is).
>
> AFAICS if you do this, it's just one cache lookups per object, or
> one indexscan for the cases with no by-OID syscache.  It should be much
> cheaper than the UNION ALL query.  And you use pg_shdepend to guide
> this, so you only do it for the objects that you already know are
> interesting.

Many thanks Alvaro for the very helpful instructions.

This function would then allow users to e.g. create a view to show the privileges
for all database objects, like the pg_privileges system view suggested in the
related discussion.

Tests and docs are added.

Best regards,
Joel Jakobsson
--928850a6996e4f019140df59379ac887
Content-Disposition: attachment; filename="0001-Add-pg_get_acl.patch"
Content-Type: application/octet-stream; name="0001-Add-pg_get_acl.patch"
Content-Transfer-Encoding: BASE64

RnJvbSA2M2QzOTk2OTU4NWY2Mjg2NjExMTBmMTY2MDViZTQ4YmIxOTg5OWI4IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBKb2VsIEpha29ic3NvbiA8Z2l0aHViQGNvbXBpbGVy
Lm9yZz4KRGF0ZTogV2VkLCAxOSBKdW4gMjAyNCAxMjozMToyMSArMDIwMApTdWJqZWN0OiBb
UEFUQ0hdIEFkZCBwZ19nZXRfYWNsKCkgZnVuY3Rpb24gdG8gZ2V0IHRoZSBBQ0wgZm9yIGEg
ZGF0YWJhc2UKIG9iamVjdC4KClRoaXMgU1FMLWNhbGxhYmxlIGZ1bmN0aW9uIHJldHVybnMg
dGhlIEFjY2VzcyBDb250cm9sIExpc3QgKEFDTCkKZm9yIGEgZGF0YWJhc2Ugb2JqZWN0LCBz
cGVjaWZpZWQgYnkgY2F0YWxvZyBPSUQgYW5kIG9iamVjdCBPSUQuCgpSZWxhdGVkIERpc2N1
c3Npb246IGh0dHBzOi8vcG9zdGdyLmVzL20vMjYxZGVmNmItMjI2ZS00MjM4LWI3ZWItZmYy
NDBjYjljMmM5QHd3dy5mYXN0bWFpbC5jb20KLS0tCiBkb2Mvc3JjL3NnbWwvZnVuYy5zZ21s
ICAgICAgICAgICAgICAgICAgIHwgMTYgKysrKysrKysrCiBzcmMvYmFja2VuZC9jYXRhbG9n
L29iamVjdGFkZHJlc3MuYyAgICAgIHwgNDMgKysrKysrKysrKysrKysrKysrKysrKysrCiBz
cmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0ICAgICAgICAgIHwgIDYgKysrKwogc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9wcml2aWxlZ2VzLm91dCB8ICA3ICsrKysKIHNyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL3ByaXZpbGVnZXMuc3FsICAgICAgfCAgMiArKwogNSBmaWxlcyBj
aGFuZ2VkLCA3NCBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL2Z1
bmMuc2dtbCBiL2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwKaW5kZXggMjYwOTI2OTYxMC4uZmE2
ZmRjZTUxNyAxMDA2NDQKLS0tIGEvZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbAorKysgYi9kb2Mv
c3JjL3NnbWwvZnVuYy5zZ21sCkBAIC0yNjY4Niw2ICsyNjY4NiwyMiBAQCBTRUxFQ1QgY3Vy
cnZhbChwZ19nZXRfc2VyaWFsX3NlcXVlbmNlKCdzb21ldGFibGUnLCAnaWQnKSk7CiAgICAg
ICAgIFVuZGVmaW5lZCBvYmplY3RzIGFyZSBpZGVudGlmaWVkIHdpdGggPGxpdGVyYWw+TlVM
TDwvbGl0ZXJhbD4gdmFsdWVzLgogICAgICAgIDwvcGFyYT48L2VudHJ5PgogICAgICAgPC9y
b3c+CisKKyAgICAgIDxyb3c+CisgICAgICAgPGVudHJ5IHJvbGU9ImZ1bmNfdGFibGVfZW50
cnkiPjxwYXJhIHJvbGU9ImZ1bmNfc2lnbmF0dXJlIj4KKyAgICAgICAgPGluZGV4dGVybT4K
KyAgICAgICAgIDxwcmltYXJ5PnBnX2dldF9hY2w8L3ByaW1hcnk+CisgICAgICAgIDwvaW5k
ZXh0ZXJtPgorICAgICAgICA8ZnVuY3Rpb24+cGdfZ2V0X2FjbDwvZnVuY3Rpb24+ICggPHBh
cmFtZXRlcj5jbGFzc2lkPC9wYXJhbWV0ZXI+IDx0eXBlPm9pZDwvdHlwZT4sIDxwYXJhbWV0
ZXI+b2JqaWQ8L3BhcmFtZXRlcj4gPHR5cGU+b2lkPC90eXBlPiApCisgICAgICAgIDxyZXR1
cm52YWx1ZT5hY2xpdGVtW108L3JldHVybnZhbHVlPgorICAgICAgIDwvcGFyYT4KKyAgICAg
ICA8cGFyYT4KKyAgICAgICAgUmV0dXJucyB0aGUgQWNjZXNzIENvbnRyb2wgTGlzdCAoQUNM
KSBmb3IgYSBkYXRhYmFzZSBvYmplY3QsCisgICAgICAgIHNwZWNpZmllZCBieSBjYXRhbG9n
IE9JRCBhbmQgb2JqZWN0IE9JRC4KKyAgICAgICAgVGhpcyBmdW5jdGlvbiBpcyB1c2VmdWwg
Zm9yIHJldHJpZXZpbmcgYW5kIGluc3BlY3RpbmcgdGhlIHByaXZpbGVnZXMgYXNzb2NpYXRl
ZCB3aXRoIGRhdGFiYXNlIG9iamVjdHMuCisgICAgICAgIFRoaXMgZnVuY3Rpb24gcmV0dXJu
cyBOVUxMIHZhbHVlcyBmb3IgdW5kZWZpbmVkIG9iamVjdHMuCisgICAgICAgPC9wYXJhPjwv
ZW50cnk+CisgICAgICA8L3Jvdz4KICAgICAgPC90Ym9keT4KICAgICA8L3Rncm91cD4KICAg
IDwvdGFibGU+CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jYXRhbG9nL29iamVjdGFkZHJl
c3MuYyBiL3NyYy9iYWNrZW5kL2NhdGFsb2cvb2JqZWN0YWRkcmVzcy5jCmluZGV4IDdiNTM2
YWM2ZmQuLjA1NzliNjZhYjcgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvb2Jq
ZWN0YWRkcmVzcy5jCisrKyBiL3NyYy9iYWNrZW5kL2NhdGFsb2cvb2JqZWN0YWRkcmVzcy5j
CkBAIC00MzYyLDYgKzQzNjIsNDkgQEAgcGdfaWRlbnRpZnlfb2JqZWN0X2FzX2FkZHJlc3Mo
UEdfRlVOQ1RJT05fQVJHUykKIAlQR19SRVRVUk5fREFUVU0oSGVhcFR1cGxlR2V0RGF0dW0o
aHR1cCkpOwogfQogCisvKgorICogU1FMLWxldmVsIGNhbGxhYmxlIGZ1bmN0aW9uIHRvIG9i
dGFpbiB0aGUgQWNjZXNzIENvbnRyb2wgTGlzdCAoQUNMKQorICogb2YgYSBzcGVjaWZpZWQg
b2JqZWN0LCBnaXZlbiBpdHMgY2F0YWxvZyBPSUQgYW5kIG9iamVjdCBPSUQuCisgKi8KK0Rh
dHVtCitwZ19nZXRfYWNsKFBHX0ZVTkNUSU9OX0FSR1MpCit7CisJT2lkCQkJY2xhc3NJZCA9
IFBHX0dFVEFSR19PSUQoMCk7CisJT2lkCQkJb2JqZWN0SWQgPSBQR19HRVRBUkdfT0lEKDEp
OworCU9pZAkJCWNhdGFsb2dJZCA9IChjbGFzc0lkID09IExhcmdlT2JqZWN0UmVsYXRpb25J
ZCkgPyBMYXJnZU9iamVjdE1ldGFkYXRhUmVsYXRpb25JZCA6IGNsYXNzSWQ7CisJQXR0ck51
bWJlcglBbnVtX29pZCA9IGdldF9vYmplY3RfYXR0bnVtX29pZChjYXRhbG9nSWQpOworCUF0
dHJOdW1iZXIJQW51bV9hY2wgPSBnZXRfb2JqZWN0X2F0dG51bV9hY2woY2F0YWxvZ0lkKTsK
KwlSZWxhdGlvbglyZWw7CisJSGVhcFR1cGxlCXR1cDsKKwlEYXR1bQkJZGF0dW07CisJYm9v
bAkJaXNudWxsOworCisJLyogZm9yICJwaW5uZWQiIGl0ZW1zIGluIHBnX2RlcGVuZCwgcmV0
dXJuIG51bGwgKi8KKwlpZiAoIU9pZElzVmFsaWQoY2xhc3NJZCkgJiYgIU9pZElzVmFsaWQo
b2JqZWN0SWQpKQorCQlQR19SRVRVUk5fTlVMTCgpOworCisJcmVsID0gdGFibGVfb3Blbihj
YXRhbG9nSWQsIEFjY2Vzc1NoYXJlTG9jayk7CisKKwl0dXAgPSBnZXRfY2F0YWxvZ19vYmpl
Y3RfYnlfb2lkKHJlbCwgQW51bV9vaWQsIG9iamVjdElkKTsKKwlpZiAodHVwID09IE5VTEwp
CisJCWVsb2coRVJST1IsICJjYWNoZSBsb29rdXAgZmFpbGVkIGZvciBvYmplY3QgJXUgb2Yg
Y2F0YWxvZyBcIiVzXCIiLAorCQkJIG9iamVjdElkLCBSZWxhdGlvbkdldFJlbGF0aW9uTmFt
ZShyZWwpKTsKKworCWlmIChBbnVtX2FjbCAhPSBJbnZhbGlkQXR0ck51bWJlcikKKwl7CisJ
CWRhdHVtID0gaGVhcF9nZXRhdHRyKHR1cCwgQW51bV9hY2wsIFJlbGF0aW9uR2V0RGVzY3Io
cmVsKSwgJmlzbnVsbCk7CisJCWlmICghaXNudWxsKQorCQl7CisJCQl0YWJsZV9jbG9zZShy
ZWwsIEFjY2Vzc1NoYXJlTG9jayk7CisJCQlQR19SRVRVUk5fREFUVU0oZGF0dW0pOworCQl9
CisJfQorCisJdGFibGVfY2xvc2UocmVsLCBBY2Nlc3NTaGFyZUxvY2spOworCVBHX1JFVFVS
Tl9OVUxMKCk7Cit9CisKKwogLyoKICAqIFJldHVybiBhIHBhbGxvYydlZCBzdHJpbmcgdGhh
dCBkZXNjcmliZXMgdGhlIHR5cGUgb2Ygb2JqZWN0IHRoYXQgdGhlCiAgKiBwYXNzZWQgYWRk
cmVzcyBpcyBmb3IuCmRpZmYgLS1naXQgYS9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2Mu
ZGF0IGIvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19wcm9jLmRhdAppbmRleCA2YTU0NzZkM2M0
Li41YWI5YjExYjQ3IDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2Mu
ZGF0CisrKyBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvcGdfcHJvYy5kYXQKQEAgLTYzNjIsNiAr
NjM2MiwxMiBAQAogICBwcm9uYW1lID0+ICdwZ19kZXNjcmliZV9vYmplY3QnLCBwcm92b2xh
dGlsZSA9PiAncycsIHByb3JldHR5cGUgPT4gJ3RleHQnLAogICBwcm9hcmd0eXBlcyA9PiAn
b2lkIG9pZCBpbnQ0JywgcHJvc3JjID0+ICdwZ19kZXNjcmliZV9vYmplY3QnIH0sCiAKK3sg
b2lkID0+ICc2MzQ3JywgZGVzY3IgPT4gJ2dldCBBQ0wgZm9yIFNRTCBvYmplY3QnLAorICBw
cm9uYW1lID0+ICdwZ19nZXRfYWNsJywgcHJvdm9sYXRpbGUgPT4gJ3MnLCBwcm9yZXR0eXBl
ID0+ICdfYWNsaXRlbScsCisgIHByb2FyZ3R5cGVzID0+ICdvaWQgb2lkJywKKyAgcHJvYXJn
bmFtZXMgPT4gJ3tjbGFzc2lkLG9iamlkfScsCisgIHByb3NyYyA9PiAncGdfZ2V0X2FjbCcg
fSwKKwogeyBvaWQgPT4gJzM4MzknLAogICBkZXNjciA9PiAnZ2V0IG1hY2hpbmUtcGFyc2Vh
YmxlIGlkZW50aWZpY2F0aW9uIG9mIFNRTCBvYmplY3QnLAogICBwcm9uYW1lID0+ICdwZ19p
ZGVudGlmeV9vYmplY3QnLCBwcm92b2xhdGlsZSA9PiAncycsIHByb3JldHR5cGUgPT4gJ3Jl
Y29yZCcsCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3ByaXZpbGVn
ZXMub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9wcml2aWxlZ2VzLm91dAppbmRl
eCBlYjRiNzYyZWExLi43YWRhMTI0MjY1IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNz
L2V4cGVjdGVkL3ByaXZpbGVnZXMub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0
ZWQvcHJpdmlsZWdlcy5vdXQKQEAgLTIxOSw2ICsyMTksMTMgQEAgR1JBTlQgSU5TRVJUIE9O
IGF0ZXN0MiBUTyByZWdyZXNzX3ByaXZfdXNlcjQgR1JBTlRFRCBCWSBDVVJSRU5UX1VTRVI7
CiBHUkFOVCBUUlVOQ0FURSBPTiBhdGVzdDIgVE8gcmVncmVzc19wcml2X3VzZXI1IEdSQU5U
RUQgQlkgQ1VSUkVOVF9ST0xFOwogR1JBTlQgVFJVTkNBVEUgT04gYXRlc3QyIFRPIHJlZ3Jl
c3NfcHJpdl91c2VyNCBHUkFOVEVEIEJZIHJlZ3Jlc3NfcHJpdl91c2VyNTsgIC0tIGVycm9y
CiBFUlJPUjogIGdyYW50b3IgbXVzdCBiZSBjdXJyZW50IHVzZXIKKy0tIHRlc3QgcGdfZ2V0
X2FjbCgpCitTRUxFQ1QgcGdfZ2V0X2FjbCgncGdfY2xhc3MnOjpyZWdjbGFzcywgJ2F0ZXN0
Mic6OnJlZ2NsYXNzOjpvaWQpOworICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIHBnX2dldF9hY2wgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0KKyB7cmVncmVzc19wcml2X3VzZXIxPWFyd2REeHRtL3JlZ3Jlc3NfcHJp
dl91c2VyMSxyZWdyZXNzX3ByaXZfdXNlcjI9ci9yZWdyZXNzX3ByaXZfdXNlcjEscmVncmVz
c19wcml2X3VzZXIzPXcvcmVncmVzc19wcml2X3VzZXIxLHJlZ3Jlc3NfcHJpdl91c2VyND1h
L3JlZ3Jlc3NfcHJpdl91c2VyMSxyZWdyZXNzX3ByaXZfdXNlcjU9RC9yZWdyZXNzX3ByaXZf
dXNlcjF9CisoMSByb3cpCisKIFNFVCBTRVNTSU9OIEFVVEhPUklaQVRJT04gcmVncmVzc19w
cml2X3VzZXIyOwogU0VMRUNUIHNlc3Npb25fdXNlciwgY3VycmVudF91c2VyOwogICAgIHNl
c3Npb25fdXNlciAgICB8ICAgIGN1cnJlbnRfdXNlciAgICAKZGlmZiAtLWdpdCBhL3NyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL3ByaXZpbGVnZXMuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
cHJpdmlsZWdlcy5zcWwKaW5kZXggZWViNGMwMDI5Mi4uMzZmMzQ4MDUxZSAxMDA2NDQKLS0t
IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvcHJpdmlsZWdlcy5zcWwKKysrIGIvc3JjL3Rlc3Qv
cmVncmVzcy9zcWwvcHJpdmlsZWdlcy5zcWwKQEAgLTE5MCw2ICsxOTAsOCBAQCBHUkFOVCBU
UlVOQ0FURSBPTiBhdGVzdDIgVE8gcmVncmVzc19wcml2X3VzZXI1IEdSQU5URUQgQlkgQ1VS
UkVOVF9ST0xFOwogCiBHUkFOVCBUUlVOQ0FURSBPTiBhdGVzdDIgVE8gcmVncmVzc19wcml2
X3VzZXI0IEdSQU5URUQgQlkgcmVncmVzc19wcml2X3VzZXI1OyAgLS0gZXJyb3IKIAorLS0g
dGVzdCBwZ19nZXRfYWNsKCkKK1NFTEVDVCBwZ19nZXRfYWNsKCdwZ19jbGFzcyc6OnJlZ2Ns
YXNzLCAnYXRlc3QyJzo6cmVnY2xhc3M6Om9pZCk7CiAKIFNFVCBTRVNTSU9OIEFVVEhPUkla
QVRJT04gcmVncmVzc19wcml2X3VzZXIyOwogU0VMRUNUIHNlc3Npb25fdXNlciwgY3VycmVu
dF91c2VyOwotLSAKMi40NS4xCgo=

--928850a6996e4f019140df59379ac887--