Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 4564f1c

Browse filesBrowse 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
Copy full SHA for 4564f1c

File tree

Expand file treeCollapse file tree

6 files changed

+130
-1
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+130
-1
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+41Lines 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

Copy file name to clipboardExpand all lines: src/backend/catalog/objectaddress.c
+48Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+5Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/expected/privileges.out
+29Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/privileges.sql
+6Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.