ads' corner

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.

1. Dump the data from the old version (14 and older), and restore into the new version (15 and newer)
2. 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:

/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.

/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:

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):

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:

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.

/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:

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:

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

You can also set the owner to the new pg_database_owner:

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.