public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joel Jacobson <[email protected]>
To: [email protected]
Subject: Add pg_get_acl() function get the ACL for a database object
Date: Wed, 19 Jun 2024 13:34:31 +0200
Message-ID: <[email protected]> (raw)
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/[email protected]
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
Attachments:
[application/octet-stream] 0001-Add-pg_get_acl.patch (6.1K, ../[email protected]/2-0001-Add-pg_get_acl.patch)
download | inline diff:
From 63d39969585f628661110f16605be48bb19899b8 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <[email protected]>
Date: Wed, 19 Jun 2024 12:31:21 +0200
Subject: [PATCH] Add pg_get_acl() function to get the ACL for a database
object.
This SQL-callable function returns the Access Control List (ACL)
for a database object, specified by catalog OID and object OID.
Related Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/func.sgml | 16 +++++++++
src/backend/catalog/objectaddress.c | 43 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 ++++
src/test/regress/expected/privileges.out | 7 ++++
src/test/regress/sql/privileges.sql | 2 ++
5 files changed, 74 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..fa6fdce517 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26686,6 +26686,22 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
Undefined objects are identified with <literal>NULL</literal> values.
</para></entry>
</row>
+
+ <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 Access Control List (ACL) for a database object,
+ specified by catalog OID and object OID.
+ This function is useful for retrieving and inspecting the privileges associated with database objects.
+ This function returns NULL values for undefined objects.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..0579b66ab7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,49 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
}
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+ Oid classId = PG_GETARG_OID(0);
+ Oid objectId = PG_GETARG_OID(1);
+ Oid catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+ AttrNumber Anum_oid = get_object_attnum_oid(catalogId);
+ AttrNumber Anum_acl = get_object_attnum_acl(catalogId);
+ Relation rel;
+ HeapTuple tup;
+ Datum datum;
+ bool isnull;
+
+ /* for "pinned" items in pg_depend, return null */
+ if (!OidIsValid(classId) && !OidIsValid(objectId))
+ PG_RETURN_NULL();
+
+ rel = table_open(catalogId, AccessShareLock);
+
+ tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+ if (tup == NULL)
+ elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+ objectId, RelationGetRelationName(rel));
+
+ if (Anum_acl != InvalidAttrNumber)
+ {
+ datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+ if (!isnull)
+ {
+ table_close(rel, AccessShareLock);
+ PG_RETURN_DATUM(datum);
+ }
+ }
+
+ table_close(rel, AccessShareLock);
+ PG_RETURN_NULL();
+}
+
+
/*
* Return a palloc'ed string that describes the type of object that the
* passed address is for.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
+{ oid => '6347', descr => 'get ACL for SQL object',
+ proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+ proargtypes => 'oid oid',
+ proargnames => '{classid,objid}',
+ prosrc => 'pg_get_acl' },
+
{ oid => '3839',
descr => 'get machine-parseable identification of SQL object',
proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7ada124265 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -219,6 +219,13 @@ GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
ERROR: grantor must be current user
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
+ pg_get_acl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {regress_priv_user1=arwdDxtm/regress_priv_user1,regress_priv_user2=r/regress_priv_user1,regress_priv_user3=w/regress_priv_user1,regress_priv_user4=a/regress_priv_user1,regress_priv_user5=D/regress_priv_user1}
+(1 row)
+
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT session_user, current_user;
session_user | current_user
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..36f348051e 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -190,6 +190,8 @@ GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
+-- test pg_get_acl()
+SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT session_user, current_user;
--
2.45.1
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Add pg_get_acl() function get the ACL for a database object
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox