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