Re: GRANT ON ALL IN schema
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
So, here is the first version of the patch.
It includes functionality itself, simple regression test and also very
simple documentation.
The patch allows "GRANT ON ALL TABLES/VIEWS/FUNCTIONS/SEQUENCES IN
schemaname, schemaname2 TO username" and same thing for REVOKE.
Words TABLES, VIEWS, FUNCTIONS and SEQUENCES were added as unreserved
keywords. Unfortunately I was unable to create syntax with optional
SCHEMA keyword after IN (shift/reduce conflicts), if it's needed maybe
somebody with better bison knowledge might add it.
Also since this patch introduces VIEWS as object with grantable
privileges, I added GRANT ON VIEW foo syntax which is more or less
synonymous to GRANT ON TABLE foo syntax. It felt weird to have GRANT ON
ALL VIEWS but not GRANT ON VIEW.
Any comments/suggestions are welcome (I especially wonder if the use of
list_union_ptr is acceptable).
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..7ddbd25 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT rol
*** 143,148 ****
--- 146,158 ----
+ There is also the posibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL TABLES IN schemanema syntax in place of object name.
+
+
+
The possible privileges are:
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..98fbd27 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9a45355..3bc5dc5 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static bool QueryIsRule = FALSE;
*** 98,103 ****
--- 98,104 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 449,455 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 450,456 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 487,499 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 488,500 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 501,507 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 502,508 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4227,4232 ****
--- 4228,4234 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4243,4248 ****
--- 4245,4251 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4256,4261 ****
--- 4259,4265 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4338,4343 ****
--- 4342,4348 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4345,4350 ****
--- 4350,4364 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4352,4357 ****
--- 4366,4372 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4359,4364 ****
--- 4374,4380 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4366,4371 ****
--- 4382,4388 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4373,4378 ****
--- 4390,4396 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4380,4385 ****
--- 4398,4404 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4387,4392 ****
--- 4406,4412 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4394,4399 ****
--- 4414,4420 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4401,4409 ****
--- 4422,4463 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10212,10217 ****
--- 10266,10272 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10321,10326 ****
--- 10376,10382 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10341,10346 ****
--- 10397,10403 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10365,10370 ****
--- 10422,10428 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 71c864a..3c79a1c 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Petr Jelinek wrote:
> So, here is the first version of the patch.
Attached is v2 with slightly improved code, nothing has changed
feature-wise.
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..7ddbd25 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT rol
*** 143,148 ****
--- 146,158 ----
+ There is also the posibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL TABLES IN schemanema syntax in place of object name.
+
+
+
The possible privileges are:
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..0bf4eb1 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ac17b93..8d543b4 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static bool QueryIsRule = FALSE;
*** 99,104 ****
--- 99,105 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 449,455 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 450,456 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 487,499 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 488,500 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 501,507 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 502,508 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4216,4221 ****
--- 4217,4223 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4232,4237 ****
--- 4234,4240 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4245,4250 ****
--- 4248,4254 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4327,4332 ****
--- 4331,4337 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4334,4339 ****
--- 4339,4353 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4341,4346 ****
--- 4355,4361 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4348,4353 ****
--- 4363,4369 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4355,4360 ****
--- 4371,4377 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4362,4367 ****
--- 4379,4385 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4369,4374 ****
--- 4387,4393 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4376,4381 ****
--- 4395,4401 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4383,4388 ****
--- 4403,4409 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4390,4398 ****
--- 4411,4452 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10201,10206 ****
--- 10255,10261 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10310,10315 ****
--- 10365,10371 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10330,10335 ****
--- 10386,10392 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10354,10359 ****
--- 10411,10417 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a108b80..fa040df 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Nikhil Sontakke wrote:
> grant.sgml
> * Maybe we should use
> schemaname in the sgml
> references instead of just schemaname
>
> + There is also the posibility of granting permissions to all objects of
> + given type inside one or multiple schemas. This functionality is supported
> + for tables, views, sequences and functions and can done by using
> + ALL TABLES IN schemanema syntax in place of object name.
> +
> +
> +
>
> typo "posibility"
> It should be ALL [TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemaname
> (note the other typo here) syntax to be precise IMHO.
>
Right, fixed.
> aclchk.c
> + elog(ERROR, "unrecognized GrantStmt.objtype: %d",
> + (int) objtype);
>
> Kinda funny to mention the C structure name in the error. But I see
> that the other functions in the file do the same, so should be ok. I
> doubt if the syntax allows any other object type to reach upto this
> function anyways :)
>
It's copy paste :)
But it seemed a bit strange to me too as this kind of thing is not
recommended in developer "guide". On the other hand ordinary user should
not ever see this unless something is horribly wrong with bison.
> parsenodes.h
> GrantObjectType objtype; /* kind of object being operated on */
> + bool is_schema; /* if true we want all objects
> + * of objtype in schema */
>
> You forgot to make changes in _copyGrantStmt and _equalGrantStmt to
> account for this new field.
>
Fixed.
> As an aside, I was just wondering the behaviour for RELKIND_INDEX?
>
Indexes don't have permissions afaik so nothing.
I attached modified patch per your comments and also updated to current
HEAD.
Thanks for your review.
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..51aad15 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT rol
*** 143,148 ****
--- 146,159 ----
+ There is also the possibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL {TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemanema syntax in place
+ of object name.
+
+
+
The possible privileges are:
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..0bf4eb1 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1976648..ac2bd64 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyGrantStmt(GrantStmt *from)
*** 2297,2302 ****
--- 2297,2303 ----
COPY_SCALAR_FIELD(is_grant);
COPY_SCALAR_FIELD(objtype);
+ COPY_SCALAR_FIELD(is_schema);
COPY_NODE_FIELD(objects);
COPY_NODE_FIELD(privileges);
COPY_NODE_FIELD(grantees);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8b466f4..0834199 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalGrantStmt(GrantStmt *a, GrantStmt
*** 979,984 ****
--- 979,985 ----
{
COMPARE_SCALAR_FIELD(is_grant);
COMPARE_SCALAR_FIELD(objtype);
+ COMPARE_SCALAR_FIELD(is_schema);
COMPARE_NODE_FIELD(objects);
COMPARE_NODE_FIELD(privileges);
COMPARE_NODE_FIELD(grantees);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 858e16c..c3617e7 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 96,101 ****
--- 96,102 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 465,471 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 466,472 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 503,515 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 504,516 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 517,523 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 518,524 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4233,4238 ****
--- 4234,4240 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4249,4254 ****
--- 4251,4257 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4262,4267 ****
--- 4265,4271 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4344,4349 ****
--- 4348,4354 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4351,4356 ****
--- 4356,4370 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4358,4363 ****
--- 4372,4378 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4365,4370 ****
--- 4380,4386 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4372,4377 ****
--- 4388,4394 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4379,4384 ****
--- 4396,4402 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4386,4391 ****
--- 4404,4410 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4393,4398 ****
--- 4412,4418 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4400,4405 ****
--- 4420,4426 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4407,4415 ****
--- 4428,4469 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10228,10233 ****
--- 10282,10288 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10337,10342 ****
--- 10392,10398 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10357,10362 ****
--- 10413,10419 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10381,10386 ****
--- 10438,10444 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9d53ab9..eab50c3 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
One more typo fix in docs
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..6400f9e 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT rol
*** 143,148 ****
--- 146,159 ----
+ There is also the possibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL {TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemaname syntax in place
+ of object name.
+
+
+
The possible privileges are:
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE sequencename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] tablename [, ...] }
! | ALL [ TABLES | VIEWS ] IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
! ON [ TABLE | VIEW ] tablename [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE sequencename [, ...]
! | ALL SEQUENCES IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN schemaname [, ...] }
FROM { [ GROUP ] rolename | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..0bf4eb1 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_concat(objects, relations);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1976648..ac2bd64 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyGrantStmt(GrantStmt *from)
*** 2297,2302 ****
--- 2297,2303 ----
COPY_SCALAR_FIELD(is_grant);
COPY_SCALAR_FIELD(objtype);
+ COPY_SCALAR_FIELD(is_schema);
COPY_NODE_FIELD(objects);
COPY_NODE_FIELD(privileges);
COPY_NODE_FIELD(grantees);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8b466f4..0834199 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalGrantStmt(GrantStmt *a, GrantStmt
*** 979,984 ****
--- 979,985 ----
{
COMPARE_SCALAR_FIELD(is_grant);
COMPARE_SCALAR_FIELD(objtype);
+ COMPARE_SCALAR_FIELD(is_schema);
COMPARE_NODE_FIELD(objects);
COMPARE_NODE_FIELD(privileges);
COMPARE_NODE_FIELD(grantees);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 858e16c..c3617e7 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 96,101 ****
--- 96,102 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 465,471 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 466,472 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 503,515 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 504,516 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 517,523 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 518,524 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4233,4238 ****
--- 4234,4240 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4249,4254 ****
--- 4251,4257 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4262,4267 ****
--- 4265,4271 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4344,4349 ****
--- 4348,4354 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4351,4356 ****
--- 4356,4370 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4358,4363 ****
--- 4372,4378 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4365,4370 ****
--- 4380,4386 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4372,4377 ****
--- 4388,4394 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4379,4384 ****
--- 4396,4402 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4386,4391 ****
--- 4404,4410 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4393,4398 ****
--- 4412,4418 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4400,4405 ****
--- 4420,4426 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4407,4415 ****
--- 4428,4469 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10228,10233 ****
--- 10282,10288 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10337,10342 ****
--- 10392,10398 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10357,10362 ****
--- 10413,10419 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10381,10386 ****
--- 10438,10444 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9d53ab9..eab50c3 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Hi, I attached revised version of the patch. Changes, thoughts: - SCHEMA is mandatory now - removed VIEWS and GRANT ON VIEW since it looks like only me and Stephen want it there - the patch is now made so that adding new filters in the future won't mean tearing of half of the parser code and replacing it - I decided to go with GRANT ON ALL IN SCHEMA syntax, because I am thinking there is no difference in adding extended syntax to the standard command in GRANT and in SELECT, ALTER TABLE and other commands we extended. And I don't see any way standard could add exactly same syntax for doing something completely different (which is the only way they could break this). -- Regards Petr Jelinek (PJMODOS)
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Petr Jelinek wrote: > I attached revised version of the patch. Changes, thoughts: > - SCHEMA is mandatory now > - removed VIEWS and GRANT ON VIEW since it looks like only me and > Stephen want it there > - the patch is now made so that adding new filters in the future won't > mean tearing of half of the parser code and replacing it > - I decided to go with GRANT ON ALL IN SCHEMA syntax, because I am > thinking there is no difference in adding extended syntax to the > standard command in GRANT and in SELECT, ALTER TABLE and other > commands we extended. And I don't see any way standard could add > exactly same syntax for doing something completely different (which is > the only way they could break this). Argh, why does this always happen to me ? Immediately after sending the patch I realized there needs to be one more little change done (merging tables and views in the getNamespacesObjectsOids function). -- Regards Petr Jelinek (PJMODOS)
Anonymous code blocks (was: Re: GRANT ON ALL IN schema)
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
> The question is still valid, though it's better put in your words - do > we want to refactor the existing compiler or write a separate one ? So, for now I went with the path of custom compiler and current executor. I attached current version of the patch. I don't expect this to get committed or anything, but I'd like other eyes to take a look at it. What it does: Adds laninline Oid which points to function handling inline code (aka anonymous code block). Adds DO $$some code$$ [ LANGUAGE lanname ] syntax which sends the source code to that laninline function of the specified language (or language set by default_do_language guc). There is implementation for plpgsql with simpler compiler which still creates function struct for the executor (I believe there is no harm in adjusting executor later, when current one works, just does unnecessary stuff). There is doc and a simple regression test for plpgsql implementation. -- Regards Petr Jelinek (PJMODOS)
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Abhijit Menon-Sen wrote:
I'll leave the exact wording to commiter, but in the attached patch I changed it to say "all existing objects" instead of "all objects".
Except for above two changes and the fact that it's against current head, the patch is exactly the same.
Thanks again.
Thanks for your review.I have not yet been able to do a complete review of this patch, but I am posting this because I'll be travelling for a week starting tomorrow. My comments are based mostly on reading the patch, and not on any intensive testing of the feature. I have left the patch status unchanged at "needs review", although I think it's close to "ready for committer".
I fixed those conflicts in attached patch.1. The patch did apply to HEAD and build cleanly, but there are now a couple of minor (documentation) conflicts. (Sorry, I would have fixed them and reposted a patch, but I'm running out of time right now.)
*** a/doc/src/sgml/ref/grant.sgml --- b/doc/src/sgml/ref/grant.sgml [...] <para> + There is also the possibility of granting permissions to all objects of + given type inside one or multiple schemas. This functionality is supported + for tables, views, sequences and functions and can done by using + ALL {TABLES|SEQUENCES|FUNCTIONS} IN SCHEMA schemaname syntax in place + of object name. + </para> + + <para>2. Here I suggest the following wording: <para> You can also grant permissions on all tables, sequences, or functions that currently exist within a given schema by specifying "ALL {TABLES|SEQUENCES|FUNCTIONS} IN SCHEMA schemaname" in place of an object name. </para> 3. I believe MySQL's "grant all privileges on foo.* to someone" grants privileges on all existing objects in foo _but also_ on any objects that may be created later. This patch only gives you a way to grant privileges only on the objects currently within a schema. I strongly prefer this behaviour myself, but I do think the documentation needs a brief mention of this fact, to avoid surprising people. That's why I added "that currently exist" to (2), above. Maybe another sentence that specifically says that objects created later are unaffected is in order. I'm not sure.
I'll leave the exact wording to commiter, but in the attached patch I changed it to say "all existing objects" instead of "all objects".
Except for above two changes and the fact that it's against current head, the patch is exactly the same.
Thanks again.
-- Regards Petr Jelinek (PJMODOS)
Re: Anonymous code blocks
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Dimitri Fontaine napsal(a):
It is. I attached patch which does not have this part.
Actually I think we might not need that function memory context for anonymous code blocks at all since we don't cache compiled functions. But I am not sure so I basically copied it from standard function compiler to be on safe side. I am sure commiter will comment on this :)
Hi, Dimitri Fontaine <dfontaine@hi-media.com> writes:Patch applies cleanly and build cleanly too, basic examples are working fine.I've been reading through the code and am going to mark it as ready for commiter, as only remarks I have are probably because I do not know enough about PostgreSQL internals, and the one I missed are in the same category. The patch is easy to read and all it does looks straightforward, even for me :) Here we go: *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c ... *************** UtilityReturnsTuples(Node *parsetree) *** 1147,1155 **** ... - case T_ExplainStmt: - return true; - Is this not a oversight in the final patch?
It is. I attached patch which does not have this part.
+ /* This is short-lived, so needn't allocate in function's cxt */ + plpgsql_Datums = palloc(sizeof(PLpgSQL_datum *) * datums_alloc); ... + compile_tmp_cxt = MemoryContextSwitchTo(func_cxt); I wonder why not having the datums into the func_cxt too.
Actually I think we might not need that function memory context for anonymous code blocks at all since we don't cache compiled functions. But I am not sure so I basically copied it from standard function compiler to be on safe side. I am sure commiter will comment on this :)
-- Regards Petr Jelinek (PJMODOS)
Re: GRANT ON ALL IN schema
От:
Petr Jelinek <pjmodos@pjmodos.net>
Дата:
Jaime Casanova napsal(a):
Fixed.
Fixed.
Right.
On Sun, Sep 27, 2009 at 11:54 AM, Robert Haas <robertmhaas@gmail.com> wrote:If this patch looks good now, can you mark it Ready for Committer in the CommitFest app? If there are any remaining issues, please post a further review.while i'm not the reviewer this patch doesn't apply cleanly anymore...
Fixed.
some comments: 1) in docs for REVOKE you're omitting the SCHEMA part of the new syntax.
Fixed.
2) i think that getNamespacesObjectsOids() could be rewritten in something like:
Right.
-- Regards Petr Jelinek (PJMODOS)
Re: Anonymous code blocks (was: Re: GRANT ON ALL IN schema)
От:
Dimitri Fontaine <dfontaine@hi-media.com>
Дата:
