v2-0002-Add-pg_get_acl-overloads.patch

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