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).