Skip to content

Commit b073c3c

Browse files
nmischnmisch
committed
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 Peter Eisentraut. Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com
1 parent cba79a1 commit b073c3c

File tree

13 files changed

+86
-64
lines changed

13 files changed

+86
-64
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9428,7 +9428,7 @@ $d$;
94289428
-- But creation of user mappings for non-superusers should fail
94299429
CREATE USER MAPPING FOR public SERVER loopback_nopw;
94309430
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
9431-
CREATE FOREIGN TABLE ft1_nopw (
9431+
CREATE FOREIGN TABLE pg_temp.ft1_nopw (
94329432
c1 int NOT NULL,
94339433
c2 int NOT NULL,
94349434
c3 text,

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2797,7 +2797,7 @@ $d$;
27972797
CREATE USER MAPPING FOR public SERVER loopback_nopw;
27982798
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
27992799

2800-
CREATE FOREIGN TABLE ft1_nopw (
2800+
CREATE FOREIGN TABLE pg_temp.ft1_nopw (
28012801
c1 int NOT NULL,
28022802
c2 int NOT NULL,
28032803
c3 text,

‎doc/src/sgml/ddl.sgml

Lines changed: 31 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -3001,20 +3001,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
30013001
<para>
30023002
By default, users cannot access any objects in schemas they do not
30033003
own. To allow that, the owner of the schema must grant the
3004-
<literal>USAGE</literal> privilege on the schema. To allow users
3005-
to make use of the objects in the schema, additional privileges
3006-
might need to be granted, as appropriate for the object.
3004+
<literal>USAGE</literal> privilege on the schema. By default, everyone
3005+
has that privilege on the schema <literal>public</literal>. To allow
3006+
users to make use of the objects in a schema, additional privileges might
3007+
need to be granted, as appropriate for the object.
30073008
</para>
30083009

30093010
<para>
3010-
A user can also be allowed to create objects in someone else's
3011-
schema. To allow that, the <literal>CREATE</literal> privilege on
3012-
the schema needs to be granted. Note that by default, everyone
3013-
has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
3014-
the schema
3015-
<literal>public</literal>. This allows all users that are able to
3016-
connect to a given database to create objects in its
3017-
<literal>public</literal> schema.
3011+
A user can also be allowed to create objects in someone else's schema. To
3012+
allow that, the <literal>CREATE</literal> privilege on the schema needs to
3013+
be granted. In databases upgraded from
3014+
<productname>PostgreSQL</productname> 14 or earlier, everyone has that
3015+
privilege on the schema <literal>public</literal>.
30183016
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
30193017
revoking that privilege:
30203018
<programlisting>
@@ -3087,20 +3085,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
30873085
database owner attack. -->
30883086
<para>
30893087
Constrain ordinary users to user-private schemas. To implement this,
3090-
issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
3091-
and create a schema for each user with the same name as that user.
3092-
Recall that the default search path starts
3093-
with <literal>$user</literal>, which resolves to the user name.
3094-
Therefore, if each user has a separate schema, they access their own
3095-
schemas by default. After adopting this pattern in a database where
3096-
untrusted users had already logged in, consider auditing the public
3097-
schema for objects named like objects in
3088+
first issue <literal>REVOKE CREATE ON SCHEMA public FROM
3089+
PUBLIC</literal>. Then, for every user needing to create non-temporary
3090+
objects, create a schema with the same name as that user. Recall that
3091+
the default search path starts with <literal>$user</literal>, which
3092+
resolves to the user name. Therefore, if each user has a separate
3093+
schema, they access their own schemas by default. After adopting this
3094+
pattern in a database where untrusted users had already logged in,
3095+
consider auditing the public schema for objects named like objects in
30983096
schema <literal>pg_catalog</literal>. This pattern is a secure schema
30993097
usage pattern unless an untrusted user is the database owner or holds
31003098
the <literal>CREATEROLE</literal> privilege, in which case no secure
31013099
schema usage pattern exists.
31023100
</para>
31033101
<para>
3102+
If the database originated in an upgrade
3103+
from <productname>PostgreSQL</productname> 14 or earlier,
3104+
the <literal>REVOKE</literal> is essential. Otherwise, the default
3105+
configuration follows this pattern; ordinary users can create only
3106+
temporary objects until a privileged user furnishes a schema.
31043107
</para>
31053108
</listitem>
31063109

@@ -3109,10 +3112,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
31093112
Remove the public schema from the default search path, by modifying
31103113
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
31113114
or by issuing <literal>ALTER ROLE ALL SET search_path =
3112-
"$user"</literal>. Everyone retains the ability to create objects in
3113-
the public schema, but only qualified names will choose those objects.
3114-
While qualified table references are fine, calls to functions in the
3115-
public schema <link linkend="typeconv-func">will be unsafe or
3115+
"$user"</literal>. Then, grant privileges to create in the public
3116+
schema. Only qualified names will choose public schema objects. While
3117+
qualified table references are fine, calls to functions in the public
3118+
schema <link linkend="typeconv-func">will be unsafe or
31163119
unreliable</link>. If you create functions or extensions in the public
31173120
schema, use the first pattern instead. Otherwise, like the first
31183121
pattern, this is secure unless an untrusted user is the database owner
@@ -3122,11 +3125,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
31223125

31233126
<listitem>
31243127
<para>
3125-
Keep the default. All users access the public schema implicitly. This
3128+
Keep the default search path, and grant privileges to create in the
3129+
public schema. All users access the public schema implicitly. This
31263130
simulates the situation where schemas are not available at all, giving
31273131
a smooth transition from the non-schema-aware world. However, this is
31283132
never a secure pattern. It is acceptable only when the database has a
3129-
single user or a few mutually-trusting users.
3133+
single user or a few mutually-trusting users. In databases upgraded
3134+
from <productname>PostgreSQL</productname> 14 or earlier, this is the
3135+
default.
31303136
</para>
31313137
</listitem>
31323138
</itemizedlist>

‎doc/src/sgml/user-manag.sgml

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -597,13 +597,14 @@ DROP ROLE doomed_role;
597597

598598
<para>
599599
The <literal>pg_database_owner</literal> role has one implicit,
600-
situation-dependent member, namely the owner of the current database. The
601-
role conveys no rights at first. Like any role, it can own objects or
602-
receive grants of access privileges. Consequently, once
603-
<literal>pg_database_owner</literal> has rights within a template database,
604-
each owner of a database instantiated from that template will exercise those
605-
rights. <literal>pg_database_owner</literal> cannot be a member of any
606-
role, and it cannot have non-implicit members.
600+
situation-dependent member, namely the owner of the current database. Like
601+
any role, it can own objects or receive grants of access privileges.
602+
Consequently, once <literal>pg_database_owner</literal> has rights within a
603+
template database, each owner of a database instantiated from that template
604+
will exercise those rights. <literal>pg_database_owner</literal> cannot be
605+
a member of any role, and it cannot have non-implicit members. Initially,
606+
this role owns the <literal>public</literal> schema, so each database owner
607+
governs local use of the schema.
607608
</para>
608609

609610
<para>
@@ -652,8 +653,8 @@ GRANT pg_signal_backend TO admin_user;
652653
horse</quote> others with relative ease. The strongest protection is tight
653654
control over who can define objects. Where that is infeasible, write
654655
queries referring only to objects having trusted owners. Remove
655-
from <varname>search_path</varname> the public schema and any other schemas
656-
that permit untrusted users to create objects.
656+
from <varname>search_path</varname> any schemas that permit untrusted users
657+
to create objects.
657658
</para>
658659

659660
<para>

‎src/bin/initdb/initdb.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1633,8 +1633,7 @@ setup_privileges(FILE *cmdfd)
16331633
CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", "
16341634
CppAsString2(RELKIND_SEQUENCE) ")"
16351635
" AND relacl IS NULL;\n\n",
1636-
"GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n",
1637-
"GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
1636+
"GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n",
16381637
"REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n",
16391638
"INSERT INTO pg_init_privs "
16401639
" (objoid, classoid, objsubid, initprivs, privtype)"

‎src/bin/pg_dump/pg_dump.c

Lines changed: 17 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1623,11 +1623,12 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
16231623
* no-mans-land between being a system object and a user object.
16241624
* CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
16251625
* a comment and an indication of ownership. If the owner is the
1626-
* default, that DUMP_COMPONENT_DEFINITION is superfluous.
1626+
* default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before
1627+
* v15, the default owner was BOOTSTRAP_SUPERUSERID.
16271628
*/
16281629
nsinfo->create = false;
16291630
nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
1630-
if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID)
1631+
if (nsinfo->nspowner == ROLE_PG_DATABASE_OWNER)
16311632
nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
16321633
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
16331634
}
@@ -4850,21 +4851,26 @@ getNamespaces(Archive *fout, int *numNamespaces)
48504851
PQExpBuffer init_racl_subquery = createPQExpBuffer();
48514852

48524853
/*
4853-
* Bypass pg_init_privs.initprivs for the public schema. Dropping and
4854-
* recreating the schema detaches it from its pg_init_privs row, but
4855-
* an empty destination database starts with this ACL nonetheless.
4856-
* Also, we support dump/reload of public schema ownership changes.
4857-
* ALTER SCHEMA OWNER filters nspacl through aclnewowner(), but
4858-
* initprivs continues to reflect the initial owner (the bootstrap
4859-
* superuser). Hence, synthesize the value that nspacl will have
4860-
* after the restore's ALTER SCHEMA OWNER.
4854+
* Bypass pg_init_privs.initprivs for the public schema, for several
4855+
* reasons. First, dropping and recreating the schema detaches it
4856+
* from its pg_init_privs row, but an empty destination database
4857+
* starts with this ACL nonetheless. Second, we support dump/reload
4858+
* of public schema ownership changes. ALTER SCHEMA OWNER filters
4859+
* nspacl through aclnewowner(), but initprivs continues to reflect
4860+
* the initial owner. Hence, synthesize the value that nspacl will
4861+
* have after the restore's ALTER SCHEMA OWNER. Third, this makes the
4862+
* destination database match the source's ACL, even if the latter was
4863+
* an initdb-default ACL, which changed in v15. An upgrade pulls in
4864+
* changes to most system object ACLs that the DBA had not customized.
4865+
* We've made the public schema depart from that, because changing its
4866+
* ACL so easily breaks applications.
48614867
*/
48624868
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
48634869
init_racl_subquery, "n.nspacl", "n.nspowner",
48644870
"CASE WHEN n.nspname = 'public' THEN array["
48654871
" format('%s=UC/%s', "
48664872
" n.nspowner::regrole, n.nspowner::regrole),"
4867-
" format('=UC/%s', n.nspowner::regrole)]::aclitem[] "
4873+
" format('=U/%s', n.nspowner::regrole)]::aclitem[] "
48684874
"ELSE pip.initprivs END",
48694875
"'n'", dopt->binary_upgrade);
48704876

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 8 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -628,7 +628,9 @@
628628
},
629629

