From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Add pg_get_acl() function get the ACL for a database object |
Date: | 2024-06-19 11:34:31 |
Message-ID: | 80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello hackers,
Currently, obtaining the Access Control List (ACL) for a database object
requires querying specific pg_catalog tables directly, where the user
needs to know the name of the ACL column for the object.
Consider:
```
CREATE USER test_user;
CREATE USER test_owner;
CREATE SCHEMA test_schema AUTHORIZATION test_owner;
SET ROLE TO test_owner;
CREATE TABLE test_schema.test_table ();
GRANT SELECT ON TABLE test_schema.test_table TO test_user;
```
To get the ACL we can do:
```
SELECT relacl FROM pg_class WHERE oid = 'test_schema.test_table'::regclass::oid;
relacl
---------------------------------------------------------
{test_owner=arwdDxtm/test_owner,test_user=r/test_owner}
```
Attached patch adds a new SQL-callable functoin `pg_get_acl()`, so we can do:
```
SELECT pg_get_acl('pg_class'::regclass, 'test_schema.test_table'::regclass::oid);
pg_get_acl
---------------------------------------------------------
{test_owner=arwdDxtm/test_owner,test_user=r/test_owner}
```
The original idea for this function came from Alvaro Herrera,
in this related discussion:
https://postgr.es/m/261def6b-226e-4238-b7eb-ff240cb9c2c9@www.fastmail.com
On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
> On 2021-Mar-25, Joel Jacobson wrote:
>
>> pg_shdepend doesn't contain the aclitem info though,
>> so it won't work for pg_permissions if we want to expose
>> privilege_type, is_grantable and grantor.
>
> Ah, of course -- the only way to obtain the acl columns is by going
> through the catalogs individually, so it won't be possible. I think
> this could be fixed with some very simple, quick function pg_get_acl()
> that takes a catalog OID and object OID and returns the ACL; then
> use aclexplode() to obtain all those details.
The pg_get_acl() function has been implemented by following
the guidance from Alvaro in the related dicussion:
On Fri, Mar 26, 2021, at 13:43, Alvaro Herrera wrote:
> AFAICS the way to do it is like AlterObjectOwner_internal obtains data
> -- first do get_catalog_object_by_oid (gives you the HeapTuple that
> represents the object), then
> heap_getattr( ..., get_object_attnum_acl(), ..), and there you have the
> ACL which you can "explode" (or maybe just return as-is).
>
> AFAICS if you do this, it's just one cache lookups per object, or
> one indexscan for the cases with no by-OID syscache. It should be much
> cheaper than the UNION ALL query. And you use pg_shdepend to guide
> this, so you only do it for the objects that you already know are
> interesting.
Many thanks Alvaro for the very helpful instructions.
This function would then allow users to e.g. create a view to show the privileges
for all database objects, like the pg_privileges system view suggested in the
related discussion.
Tests and docs are added.
Best regards,
Joel Jakobsson
Attachment | Content-Type | Size |
---|---|---|
0001-Add-pg_get_acl.patch | application/octet-stream | 6.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Shubham Khanna | 2024-06-19 11:36:31 | Re: Pgoutput not capturing the generated columns |
Previous Message | Shubham Khanna | 2024-06-19 11:22:20 | Re: Pgoutput not capturing the generated columns |