Changes to the public schema in PostgreSQL 15 and how to handle upgrades

In September 2021, a patch for the upcoming PostgreSQL version 15 was committed which introduces a visible change for users: the CREATE privilege for the public schema is no longer set by default. This is a recommendation from CVE-2018-1058.

What does that mean for the average (non-superuser) user?

In PostgreSQL 14:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
postgres=# SELECT version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                       List of schemas
  Name  | Owner | Access privileges |      Description
--------+-------+-------------------+------------------------
 public | ads   | ads=UC/ads       +| standard public schema
        |       | =UC/ads           |
(1 row)

We see that PUBLIC (the second line in Access privileges) has USAGE (U) and CREATE (C) privileges. A regular user can create a table in the public schema:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
CREATE TABLE
priv_test=> \dp priv_test
                               Access privileges
 Schema |   Name    | Type  | Access privileges | Column privileges | Policies
--------+-----------+-------+-------------------+-------------------+----------
 public | priv_test | table |                   |                   |
(1 row)

This is how the public schema privileges look in PostgreSQL 15:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
postgres=# SELECT version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

The C (CREATE) is missing. And the CREATE TABLE for a regular user is no longer working by default:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE priv_test (id INT);

But how is this change handled in upgrades?

There are two ways to do upgrades. Ok, there are a few more ways, like reinstalling your application. However for the purpose of this article I’m only looking at ways which transfer over the data from the old into the new version.

Dump the data from the old version (14 and older), and restore into the new version (15 and newer)
Run pg_upgrade

Both ways work a bit different. Let’s look at the details.

Dump & Restore

Dumping a database is done using the pg_dump tool which comes with PostgreSQL. Preferably using either the custom or the directory format. And one needs to use the pg_dump from the newer version (15) to dump the old database:

1
/path/to/15/bin/pg_dump -F c -f /tmp/backup.dump priv_test

The restore is done by creating an empty database (use template0 as source/template), and then restore the dump into the new database, using pg_restore.

1
2
3
/path/to/15/bin/dropdb --if-exists priv_test
/path/to/15/bin/createdb -T template0 priv_test
/path/to/15/bin/pg_restore -d priv_test -e /tmp/backup.dump

However:

1
2
3
4
5
6
7
priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

Why does the public schema have the “new” privileges, where CREATE is missing?

Because the public schema is included in template0, and is copied into the newly created database. And in PostgreSQL 15, the public schema in template0 has the “new” privileges. Let’s have a look (don’t do this in your production system):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
priv_test=# SELECT datname, datallowconn FROM pg_database;
  datname  | datallowconn
-----------+--------------
 postgres  | t
 template1 | t
 template0 | f
 priv_test | t
(4 rows)

priv_test=# UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
UPDATE 1

priv_test=# \c template0
You are now connected to database "template0" as user "ads".

template0=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

template0=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

As we can see, public in template0 is missing the CREATE privilege, and this is then copied into the new database. Any application expecting otherwise and depending on public being writable by regular users will run into problems. This problem can be fixed by adding the CREATE privilege for public in the new database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
priv_test=# GRANT CREATE ON SCHEMA public TO PUBLIC;
GRANT

priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =UC/pg_database_owner                  |
(1 row)

If you want this the default for new databases, apply this change to template1.

pg_upgrade

The second way for upgrades is pg_upgrade. This copies the catalog from the old database into the new one, and then either copies or links the data files. This is a smooth way to upgrade a server to a newer version.

1
2
3
4
5
/path/to/14/bin/pg_ctl -m fast -D /data/14/data stop
/path/to/15/bin/pg_ctl -m fast -D /data/15/data stop
rm -rf /data/15/data/*
/path/to/15/bin/initdb --pgdata=/data/15/data
/path/to/15/bin/pg_upgrade -b /path/to/14/bin -B /path/to/15/bin -d /data/14/data -D /data/15/data -p 5454 -P 5455 -v

This runs pg_upgrade and updates version 14 to version 15. Afterwards the public schema looks the same as in version 14:

1
2
3
4
5
6
7
priv_test=# \dn+ public
                       List of schemas
  Name  | Owner | Access privileges |      Description
--------+-------+-------------------+------------------------
 public | ads   | ads=UC/ads       +| standard public schema
        |       | =UC/ads           |
(1 row)

PostgreSQL does not apply the pg_database_owner, and does not set (revoke) the “new” permissions. Instead everything is copied as it was in the previous version. If you want to have the new behavior from version 15, you need to revoke CREATE from public:

1
2
priv_test=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE

You can also set the owner to the new pg_database_owner:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
priv_test=# ALTER SCHEMA public OWNER TO pg_database_owner;
ALTER SCHEMA

priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  |
(1 row)

Summary

Upgrading a PostgreSQL database from version <= 14 to version 15 or newer brings a few challenges regarding the public schema. Both of the most common upgrade ways behave differently while dealing with the changes.

It’s best not to rely on a writable public schema at all.

Categories: [Postgresql-News]

Tags: [Backup] [Comment-Option] [Pg_dump] [Postgresql] [Psql] [Restore] [Search_path] [Update] [Upgrade]

Comments

With an account on the Fediverse or Mastodon, you can respond to this post. Known replies are displayed below:

Learn how this is implemented here.

Dump & Restore

pg_upgrade

Summary

Comments

Friends