Lock down the public schema

The fix

-- Run against every database, not just one
ALTER SCHEMA public OWNER TO pg_database_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

On a cluster created fresh with PostgreSQL 15 or later, this is already done. On a cluster upgraded from 14 or earlier, it is not — see below.

Why it matters

Before PostgreSQL 15, the public schema granted CREATE to PUBLIC — that is, to every role in the database. Any user who could connect could create objects there.

That sounds like a tidiness problem. It’s a privilege-escalation primitive, because of search_path. The default search_path puts public first, so an unprivileged user can create a function or operator in public that shadows one a privileged user is about to call. When the superuser (or a SECURITY DEFINER function) runs a query that resolves to the attacker’s object, it executes with the caller’s privileges.

PostgreSQL 15 changed this: public is now owned by the new pg_database_owner role, and CREATE is no longer granted to PUBLIC. USAGE is still granted, so reading and using existing objects still works — only creating them is restricted.

The upgrade trap

This is the part that matters, and it is easy to miss entirely:

Upgrading does not give you the new behaviour. pg_upgrade and pg_dump faithfully preserve the original privileges, because silently revoking grants during an upgrade would break applications. The secure default applies to databases created by initdb on 15+, and to new databases created from template1/template0 on 15+ — not to databases carried forward.

So a cluster reporting PostgreSQL 18 may still have the pre-15 permissive public schema, and nothing in SELECT version() reveals it. The version number is not the answer to this question; the ACL is.

If you inherited a long-lived cluster, assume it is unfixed until you have checked the ACL.

Do it per database

public is a schema, and schemas live inside databases. Fixing it in postgres does nothing for appdb. Iterate:

for db in $(psql -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"); do
  psql -d "$db" -c 'ALTER SCHEMA public OWNER TO pg_database_owner;
                    REVOKE CREATE ON SCHEMA public FROM PUBLIC;'
done

Fix template1 too, or every database you create from now on inherits the old grants again.

Verify it worked

Look at the ACL rather than the version. Run against each database:

SELECT nspname, nspowner::regrole AS owner, nspacl
FROM pg_namespace WHERE nspname = 'public';

A fixed schema is owned by pg_database_owner and its ACL has no =UC/ entry for PUBLIC. The tell is the ACL string: an entry beginning =UC/ means PUBLIC has both USAGE and CREATE. =U/ alone is what you want.

Ask directly, which is harder to misread:

SELECT has_schema_privilege('public', 'public', 'CREATE') AS public_can_create;

Expect f. t means any role can create objects in public.

Sweep every database at once:

psql -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" |
while read db; do
  printf '%-20s %s\n' "$db" "$(psql -At -d "$db" -c "SELECT has_schema_privilege('public','public','CREATE')")"
done

Before you apply this

This breaks applications that create tables in public at runtime, which is more common than it should be: migration tools, ORMs with auto-create, test harnesses, and anything that runs CREATE TEMP-style DDL as a non-owner role.

The failure is ERROR: permission denied for schema public — the single most searched PostgreSQL 15 upgrade error, and this change is why.

Before revoking, find out who creates objects. If an application legitimately needs to, grant it explicitly rather than reopening the schema to everyone:

GRANT CREATE ON SCHEMA public TO myapp;

That is the whole point of the change — the privilege becomes a decision about one role instead of a default for all of them.

Better still, give the application its own schema and leave public alone:

CREATE SCHEMA app AUTHORIZATION myapp;
ALTER ROLE myapp SET search_path = app, public;

Low risk otherwise: revoking CREATE does not affect reads, writes, or existing objects. Nothing is dropped and no data moves.

Related

• Grant least privilege to roles — the rest of the privilege story.