Add pg_get_acl() function get the ACL for a database object

Started by Joel Jacobsonover 1 year ago23 messages

joel@compiler.org

over 1 year ago

1 attachment(s)

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:
/messages/by-id/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

Attachments:

0001-Add-pg_get_acl.patchapplication/octet-stream; name=0001-Add-pg_get_acl.patchDownload

From 63d39969585f628661110f16605be48bb19899b8 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Wed, 19 Jun 2024 12:31:21 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 16 +++++++++
 src/backend/catalog/objectaddress.c      | 43 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 ++++
 src/test/regress/expected/privileges.out |  7 ++++
 src/test/regress/sql/privileges.sql      |  2 ++
 5 files changed, 74 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..fa6fdce517 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,22 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..0579b66ab7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,49 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	AttrNumber	Anum_oid = get_object_attnum_oid(catalogId);
+	AttrNumber	Anum_acl = get_object_attnum_acl(catalogId);
+	Relation	rel;
+	HeapTuple	tup;
+	Datum		datum;
+	bool		isnull;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	rel = table_open(catalogId, AccessShareLock);
+
+	tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+	if (tup == NULL)
+		elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+			 objectId, RelationGetRelationName(rel));
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		if (!isnull)
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_DATUM(datum);
+		}
+	}
+
+	table_close(rel, AccessShareLock);
+	PG_RETURN_NULL();
+}
+
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7ada124265 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,13 @@ GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
     session_user    |    current_user    
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..36f348051e 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -190,6 +190,8 @@ GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
-- 
2.45.1

Ranier Vilela

ranier.vf@gmail.com

over 1 year ago

