Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit4564f1c

Browse files
committed
Add pg_get_acl() to get the ACL for a database object
This function returns the ACL for a database object, specified bycatalog OID and object OID. This is useful to be able toretrieve the ACL associated to an object specified with a(class_id,objid) couple, similarly to the other functions for objectidentification, when joined with pg_depend or pg_shdepend.Original idea by Álvaro Herrera.Bump catalog version.Author: Joel JacobsonReviewed-by: Isaac Morland, Michael Paquier, Ranier VilelaDiscussion:https://postgr.es/m/80b16434-b9b1-4c3d-8f28-569f21c2c102@app.fastmail.com
1 parent3a8a1f3 commit4564f1c

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+
OidclassId=PG_GETARG_OID(0);
4373+
OidobjectId=PG_GETARG_OID(1);
4374+
OidcatalogId;
4375+
AttrNumberAnum_acl;
4376+
Relationrel;
4377+
HeapTupletup;
4378+
Datumdatum;
4379+
boolisnull;
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-
#defineCATALOG_VERSION_NO202407012
60+
#defineCATALOG_VERSION_NO202407041
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
CREATETABLEatest2 (col1varchar(10), col2boolean);
186+
SELECT pg_get_acl('pg_class'::regclass,'atest2'::regclass::oid);
186187
GRANTSELECTON atest2 TO regress_priv_user2;
187188
GRANTUPDATEON atest2 TO regress_priv_user3;
188189
GRANT INSERTON atest2 TO regress_priv_user4 GRANTED BYCURRENT_USER;
189190
GRANT TRUNCATEON 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 TRUNCATEON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;-- error
192198

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp