aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out2
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql2
-rw-r--r--doc/src/sgml/ddl.sgml56
-rw-r--r--doc/src/sgml/user-manag.sgml19
-rw-r--r--src/bin/initdb/initdb.c3
-rw-r--r--src/bin/pg_dump/pg_dump.c28
-rw-r--r--src/bin/pg_dump/t/002_pg_dump.pl19
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/include/catalog/pg_namespace.dat2
-rw-r--r--src/pl/plperl/expected/plperl_setup.out4
-rw-r--r--src/pl/plperl/sql/plperl_setup.sql4
-rw-r--r--src/test/regress/input/tablespace.source5
-rw-r--r--src/test/regress/output/tablespace.source4
13 files changed, 86 insertions, 64 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e3ee30f1aaf..c7b7db80650 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9428,7 +9428,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 30b5175da5b..38f4a7837fe 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2797,7 +2797,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 e0ffb020bf9..94f745aed03 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> 14 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> 14 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> 14 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 7b4b2271734..afbf67c28cf 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -597,13 +597,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>
@@ -652,8 +653,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 a16ad026f32..1ed4808d53f 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1633,8 +1633,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 2febcd4213c..a485fb2d070 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1623,11 +1623,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
+ * v15, 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;
}
@@ -4850,21 +4851,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 v15. 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 e1b7e31458f..c61d95e8174 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -628,7 +628,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,
@@ -3472,17 +3474,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/catversion.h b/src/include/catalog/catversion.h
index fb35d3d5be4..3253b8751b1 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202109061
+#define CATALOG_VERSION_NO 202109101
#endif
diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat
index 33992afd500..9a239754d69 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 a1a24dfb41c..5234febefd6 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 74844782f87..a89cf56617e 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 c133e73499f..cb9774ecc85 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 1bbe7e03236..e7629d470e5 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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 12:15:32 +0000