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

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

> Michael Paquier <michael(at)paquier(dot)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.