Hi Ranier, Thanks for looking at this.
I've double-checked the patch I sent, and it works fine. I think I know the cause of your problem: Since this is a catalog change, you need to run `make clean`, to ensure the catalog is rebuilt, followed by the usual `make && make install`. You also need to run `initdb` to create a new database cluster, with the new catalog version. Let me know if you need more specific instructions. Best, Joel On Wed, Jun 19, 2024, at 14:59, Ranier Vilela wrote: > Em qua., 19 de jun. de 2024 às 08:35, Joel Jacobson <j...@compiler.org> > escreveu: >> 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. > Hi, > For some reason, the function pg_get_acl, does not exist in generated > fmgrtab.c > > So, when install postgres, the function does not work. > > postgres=# SELECT pg_get_acl('pg_class'::regclass, > 'atest2'::regclass::oid); > ERROR: function pg_get_acl(regclass, oid) does not exist > LINE 1: SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::... > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > best regards, > Ranier Vilela -- Kind regards, Joel