Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 2897D6339B8 for ; Wed, 17 Jun 2009 05:29:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 89257-02 for ; Wed, 17 Jun 2009 05:29:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from phoenix.advel.cz (phoenix.advel.cz [81.0.239.26]) by mail.postgresql.org (Postfix) with SMTP id E2654632928 for ; Wed, 17 Jun 2009 05:29:21 -0300 (ADT) Received: (qmail 31024 invoked from network); 17 Jun 2009 10:30:42 +0200 Received: from unknown (HELO ?10.12.0.96?) (88.103.48.48) by 192.168.1.50 with SMTP; 17 Jun 2009 10:30:42 +0200 Message-ID: <4A38A956.8080600@pjmodos.net> Date: Wed, 17 Jun 2009 10:29:10 +0200 From: Petr Jelinek User-Agent: Thunderbird 2.0.0.21 (Windows/20090302) MIME-Version: 1.0 To: PostgreSQL-development Subject: Re: GRANT ON ALL IN schema References: <4A37BF63.50008@pjmodos.net> <4A37E122.8070303@pjmodos.net> In-Reply-To: <4A37E122.8070303@pjmodos.net> Content-Type: multipart/mixed; boundary="------------090706070800030206090807" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.088 tagged_above=0 required=5 tests=AWL=-1.907, FB_REPLIC_CAP=3.995 X-Spam-Level: ** X-Archive-Number: 200906/1034 X-Sequence-Number: 140089 This is a multi-part message in MIME format. --------------090706070800030206090807 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit 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) --------------090706070800030206090807 Content-Type: text/plain; name="grant-on-all.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="grant-on-all.diff" 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; --------------090706070800030206090807--