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

This function returns the ACL for a database object, specified by
catalog OID and object OID.  This is useful to be able to
retrieve the ACL associated to an object specified with a
(class_id,objid) couple, similarly to the other functions for object
identification, when joined with pg_depend or pg_shdepend.

Original idea by Álvaro Herrera.

Bump catalog version.

Author: Joel Jacobson
Reviewed-by: Isaac Morland, Michael Paquier, Ranier Vilela
Discussion: https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com

1 files changed, 41 insertions, 0 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a19601..93ee3d4b60c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26590,6 +26590,21 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
       <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 returns
+        <literal>NULL</literal> values for undefined objects.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>pg_describe_object</primary>
         </indexterm>
         <function>pg_describe_object</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type>, <parameter>objsubid</parameter> <type>integer</type> )
@@ -26700,6 +26715,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
     </tgroup>
    </table>
 
+   <para>
+    <function>pg_get_acl</function> is useful for retrieving and inspecting
+    the privileges associated with database objects without looking at
+    specific catalogs. For example, to retrieve all the granted privileges
+    on objects in the current database:
+<programlisting>
+postgres=# SELECT
+    (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
+    pg_catalog.pg_get_acl(s.classid,s.objid) AS acl
+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';
+-[ RECORD 1 ]-----------------------------------------
+type     | table
+schema   | public
+name     | testtab
+identity | public.testtab
+acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
+</programlisting>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-info-comment">