Обсуждение: pgsql: Add pg_get_acl() to get the ACL for a database object

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

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/4564f1cebd437d93590027c9ff46ef60bc3286ae

Modified Files
--------------
doc/src/sgml/func.sgml                   | 41 +++++++++++++++++++++++++++
src/backend/catalog/objectaddress.c      | 48 ++++++++++++++++++++++++++++++++
src/include/catalog/catversion.h         |  2 +-
src/include/catalog/pg_proc.dat          |  5 ++++
src/test/regress/expected/privileges.out | 29 +++++++++++++++++++
src/test/regress/sql/privileges.sql      |  6 ++++
6 files changed, 130 insertions(+), 1 deletion(-)

Michael Paquier <michael@paquier.xyz> writes:
> 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.

Uh, why is it defined like that rather than allowing a subobject?
This definition is unable to fetch column-specific ACLs.

            regards, tom lane

On Thu, Jul 4, 2024, at 17:44, Tom Lane wrote:
> Michael Paquier <michael@paquier.xyz> writes:
>> 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.
>
> Uh, why is it defined like that rather than allowing a subobject?
> This definition is unable to fetch column-specific ACLs.

Good point, that's surely an important missing feature,
that I hadn't thought about up until now.
Probably because all object classes, except columns, don't have subobjects.

I wonder if it would be motivated to provide overloads for this function,
and perhaps even for pg_get_object_address and pg_identify_object_as_address?

That is, two param versions (class OID and object OID),
and three param versions that in addition also take subobject ID.

Why I think this could be motivated, is since during discussion,
some even wanted reg* overloads, to avoid having to pass the class OID.

As a middle ground, maybe users would appreciate if they at least
didn't have pass in the extra 0, since it's meaningless anyway,
most of the times (for all classes except columns)?

Anyway, that's just an idea. We still need support for subobject,
so I had a look on how to implement it.

Unfortunately, the AlterObjectOwner_internal function in alter.c,
which pg_get_acl in objectaddress.c is based upon,
doesn't deal with subobjects.

I found some code in aclchk.c on line 4452-4468 that seems useful,
but not sure. Maybe there is some other existing code that is better
as an inspiration?

I guess we need to handle the RelationRelationId separately,
and handle all other classes using the current code in pg_get_acl()?

Regards,
Joel