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

Commit11ca04b

Browse files
committed
Support GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA.
Petr Jelinek
1 parentfaa1afc commit11ca04b

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
staticvoidSetDefaultACL(InternalDefaultACL*iacls);
111111

112112
staticList*objectNamesToOids(GrantObjectTypeobjtype,List*objnames);
113+
staticList*objectsInSchemaToOids(GrantObjectTypeobjtype,List*nspnames);
114+
staticList*getRelationsInNamespace(OidnamespaceId,charrelkind);
113115
staticvoidexpand_col_privileges(List*colnames,Oidtable_oid,
114116
AclModethis_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+
caseACL_TARGET_OBJECT:
345+
istmt.objects=objectNamesToOids(stmt->objtype,stmt->objects);
346+
break;
347+
caseACL_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
returnobjects;
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+
staticList*
684+
objectsInSchemaToOids(GrantObjectTypeobjtype,List*nspnames)
685+
{
686+
List*objects=NIL;
687+
ListCell*cell;
688+
689+
foreach(cell,nspnames)
690+
{
691+
char*nspname=strVal(lfirst(cell));
692+
OidnamespaceId;
693+
List*objs;
694+
695+
namespaceId=LookupExplicitNamespace(nspname);
696+
697+
switch (objtype)
698+
{
699+
caseACL_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+
caseACL_OBJECT_SEQUENCE:
707+
objs=getRelationsInNamespace(namespaceId,RELKIND_SEQUENCE);
708+
objects=list_concat(objects,objs);
709+
break;
710+
caseACL_OBJECT_FUNCTION:
711+
{
712+
ScanKeyDatakey[1];
713+
Relationrel;
714+
HeapScanDescscan;
715+
HeapTupletuple;
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+
returnobjects;
742+
}
743+
744+
/*
745+
* getRelationsInNamespace
746+
*
747+
* Return Oid list of relations in given namespace filtered by relation kind
748+
*/
749+
staticList*
750+
getRelationsInNamespace(OidnamespaceId,charrelkind)
751+
{
752+
List*relations=NIL;
753+
ScanKeyDatakey[2];
754+
Relationrel;
755+
HeapScanDescscan;
756+
HeapTupletuple;
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+
returnrelations;
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/1219:49:24 adunstan Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.446 2009/10/1220: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/1219:49:24 adunstan Exp $
25+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.368 2009/10/1220: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)
[8]ページ先頭
©2009-2025 Movatter.jp