New Public Schema Permissions in PostgreSQL 15

One of the changes in PostgreSQL 15 is a new default set of permissions on the public schema. The details of this are subtle.

Each database contains a schema named public by default. This schema is also by default part of the search_path. The effect is that if you don't specify any schema in any commands, all the activities take place in this public schema. If you don't care about schemas, then this allows you to use the database as if tables are directly inside a database, as it was before schemas were added to PostgreSQL (version 7.3!). In fact, this whole setup was a carefully calibrated dance to make the transition from before-schemas to schemas seamless.

As part of this setup, the public schema also has a set of default permissions: It has the USAGE privilege granted to PUBLIC (meaning "everyone", which is unrelated to the equally named schema), which allows anyone to refer to objects in the schema. And it has the CREATE privilege granted, which allows anyone to create objects in the schema. The latter one is the problematic one. If anyone can create objects in the schema, then you can trick others into using your object instead of one in the system catalogs (pg_catalog schema). This was the subject of CVE-2018-1058. The workarounds described for this were basically elaborate versions of "don't do that" or "be careful", but a better default setup proved elusive so far.

We could just not grant the CREATE privilege by default. Then the database owner could grant the CREATE privilege explicitly themselves if they want to. This would be one extra step for a bit more security. However, if you look carefully into a PostgreSQL 14 (or earlier) installation, the public schema is actually owned by the database bootstrap user (usually called postgres). But databases can each have owners that are not the bootstrap user or superusers at all. This is because when you create a database in PostgreSQL, it actually copies the template database, and at that point it doesn't know who the database owner will be. So a non-superuser database owner will see their newly minted database and find in it a schema named public owned by some other user named postgres, and so they cannot do any GRANT statements on that schema!

The fix here required a sequence of changes:

1. There is a new role pg_database_owner, which implicitly has the actual owner of the current database as a member.
2. The public schema is owned by that role now.
3. The public schema does not have CREATE privileges granted by default anymore.

So now, by default, unprivileged users cannot lay traps in the public schema anymore. But database owners now have the full capability to adjust these privileges as they see fit.

Note that upgrading from a previous version to PostgreSQL 15 (using pg_dump or pg_upgrade) preserves the original privileges, so you will not automatically get this more secure setup. To fix up upgraded databases, run these commands:

ALTER SCHEMA public OWNER TO pg_database_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Going forward, perhaps reconsider your use of the public schema, except for casual use. Consider creating specific schemas for applications and give out specific privileges as required.