On Sat, Mar 27, 2021 at 11:41:07AM +0100, Laurenz Albe wrote:
> On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
> > On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
> > > I'm attaching the patch for $SUBJECT, which applies atop the four patches
> > > from
> > > the two other threads below. For convenience of testing, I've included a
> > > rollup patch, equivalent to applying all five patches.
> >
> > I committed prerequisites from one thread, so here's a rebased rollup patch.
>
> I am happy to see this problem tackled!
Rebased. I've pushed all prerequisites, so there's no longer a distinct
rollup patch.
Author: Noah Misch <n...@leadboat.com>
Commit: Noah Misch <n...@leadboat.com>
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.gb4039...@rfd.leadboat.com
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 31b5de9..3d77cea 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9215,7 +9215,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 286dd99..36db65f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2735,7 +2735,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 4986548..e84c41a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3001,20 +3001,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
<para>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
- <literal>USAGE</literal> 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.
+ <literal>USAGE</literal> privilege on the schema. By default, everyone
+ has that privilege on the schema <literal>public</literal>. To allow
+ users to make use of the objects in a schema, additional privileges might
+ need to be granted, as appropriate for the object.
</para>
<para>
- A user can also be allowed to create objects in someone else's
- schema. To allow that, the <literal>CREATE</literal> privilege on
- the schema needs to be granted. Note that by default, everyone
- has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
- the schema
- <literal>public</literal>. This allows all users that are able to
- connect to a given database to create objects in its
- <literal>public</literal> schema.
+ A user can also be allowed to create objects in someone else's schema. To
+ allow that, the <literal>CREATE</literal> privilege on the schema needs to
+ be granted. In databases upgraded from
+ <productname>PostgreSQL</productname> 13 or earlier, everyone has that
+ privilege on the schema <literal>public</literal>.
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
revoking that privilege:
<programlisting>
@@ -3087,20 +3085,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
database owner attack. -->
<para>
Constrain ordinary users to user-private schemas. To implement this,
- issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
- and create a schema for each user with the same name as that user.
- Recall that the default search path starts
- with <literal>$user</literal>, 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 <literal>REVOKE CREATE ON SCHEMA public FROM
+ PUBLIC</literal>. 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 <literal>$user</literal>, 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 <literal>pg_catalog</literal>. This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or holds
the <literal>CREATEROLE</literal> privilege, in which case no secure
schema usage pattern exists.
</para>
<para>
+ If the database originated in an upgrade
+ from <productname>PostgreSQL</productname> 13 or earlier,
+ the <literal>REVOKE</literal> is essential. Otherwise, the default
+ configuration follows this pattern; ordinary users can create only
+ temporary objects until a privileged user furnishes a schema.
</para>
</listitem>
@@ -3109,10 +3112,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Remove the public schema from the default search path, by modifying
<link
linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
or by issuing <literal>ALTER ROLE ALL SET search_path =
- "$user"</literal>. 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 <link linkend="typeconv-func">will be unsafe or
+ "$user"</literal>. 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 <link linkend="typeconv-func">will be unsafe or
unreliable</link>. 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
@@ -3122,11 +3125,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
<listitem>
<para>
- 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 <productname>PostgreSQL</productname> 13 or earlier, this is the
+ default.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index fe0bdb7..7b7c488 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -596,13 +596,14 @@ DROP ROLE doomed_role;
<para>
The <literal>pg_database_owner</literal> 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
- <literal>pg_database_owner</literal> has rights within a template database,
- each owner of a database instantiated from that template will exercise those
- rights. <literal>pg_database_owner</literal> 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 <literal>pg_database_owner</literal> has rights within a
+ template database, each owner of a database instantiated from that template
+ will exercise those rights. <literal>pg_database_owner</literal> cannot be
+ a member of any role, and it cannot have non-implicit members. Initially,
+ this role owns the <literal>public</literal> schema, so each database owner
+ governs local use of the schema.
</para>
<para>
@@ -651,8 +652,8 @@ GRANT pg_signal_backend TO admin_user;
horse</quote> 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 <varname>search_path</varname> the public schema and any other schemas
- that permit untrusted users to create objects.
+ from <varname>search_path</varname> any schemas that permit untrusted users
+ to create objects.
</para>
<para>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 152d21e..eba3385 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1703,8 +1703,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 3211521..37e6be7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1647,11 +1647,12 @@ 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->create = false;
nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
- if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID)
+ if (nsinfo->nspowner == ROLE_PG_DATABASE_OWNER)
nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
}
@@ -4867,21 +4868,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 448b1be2..edfbe41 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -629,7 +629,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,
@@ -3414,17 +3416,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";',
- regexp => qr/^
- \QREVOKE ALL ON SCHEMA public FROM "regress_quoted
\E\\""\ role";
- \n\QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
- \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
- /xm,
+ create_sql =>
+ 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \""
role";',
+ regexp =>
+ qr/^REVOKE ALL ON SCHEMA public FROM "regress_quoted \\""
role";/m,
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 33992af..9a23975 100644
--- a/src/include/catalog/pg_namespace.dat
+++ b/src/include/catalog/pg_namespace.dat
@@ -21,6 +21,6 @@
# update dumpNamespace() 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;
