Fix "permission denied for schema public" on PostgreSQL 15+

Problem statement

On a new database, the first python manage.py migrate fails:

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the
django_migrations table (permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ...

The same role worked on PostgreSQL 14. PostgreSQL 15 changed a long-standing default: ordinary users no longer have CREATE permission on the public schema. Only the database owner (and superusers) can create tables there. If your app role is not the owner of the database or schema, migrations cannot create a single table.

1. Simplest: make the app role own the database: ALTER DATABASE myapp OWNER TO myapp; (on new databases, CREATE DATABASE myapp OWNER myapp;).
2. Then, connected to that database, ALTER SCHEMA public OWNER TO myapp; if needed.
3. Or grant explicitly: GRANT USAGE, CREATE ON SCHEMA public TO myapp;.
4. On managed databases, run these as the provider's admin role.
5. Re-run python manage.py migrate.

Step-by-step solution

1. Check owners and privileges

Connect as an admin role to the app's database:

\l myapp
\dn+ public
SELECT current_user;

\dn+ public shows the schema owner and access privileges. On PostgreSQL 15+, a fresh database shows public owned by pg_database_owner, a special role meaning "whoever owns the database", with USAGE but not CREATE granted to PUBLIC.

2. Make the app role the owner

For a database dedicated to one Django project, ownership is the cleanest fix:

ALTER DATABASE myapp OWNER TO myapp;

Because public is owned by pg_database_owner on 15+, the new database owner automatically gets full rights on public. If the schema was created differently (for example by a restore from an older version), set it explicitly while connected to myapp:

\c myapp
ALTER SCHEMA public OWNER TO myapp;

3. Or grant CREATE explicitly

If the database must stay owned by an admin role, for example due to organisational policy, grant the app role what migrations need:

\c myapp
GRANT USAGE, CREATE ON SCHEMA public TO myapp;

Grants on a schema are per database: run them while connected to the app database, not to postgres.

4. Managed databases

On RDS, Cloud SQL, Azure, DigitalOcean, and others, you connect as an admin role that is not a true superuser but can grant and alter ownership of objects it controls:

CREATE ROLE myapp LOGIN PASSWORD '...';
GRANT myapp TO current_user;            -- some providers require membership to change owner
CREATE DATABASE myapp OWNER myapp;

If the provider created the database for you, use ALTER DATABASE ... OWNER TO or the GRANT from step 3 as the admin user.

5. Fix existing objects after a restore

A dump restored with the original owner names may leave tables owned by another role, so later migrations fail with must be owner of table. Reassign everything:

\c myapp
REASSIGN OWNED BY old_owner TO myapp;

Use pg_restore --no-owner --role=myapp on future restores so objects are created as the app role.

Verification check:

python manage.py migrate
python manage.py dbshell -c "\dt django_migrations"

migrate should apply all migrations, and the table should be owned by your app role.

Explanation

Before version 15, every database's public schema granted CREATE to the PUBLIC pseudo-role, so any user able to connect could create objects that other users might accidentally use, a long-known security weakness (CVE-2018-1058 discussed the search_path risk). PostgreSQL 15 removed that default and made public owned by pg_database_owner. The change is safer, but it broke the common recipe of creating a database as postgres and granting the app user only CONNECT or ALL PRIVILEGES ON DATABASE, which never covered schema-level CREATE.

Edge cases / notes

• GRANT ALL ON DATABASE grants database-level privileges (CONNECT, CREATE schemas, TEMP), not table creation in public. It does not fix this error.
• Upgrades. A cluster upgraded from 14 with pg_upgrade keeps the old permissions; only newly created databases get the new default.
• Separate migration role. Some teams run migrations as an owner role and the app as a role with only DML grants. Then also set ALTER DEFAULT PRIVILEGES so new tables are accessible to the app role.
• Tests need CREATEDB on the role, or a TEST settings block pointing at an existing database.

Next steps

• Create roles correctly from the start with Connect Django to PostgreSQL in Production.
• Planning an upgrade to 15 or newer? Read Upgrade PostgreSQL Major Version for Django.

FAQ

Why did this work on PostgreSQL 14?

Older versions let every role create objects in public. Version 15 removed that default.

Is GRANT ALL PRIVILEGES ON DATABASE enough?

No. That covers database-level privileges, not creating tables inside the public schema.

Should the Django role be a superuser?

No. Owning its own database is enough and keeps the blast radius small.

Does this affect existing databases after upgrading?

Databases upgraded in place keep their old permissions. New databases on 15+ get the stricter default.