Skip to content

Commit 11ca04b

Browse files
tglsfdctglsfdc
committed
Support GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA.
Petr Jelinek
1 parent faa1afc commit 11ca04b

File tree

10 files changed

+300
-20
lines changed

10 files changed

+300
-20
lines changed

‎doc/src/sgml/ref/grant.sgml

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.78 2009/10/05 19:24:34 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.79 2009/10/12 20:39:39 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,7 +23,8 @@ PostgreSQL documentation
2323
<synopsis>
2424
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
2525
[,...] | ALL [ PRIVILEGES ] }
26-
ON [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
26+
ON { [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
27+
| ALL TABLES IN SCHEMA <replaceable class="PARAMETER">schema_name</replaceable> [, ...] }
2728
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
2829

2930
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
@@ -33,7 +34,8 @@ GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETE
3334

3435
GRANT { { USAGE | SELECT | UPDATE }
3536
[,...] | ALL [ PRIVILEGES ] }
36-
ON SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable> [, ...]
37+
ON { SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable> [, ...]
38+
| ALL SEQUENCES IN SCHEMA <replaceable class="PARAMETER">schema_name</replaceable> [, ...] }
3739
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
3840

3941
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
@@ -49,7 +51,8 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
4951
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5052

5153
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
52-
ON FUNCTION <replaceable>function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) [, ...]
54+
ON { FUNCTION <replaceable>function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) [, ...]
55+
| ALL FUNCTIONS IN SCHEMA <replaceable class="PARAMETER">schema_name</replaceable> [, ...] }
5356
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5457

5558
GRANT { USAGE | ALL [ PRIVILEGES ] }
@@ -90,6 +93,13 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
9093
to those already granted, if any.
9194
</para>
9295

96+
<para>
97+
There is also an option to grant privileges on all objects of the same
98+
type within one or more schemas. This functionality is currently supported
99+
only for tables, sequences, and functions (but note that <literal>ALL
100+
TABLES</> is considered to include views).
101+
</para>
102+
93103
<para>
94104
The key word <literal>PUBLIC</literal> indicates that the
95105
privileges are to be granted to all roles, including those that might

