Skip to content

Commit 4564f1c

Browse files
michaelpqmichaelpq
committed
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
1 parent 3a8a1f3 commit 4564f1c

File tree

6 files changed

+130
-1
lines changed

6 files changed

+130
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26587,6 +26587,21 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
2658726587
</thead>
2658826588

2658926589
<tbody>
26590+
<row>
26591+
<entry role="func_table_entry"><para role="func_signature">
26592+
<indexterm>
26593+
<primary>pg_get_acl</primary>
26594+
</indexterm>
26595+
<function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
26596+
<returnvalue>aclitem[]</returnvalue>
26597+
</para>
26598+
<para>
26599+
Returns the <acronym>ACL</acronym> for a database object, specified
26600+
by catalog OID and object OID. This function returns
26601+
<literal>NULL</literal> values for undefined objects.
26602+
</para></entry>
26603+
</row>
26604+
2659026605
<row>
2659126606
<entry role="func_table_entry"><para role="func_signature">
2659226607
<indexterm>
@@ -26700,6 +26715,32 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
2670026715
</tgroup>
2670126716
</table>
2670226717

26718+
<para>
26719+
<function>pg_get_acl</function> is useful for retrieving and inspecting
26720+
the privileges associated with database objects without looking at
26721+
specific catalogs. For example, to retrieve all the granted privileges
26722+
on objects in the current database:
26723+
<programlisting>
26724+
postgres=# SELECT
26725+
(pg_identify_object(s.classid,s.objid,s.objsubid)).*,
26726+
pg_catalog.pg_get_acl(s.classid,s.objid) AS acl
26727+
FROM pg_catalog.pg_shdepend AS s
26728+
JOIN pg_catalog.pg_database AS d
26729+
ON d.datname = current_database() AND
26730+
d.oid = s.dbid
26731+
JOIN pg_catalog.pg_authid AS a
26732+
ON a.oid = s.refobjid AND
26733+
s.refclassid = 'pg_authid'::regclass
26734+
WHERE s.deptype = 'a';
26735+
-[ RECORD 1 ]-----------------------------------------
26736+
type | table
26737+
schema | public
26738+
name | testtab
26739+
identity | public.testtab
26740+
acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
26741+
</programlisting>
26742+
</para>
26743+
2670326744
</sect2>
2670426745

2670526746
<sect2 id="functions-info-comment">

‎src/backend/catalog/objectaddress.c

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4362,6 +4362,54 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
43624362
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
43634363
}
43644364

4365+
/*
4366+
* SQL-level callable function to obtain the ACL of a specified object, given
4367+
* its catalog OID and object OID.
4368+
*/
4369+
Datum
4370+
pg_get_acl(PG_FUNCTION_ARGS)
4371+
{
4372+
Oid classId = PG_GETARG_OID(0);
4373+
Oid objectId = PG_GETARG_OID(1);
4374+
Oid catalogId;
4375+
AttrNumber Anum_acl;
4376+
Relation rel;
4377+
HeapTuple tup;
4378+
Datum datum;
4379+
bool isnull;
4380+
4381+
/* for "pinned" items in pg_depend, return null */
4382+
if (!OidIsValid(classId) && !OidIsValid(objectId))
4383+
PG_RETURN_NULL();
4384+
4385+
/* for large objects, the catalog to look at is pg_largeobject_metadata */
4386+
catalogId = (classId == LargeObjectRelationId) ?
4387+
LargeObjectMetadataRelationId : classId;
4388+
Anum_acl = get_object_attnum_acl(catalogId);
4389+
4390+
/* return NULL if no ACL field for this catalog */
4391+
if (Anum_acl == InvalidAttrNumber)
4392+
PG_RETURN_NULL();
4393+
4394+
rel = table_open(catalogId, AccessShareLock);
4395+
4396+
tup = get_catalog_object_by_oid(rel, get_object_attnum_oid(catalogId),
4397+
objectId);
4398+
if (!HeapTupleIsValid(tup))
4399+
{
4400+
table_close(rel, AccessShareLock);
4401+
PG_RETURN_NULL();
4402+
}
4403+
4404+
datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
4405+
table_close(rel, AccessShareLock);
4406+
4407+
if (isnull)
4408+
PG_RETURN_NULL();
4409+
4410+
PG_RETURN_DATUM(datum);
4411+
}
4412+
43654413
/*
43664414
* Return a palloc'ed string that describes the type of object that the
43674415
* passed address is for.

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202407012
60+
#define CATALOG_VERSION_NO 202407041
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6362,6 +6362,11 @@
63626362
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
63636363
proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
63646364

6365+
{ oid => '6347', descr => 'get ACL for SQL object',
6366+
proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
6367+
proargtypes => 'oid oid', proargnames => '{classid,objid}',
6368+
prosrc => 'pg_get_acl' },
6369+
63656370
{ oid => '3839',
63666371
descr => 'get machine-parseable identification of SQL object',
63676372
proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',

‎src/test/regress/expected/privileges.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,10 +213,39 @@ SELECT * FROM atest1;
213213
(0 rows)
214214

215215
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
216+
SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
217+
pg_get_acl
218+
------------
219+
220+
(1 row)
221+
216222
GRANT SELECT ON atest2 TO regress_priv_user2;
217223
GRANT UPDATE ON atest2 TO regress_priv_user3;
218224
GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
219225
GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
226+
SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
227+
unnest
228+
------------------------------------------------
229+
regress_priv_user1=arwdDxtm/regress_priv_user1
230+
regress_priv_user2=r/regress_priv_user1
231+
regress_priv_user3=w/regress_priv_user1
232+
regress_priv_user4=a/regress_priv_user1
233+
regress_priv_user5=D/regress_priv_user1
234+
(5 rows)
235+
236+
-- Invalid inputs
237+
SELECT pg_get_acl('pg_class'::regclass, 0); -- null
238+
pg_get_acl
239+
------------
240+
241+
(1 row)
242+
243+
SELECT pg_get_acl(0, 0); -- null
244+
pg_get_acl
245+
------------
246+
247+
(1 row)
248+
220249
GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
221250
ERROR: grantor must be current user
222251
SET SESSION AUTHORIZATION regress_priv_user2;

‎src/test/regress/sql/privileges.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,10 +183,16 @@ GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
183183
SELECT * FROM atest1;
184184

185185
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
186+
SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid);
186187
GRANT SELECT ON atest2 TO regress_priv_user2;
187188
GRANT UPDATE ON atest2 TO regress_priv_user3;
188189
GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
189190
GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
191+
SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid));
192+
193+
-- Invalid inputs
194+
SELECT pg_get_acl('pg_class'::regclass, 0); -- null
195+
SELECT pg_get_acl(0, 0); -- null
190196

191197
GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
192198

0 commit comments

Comments
 (0)