630630
'ALTER SCHEMA public OWNER TO' => {
631-
# see test "REVOKE CREATE ON SCHEMA public" for causative create_sql
631+
create_order => 15,
632+
create_sql =>
633+
'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
632634
regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m,
633635
like => {
634636
%full_runs, section_pre_data => 1,
@@ -3472,17 +3474,12 @@
34723474
unlike => { no_privs => 1, },
34733475
},
34743476
3475-
'REVOKE CREATE ON SCHEMA public FROM public' => {
3477+
'REVOKE ALL ON SCHEMA public' => {
34763478
create_order => 16,
3477-
create_sql => '
3478-
REVOKE CREATE ON SCHEMA public FROM public;
3479-
ALTER SCHEMA public OWNER TO "regress_quoted \"" role";
3480-
REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
3481-
regexp => qr/^
3482-
\QREVOKE ALL ON SCHEMA public FROM "regress_quoted \E\\""\ role";
3483-
\n\QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
3484-
\n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
3485-
/xm,
3479+
create_sql =>
3480+
'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
3481+
regexp =>
3482+
qr/^REVOKE ALL ON SCHEMA public FROM "regress_quoted \\"" role";/m,
34863483
like => { %full_runs, section_pre_data => 1, },
34873484
unlike => { no_privs => 1, },
34883485
},

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202109061
56+
#define CATALOG_VERSION_NO 202109101
5757

5858
#endif

‎src/include/catalog/pg_namespace.dat

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,6 @@
2121
# update dumpNamespace() if changing this descr
2222
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
2323
descr => 'standard public schema',
24-
nspname => 'public', nspacl => '_null_' },
24+
nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },
2525