‎doc/src/sgml/ref/revoke.sgml

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.52 2009/09/19 10:23:27 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.53 2009/10/12 20:39:39 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,7 +24,8 @@ PostgreSQL documentation
2424
REVOKE [ GRANT OPTION FOR ]
2525
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
2626
[,...] | ALL [ PRIVILEGES ] }
27-
ON [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
27+
ON { [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
28+
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
2829
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
2930
[ CASCADE | RESTRICT ]
3031

@@ -38,7 +39,8 @@ REVOKE [ GRANT OPTION FOR ]
3839
REVOKE [ GRANT OPTION FOR ]
3940
{ { USAGE | SELECT | UPDATE }
4041
[,...] | ALL [ PRIVILEGES ] }
41-
ON SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable> [, ...]
42+
ON { SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable> [, ...]
43+
| ALL SEQUENCES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
4244
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
4345
[ CASCADE | RESTRICT ]
4446

@@ -62,7 +64,8 @@ REVOKE [ GRANT OPTION FOR ]
6264

6365
REVOKE [ GRANT OPTION FOR ]
6466
{ EXECUTE | ALL [ PRIVILEGES ] }
65-
ON FUNCTION <replaceable>function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) [, ...]
67+
ON { FUNCTION <replaceable>function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) [, ...]
68+
| ALL FUNCTIONS IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
6669
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
6770
[ CASCADE | RESTRICT ]
6871

‎src/backend/catalog/aclchk.c

Lines changed: 125 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.155 2009/10/05 19:24:35 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.156 2009/10/12 20:39:39 tgl Exp $
1212
*
1313
* NOTES
1414
* See acl.h.
@@ -110,6 +110,8 @@ static void SetDefaultACLsInSchemas(InternalDefaultACL *iacls, List *nspnames);
110110
static void SetDefaultACL(InternalDefaultACL *iacls);
111111

112112
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
113+
static List *objectsInSchemaToOids(GrantObjectType objtype, List *nspnames);
114+
static List *getRelationsInNamespace(Oid namespaceId, char relkind);
113115
static void expand_col_privileges(List *colnames, Oid table_oid,
114116
AclMode this_privileges,
115117
AclMode *col_privileges,
@@ -335,7 +337,22 @@ ExecuteGrantStmt(GrantStmt *stmt)
335337
*/
336338
istmt.is_grant = stmt->is_grant;
337339
istmt.objtype = stmt->objtype;
338-
istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
340+
341+
/* Collect the OIDs of the target objects */
342+
switch (stmt->targtype)
343+
{
344+
case ACL_TARGET_OBJECT:
345+
istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
346+
break;
347+
case ACL_TARGET_ALL_IN_SCHEMA:
348+
istmt.objects = objectsInSchemaToOids(stmt->objtype, stmt->objects);
349+
break;
350+
/* ACL_TARGET_DEFAULTS should not be seen here */
351+
default:
352+
elog(ERROR, "unrecognized GrantStmt.targtype: %d",
353+
(int) stmt->targtype);
354+
}
355+
339356
/* all_privs to be filled below */
340357
/* privileges to be filled below */
341358
istmt.col_privs = NIL; /* may get filled below */
@@ -656,6 +673,112 @@ objectNamesToOids(GrantObjectType objtype, List *objnames)
656673
return objects;
657674
}
658675

676+
/*
677+
* objectsInSchemaToOids
678+
*
679+
* Find all objects of a given type in specified schemas, and make a list
680+
* of their Oids. We check USAGE privilege on the schemas, but there is
681+
* no privilege checking on the individual objects here.
682+
*/
683+
static List *
684+
objectsInSchemaToOids(GrantObjectType objtype, List *nspnames)
685+
{
686+
List *objects = NIL;
687+
ListCell *cell;
688+
689+
foreach(cell, nspnames)
690+
{
691+
char *nspname = strVal(lfirst(cell));
692+
Oid namespaceId;
693+
List *objs;
694+
695+
namespaceId = LookupExplicitNamespace(nspname);
696+
697+
switch (objtype)
698+
{
699+
case ACL_OBJECT_RELATION:
700+
/* Process both regular tables and views */
701+
objs = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
702+
objects = list_concat(objects, objs);
703+
objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
704+
objects = list_concat(objects, objs);
705+
break;
706+
case ACL_OBJECT_SEQUENCE:
707+
objs = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
708+
objects = list_concat(objects, objs);
709+
break;
710+
case ACL_OBJECT_FUNCTION:
711+
{
712+
ScanKeyData key[1];
713+
Relation rel;
714+
HeapScanDesc scan;
715+
HeapTuple tuple;
716+
717+
ScanKeyInit(&key[0],
718+
Anum_pg_proc_pronamespace,
719+
BTEqualStrategyNumber, F_OIDEQ,
720+
ObjectIdGetDatum(namespaceId));
721+
722+
rel = heap_open(ProcedureRelationId, AccessShareLock);
723+
scan = heap_beginscan(rel, SnapshotNow, 1, key);
724+
725+
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
726+
{
727+
objects = lappend_oid(objects, HeapTupleGetOid(tuple));
728+
}
729+
730+
heap_endscan(scan);
731+
heap_close(rel, AccessShareLock);
732+
}
733+
break;
734+
default:
735+
/* should not happen */
736+
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
737+
(int) objtype);
738+
}
739+
}
740+
741+
return objects;
742+
}
743+
744+
/*
745+
* getRelationsInNamespace
746+
*
747+
* Return Oid list of relations in given namespace filtered by relation kind
748+
*/
749+
static List *
750+
getRelationsInNamespace(Oid namespaceId, char relkind)
751+
{
752+
List *relations = NIL;
753+
ScanKeyData key[2];
754+
Relation rel;
755+
HeapScanDesc scan;
756+
HeapTuple tuple;
757+
758+
ScanKeyInit(&key[0],
759+
Anum_pg_class_relnamespace,
760+
BTEqualStrategyNumber, F_OIDEQ,
761+
ObjectIdGetDatum(namespaceId));
762+
ScanKeyInit(&key[1],
763+
Anum_pg_class_relkind,
764+
BTEqualStrategyNumber, F_CHAREQ,
765+
CharGetDatum(relkind));
766+
767+
rel = heap_open(RelationRelationId, AccessShareLock);
768+
scan = heap_beginscan(rel, SnapshotNow, 2, key);
769+
770+
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
771+
{
772+
relations = lappend_oid(relations, HeapTupleGetOid(tuple));
773+
}
774+
775+
heap_endscan(scan);
776+
heap_close(rel, AccessShareLock);
777+
778+
return relations;
779+
}
780+
781+
659782
/*
660783
* ALTER DEFAULT PRIVILEGES statement
661784
*/

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.445 2009/10/12 19:49:24 adunstan Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.446 2009/10/12 20:39:39 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2350,6 +2350,7 @@ _copyGrantStmt(GrantStmt *from)
23502350
GrantStmt *newnode = makeNode(GrantStmt);
23512351

23522352
COPY_SCALAR_FIELD(is_grant);
2353+
COPY_SCALAR_FIELD(targtype);
23532354
COPY_SCALAR_FIELD(objtype);
23542355
COPY_NODE_FIELD(objects);
23552356
COPY_NODE_FIELD(privileges);

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
* Portions Copyright (c) 1994, Regents of the University of California
2323
*
2424
* IDENTIFICATION
25-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.367 2009/10/12 19:49:24 adunstan Exp $
25+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.368 2009/10/12 20:39:40 tgl Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -990,6 +990,7 @@ static bool
990990
_equalGrantStmt(GrantStmt *a, GrantStmt *b)
991991
{
992992
COMPARE_SCALAR_FIELD(is_grant);
993+
COMPARE_SCALAR_FIELD(targtype);
993994
COMPARE_SCALAR_FIELD(objtype);
994995
COMPARE_NODE_FIELD(objects);
995996
COMPARE_NODE_FIELD(privileges);

0 commit comments

Comments
 (0)