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-ff240cb9c...@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
0001-Add-pg_get_acl.patch
Description: Binary data