From ab89e465cb2032017c4888399f47a76ac16eaf40 Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Tue, 28 Mar 2017 18:58:55 +0300 Subject: [PATCH] Altering default privileges on schemas MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Extend ALTER DEFAULT PRIVILEGES command to schemas. Author: Matheus Oliveira Reviewed-by: Petr Jelínek, Ashutosh Sharma https://commitfest.postgresql.org/13/887/ --- .../sgml/ref/alter_default_privileges.sgml | 17 ++++- src/backend/catalog/aclchk.c | 21 +++++++ src/backend/catalog/objectaddress.c | 14 ++++- src/backend/catalog/pg_namespace.c | 21 +++++-- src/backend/parser/gram.y | 4 +- src/bin/pg_dump/dumputils.c | 4 +- src/bin/pg_dump/pg_dump.c | 3 + src/bin/psql/describe.c | 4 +- src/bin/psql/tab-complete.c | 2 +- src/include/catalog/pg_default_acl.h | 1 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/privileges.out | 62 +++++++++++++++++++ src/test/regress/sql/privileges.sql | 34 ++++++++++ 13 files changed, 176 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index 04064d399c..e3363f868a 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -46,6 +46,10 @@ GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] +GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } + ON SCHEMAS + TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } @@ -71,6 +75,12 @@ REVOKE [ GRANT OPTION FOR ] ON TYPES FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] + +REVOKE [ GRANT OPTION FOR ] + { USAGE | CREATE | ALL [ PRIVILEGES ] } + ON SCHEMAS + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] @@ -81,8 +91,9 @@ REVOKE [ GRANT OPTION FOR ] ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, - only the privileges for tables (including views and foreign tables), - sequences, functions, and types (including domains) can be altered. + only the privileges for schemas, tables (including views and foreign + tables), sequences, functions, and types (including domains) can be + altered. @@ -125,6 +136,8 @@ REVOKE [ GRANT OPTION FOR ] are altered for objects later created in that schema. If IN SCHEMA is omitted, the global default privileges are altered. + IN SCHEMA is not allowed when using ON SCHEMAS + as schemas can't be nested. diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index d01930f4a8..2d535c2aad 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -959,6 +959,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s all_privileges = ACL_ALL_RIGHTS_TYPE; errormsg = gettext_noop("invalid privilege type %s for type"); break; + case ACL_OBJECT_NAMESPACE: + all_privileges = ACL_ALL_RIGHTS_NAMESPACE; + errormsg = gettext_noop("invalid privilege type %s for schema"); + break; default: elog(ERROR, "unrecognized GrantStmt.objtype: %d", (int) action->objtype); @@ -1146,6 +1150,16 @@ SetDefaultACL(InternalDefaultACL *iacls) this_privileges = ACL_ALL_RIGHTS_TYPE; break; + case ACL_OBJECT_NAMESPACE: + if (OidIsValid(iacls->nspid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_GRANT_OPERATION), + errmsg("cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS"))); + objtype = DEFACLOBJ_NAMESPACE; + if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS) + this_privileges = ACL_ALL_RIGHTS_NAMESPACE; + break; + default: elog(ERROR, "unrecognized objtype: %d", (int) iacls->objtype); @@ -1369,6 +1383,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid) case DEFACLOBJ_TYPE: iacls.objtype = ACL_OBJECT_TYPE; break; + case DEFACLOBJ_NAMESPACE: + iacls.objtype = ACL_OBJECT_NAMESPACE; + break; default: /* Shouldn't get here */ elog(ERROR, "unexpected default ACL type: %d", @@ -5259,6 +5276,10 @@ get_user_default_acl(GrantObjectType objtype, Oid ownerId, Oid nsp_oid) defaclobjtype = DEFACLOBJ_TYPE; break; + case ACL_OBJECT_NAMESPACE: + defaclobjtype = DEFACLOBJ_NAMESPACE; + break; + default: return NULL; } diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 2948d64fa7..1eb7930901 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -1843,11 +1843,14 @@ get_object_address_defacl(List *object, bool missing_ok) case DEFACLOBJ_TYPE: objtype_str = "types"; break; + case DEFACLOBJ_NAMESPACE: + objtype_str = "schemas"; + break; default: ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unrecognized default ACL object type %c", objtype), - errhint("Valid object types are \"r\", \"S\", \"f\", and \"T\"."))); + errhint("Valid object types are \"r\", \"S\", \"f\", \"T\" and \"s\"."))); } /* @@ -3255,6 +3258,11 @@ getObjectDescription(const ObjectAddress *object) _("default privileges on new types belonging to role %s"), GetUserNameFromId(defacl->defaclrole, false)); break; + case DEFACLOBJ_NAMESPACE: + appendStringInfo(&buffer, + _("default privileges on new schemas belonging to role %s"), + GetUserNameFromId(defacl->defaclrole, false)); + break; default: /* shouldn't get here */ appendStringInfo(&buffer, @@ -4762,6 +4770,10 @@ getObjectIdentityParts(const ObjectAddress *object, appendStringInfoString(&buffer, " on types"); break; + case DEFACLOBJ_NAMESPACE: + appendStringInfoString(&buffer, + " on schemas"); + break; } if (objname) diff --git a/src/backend/catalog/pg_namespace.c b/src/backend/catalog/pg_namespace.c index 5672536d31..613b963683 100644 --- a/src/backend/catalog/pg_namespace.c +++ b/src/backend/catalog/pg_namespace.c @@ -31,10 +31,11 @@ * Create a namespace (schema) with the given name and owner OID. * * If isTemp is true, this schema is a per-backend schema for holding - * temporary tables. Currently, the only effect of that is to prevent it - * from being linked as a member of any active extension. (If someone - * does CREATE TEMP TABLE in an extension script, we don't want the temp - * schema to become part of the extension.) + * temporary tables. Currently, it is used to prevent it from being + * linked as a member of any active extension. (If someone does CREATE + * TEMP TABLE in an extension script, we don't want the temp schema to + * become part of the extension). And to avoid checking for default ACL + * for temp namespace (as it is not necessary). * --------------- */ Oid @@ -49,6 +50,7 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp) TupleDesc tupDesc; ObjectAddress myself; int i; + Acl *nspacl; /* sanity checks */ if (!nspName) @@ -60,6 +62,12 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp) (errcode(ERRCODE_DUPLICATE_SCHEMA), errmsg("schema \"%s\" already exists", nspName))); + if (!isTemp) + nspacl = get_user_default_acl(ACL_OBJECT_NAMESPACE, ownerId, + InvalidOid); + else + nspacl = NULL; + /* initialize nulls and values */ for (i = 0; i < Natts_pg_namespace; i++) { @@ -69,7 +77,10 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp) namestrcpy(&nname, nspName); values[Anum_pg_namespace_nspname - 1] = NameGetDatum(&nname); values[Anum_pg_namespace_nspowner - 1] = ObjectIdGetDatum(ownerId); - nulls[Anum_pg_namespace_nspacl - 1] = true; + if (nspacl != NULL) + values[Anum_pg_namespace_nspacl - 1] = PointerGetDatum(nspacl); + else + nulls[Anum_pg_namespace_nspacl - 1] = true; nspdesc = heap_open(NamespaceRelationId, RowExclusiveLock); tupDesc = nspdesc->rd_att; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 19dd77d787..20865c0ee0 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -668,7 +668,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROW ROWS RULE - SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES + SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW SIMILAR SIMPLE SKIP SLOT SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P @@ -7035,6 +7035,7 @@ defacl_privilege_target: | FUNCTIONS { $$ = ACL_OBJECT_FUNCTION; } | SEQUENCES { $$ = ACL_OBJECT_SEQUENCE; } | TYPES_P { $$ = ACL_OBJECT_TYPE; } + | SCHEMAS { $$ = ACL_OBJECT_NAMESPACE; } ; @@ -14713,6 +14714,7 @@ unreserved_keyword: | RULE | SAVEPOINT | SCHEMA + | SCHEMAS | SCROLL | SEARCH | SECOND_P diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index b41f2b9125..c74153acce 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -520,7 +520,9 @@ do { \ CONVERT_PRIV('X', "EXECUTE"); else if (strcmp(type, "LANGUAGE") == 0) CONVERT_PRIV('U', "USAGE"); - else if (strcmp(type, "SCHEMA") == 0) + else if (strcmp(type, "SCHEMA") == 0 || + strcmp(type, "SCHEMAS") == 0 + ) { CONVERT_PRIV('C', "CREATE"); CONVERT_PRIV('U', "USAGE"); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index ba34cc163e..262f5539bc 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -14295,6 +14295,9 @@ dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo) case DEFACLOBJ_TYPE: type = "TYPES"; break; + case DEFACLOBJ_NAMESPACE: + type = "SCHEMAS"; + break; default: /* shouldn't get here */ exit_horribly(NULL, diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index bcf675208b..b0f3e5e347 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1028,7 +1028,7 @@ listDefaultACLs(const char *pattern) printfPQExpBuffer(&buf, "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n" " n.nspname AS \"%s\",\n" - " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n" + " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n" " ", gettext_noop("Owner"), gettext_noop("Schema"), @@ -1040,6 +1040,8 @@ listDefaultACLs(const char *pattern) gettext_noop("function"), DEFACLOBJ_TYPE, gettext_noop("type"), + DEFACLOBJ_NAMESPACE, + gettext_noop("schema"), gettext_noop("Type")); printACLColumn(&buf, "d.defaclacl"); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index f7494065de..dc2794d48a 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -2796,7 +2796,7 @@ psql_completion(const char *text, int start, int end) * to the kinds of objects supported. */ if (HeadMatches3("ALTER","DEFAULT","PRIVILEGES")) - COMPLETE_WITH_LIST4("TABLES", "SEQUENCES", "FUNCTIONS", "TYPES"); + COMPLETE_WITH_LIST5("TABLES", "SEQUENCES", "FUNCTIONS", "TYPES", "SCHEMAS"); else COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, " UNION SELECT 'ALL FUNCTIONS IN SCHEMA'" diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h index 42fb224f9d..78bbeb64fe 100644 --- a/src/include/catalog/pg_default_acl.h +++ b/src/include/catalog/pg_default_acl.h @@ -70,5 +70,6 @@ typedef FormData_pg_default_acl *Form_pg_default_acl; #define DEFACLOBJ_SEQUENCE 'S' /* sequence */ #define DEFACLOBJ_FUNCTION 'f' /* function */ #define DEFACLOBJ_TYPE 'T' /* type */ +#define DEFACLOBJ_NAMESPACE 'n' /* namespace */ #endif /* PG_DEFAULT_ACL_H */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 6cd36c7fe3..cd21a789d5 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -344,6 +344,7 @@ PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD) PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD) PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD) PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD) +PG_KEYWORD("schemas", SCHEMAS, UNRESERVED_KEYWORD) PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD) PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD) PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD) diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index f349980759..c6e7031bef 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1356,6 +1356,64 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no (1 row) ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; +ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error +ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS +ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2; +CREATE SCHEMA testns2; +SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes + has_schema_privilege +---------------------- + t +(1 row) + +SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no + has_schema_privilege +---------------------- + f +(1 row) + +ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2; +CREATE SCHEMA testns3; +SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no + has_schema_privilege +---------------------- + f +(1 row) + +SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no + has_schema_privilege +---------------------- + f +(1 row) + +ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2; +CREATE SCHEMA testns4; +SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes + has_schema_privilege +---------------------- + t +(1 row) + +SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes + has_schema_privilege +---------------------- + t +(1 row) + +ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2; +CREATE SCHEMA testns5; +SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no + has_schema_privilege +---------------------- + f +(1 row) + +SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no + has_schema_privilege +---------------------- + f +(1 row) + SET ROLE regress_user1; CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no @@ -1403,6 +1461,10 @@ SELECT count(*) DROP SCHEMA testns CASCADE; NOTICE: drop cascades to table testns.acltest1 +DROP SCHEMA testns2 CASCADE; +DROP SCHEMA testns3 CASCADE; +DROP SCHEMA testns4 CASCADE; +DROP SCHEMA testns5 CASCADE; 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; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 166e903012..38215954da 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -816,6 +816,36 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; +ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error + +ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2; + +CREATE SCHEMA testns2; + +SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes +SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no + +ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2; + +CREATE SCHEMA testns3; + +SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no +SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no + +ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2; + +CREATE SCHEMA testns4; + +SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes +SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes + +ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2; + +CREATE SCHEMA testns5; + +SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no +SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no + SET ROLE regress_user1; CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; @@ -853,6 +883,10 @@ SELECT count(*) WHERE nspname = 'testns'; DROP SCHEMA testns CASCADE; +DROP SCHEMA testns2 CASCADE; +DROP SCHEMA testns3 CASCADE; +DROP SCHEMA testns4 CASCADE; +DROP SCHEMA testns5 CASCADE; SELECT d.* -- check that entries went away FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid -- 2.40.0