In reply to: Joel Jacobson (#1)

Re: Add pg_get_acl() function get the ACL for a database object

Em qua., 19 de jun. de 2024 às 08:35, Joel Jacobson <joel@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:
/messages/by-id/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.

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

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Ranier Vilela (#2)

Re: Add pg_get_acl() function get the ACL for a database object

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 <joel@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:
/messages/by-id/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.

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

Ranier Vilela

ranier.vf@gmail.com

over 1 year ago

In reply to: Joel Jacobson (#3)

Re: Add pg_get_acl() function get the ACL for a database object

Em qua., 19 de jun. de 2024 às 10:26, Joel Jacobson <joel@compiler.org>
escreveu:

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.

Sorry, sorry but I'm on Windows -> meson.

Double checked with:
ninja clean
ninja
ninja install

best regards,
Ranier Vilela

Ranier Vilela

ranier.vf@gmail.com

over 1 year ago

In reply to: Ranier Vilela (#4)

Re: Add pg_get_acl() function get the ACL for a database object

Em qua., 19 de jun. de 2024 às 10:28, Ranier Vilela <ranier.vf@gmail.com>
escreveu:

Em qua., 19 de jun. de 2024 às 10:26, Joel Jacobson <joel@compiler.org>
escreveu:

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.

Sorry, sorry but I'm on Windows -> meson.

Double checked with:
ninja clean
ninja
ninja install

Sorry for the noise, now pg_get_acl is shown in the regress test.

Regarding the patch, could it be written in the following style?

Datum
pg_get_acl(PG_FUNCTION_ARGS)
{
Oid classId = PG_GETARG_OID(0);
Oid objectId = PG_GETARG_OID(1);
Oid catalogId;
AttrNumber Anum_oid;
AttrNumber Anum_acl;

/* for "pinned" items in pg_depend, return null */
if (!OidIsValid(classId) && !OidIsValid(objectId))
PG_RETURN_NULL();

catalogId = (classId == LargeObjectRelationId) ?
LargeObjectMetadataRelationId : classId;
Anum_oid = get_object_attnum_oid(catalogId);
Anum_acl = get_object_attnum_acl(catalogId);

if (Anum_acl != InvalidAttrNumber)
{
Relation rel;
HeapTuple tup;
Datum datum;
bool isnull;

rel = table_open(catalogId, AccessShareLock);

tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
objectId, RelationGetRelationName(rel));

datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);

table_close(rel, AccessShareLock);

if (!isnull)
PG_RETURN_DATUM(datum);
}

PG_RETURN_NULL();
}

best regards,
Ranier Vilela

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Ranier Vilela (#5)

1 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Wed, Jun 19, 2024, at 15:51, Ranier Vilela wrote:

Regarding the patch, could it be written in the following style?

Thanks for nice improvement. New version attached.

Best,
Joel

Attachments:

v2-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v2-0001-Add-pg_get_acl.patchDownload

From 2639b778e84169aab8f42dc63af125d66f08392c Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Wed, 19 Jun 2024 12:31:21 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 16 +++++++++
 src/backend/catalog/objectaddress.c      | 45 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 ++++
 src/test/regress/expected/privileges.out |  7 ++++
 src/test/regress/sql/privileges.sql      |  2 ++
 5 files changed, 76 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..fa6fdce517 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,22 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..069e35fe9a 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,51 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+			elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+				objectId, RelationGetRelationName(rel));
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7ada124265 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,13 @@ GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
     session_user    |    current_user    
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..36f348051e 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -190,6 +190,8 @@ GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
-- 
2.45.1

Isaac Morland

isaac.morland@gmail.com

over 1 year ago

In reply to: Joel Jacobson (#1)

Re: Add pg_get_acl() function get the ACL for a database object

On Wed, 19 Jun 2024 at 07:35, Joel Jacobson <joel@compiler.org> wrote:

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.

I have no idea how often this would be useful, but I wonder if it could
work to have overloaded single-parameter versions for each of regprocedure
(pg_proc.proacl), regclass (pg_class.relacl), …. To call, just cast the OID
to the appropriate reg* type.

For example: To get the ACL for table 'example_table', call pg_get_acl
('example_table'::regclass)

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Isaac Morland (#7)

1 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Wed, Jun 19, 2024, at 16:23, Isaac Morland wrote:

I have no idea how often this would be useful, but I wonder if it could
work to have overloaded single-parameter versions for each of
regprocedure (pg_proc.proacl), regclass (pg_class.relacl), …. To call,
just cast the OID to the appropriate reg* type.

For example: To get the ACL for table 'example_table', call pg_get_acl
('example_table'::regclass)

New patch attached.

I've added overloaded versions for regclass and regproc so far:

/Joel

Attachments:

v3-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v3-0001-Add-pg_get_acl.patchDownload

From 2a5d5f24ed555ffbc58537cc930a00765ed2f25d Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Wed, 19 Jun 2024 12:31:21 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 44 ++++++++++++++++
 src/backend/catalog/objectaddress.c      | 65 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          | 18 +++++++
 src/test/regress/expected/privileges.out | 20 ++++++++
 src/test/regress/sql/privileges.sql      |  6 +++
 5 files changed, 153 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..69b8c67bd8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,50 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regclass</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a table, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regproc</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a function, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..4c242b2c41 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,71 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+			elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+				objectId, RelationGetRelationName(rel));
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regclass type.
+ */
+Datum
+pg_get_acl_regclass(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	PG_RETURN_DATUM(DirectFunctionCall2(pg_get_acl, ObjectIdGetDatum(RelationRelationId), ObjectIdGetDatum(objectId)));
+}
+
+/*
+ * Overloaded pg_get_acl function for regproc type.
+ */
+Datum
+pg_get_acl_regproc(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	PG_RETURN_DATUM(DirectFunctionCall2(pg_get_acl, ObjectIdGetDatum(ProcedureRelationId), ObjectIdGetDatum(objectId)));
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..7f419bac79 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,24 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
+{ oid => '6348', descr => 'get ACL for SQL object of regclass type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regclass',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regclass' },
+
+{ oid => '6349', descr => 'get ACL for SQL object of regproc type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regproc',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regproc' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..eb9acbe775 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,19 @@ GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
+SELECT pg_get_acl('atest2'::regclass);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
     session_user    |    current_user    
@@ -1139,6 +1152,13 @@ CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+-- test pg_get_acl() for regproc type
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=X/regress_priv_user1,regress_priv_user1=X/regress_priv_user1,regress_priv_user3=X/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
  priv_testfunc1 | priv_testfunc2 
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..8a15fbdb2f 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -190,6 +190,9 @@ GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
@@ -822,6 +825,9 @@ CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   LANGUAGE sql SECURITY DEFINER;
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
 
+-- test pg_get_acl() for regproc type
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
 CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
-- 
2.45.1

Michael Paquier

michael@paquier.xyz

over 1 year ago

In reply to: Joel Jacobson (#8)

Re: Add pg_get_acl() function get the ACL for a database object

On Thu, Jun 20, 2024 at 08:32:57AM +0200, Joel Jacobson wrote:

I've added overloaded versions for regclass and regproc so far:

\df pg_get_acl
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+------------------------+------
pg_catalog | pg_get_acl | aclitem[] | classid oid, objid oid | func
pg_catalog | pg_get_acl | aclitem[] | objid regclass | func
pg_catalog | pg_get_acl | aclitem[] | objid regproc | func
(3 rows)

Interesting idea.

I am not really convinced that the regproc and regclass overloads are
really necessary, considering the fact that one of the goals
mentioned, as far as I understand, is to be able to get an idea of the
ACLs associated to an object with its dependencies in pg_depend and/or
pg_shdepend. Another one is to reduce the JOIN burden when querying
a set of them, like attribute ACLs.

Perhaps the documentation should add one or two examples to show this
point?

+        tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+        if (!HeapTupleIsValid(tup))
+            elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+                objectId, RelationGetRelationName(rel));

get_catalog_object_by_oid() is handled differently here than in
functions line pg_identify_object(). Shouldn't we return NULL for
this case? That would be more useful when using this function with
one or more large scans.
--
Michael

#10

Tom Lane

tgl@sss.pgh.pa.us

over 1 year ago

In reply to: Michael Paquier (#9)

Re: Add pg_get_acl() function get the ACL for a database object

Michael Paquier <michael@paquier.xyz> writes:

On Thu, Jun 20, 2024 at 08:32:57AM +0200, Joel Jacobson wrote:

I've added overloaded versions for regclass and regproc so far:

\df pg_get_acl
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+------------------------+------
pg_catalog | pg_get_acl | aclitem[] | classid oid, objid oid | func
pg_catalog | pg_get_acl | aclitem[] | objid regclass | func
pg_catalog | pg_get_acl | aclitem[] | objid regproc | func
(3 rows)

Interesting idea.

Doesn't that result in "cannot resolve ambiguous function call"
failures?

regards, tom lane

#11

Isaac Morland

isaac.morland@gmail.com

over 1 year ago

In reply to: Joel Jacobson (#8)

Re: Add pg_get_acl() function get the ACL for a database object

On Thu, 20 Jun 2024 at 02:33, Joel Jacobson <joel@compiler.org> wrote:

On Wed, Jun 19, 2024, at 16:23, Isaac Morland wrote:

I have no idea how often this would be useful, but I wonder if it could
work to have overloaded single-parameter versions for each of
regprocedure (pg_proc.proacl), regclass (pg_class.relacl), …. To call,
just cast the OID to the appropriate reg* type.

For example: To get the ACL for table 'example_table', call pg_get_acl
('example_table'::regclass)

+1

New patch attached.

I've added overloaded versions for regclass and regproc so far:

\df pg_get_acl
List of functions
Schema | Name | Result data type | Argument data types | Type

------------+------------+------------------+------------------------+------
pg_catalog | pg_get_acl | aclitem[] | classid oid, objid oid | func
pg_catalog | pg_get_acl | aclitem[] | objid regclass | func
pg_catalog | pg_get_acl | aclitem[] | objid regproc | func
(3 rows)

Those were just examples. I think for completeness there should be 5
overloads:

[input type] → [relation.aclattribute]
regproc/regprocedure → pg_proc.proacl
regtype → pg_type.typacl
regclass → pg_class.relacl
regnamespace → pg_namespace.nspacl

I believe the remaining reg* types don't correspond to objects with ACLs,
and the remaining ACL fields are for objects which don't have a
corresponding reg* type.

In general I believe the reg* types are underutilized. All over the place I
see examples where people write code to generate SQL statements and they
take schema and object name and then format with %I.%I when all that is
needed is a reg* value and then format it with a simple %s (of course, need
to make sure the SQL will execute with the same search_path as when the SQL
was generated, or generate with an empty search_path).

#12

Isaac Morland

isaac.morland@gmail.com

over 1 year ago

In reply to: Tom Lane (#10)

Re: Add pg_get_acl() function get the ACL for a database object

On Thu, 20 Jun 2024 at 23:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Paquier <michael@paquier.xyz> writes:

On Thu, Jun 20, 2024 at 08:32:57AM +0200, Joel Jacobson wrote:

I've added overloaded versions for regclass and regproc so far:

\df pg_get_acl
List of functions
Schema | Name | Result data type | Argument data types | Type

------------+------------+------------------+------------------------+------

pg_catalog | pg_get_acl | aclitem[] | classid oid, objid oid |

func

pg_catalog | pg_get_acl | aclitem[] | objid regclass |

func

pg_catalog | pg_get_acl | aclitem[] | objid regproc |

func

(3 rows)

Interesting idea.

Doesn't that result in "cannot resolve ambiguous function call"
failures?

If you try to pass an oid directly, as a value of type oid, you should get
"function is not unique". But if you cast a string or numeric value to the
appropriate reg* type for the object you are using, it should work fine.

I have functions which reset object permissions on all objects in a
specified schema back to the default state as if they had been freshly
created which rely on this. They work very well, and allow me to have a
privilege-granting script for each project which always fully resets all
the privileges back to a known state.

#13

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Isaac Morland (#12)

2 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Fri, Jun 21, 2024, at 05:25, Michael Paquier wrote:

Interesting idea.

I am not really convinced that the regproc and regclass overloads are
really necessary, considering the fact that one of the goals
mentioned, as far as I understand, is to be able to get an idea of the
ACLs associated to an object with its dependencies in pg_depend and/or
pg_shdepend. Another one is to reduce the JOIN burden when querying
a set of them, like attribute ACLs.

Overloads moved to a second patch, which can be applied
on top of the first one. I think they would be quite nice, but I could
also live without them.

Perhaps the documentation should add one or two examples to show this
point?

Good point, added.

+        tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+        if (!HeapTupleIsValid(tup))
+            elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+                objectId, RelationGetRelationName(rel));
get_catalog_object_by_oid() is handled differently here than in
functions line pg_identify_object(). Shouldn't we return NULL for
this case? That would be more useful when using this function with
one or more large scans.

Right, I've changed the patch accordingly.

On Fri, Jun 21, 2024, at 05:48, Isaac Morland wrote:

Those were just examples. I think for completeness there should be 5 overloads:

[input type] → [relation.aclattribute]
regproc/regprocedure → pg_proc.proacl
regtype → pg_type.typacl
regclass → pg_class.relacl
regnamespace → pg_namespace.nspacl

I believe the remaining reg* types don't correspond to objects with
ACLs, and the remaining ACL fields are for objects which don't have a
corresponding reg* type.

In general I believe the reg* types are underutilized. All over the
place I see examples where people write code to generate SQL statements
and they take schema and object name and then format with %I.%I when
all that is needed is a reg* value and then format it with a simple %s
(of course, need to make sure the SQL will execute with the same
search_path as when the SQL was generated, or generate with an empty
search_path).

I've added regtype and regnamespace overloads to the second patch.

On Fri, Jun 21, 2024, at 05:58, Isaac Morland wrote:

On Thu, 20 Jun 2024 at 23:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Doesn't that result in "cannot resolve ambiguous function call"
failures?

If you try to pass an oid directly, as a value of type oid, you should
get "function is not unique". But if you cast a string or numeric value
to the appropriate reg* type for the object you are using, it should
work fine.

Yes, I can confirm that's the case, it works fine when casting a string
to reg* type.

/Joel

Attachments:

v4-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v4-0001-Add-pg_get_acl.patchDownload

From 100bc5fec38843fde327bd8582a822d020744125 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Fri, 21 Jun 2024 08:38:19 +0200
Subject: [PATCH 1/2] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 26 +++++++++++++
 src/backend/catalog/objectaddress.c      | 47 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out |  7 ++++
 src/test/regress/sql/privileges.sql      |  2 +
 5 files changed, 88 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..3412228f97 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..5206e29df1 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,53 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7ada124265 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,13 @@ GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
     session_user    |    current_user    
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..36f348051e 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -190,6 +190,8 @@ GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
-- 
2.45.1

0002-Add-pg_get_acl-overloads.patchapplication/octet-stream; name="=?UTF-8?Q?0002-Add-pg=5Fget=5Facl-overloads.patch?="Download

From b37987fcf080e71c52e12ce3f5b8a9b53b1b645d Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Sat, 22 Jun 2024 02:36:34 +0200
Subject: [PATCH 2/2] Add pg_get_acl() overloads for reg* types

---
 doc/src/sgml/func.sgml                   |  56 ++++++++++++
 src/backend/catalog/objectaddress.c      | 103 ++++++++++++++++++++---
 src/include/catalog/pg_proc.dat          |  24 ++++++
 src/test/regress/expected/privileges.out |  62 +++++++++++++-
 src/test/regress/sql/privileges.sql      |  17 +++-
 5 files changed, 242 insertions(+), 20 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3412228f97..d892e35298 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26712,6 +26712,62 @@ WHERE s.deptype = 'a';
 </programlisting>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regclass</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a table, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regproc</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a function, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regtype</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a type, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regnamespace</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a schema, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 5206e29df1..39dd0d2a21 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4363,21 +4363,20 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 }
 
 /*
- * SQL-level callable function to obtain the Access Control List (ACL)
+ * Helper function to obtain the Access Control List (ACL)
  * of a specified object, given its catalog OID and object OID.
+ * Returns true if ACL is found, false otherwise.
  */
-Datum
-pg_get_acl(PG_FUNCTION_ARGS)
+static bool
+get_acl_internal(Oid classId, Oid objectId, Datum *datum)
 {
-	Oid			classId = PG_GETARG_OID(0);
-	Oid			objectId = PG_GETARG_OID(1);
-	Oid			catalogId;
-	AttrNumber	Anum_oid;
-	AttrNumber	Anum_acl;
+	Oid catalogId;
+	AttrNumber Anum_oid;
+	AttrNumber Anum_acl;
 
-	/* for "pinned" items in pg_depend, return null */
+	/* for "pinned" items in pg_depend, return false */
 	if (!OidIsValid(classId) && !OidIsValid(objectId))
-		PG_RETURN_NULL();
+		return false;
 
 	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
 	Anum_oid = get_object_attnum_oid(catalogId);
@@ -4387,7 +4386,6 @@ pg_get_acl(PG_FUNCTION_ARGS)
 	{
 		Relation rel;
 		HeapTuple tup;
-		Datum datum;
 		bool isnull;
 
 		rel = table_open(catalogId, AccessShareLock);
@@ -4396,16 +4394,93 @@ pg_get_acl(PG_FUNCTION_ARGS)
 		if (!HeapTupleIsValid(tup))
 		{
 			table_close(rel, AccessShareLock);
-			PG_RETURN_NULL();
+			return false;
 		}
 
-		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		*datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
 		table_close(rel, AccessShareLock);
 
 		if (!isnull)
-			PG_RETURN_DATUM(datum);
+			return true;
 	}
 
+	return false;
+}
+
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid classId = PG_GETARG_OID(0);
+	Oid objectId = PG_GETARG_OID(1);
+	Datum datum;
+
+	if (get_acl_internal(classId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regclass type.
+ */
+Datum
+pg_get_acl_regclass(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(RelationRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regproc type.
+ */
+Datum
+pg_get_acl_regproc(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(ProcedureRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regtype type.
+ */
+Datum
+pg_get_acl_regtype(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(TypeRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regnamespace type.
+ */
+Datum
+pg_get_acl_regnamespace(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(NamespaceRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
 	PG_RETURN_NULL();
 }
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5ab9b11b47..38981e0573 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6368,6 +6368,30 @@
   proargnames => '{classid,objid}',
   prosrc => 'pg_get_acl' },
 
+{ oid => '6348', descr => 'get ACL for SQL object of regclass type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regclass',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regclass' },
+
+{ oid => '6349', descr => 'get ACL for SQL object of regproc type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regproc',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regproc' },
+
+{ oid => '6350', descr => 'get ACL for SQL object of regtype type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regtype',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regtype' },
+
+{ oid => '6351', descr => 'get ACL for SQL object of regnamespace type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regnamespace',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regnamespace' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 7ada124265..bd1edfcecc 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,19 +213,36 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+SELECT pg_get_acl('atest2'::regclass);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
-GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
-ERROR:  grantor must be current user
--- test pg_get_acl()
 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
                                                                                                     pg_get_acl                                                                                                    
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
 (1 row)
 
+SELECT pg_get_acl('atest2'::regclass);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
+GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
+ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
     session_user    |    current_user    
@@ -1145,7 +1162,19 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=X/regress_priv_user1,regress_priv_user1=X/regress_priv_user1,regress_priv_user3=X/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
  priv_testfunc1 | priv_testfunc2 
@@ -1236,6 +1265,20 @@ REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
 GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
 GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=U/regress_priv_user1,regress_priv_user1=U/regress_priv_user1,regress_priv_user2=U/regress_priv_user1}
+(1 row)
+
 -- commands that should fail
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
 ERROR:  permission denied for type priv_testdomain1
@@ -2612,6 +2655,19 @@ CREATE ROLE regress_schemauser1 superuser login;
 CREATE ROLE regress_schemauser2 superuser login;
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
+                                       pg_get_acl                                       
+----------------------------------------------------------------------------------------
+ {regress_schemauser1=UC/regress_schemauser1,regress_schemauser2=U/regress_schemauser1}
+(1 row)
+
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
  nspname |       rolname       
 ---------+---------------------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 36f348051e..09d726f412 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,16 +183,17 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
--- test pg_get_acl()
-SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
-
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
 
@@ -822,7 +823,9 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
@@ -879,6 +882,11 @@ GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 
 SET SESSION AUTHORIZATION regress_priv_user1;
 
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+
 -- commands that should fail
 
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
@@ -1627,6 +1635,9 @@ CREATE ROLE regress_schemauser2 superuser login;
 
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
 
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
 
-- 
2.45.1

#14

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Joel Jacobson (#13)

2 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Sat, Jun 22, 2024, at 02:54, Joel Jacobson wrote:

Attachments:
* v4-0001-Add-pg_get_acl.patch
* 0002-Add-pg_get_acl-overloads.patch

Rebase and reduced diff for src/test/regress/sql/privileges.sql between patches.

/Joel

Attachments:

v5-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v5-0001-Add-pg_get_acl.patchDownload

From 6f120d7906eec9644d0670499fbcea1dbfe73031 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Fri, 21 Jun 2024 08:38:19 +0200
Subject: [PATCH 1/2] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 26 +++++++++++++
 src/backend/catalog/objectaddress.c      | 47 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out | 12 ++++++
 src/test/regress/sql/privileges.sql      |  2 +
 5 files changed, 93 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..3412228f97 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..5206e29df1 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,53 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..f82338354e 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,10 +213,22 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..eddb597f87 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,10 +183,12 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-- 
2.45.1

v2-0002-Add-pg_get_acl-overloads.patchapplication/octet-stream; name="=?UTF-8?Q?v2-0002-Add-pg=5Fget=5Facl-overloads.patch?="Download

From e46ac35ca28e604aa2ee113249dc6ae759fec642 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Sat, 22 Jun 2024 11:40:45 +0200
Subject: [PATCH 2/2] Add pg_get_acl() overloads for reg* types

---
 doc/src/sgml/func.sgml                   |  56 ++++++++++++
 src/backend/catalog/objectaddress.c      | 103 ++++++++++++++++++++---
 src/include/catalog/pg_proc.dat          |  24 ++++++
 src/test/regress/expected/privileges.out |  51 +++++++++++
 src/test/regress/sql/privileges.sql      |  13 ++-
 5 files changed, 232 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3412228f97..d892e35298 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26712,6 +26712,62 @@ WHERE s.deptype = 'a';
 </programlisting>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regclass</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a table, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regproc</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a function, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regtype</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a type, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regnamespace</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a schema, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 5206e29df1..39dd0d2a21 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4363,21 +4363,20 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 }
 
 /*
- * SQL-level callable function to obtain the Access Control List (ACL)
+ * Helper function to obtain the Access Control List (ACL)
  * of a specified object, given its catalog OID and object OID.
+ * Returns true if ACL is found, false otherwise.
  */
-Datum
-pg_get_acl(PG_FUNCTION_ARGS)
+static bool
+get_acl_internal(Oid classId, Oid objectId, Datum *datum)
 {
-	Oid			classId = PG_GETARG_OID(0);
-	Oid			objectId = PG_GETARG_OID(1);
-	Oid			catalogId;
-	AttrNumber	Anum_oid;
-	AttrNumber	Anum_acl;
+	Oid catalogId;
+	AttrNumber Anum_oid;
+	AttrNumber Anum_acl;
 
-	/* for "pinned" items in pg_depend, return null */
+	/* for "pinned" items in pg_depend, return false */
 	if (!OidIsValid(classId) && !OidIsValid(objectId))
-		PG_RETURN_NULL();
+		return false;
 
 	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
 	Anum_oid = get_object_attnum_oid(catalogId);
@@ -4387,7 +4386,6 @@ pg_get_acl(PG_FUNCTION_ARGS)
 	{
 		Relation rel;
 		HeapTuple tup;
-		Datum datum;
 		bool isnull;
 
 		rel = table_open(catalogId, AccessShareLock);
@@ -4396,16 +4394,93 @@ pg_get_acl(PG_FUNCTION_ARGS)
 		if (!HeapTupleIsValid(tup))
 		{
 			table_close(rel, AccessShareLock);
-			PG_RETURN_NULL();
+			return false;
 		}
 
-		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		*datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
 		table_close(rel, AccessShareLock);
 
 		if (!isnull)
-			PG_RETURN_DATUM(datum);
+			return true;
 	}
 
+	return false;
+}
+
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid classId = PG_GETARG_OID(0);
+	Oid objectId = PG_GETARG_OID(1);
+	Datum datum;
+
+	if (get_acl_internal(classId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regclass type.
+ */
+Datum
+pg_get_acl_regclass(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(RelationRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regproc type.
+ */
+Datum
+pg_get_acl_regproc(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(ProcedureRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regtype type.
+ */
+Datum
+pg_get_acl_regtype(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(TypeRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regnamespace type.
+ */
+Datum
+pg_get_acl_regnamespace(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(NamespaceRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
 	PG_RETURN_NULL();
 }
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5ab9b11b47..38981e0573 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6368,6 +6368,30 @@
   proargnames => '{classid,objid}',
   prosrc => 'pg_get_acl' },
 
+{ oid => '6348', descr => 'get ACL for SQL object of regclass type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regclass',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regclass' },
+
+{ oid => '6349', descr => 'get ACL for SQL object of regproc type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regproc',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regproc' },
+
+{ oid => '6350', descr => 'get ACL for SQL object of regtype type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regtype',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regtype' },
+
+{ oid => '6351', descr => 'get ACL for SQL object of regnamespace type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regnamespace',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regnamespace' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index f82338354e..bd1edfcecc 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,12 @@ SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
  
 (1 row)
 
+SELECT pg_get_acl('atest2'::regclass);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
@@ -229,6 +235,12 @@ SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
  {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
 (1 row)
 
+SELECT pg_get_acl('atest2'::regclass);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
@@ -1150,7 +1162,19 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=X/regress_priv_user1,regress_priv_user1=X/regress_priv_user1,regress_priv_user3=X/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
  priv_testfunc1 | priv_testfunc2 
@@ -1241,6 +1265,20 @@ REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
 GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
 GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=U/regress_priv_user1,regress_priv_user1=U/regress_priv_user1,regress_priv_user2=U/regress_priv_user1}
+(1 row)
+
 -- commands that should fail
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
 ERROR:  permission denied for type priv_testdomain1
@@ -2617,6 +2655,19 @@ CREATE ROLE regress_schemauser1 superuser login;
 CREATE ROLE regress_schemauser2 superuser login;
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
+                                       pg_get_acl                                       
+----------------------------------------------------------------------------------------
+ {regress_schemauser1=UC/regress_schemauser1,regress_schemauser2=U/regress_schemauser1}
+(1 row)
+
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
  nspname |       rolname       
 ---------+---------------------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eddb597f87..09d726f412 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -184,15 +184,16 @@ SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
 
@@ -822,7 +823,9 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
@@ -879,6 +882,11 @@ GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 
 SET SESSION AUTHORIZATION regress_priv_user1;
 
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+
 -- commands that should fail
 
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
@@ -1627,6 +1635,9 @@ CREATE ROLE regress_schemauser2 superuser login;
 
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
 
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
 
-- 
2.45.1

#15

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Joel Jacobson (#14)

2 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Sat, Jun 22, 2024, at 11:44, Joel Jacobson wrote:

* v5-0001-Add-pg_get_acl.patch
* v2-0002-Add-pg_get_acl-overloads.patch

Rename files to ensure cfbot applies them in order; both need to have same version prefix.

Attachments:

v6-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v6-0001-Add-pg_get_acl.patchDownload

From 334b08a96d18bd328ce2acf07c40658040eb2694 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Fri, 21 Jun 2024 08:38:19 +0200
Subject: [PATCH 1/2] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 26 +++++++++++++
 src/backend/catalog/objectaddress.c      | 47 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out | 12 ++++++
 src/test/regress/sql/privileges.sql      |  2 +
 5 files changed, 93 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..3412228f97 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..5206e29df1 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,53 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..f82338354e 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,10 +213,22 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..eddb597f87 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,10 +183,12 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-- 
2.45.1

v6-0002-Add-pg_get_acl-overloads.patchapplication/octet-stream; name="=?UTF-8?Q?v6-0002-Add-pg=5Fget=5Facl-overloads.patch?="Download

From 842badc6f54070b24d1845ece3c7a4cb54a71b35 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Sat, 22 Jun 2024 11:40:45 +0200
Subject: [PATCH 2/2] Add pg_get_acl() overloads for reg* types

---
 doc/src/sgml/func.sgml                   |  56 ++++++++++++
 src/backend/catalog/objectaddress.c      | 103 ++++++++++++++++++++---
 src/include/catalog/pg_proc.dat          |  24 ++++++
 src/test/regress/expected/privileges.out |  51 +++++++++++
 src/test/regress/sql/privileges.sql      |  13 ++-
 5 files changed, 232 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3412228f97..d892e35298 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26712,6 +26712,62 @@ WHERE s.deptype = 'a';
 </programlisting>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regclass</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a table, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regproc</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a function, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regtype</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a type, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>objid</parameter> <type>regnamespace</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the ACL for a schema, specified by object OID.
+        This function returns NULL values for undefined objects.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 5206e29df1..39dd0d2a21 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4363,21 +4363,20 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 }
 
 /*
- * SQL-level callable function to obtain the Access Control List (ACL)
+ * Helper function to obtain the Access Control List (ACL)
  * of a specified object, given its catalog OID and object OID.
+ * Returns true if ACL is found, false otherwise.
  */
-Datum
-pg_get_acl(PG_FUNCTION_ARGS)
+static bool
+get_acl_internal(Oid classId, Oid objectId, Datum *datum)
 {
-	Oid			classId = PG_GETARG_OID(0);
-	Oid			objectId = PG_GETARG_OID(1);
-	Oid			catalogId;
-	AttrNumber	Anum_oid;
-	AttrNumber	Anum_acl;
+	Oid catalogId;
+	AttrNumber Anum_oid;
+	AttrNumber Anum_acl;
 
-	/* for "pinned" items in pg_depend, return null */
+	/* for "pinned" items in pg_depend, return false */
 	if (!OidIsValid(classId) && !OidIsValid(objectId))
-		PG_RETURN_NULL();
+		return false;
 
 	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
 	Anum_oid = get_object_attnum_oid(catalogId);
@@ -4387,7 +4386,6 @@ pg_get_acl(PG_FUNCTION_ARGS)
 	{
 		Relation rel;
 		HeapTuple tup;
-		Datum datum;
 		bool isnull;
 
 		rel = table_open(catalogId, AccessShareLock);
@@ -4396,16 +4394,93 @@ pg_get_acl(PG_FUNCTION_ARGS)
 		if (!HeapTupleIsValid(tup))
 		{
 			table_close(rel, AccessShareLock);
-			PG_RETURN_NULL();
+			return false;
 		}
 
-		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		*datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
 		table_close(rel, AccessShareLock);
 
 		if (!isnull)
-			PG_RETURN_DATUM(datum);
+			return true;
 	}
 
+	return false;
+}
+
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid classId = PG_GETARG_OID(0);
+	Oid objectId = PG_GETARG_OID(1);
+	Datum datum;
+
+	if (get_acl_internal(classId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regclass type.
+ */
+Datum
+pg_get_acl_regclass(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(RelationRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regproc type.
+ */
+Datum
+pg_get_acl_regproc(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(ProcedureRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regtype type.
+ */
+Datum
+pg_get_acl_regtype(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(TypeRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
+	PG_RETURN_NULL();
+}
+
+/*
+ * Overloaded pg_get_acl function for regnamespace type.
+ */
+Datum
+pg_get_acl_regnamespace(PG_FUNCTION_ARGS)
+{
+	Oid objectId = PG_GETARG_OID(0);
+	Datum datum;
+
+	if (get_acl_internal(NamespaceRelationId, objectId, &datum))
+		PG_RETURN_DATUM(datum);
+
 	PG_RETURN_NULL();
 }
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5ab9b11b47..38981e0573 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6368,6 +6368,30 @@
   proargnames => '{classid,objid}',
   prosrc => 'pg_get_acl' },
 
+{ oid => '6348', descr => 'get ACL for SQL object of regclass type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regclass',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regclass' },
+
+{ oid => '6349', descr => 'get ACL for SQL object of regproc type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regproc',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regproc' },
+
+{ oid => '6350', descr => 'get ACL for SQL object of regtype type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regtype',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regtype' },
+
+{ oid => '6351', descr => 'get ACL for SQL object of regnamespace type',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'regnamespace',
+  proargnames => '{objid}',
+  prosrc => 'pg_get_acl_regnamespace' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index f82338354e..bd1edfcecc 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,12 @@ SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
  
 (1 row)
 
+SELECT pg_get_acl('atest2'::regclass);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
@@ -229,6 +235,12 @@ SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
  {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
 (1 row)
 
+SELECT pg_get_acl('atest2'::regclass);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
@@ -1150,7 +1162,19 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=X/regress_priv_user1,regress_priv_user1=X/regress_priv_user1,regress_priv_user3=X/regress_priv_user1}
+(1 row)
+
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
  priv_testfunc1 | priv_testfunc2 
@@ -1241,6 +1265,20 @@ REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
 GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
 GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+                                               pg_get_acl                                                
+---------------------------------------------------------------------------------------------------------
+ {=U/regress_priv_user1,regress_priv_user1=U/regress_priv_user1,regress_priv_user2=U/regress_priv_user1}
+(1 row)
+
 -- commands that should fail
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
 ERROR:  permission denied for type priv_testdomain1
@@ -2617,6 +2655,19 @@ CREATE ROLE regress_schemauser1 superuser login;
 CREATE ROLE regress_schemauser2 superuser login;
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
+                                       pg_get_acl                                       
+----------------------------------------------------------------------------------------
+ {regress_schemauser1=UC/regress_schemauser1,regress_schemauser2=U/regress_schemauser1}
+(1 row)
+
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
  nspname |       rolname       
 ---------+---------------------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eddb597f87..09d726f412 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -184,15 +184,16 @@ SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+SELECT pg_get_acl('atest2'::regclass);
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT session_user, current_user;
 
@@ -822,7 +823,9 @@ GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
   AS 'select col1 from atest2 where col2 = $1;'
   LANGUAGE sql SECURITY DEFINER;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SELECT pg_get_acl('priv_testfunc4'::regproc);
 
 SET SESSION AUTHORIZATION regress_priv_user2;
 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
@@ -879,6 +882,11 @@ GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
 
 SET SESSION AUTHORIZATION regress_priv_user1;
 
+CREATE TYPE priv_testtype2 AS (a int, b text);
+SELECT pg_get_acl('priv_testtype2'::regtype);
+GRANT USAGE ON TYPE priv_testtype2 TO regress_priv_user2;
+SELECT pg_get_acl('priv_testtype2'::regtype);
+
 -- commands that should fail
 
 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
@@ -1627,6 +1635,9 @@ CREATE ROLE regress_schemauser2 superuser login;
 
 SET SESSION ROLE regress_schemauser1;
 CREATE SCHEMA testns;
+SELECT pg_get_acl('testns'::regnamespace);
+GRANT USAGE ON SCHEMA testns TO regress_schemauser2;
+SELECT pg_get_acl('testns'::regnamespace);
 
 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
 
-- 
2.45.1

#16

Michael Paquier

michael@paquier.xyz

over 1 year ago

In reply to: Joel Jacobson (#15)

Re: Add pg_get_acl() function get the ACL for a database object

On Sun, Jun 23, 2024 at 08:48:46AM +0200, Joel Jacobson wrote:

On Sat, Jun 22, 2024, at 11:44, Joel Jacobson wrote:

* v5-0001-Add-pg_get_acl.patch
* v2-0002-Add-pg_get_acl-overloads.patch

Rename files to ensure cfbot applies them in order; both need to
have same version prefix.

+       <para>
+        Returns the Access Control List (ACL) for a database object,
+        specified by catalog OID and object OID.

Rather unrelated to this patch, still this patch makes the situation
more complicated in the docs, but wouldn't it be better to add ACL as
a term in acronyms.sql, and reuse it here? It would be a doc-only
patch that applies on top of the rest (could be on a new thread of its
own), with some <acronym> markups added where needed.

+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';

Could be a bit prettier. That's a good addition.

+ catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;

Indeed, and we need to live with this tweak as per the reason in
inv_api.c related to clients, so that's fine. Still a comment is
adapted for this particular case?

+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)

How about adding a bit more coverage? I'd suggest the following
additions:
- class ID as 0 in input.
- object ID as 0 in input.
- Both class and object ID as 0 in input.

+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                                                    pg_get_acl                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)

This is hard to parse. I would add an unnest() and order the entries
so as modifications are easier to catch, with a more predictible
result.

FWIW, I'm still a bit meh with the addition of the functions
overloading the arguments with reg inputs. I'm OK with that when we
know that the input would be of a given object type, like
pg_partition_ancestors or pg_partition_tree, but for a case as generic
as this one this is less appealing to me.
--
Michael

#17

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Michael Paquier (#16)

1 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Mon, Jun 24, 2024, at 01:46, Michael Paquier wrote:

Rather unrelated to this patch, still this patch makes the situation
more complicated in the docs, but wouldn't it be better to add ACL as
a term in acronyms.sql, and reuse it here? It would be a doc-only
patch that applies on top of the rest (could be on a new thread of its
own), with some <acronym> markups added where needed.

Good idea, I've started a separate thread for this:

/messages/by-id/9253b872-dbb1-42a6-a79e-b1e96effc857@app.fastmail.com

This patch now assumes <acronym>ACL</acronym> will be supported.

+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND 
d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid 
= 'pg_authid'::regclass
+WHERE s.deptype = 'a';

Could be a bit prettier. That's a good addition.

How could we make it prettier?

+ catalogId = (classId == LargeObjectRelationId) ?
LargeObjectMetadataRelationId : classId;

Indeed, and we need to live with this tweak as per the reason in
inv_api.c related to clients, so that's fine. Still a comment is
adapted for this particular case?

Thanks, fixed.

How about adding a bit more coverage? I'd suggest the following
additions:

Thanks, good idea. I've added the tests,
but need some help reasoning if the output is expected:

- class ID as 0 in input.

SELECT pg_get_acl(0, 'atest2'::regclass::oid);
ERROR: unrecognized class ID: 0

I believe we want an error here, since: an invalid class ID,
like 0, or any other invalid OID, should raise an error,
since classes can't be dropped, so we should never
expect an invalid OID for a class ID.
Please correct me if this reasoning is incorrect.

- object ID as 0 in input.

SELECT pg_get_acl('pg_class'::regclass, 0);

This returns null, which I believe it should,
since the OID for a database object could
be invalid due to having being dropped concurrently.

- Both class and object ID as 0 in input.

This returns null, but I'm not sure I think this is correct?
Since if the class ID is zero, i.e. incorrect, that is unexpected,
and wouldn't we want to throw an error in that case,
just like if only the class ID is invalid?

+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+                                                                       
pg_get_acl

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 
{regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)

This is hard to parse. I would add an unnest() and order the entries
so as modifications are easier to catch, with a more predictible
result.

Thanks, much better, fixed.

FWIW, I'm still a bit meh with the addition of the functions
overloading the arguments with reg inputs. I'm OK with that when we
know that the input would be of a given object type, like
pg_partition_ancestors or pg_partition_tree, but for a case as generic
as this one this is less appealing to me.

I've looked at other occurrences of "<type>reg" in func.sgml,
and I now agree with you we should skip the overloads,
since adding them would seem unconventional.

/Joel

Attachments:

v7-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v7-0001-Add-pg_get_acl.patchDownload

From 5efb7cdc5247fc9bfa82a86ecf7eb0192bf0f1a0 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Tue, 25 Jun 2024 00:42:47 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the ACL for a database object,
specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 26 +++++++++++++
 src/backend/catalog/objectaddress.c      | 48 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out | 31 +++++++++++++++
 src/test/regress/sql/privileges.sql      |  7 ++++
 5 files changed, 118 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..d02a45ad52 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the <acronym>ACL</acronym> for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns NULL values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..45e3974d83 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,54 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the ACL of a specified object, given
+ * its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	/* For large objects, the catalog to modify is pg_largeobject_metadata */
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..342c466d64 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,10 +213,41 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+                     unnest                     
+------------------------------------------------
+ regress_priv_user1=arwdDxtm/regress_priv_user1
+ regress_priv_user2=r/regress_priv_user1
+ regress_priv_user3=w/regress_priv_user1
+ regress_priv_user4=a/regress_priv_user1
+ regress_priv_user5=D/regress_priv_user1
+(5 rows)
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+ERROR:  unrecognized class ID: 0
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+SELECT pg_get_acl(0, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..e1fc43ea54 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,10 +183,17 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+SELECT pg_get_acl(0, 0); -- null
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-- 
2.45.1

#18

Michael Paquier

michael@paquier.xyz

over 1 year ago

In reply to: Joel Jacobson (#17)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jun 25, 2024 at 01:21:14AM +0200, Joel Jacobson wrote:

Good idea, I've started a separate thread for this:

/messages/by-id/9253b872-dbb1-42a6-a79e-b1e96effc857@app.fastmail.com

This patch now assumes <acronym>ACL</acronym> will be supported.

Thanks for doing that! That helps in making reviews easier to follow
for all, attracting the correct audience when necessary.

+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND 
d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid 
= 'pg_authid'::regclass
+WHERE s.deptype = 'a';

Could be a bit prettier. That's a good addition.

How could we make it prettier?

Perhaps split the two JOIN conditions into two lines each, with a bit
more indentation to make it render better? Usually I handle that on a
case-by-case basis while preparing a patch for commit. I'm OK to edit
that myself with some final touches, FWIW. Depending on the input
this shows, I'd also look at some LATERAL business, that can be
cleaner in some cases for the docs.

How about adding a bit more coverage? I'd suggest the following
additions:

Thanks, good idea. I've added the tests,
but need some help reasoning if the output is expected:

Total coverage sounds good here.

- class ID as 0 in input.

SELECT pg_get_acl(0, 'atest2'::regclass::oid);
ERROR: unrecognized class ID: 0

I believe we want an error here, since: an invalid class ID,
like 0, or any other invalid OID, should raise an error,
since classes can't be dropped, so we should never
expect an invalid OID for a class ID.
Please correct me if this reasoning is incorrect.

This is an internal error, so it should never be visible to the end
user via SQL because it is an unexpected state. See for example
2a10fdc4307a, which is similar to what you are doing here.

- object ID as 0 in input.

SELECT pg_get_acl('pg_class'::regclass, 0);

This returns null, which I believe it should,
since the OID for a database object could
be invalid due to having being dropped concurrently.

That's right. It would be sad for monitoring queries doing large
scans of pg_depend or pg_shdepend to fail in obstructive ways because
of concurrent object drops, because we'd lose information about all
the other objects because of at least one object gone at the moment
where pg_get_acl() is called for its OID retrieved previously.

- Both class and object ID as 0 in input.

This returns null, but I'm not sure I think this is correct?
Since if the class ID is zero, i.e. incorrect, that is unexpected,
and wouldn't we want to throw an error in that case,
just like if only the class ID is invalid?

NULL is the correct answer for all that, IMO.

FWIW, I'm still a bit meh with the addition of the functions
overloading the arguments with reg inputs. I'm OK with that when we
know that the input would be of a given object type, like
pg_partition_ancestors or pg_partition_tree, but for a case as generic
as this one this is less appealing to me.

I've looked at other occurrences of "<type>reg" in func.sgml,
and I now agree with you we should skip the overloads,
since adding them would seem unconventional.

Okay. If another committer is interested in that, I'd be OK if there
is a consensus on this point. The fact that I'm not convinced does
not mean that it would show enough value for somebody else.
--
Michael

#19

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Michael Paquier (#18)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jun 25, 2024, at 03:57, Michael Paquier wrote:

On Tue, Jun 25, 2024 at 01:21:14AM +0200, Joel Jacobson wrote:

Good idea, I've started a separate thread for this:

/messages/by-id/9253b872-dbb1-42a6-a79e-b1e96effc857@app.fastmail.com

This patch now assumes <acronym>ACL</acronym> will be supported.

Thanks for doing that! That helps in making reviews easier to follow
for all, attracting the correct audience when necessary.
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d ON d.datname = current_database() AND 
d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a ON a.oid = s.refobjid AND s.refclassid 
= 'pg_authid'::regclass
+WHERE s.deptype = 'a';
Could be a bit prettier. That's a good addition.
How could we make it prettier?
Perhaps split the two JOIN conditions into two lines each, with a bit
more indentation to make it render better? Usually I handle that on a
case-by-case basis while preparing a patch for commit. I'm OK to edit
that myself with some final touches, FWIW. Depending on the input
this shows, I'd also look at some LATERAL business, that can be
cleaner in some cases for the docs.

Thanks, some indentation certainly helped.
Not sure where LATERAL would help, so leaving that part to you.

SELECT pg_get_acl(0, 'atest2'::regclass::oid);
ERROR: unrecognized class ID: 0

I believe we want an error here, since: an invalid class ID,
like 0, or any other invalid OID, should raise an error,
since classes can't be dropped, so we should never
expect an invalid OID for a class ID.
Please correct me if this reasoning is incorrect.

This is an internal error, so it should never be visible to the end
user via SQL because it is an unexpected state. See for example
2a10fdc4307a, which is similar to what you are doing here.

Thanks for pointing me to that commit, good to learn about missing_ok.

Not sure if I see how to implement it for pg_get_acl() though.

I've had a look at how pg_describe_object() works for this case:

SELECT pg_describe_object(0,'t'::regclass::oid,0);
ERROR: unsupported object class: 0

I suppose this is the error message we want in pg_get_acl() when
the class ID is invalid?

If no, the rest of this email can be skipped.

If yes, then I suppose we should try to see if there is any existing code
in objectaddress.c that we could reuse, that can throw this error message
for us, for an invalid class OID.

There are three places in objectaddress.c currently capable of
throwing a "unsupported object class" error message:

char *
getObjectDescription(const ObjectAddress *object, bool missing_ok)

char *
getObjectTypeDescription(const ObjectAddress *object, bool missing_ok)

char *
getObjectIdentityParts(const ObjectAddress *object,
List **objname, List **objargs,
bool missing_ok)

All three of them contain a `switch (object->classId)` statement,
where the default branch contains the code that throws the error:

default:
elog(ERROR, "unsupported object class: %u", object->classId);

It would be nice to avoid having to copy the long switch statement, with noops for each branch,
except the default branch, to throw an error in case of an invalid class OID,
but I don't see how we can use any of these three functions in pg_get_acl(), since they
do more things than just checking if the class OID is valid?

So not sure what to do here.

Maybe we want a separate new bool helper function to check if a class OID is valid or not?

That helper function would not be useful for the existing three cases where this error is thrown
in objectaddress.c, since they need actual switch branches for each class ID, whereas in pg_get_acl()
we just need to check if it's valid or not.

I haven't checked, but maybe there are other places in the sources where we just want to check
if a class OID is valid or not, that could benefit from such a helper function.

Or perhaps one exist already?

/Joel

#20

Michael Paquier

michael@paquier.xyz

over 1 year ago

In reply to: Joel Jacobson (#19)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jun 25, 2024 at 08:06:41AM +0200, Joel Jacobson wrote:

Not sure if I see how to implement it for pg_get_acl() though.

I've had a look at how pg_describe_object() works for this case:

SELECT pg_describe_object(0,'t'::regclass::oid,0);
ERROR: unsupported object class: 0

I suppose this is the error message we want in pg_get_acl() when
the class ID is invalid?

Ah, and here I thought that this was also returning NULL. My previous
work in this area only focused on the object OIDs, not their classes.
At the end, I'm OK to keep your patch as it is, checking only for the
case of pinned dependencies in pg_depend as we do for
pg_describe_object().

It's still a bit confusing, but we've been living with that for years
now without anybody complaining except me, so perhaps that's fine at
the end to keep that as this is still useful. If we change that,
applying the same rules across the board would make the most sense.
--
Michael

#21

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Michael Paquier (#20)

1 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jun 25, 2024, at 08:42, Michael Paquier wrote:

On Tue, Jun 25, 2024 at 08:06:41AM +0200, Joel Jacobson wrote:

Not sure if I see how to implement it for pg_get_acl() though.

I've had a look at how pg_describe_object() works for this case:

SELECT pg_describe_object(0,'t'::regclass::oid,0);
ERROR: unsupported object class: 0

I suppose this is the error message we want in pg_get_acl() when
the class ID is invalid?

Ah, and here I thought that this was also returning NULL. My previous
work in this area only focused on the object OIDs, not their classes.
At the end, I'm OK to keep your patch as it is, checking only for the
case of pinned dependencies in pg_depend as we do for
pg_describe_object().

It's still a bit confusing, but we've been living with that for years
now without anybody complaining except me, so perhaps that's fine at
the end to keep that as this is still useful. If we change that,
applying the same rules across the board would make the most sense.

OK, cool.

New version attached that fixes the indentation of the example,
and uses <literal>NULL</literal> instead of just NULL in the doc.

/Joel

Attachments:

v8-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v8-0001-Add-pg_get_acl.patchDownload

From 07bbb11a9009018447978b73cd4d47079a2aec6b Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Tue, 25 Jun 2024 00:42:47 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the ACL for a database object,
specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 30 +++++++++++++++
 src/backend/catalog/objectaddress.c      | 48 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out | 31 +++++++++++++++
 src/test/regress/sql/privileges.sql      |  7 ++++
 5 files changed, 122 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..f0da94b456 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,36 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the <acronym>ACL</acronym> for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns <literal>NULL</literal> values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d
+    ON d.datname = current_database()
+    AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a
+    ON a.oid = s.refobjid
+    AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..45e3974d83 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,54 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the ACL of a specified object, given
+ * its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	/* For large objects, the catalog to modify is pg_largeobject_metadata */
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..342c466d64 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,10 +213,41 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+                     unnest                     
+------------------------------------------------
+ regress_priv_user1=arwdDxtm/regress_priv_user1
+ regress_priv_user2=r/regress_priv_user1
+ regress_priv_user3=w/regress_priv_user1
+ regress_priv_user4=a/regress_priv_user1
+ regress_priv_user5=D/regress_priv_user1
+(5 rows)
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+ERROR:  unrecognized class ID: 0
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+SELECT pg_get_acl(0, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..e1fc43ea54 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,10 +183,17 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+SELECT pg_get_acl(0, 0); -- null
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-- 
2.45.1

#22

Joel Jacobson

joel@compiler.org

over 1 year ago

In reply to: Joel Jacobson (#21)

1 attachment(s)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jun 25, 2024, at 09:13, Joel Jacobson wrote:

Attachments:
* v8-0001-Add-pg_get_acl.patch

Rebased version.
Uses ACL acronym added in commit 00d819d46a6f5b7e9d2e02948a1c80d11c4ce260:
doc: Add ACL acronym for "Access Control List"

/Joel

Attachments:

v9-0001-Add-pg_get_acl.patchapplication/octet-stream; name=v9-0001-Add-pg_get_acl.patchDownload

From f66692a45e08ee3d36e456d8a2fbdda4dfbf34e7 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Tue, 2 Jul 2024 12:35:43 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
 object.

This SQL-callable function returns the ACL for a database object,
specified by catalog OID and object OID.

Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
Related Discussion: https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
---
 doc/src/sgml/func.sgml                   | 30 +++++++++++++++
 src/backend/catalog/objectaddress.c      | 48 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 +++
 src/test/regress/expected/privileges.out | 31 +++++++++++++++
 src/test/regress/sql/privileges.sql      |  7 ++++
 5 files changed, 122 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..bc47a2c390 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26696,6 +26696,36 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
         Undefined objects are identified with <literal>NULL</literal> values.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_acl</primary>
+        </indexterm>
+        <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+        <returnvalue>aclitem[]</returnvalue>
+       </para>
+       <para>
+        Returns the <acronym>ACL</acronym> for a database object,
+        specified by catalog OID and object OID.
+        This function is useful for retrieving and inspecting the privileges associated with database objects.
+        This function returns <literal>NULL</literal> values for undefined objects.
+        Example, showing explicitly granted privileges on objects in current database:
+<programlisting>
+SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid)
+FROM pg_catalog.pg_shdepend AS s
+JOIN pg_catalog.pg_database AS d
+    ON d.datname = current_database()
+    AND d.oid = s.dbid
+JOIN pg_catalog.pg_authid AS a
+    ON a.oid = s.refobjid
+    AND s.refclassid = 'pg_authid'::regclass
+WHERE s.deptype = 'a';
+</programlisting>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..45e3974d83 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,54 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-level callable function to obtain the ACL of a specified object, given
+ * its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+	Oid			classId = PG_GETARG_OID(0);
+	Oid			objectId = PG_GETARG_OID(1);
+	Oid			catalogId;
+	AttrNumber	Anum_oid;
+	AttrNumber	Anum_acl;
+
+	/* for "pinned" items in pg_depend, return null */
+	if (!OidIsValid(classId) && !OidIsValid(objectId))
+		PG_RETURN_NULL();
+
+	/* For large objects, the catalog to modify is pg_largeobject_metadata */
+	catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+	Anum_oid = get_object_attnum_oid(catalogId);
+	Anum_acl = get_object_attnum_acl(catalogId);
+
+	if (Anum_acl != InvalidAttrNumber)
+	{
+		Relation rel;
+		HeapTuple tup;
+		Datum datum;
+		bool isnull;
+
+		rel = table_open(catalogId, AccessShareLock);
+
+		tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+		if (!HeapTupleIsValid(tup))
+		{
+			table_close(rel, AccessShareLock);
+			PG_RETURN_NULL();
+		}
+
+		datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+		table_close(rel, AccessShareLock);
+
+		if (!isnull)
+			PG_RETURN_DATUM(datum);
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * Return a palloc'ed string that describes the type of object that the
  * passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d4ac578ae6..ac74e7e502 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
 
+{ oid => '6347', descr => 'get ACL for SQL object',
+  proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+  proargtypes => 'oid oid',
+  proargnames => '{classid,objid}',
+  prosrc => 'pg_get_acl' },
+
 { oid => '3839',
   descr => 'get machine-parseable identification of SQL object',
   proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..342c466d64 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -213,10 +213,41 @@ SELECT * FROM atest1;
 (0 rows)
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+                     unnest                     
+------------------------------------------------
+ regress_priv_user1=arwdDxtm/regress_priv_user1
+ regress_priv_user2=r/regress_priv_user1
+ regress_priv_user3=w/regress_priv_user1
+ regress_priv_user4=a/regress_priv_user1
+ regress_priv_user5=D/regress_priv_user1
+(5 rows)
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+ERROR:  unrecognized class ID: 0
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
+SELECT pg_get_acl(0, 0); -- null
+ pg_get_acl 
+------------
+ 
+(1 row)
+
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 ERROR:  grantor must be current user
 SET SESSION AUTHORIZATION regress_priv_user2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..e1fc43ea54 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -183,10 +183,17 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
 SELECT * FROM atest1;
 
 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
 GRANT SELECT ON atest2 TO regress_priv_user2;
 GRANT UPDATE ON atest2 TO regress_priv_user3;
 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
+
+-- try passing zero OID to pg_get_acl
+SELECT pg_get_acl(0, 'atest2'::regclass::oid); -- error
+SELECT pg_get_acl('pg_class'::regclass, 0); -- null
+SELECT pg_get_acl(0, 0); -- null
 
 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
 
-- 
2.45.1

#23

Michael Paquier

michael@paquier.xyz

over 1 year ago

In reply to: Joel Jacobson (#22)

Re: Add pg_get_acl() function get the ACL for a database object

On Tue, Jul 02, 2024 at 12:38:07PM +0200, Joel Jacobson wrote:

Rebased version.
Uses ACL acronym added in commit 00d819d46a6f5b7e9d2e02948a1c80d11c4ce260:
doc: Add ACL acronym for "Access Control List"

Forgot to push the send button for this one yesterday, done now..

While looking at that, I've finished by applying what you have here as
it is good enough to retrieve any ACLs for all catalogs that don't use
a subobjid (aka everything except pg_attribute's ACL, for which
dependencies are stored with pg_class in pg_shdepend so we'd need a
shortcut in pg_get_acl() or more data in ObjectProperty but I'm not
much a fan of tracking in that the dependency between pg_attribute and
pg_class coming from pg_shdepend), with two tweaks:
- Slightly reshaped the code to avoid more blocks, even if it means
one more PG_RETURN_NULL().
- Moved the example outside the main function table as it was rather
complex, with some output provided that should fit in the width of
the PDF docs.
--
Michael