2626
]

‎src/pl/plperl/expected/plperl_setup.out

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,9 @@ CREATE EXTENSION plperl;
2525
CREATE EXTENSION plperlu; -- fail
2626
ERROR: permission denied to create extension "plperlu"
2727
HINT: Must be superuser to create this extension.
28+
CREATE SCHEMA plperl_setup_scratch;
29+
SET search_path = plperl_setup_scratch;
30+
GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
2831
CREATE FUNCTION foo1() returns int language plperl as '1;';
2932
SELECT foo1();
3033
foo1
@@ -34,6 +37,7 @@ SELECT foo1();
3437

3538
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
3639
\c -
40+
SET search_path = plperl_setup_scratch;
3741
SET ROLE regress_user1;
3842
-- Should be able to change privileges on the language
3943
revoke all on language plperl from public;

‎src/pl/plperl/sql/plperl_setup.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,12 +27,16 @@ SET ROLE regress_user1;
2727

2828
CREATE EXTENSION plperl;
2929
CREATE EXTENSION plperlu; -- fail
30+
CREATE SCHEMA plperl_setup_scratch;
31+
SET search_path = plperl_setup_scratch;
32+
GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
3033

3134
CREATE FUNCTION foo1() returns int language plperl as '1;';
3235
SELECT foo1();
3336

3437
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
3538
\c -
39+
SET search_path = plperl_setup_scratch;
3640

3741
SET ROLE regress_user1;
3842

‎src/test/regress/input/tablespace.source

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
388388
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
389389

390390
SET SESSION ROLE regress_tablespace_user2;
391-
CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
391+
CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
392392
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
393393
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
394394
REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail
@@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE;
409409

410410
DROP ROLE regress_tablespace_user1;
411411
DROP ROLE regress_tablespace_user2;
412+
413+
-- Rest of this suite can use the public schema freely.
414+
GRANT ALL ON SCHEMA public TO public;

‎src/test/regress/output/tablespace.source

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int);
908908
CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
909909
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
910910
SET SESSION ROLE regress_tablespace_user2;
911-
CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
911+
CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
912912
ERROR: permission denied for tablespace regress_tblspace
913913
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
914914
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
@@ -934,3 +934,5 @@ drop cascades to table testschema.atable
934934
drop cascades to table testschema.tablespace_acl
935935
DROP ROLE regress_tablespace_user1;
936936
DROP ROLE regress_tablespace_user2;
937+
-- Rest of this suite can use the public schema freely.
938+
GRANT ALL ON SCHEMA public TO public;

0 commit comments

Comments
 (0)