v3-0001-Add-pg_get_acl.patch

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