Author: Noah Misch Commit: Noah Misch Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner. This switches the default ACL to what the documentation has recommended since CVE-2018-1058. Upgrades will carry forward any old ownership and ACL. Sites that declined the 2018 recommendation should take a fresh look. Recipes for commissioning a new database cluster from scratch may need to create a schema, grant more privileges, etc. Out-of-tree test suites may require such updates. Reviewed by FIXME. Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 60c7e11..8b3b37b 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8925,7 +8925,7 @@ $d$; -- But creation of user mappings for non-superusers should fail CREATE USER MAPPING FOR public SERVER loopback_nopw; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; -CREATE FOREIGN TABLE ft1_nopw ( +CREATE FOREIGN TABLE pg_temp.ft1_nopw ( c1 int NOT NULL, c2 int NOT NULL, c3 text, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 151f4f1..bf35097 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2595,7 +2595,7 @@ $d$; CREATE USER MAPPING FOR public SERVER loopback_nopw; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; -CREATE FOREIGN TABLE ft1_nopw ( +CREATE FOREIGN TABLE pg_temp.ft1_nopw ( c1 int NOT NULL, c2 int NOT NULL, c3 text, diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 1e9a462..1b30a0d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2982,20 +2982,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the - USAGE privilege on the schema. To allow users - to make use of the objects in the schema, additional privileges - might need to be granted, as appropriate for the object. + USAGE privilege on the schema. By default, everyone + has that privilege on the schema public. To allow + users to make use of the objects in a schema, additional privileges might + need to be granted, as appropriate for the object. - A user can also be allowed to create objects in someone else's - schema. To allow that, the CREATE privilege on - the schema needs to be granted. Note that by default, everyone - has CREATE and USAGE privileges on - the schema - public. This allows all users that are able to - connect to a given database to create objects in its - public schema. + A user can also be allowed to create objects in someone else's schema. To + allow that, the CREATE privilege on the schema needs to + be granted. In databases upgraded from + PostgreSQL 13 or earlier, everyone has that + privilege on the schema public. Some usage patterns call for revoking that privilege: @@ -3068,20 +3066,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; database owner attack. --> Constrain ordinary users to user-private schemas. To implement this, - issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, - and create a schema for each user with the same name as that user. - Recall that the default search path starts - with $user, which resolves to the user name. - Therefore, if each user has a separate schema, they access their own - schemas by default. After adopting this pattern in a database where - untrusted users had already logged in, consider auditing the public - schema for objects named like objects in + first issue REVOKE CREATE ON SCHEMA public FROM + PUBLIC. Then, for every user needing to create non-temporary + objects, create a schema with the same name as that user. Recall that + the default search path starts with $user, which + resolves to the user name. Therefore, if each user has a separate + schema, they access their own schemas by default. After adopting this + pattern in a database where untrusted users had already logged in, + consider auditing the public schema for objects named like objects in schema pg_catalog. This pattern is a secure schema usage pattern unless an untrusted user is the database owner or holds the CREATEROLE privilege, in which case no secure schema usage pattern exists. + If the database originated in an upgrade + from PostgreSQL 13 or earlier, + the REVOKE is essential. Otherwise, the default + configuration follows this pattern; ordinary users can create only + temporary objects until a privileged user furnishes a schema. @@ -3090,10 +3093,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = - "$user". Everyone retains the ability to create objects in - the public schema, but only qualified names will choose those objects. - While qualified table references are fine, calls to functions in the - public schema will be unsafe or + "$user". Then, grant privileges to create in the public + schema. Only qualified names will choose public schema objects. While + qualified table references are fine, calls to functions in the public + schema will be unsafe or unreliable. If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner @@ -3103,11 +3106,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; - Keep the default. All users access the public schema implicitly. This + Keep the default search path, and grant privileges to create in the + public schema. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a - single user or a few mutually-trusting users. + single user or a few mutually-trusting users. In databases upgraded + from PostgreSQL 13 or earlier, this is the + default. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 758493c..768a37f 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -577,13 +577,14 @@ DROP ROLE doomed_role; The pg_database_owner role has one implicit, - situation-dependent member, namely the owner of the current database. The - role conveys no rights at first. Like any role, it can own objects or - receive grants of access privileges. Consequently, once - pg_database_owner has rights within a template database, - each owner of a database instantiated from that template will exercise those - rights. pg_database_owner cannot be a member of any - role, and it cannot have non-implicit members. + situation-dependent member, namely the owner of the current database. Like + any role, it can own objects or receive grants of access privileges. + Consequently, once pg_database_owner has rights within a + template database, each owner of a database instantiated from that template + will exercise those rights. pg_database_owner cannot be + a member of any role, and it cannot have non-implicit members. Initially, + this role owns the public schema, so each database owner + governs local use of the schema. @@ -632,8 +633,8 @@ GRANT pg_signal_backend TO admin_user; horse others with relative ease. The strongest protection is tight control over who can define objects. Where that is infeasible, write queries referring only to objects having trusted owners. Remove - from search_path the public schema and any other schemas - that permit untrusted users to create objects. + from search_path any schemas that permit untrusted users + to create objects. diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index 62540a1..3a72fbb 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -1696,8 +1696,7 @@ setup_privileges(FILE *cmdfd) CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", " CppAsString2(RELKIND_SEQUENCE) ")" " AND relacl IS NULL;\n\n", - "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n", - "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n", + "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n", "REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n", "INSERT INTO pg_init_privs " " (objoid, classoid, objsubid, initprivs, privtype)" diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 0a3f40d..d59e063 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1568,10 +1568,11 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout) * no-mans-land between being a system object and a user object. * CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just * a comment and an indication of ownership. If the owner is the - * default, that DUMP_COMPONENT_DEFINITION is superfluous. + * default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before + * v14, the default owner was BOOTSTRAP_SUPERUSERID. */ nsinfo->dobj.dump = DUMP_COMPONENT_ALL; - if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID) + if (nsinfo->nspowner == DEFAULT_ROLE_DATABASE_OWNER) nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION; nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL; } @@ -4777,21 +4778,26 @@ getNamespaces(Archive *fout, int *numNamespaces) PQExpBuffer init_racl_subquery = createPQExpBuffer(); /* - * Bypass pg_init_privs.initprivs for the public schema. Dropping and - * recreating the schema detaches it from its pg_init_privs row, but - * an empty destination database starts with this ACL nonetheless. - * Also, we support dump/reload of public schema ownership changes. - * ALTER SCHEMA OWNER filters nspacl through aclnewowner(), but - * initprivs continues to reflect the initial owner (the bootstrap - * superuser). Hence, synthesize the value that nspacl will have - * after the restore's ALTER SCHEMA OWNER. + * Bypass pg_init_privs.initprivs for the public schema, for several + * reasons. First, dropping and recreating the schema detaches it + * from its pg_init_privs row, but an empty destination database + * starts with this ACL nonetheless. Second, we support dump/reload + * of public schema ownership changes. ALTER SCHEMA OWNER filters + * nspacl through aclnewowner(), but initprivs continues to reflect + * the initial owner. Hence, synthesize the value that nspacl will + * have after the restore's ALTER SCHEMA OWNER. Third, this makes the + * destination database match the source's ACL, even if the latter was + * an initdb-default ACL, which changed in v14. An upgrade pulls in + * changes to most system object ACLs that the DBA had not customized. + * We've made the public schema depart from that, because changing its + * ACL so easily breaks applications. */ buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery, init_racl_subquery, "n.nspacl", "n.nspowner", "CASE WHEN n.nspname = 'public' THEN array[" " format('%s=UC/%s', " " n.nspowner::regrole, n.nspowner::regrole)," - " format('=UC/%s', n.nspowner::regrole)]::aclitem[] " + " format('=U/%s', n.nspowner::regrole)]::aclitem[] " "ELSE pip.initprivs END", "'n'", dopt->binary_upgrade); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 4693292..02b4418 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -614,7 +614,9 @@ my %tests = ( }, 'ALTER SCHEMA public OWNER TO' => { - # see test "REVOKE CREATE ON SCHEMA public" for causative create_sql + create_order => 15, + create_sql => + 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";', regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m, like => { %full_runs, section_pre_data => 1, @@ -3342,16 +3344,12 @@ my %tests = ( unlike => { no_privs => 1, }, }, - 'REVOKE CREATE ON SCHEMA public FROM public' => { + 'REVOKE ALL ON SCHEMA public' => { create_order => 16, - create_sql => ' - REVOKE CREATE ON SCHEMA public FROM public; - ALTER SCHEMA public OWNER TO "regress_quoted \"" role"; - REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";', + create_sql => + 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";', regexp => qr/^ \QREVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";\E - \n\QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E - \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E /xm, like => { %full_runs, section_pre_data => 1, }, unlike => { no_privs => 1, }, diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat index 988f1c4..7932aa6 100644 --- a/src/include/catalog/pg_namespace.dat +++ b/src/include/catalog/pg_namespace.dat @@ -21,6 +21,6 @@ # update dumpComment() if changing this descr { oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE', descr => 'standard public schema', - nspname => 'public', nspacl => '_null_' }, + nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' }, ] diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out index a1a24df..5234feb 100644 --- a/src/pl/plperl/expected/plperl_setup.out +++ b/src/pl/plperl/expected/plperl_setup.out @@ -25,6 +25,9 @@ CREATE EXTENSION plperl; CREATE EXTENSION plperlu; -- fail ERROR: permission denied to create extension "plperlu" HINT: Must be superuser to create this extension. +CREATE SCHEMA plperl_setup_scratch; +SET search_path = plperl_setup_scratch; +GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2; CREATE FUNCTION foo1() returns int language plperl as '1;'; SELECT foo1(); foo1 @@ -34,6 +37,7 @@ SELECT foo1(); -- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters \c - +SET search_path = plperl_setup_scratch; SET ROLE regress_user1; -- Should be able to change privileges on the language revoke all on language plperl from public; diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql index 7484478..a89cf56 100644 --- a/src/pl/plperl/sql/plperl_setup.sql +++ b/src/pl/plperl/sql/plperl_setup.sql @@ -27,12 +27,16 @@ SET ROLE regress_user1; CREATE EXTENSION plperl; CREATE EXTENSION plperlu; -- fail +CREATE SCHEMA plperl_setup_scratch; +SET search_path = plperl_setup_scratch; +GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2; CREATE FUNCTION foo1() returns int language plperl as '1;'; SELECT foo1(); -- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters \c - +SET search_path = plperl_setup_scratch; SET ROLE regress_user1; diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index c133e73..cb9774e 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace; ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2; SET SESSION ROLE regress_tablespace_user2; -CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail +CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint; REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail @@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE; DROP ROLE regress_tablespace_user1; DROP ROLE regress_tablespace_user2; + +-- Rest of this suite can use the public schema freely. +GRANT ALL ON SCHEMA public TO public; diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index 1bbe7e0..e7629d4 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int); CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace; ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2; SET SESSION ROLE regress_tablespace_user2; -CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail +CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail ERROR: permission denied for tablespace regress_tblspace ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint; REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail @@ -934,3 +934,5 @@ drop cascades to table testschema.atable drop cascades to table testschema.tablespace_acl DROP ROLE regress_tablespace_user1; DROP ROLE regress_tablespace_user2; +-- Rest of this suite can use the public schema freely. +GRANT ALL ON SCHEMA public TO public;