From 11ca04b4b71dd5bebafc97eefe96614873158f87 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 12 Oct 2009 20:39:42 +0000 Subject: [PATCH] Support GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA. Petr Jelinek --- doc/src/sgml/ref/grant.sgml | 18 +++- doc/src/sgml/ref/revoke.sgml | 11 +- src/backend/catalog/aclchk.c | 127 ++++++++++++++++++++++- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 3 +- src/backend/parser/gram.y | 52 +++++++++- src/include/nodes/parsenodes.h | 10 +- src/include/parser/kwlist.h | 5 +- src/test/regress/expected/privileges.out | 56 +++++++++- src/test/regress/sql/privileges.sql | 35 ++++++- 10 files changed, 300 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 2dcf4aa0f0..2e8f2050f1 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -1,5 +1,5 @@ @@ -23,7 +23,8 @@ PostgreSQL documentation GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } - ON [ TABLE ] table_name [, ...] + ON { [ TABLE ] table_name [, ...] + | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] ) @@ -33,7 +34,8 @@ GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( sequence_name [, ...] + ON { SEQUENCE sequence_name [, ...] + | ALL SEQUENCES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } @@ -49,7 +51,8 @@ GRANT { USAGE | ALL [ PRIVILEGES ] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } - ON FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] + ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] + | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } @@ -90,6 +93,13 @@ GRANT role_name [, ...] TO + + There is also an option to grant privileges on all objects of the same + type within one or more schemas. This functionality is currently supported + only for tables, sequences, and functions (but note that ALL + TABLES is considered to include views). + + The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index f46c9174aa..0b8aea534c 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -1,5 +1,5 @@ @@ -24,7 +24,8 @@ PostgreSQL documentation REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } - ON [ TABLE ] table_name [, ...] + ON { [ TABLE ] table_name [, ...] + | ALL TABLES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] @@ -38,7 +39,8 @@ REVOKE [ GRANT OPTION FOR ] REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } - ON SEQUENCE sequence_name [, ...] + ON { SEQUENCE sequence_name [, ...] + | ALL SEQUENCES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] @@ -62,7 +64,8 @@ REVOKE [ GRANT OPTION FOR ] REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } - ON FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] + ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] + | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index b06e587a1b..8b2599a99f 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.155 2009/10/05 19:24:35 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.156 2009/10/12 20:39:39 tgl Exp $ * * NOTES * See acl.h. @@ -110,6 +110,8 @@ static void SetDefaultACLsInSchemas(InternalDefaultACL *iacls, List *nspnames); static void SetDefaultACL(InternalDefaultACL *iacls); static List *objectNamesToOids(GrantObjectType objtype, List *objnames); +static List *objectsInSchemaToOids(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, @@ -335,7 +337,22 @@ ExecuteGrantStmt(GrantStmt *stmt) */ istmt.is_grant = stmt->is_grant; istmt.objtype = stmt->objtype; - istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects); + + /* Collect the OIDs of the target objects */ + switch (stmt->targtype) + { + case ACL_TARGET_OBJECT: + istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects); + break; + case ACL_TARGET_ALL_IN_SCHEMA: + istmt.objects = objectsInSchemaToOids(stmt->objtype, stmt->objects); + break; + /* ACL_TARGET_DEFAULTS should not be seen here */ + default: + elog(ERROR, "unrecognized GrantStmt.targtype: %d", + (int) stmt->targtype); + } + /* all_privs to be filled below */ /* privileges to be filled below */ istmt.col_privs = NIL; /* may get filled below */ @@ -656,6 +673,112 @@ objectNamesToOids(GrantObjectType objtype, List *objnames) return objects; } +/* + * objectsInSchemaToOids + * + * Find all objects of a given type in specified schemas, and make a list + * of their Oids. We check USAGE privilege on the schemas, but there is + * no privilege checking on the individual objects here. + */ +static List * +objectsInSchemaToOids(GrantObjectType objtype, List *nspnames) +{ + List *objects = NIL; + ListCell *cell; + + foreach(cell, nspnames) + { + char *nspname = strVal(lfirst(cell)); + Oid namespaceId; + List *objs; + + namespaceId = LookupExplicitNamespace(nspname); + + switch (objtype) + { + case ACL_OBJECT_RELATION: + /* Process both regular tables and views */ + objs = getRelationsInNamespace(namespaceId, RELKIND_RELATION); + objects = list_concat(objects, objs); + objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW); + objects = list_concat(objects, objs); + break; + case ACL_OBJECT_SEQUENCE: + objs = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE); + objects = list_concat(objects, objs); + break; + case ACL_OBJECT_FUNCTION: + { + ScanKeyData key[1]; + Relation rel; + HeapScanDesc scan; + HeapTuple tuple; + + 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: + /* should not happen */ + elog(ERROR, "unrecognized GrantStmt.objtype: %d", + (int) objtype); + } + } + + return objects; +} + +/* + * getRelationsInNamespace + * + * Return Oid list of relations in given namespace filtered by relation kind + */ +static List * +getRelationsInNamespace(Oid namespaceId, char relkind) +{ + List *relations = NIL; + ScanKeyData key[2]; + Relation rel; + HeapScanDesc scan; + HeapTuple tuple; + + 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; +} + + /* * ALTER DEFAULT PRIVILEGES statement */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 8365586a00..efbb0f57be 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.445 2009/10/12 19:49:24 adunstan Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.446 2009/10/12 20:39:39 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2350,6 +2350,7 @@ _copyGrantStmt(GrantStmt *from) GrantStmt *newnode = makeNode(GrantStmt); COPY_SCALAR_FIELD(is_grant); + COPY_SCALAR_FIELD(targtype); COPY_SCALAR_FIELD(objtype); COPY_NODE_FIELD(objects); COPY_NODE_FIELD(privileges); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 2c916af1b7..de5497c492 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -22,7 +22,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.367 2009/10/12 19:49:24 adunstan Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.368 2009/10/12 20:39:40 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -990,6 +990,7 @@ static bool _equalGrantStmt(GrantStmt *a, GrantStmt *b) { COMPARE_SCALAR_FIELD(is_grant); + COMPARE_SCALAR_FIELD(targtype); COMPARE_SCALAR_FIELD(objtype); COMPARE_NODE_FIELD(objects); COMPARE_NODE_FIELD(privileges); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 50ce33070f..4f50287643 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.683 2009/10/12 19:49:24 adunstan Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.684 2009/10/12 20:39:41 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -95,6 +95,7 @@ /* Private struct for the result of privilege_target production */ typedef struct PrivTarget { + GrantTargetType targtype; GrantObjectType objtype; List *objs; } PrivTarget; @@ -480,7 +481,7 @@ static TypeName *TableFuncTypeName(List *columns); EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD - FREEZE FROM FULL FUNCTION + FREEZE FROM FULL FUNCTION FUNCTIONS GLOBAL GRANT GRANTED GREATEST GROUP_P @@ -518,13 +519,13 @@ static TypeName *TableFuncTypeName(List *columns); 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 + 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 TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP + TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P @@ -4321,6 +4322,7 @@ GrantStmt: GRANT privileges ON privilege_target TO grantee_list GrantStmt *n = makeNode(GrantStmt); n->is_grant = true; n->privileges = $2; + n->targtype = ($4)->targtype; n->objtype = ($4)->objtype; n->objects = ($4)->objs; n->grantees = $6; @@ -4337,6 +4339,7 @@ RevokeStmt: n->is_grant = false; n->grant_option = false; n->privileges = $2; + n->targtype = ($4)->targtype; n->objtype = ($4)->objtype; n->objects = ($4)->objs; n->grantees = $6; @@ -4350,6 +4353,7 @@ RevokeStmt: n->is_grant = false; n->grant_option = true; n->privileges = $5; + n->targtype = ($7)->targtype; n->objtype = ($7)->objtype; n->objects = ($7)->objs; n->grantees = $9; @@ -4432,6 +4436,7 @@ privilege_target: qualified_name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_RELATION; n->objs = $1; $$ = n; @@ -4439,6 +4444,7 @@ privilege_target: | TABLE qualified_name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_RELATION; n->objs = $2; $$ = n; @@ -4446,6 +4452,7 @@ privilege_target: | SEQUENCE qualified_name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_SEQUENCE; n->objs = $2; $$ = n; @@ -4453,6 +4460,7 @@ privilege_target: | FOREIGN DATA_P WRAPPER name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_FDW; n->objs = $4; $$ = n; @@ -4460,6 +4468,7 @@ privilege_target: | FOREIGN SERVER name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_FOREIGN_SERVER; n->objs = $3; $$ = n; @@ -4467,6 +4476,7 @@ privilege_target: | FUNCTION function_with_argtypes_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_FUNCTION; n->objs = $2; $$ = n; @@ -4474,6 +4484,7 @@ privilege_target: | DATABASE name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_DATABASE; n->objs = $2; $$ = n; @@ -4481,6 +4492,7 @@ privilege_target: | LANGUAGE name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_LANGUAGE; n->objs = $2; $$ = n; @@ -4488,6 +4500,7 @@ privilege_target: | SCHEMA name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_NAMESPACE; n->objs = $2; $$ = n; @@ -4495,10 +4508,35 @@ privilege_target: | TABLESPACE name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; n->objtype = ACL_OBJECT_TABLESPACE; n->objs = $2; $$ = n; } + | ALL TABLES IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = ACL_OBJECT_RELATION; + n->objs = $5; + $$ = n; + } + | ALL SEQUENCES IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = ACL_OBJECT_SEQUENCE; + n->objs = $5; + $$ = n; + } + | ALL FUNCTIONS IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = ACL_OBJECT_FUNCTION; + n->objs = $5; + $$ = n; + } ; @@ -4648,6 +4686,7 @@ DefACLAction: GrantStmt *n = makeNode(GrantStmt); n->is_grant = true; n->privileges = $2; + n->targtype = ACL_TARGET_DEFAULTS; n->objtype = $4; n->objects = NIL; n->grantees = $6; @@ -4661,6 +4700,7 @@ DefACLAction: n->is_grant = false; n->grant_option = false; n->privileges = $2; + n->targtype = ACL_TARGET_DEFAULTS; n->objtype = $4; n->objects = NIL; n->grantees = $6; @@ -4674,6 +4714,7 @@ DefACLAction: n->is_grant = false; n->grant_option = true; n->privileges = $5; + n->targtype = ACL_TARGET_DEFAULTS; n->objtype = $7; n->objects = NIL; n->grantees = $9; @@ -10535,6 +10576,7 @@ unreserved_keyword: | FORCE | FORWARD | FUNCTION + | FUNCTIONS | GLOBAL | GRANTED | HANDLER @@ -10644,6 +10686,7 @@ unreserved_keyword: | SECOND_P | SECURITY | SEQUENCE + | SEQUENCES | SERIALIZABLE | SERVER | SESSION @@ -10664,6 +10707,7 @@ unreserved_keyword: | SUPERUSER_P | SYSID | SYSTEM_P + | TABLES | TABLESPACE | TEMP | TEMPLATE diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index cf755724cc..7d7f6da159 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.407 2009/10/12 19:49:24 adunstan Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.408 2009/10/12 20:39:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1182,6 +1182,13 @@ typedef struct AlterDomainStmt * Grant|Revoke Statement * ---------------------- */ +typedef enum GrantTargetType +{ + ACL_TARGET_OBJECT, /* grant on specific named object(s) */ + ACL_TARGET_ALL_IN_SCHEMA, /* grant on all objects in given schema(s) */ + ACL_TARGET_DEFAULTS /* ALTER DEFAULT PRIVILEGES */ +} GrantTargetType; + typedef enum GrantObjectType { ACL_OBJECT_COLUMN, /* column */ @@ -1200,6 +1207,7 @@ typedef struct GrantStmt { NodeTag type; bool is_grant; /* true = GRANT, false = REVOKE */ + GrantTargetType targtype; /* type of the grant target */ GrantObjectType objtype; /* kind of object being operated on */ List *objects; /* list of RangeVar nodes, FuncWithArgs nodes, * or plain names (as Value strings) */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 4b1284f338..97b3c16ed0 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -11,7 +11,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/include/parser/kwlist.h,v 1.4 2009/10/12 19:49:24 adunstan Exp $ + * $PostgreSQL: pgsql/src/include/parser/kwlist.h,v 1.5 2009/10/12 20:39:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -164,6 +164,7 @@ PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_NAME_KEYWORD) 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) @@ -329,6 +330,7 @@ PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD) 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) @@ -357,6 +359,7 @@ PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD) 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) diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 24239276db..8214b77901 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -3,7 +3,7 @@ -- -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist -SET client_min_messages TO 'error'; +SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regressgroup1; DROP ROLE IF EXISTS regressgroup2; DROP ROLE IF EXISTS regressuser1; @@ -947,6 +947,60 @@ SELECT d.* -- check that entries went away ------------+-----------------+---------------+----------- (0 rows) +-- Grant on all objects of given type in a schema +\c - +CREATE SCHEMA testns; +CREATE TABLE testns.t1 (f1 int); +CREATE TABLE testns.t2 (f1 int); +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false + has_table_privilege +--------------------- + f +(1 row) + +GRANT ALL ON ALL TABLES IN SCHEMA testns TO regressuser1; +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- true + has_table_privilege +--------------------- + t +(1 row) + +SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- true + has_table_privilege +--------------------- + t +(1 row) + +REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regressuser1; +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false + has_table_privilege +--------------------- + f +(1 row) + +SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- false + has_table_privilege +--------------------- + f +(1 row) + +CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; +SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default + has_function_privilege +------------------------ + t +(1 row) + +REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; +SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- false + has_function_privilege +------------------------ + f +(1 row) + +SET client_min_messages TO 'warning'; +DROP SCHEMA testns CASCADE; +RESET client_min_messages; -- clean up \c drop sequence x_seq; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index eaa879efa2..c5aaffc8d5 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -5,7 +5,7 @@ -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist -SET client_min_messages TO 'error'; +SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regressgroup1; DROP ROLE IF EXISTS regressgroup2; @@ -551,6 +551,39 @@ SELECT d.* -- check that entries went away FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid WHERE nspname IS NULL AND defaclnamespace != 0; + +-- Grant on all objects of given type in a schema +\c - + +CREATE SCHEMA testns; +CREATE TABLE testns.t1 (f1 int); +CREATE TABLE testns.t2 (f1 int); + +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false + +GRANT ALL ON ALL TABLES IN SCHEMA testns TO regressuser1; + +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- true +SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- true + +REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regressuser1; + +SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false +SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- false + +CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; + +SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default + +REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; + +SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- false + +SET client_min_messages TO 'warning'; +DROP SCHEMA testns CASCADE; +RESET client_min_messages; + + -- clean up \c -- 2.40.0