A role cannot create tables in the public schema after upgrading to PostgreSQL 15
ERROR: permission denied for schema publicShort answer
Since PostgreSQL 15, ordinary users no longer have CREATE on the public schema in new databases. Connect as the database owner or a superuser and grant CREATE on schema public to the role, or give the role its own schema. Upgraded and restored databases keep their old permissions.
Fix it fast
1Find out which cause you have
Look at the privileges on the public schema and at the role you are using. In psql, \dn+ lists a schema with its permissions.
\dn+ public SELECT current_user;2Match what you see, then jump to its fix
- 1
Migrations or CREATE TABLE fail for an app role on a database created with 15 or newer; the same app worked on 14The new default removed CREATE for ordinary rolesFix - 2
You do not want shared write access to public, or the database has several usersThe role has no schema of its ownFix - 3
You expect to manage public as the person who created the database, but you are a different roleThe role is not the database ownerFix
- 1
What does the error look like?
ERROR: permission denied for schema public
Cause 1 of 3
The new default removed CREATE for ordinary roles
1Check
The 15.0 release notes say PUBLIC no longer has CREATE on schema public in new clusters and in newly created databases. Upgrading a cluster or restoring a dump preserves the old permissions, so a fresh database behaves differently from an upgraded one.
\dn+ public2Fix
As the schema owner or a superuser, grant CREATE to the role that runs your migrations. Granting to one role is tighter than granting to PUBLIC.
GRANT USAGE, CREATE ON SCHEMA public TO app_user;3Verify
Reconnect as the role and create a table.
CREATE TABLE public.permission_probe (id int);
DROP TABLE public.permission_probe;Confirmed by postgresql.org
Cause 2 of 3
The role has no schema of its own
1Check
The manual's recommended pattern is user-private schemas: each user creates objects in a schema with the same name as the user. The default search path is "$user", public, so objects go to that schema first.
SHOW search_path;2Fix
Create a schema owned by the role. Unqualified CREATE TABLE then lands in it.
CREATE SCHEMA app_user AUTHORIZATION app_user;3Verify
Create a table without a schema name and see where it landed.
CREATE TABLE probe (id int);
SELECT schemaname FROM pg_tables WHERE tablename = 'probe';
DROP TABLE probe;Confirmed by postgresql.org
Cause 3 of 3
The role is not the database owner
1Check
The 15.0 notes say the public schema is owned by the pg_database_owner role, so whoever owns the database owns public in that database. Check the database owner.
SELECT datname, pg_get_userbyid(datdba) AS owner
FROM pg_database
WHERE datname = current_database();2Fix
Make the application role the database owner. You need to be able to SET ROLE to the new owner and have CREATEDB, or be a superuser.
ALTER DATABASE mydb OWNER TO app_user;3Verify
Check the owner again, then create a table in public as app_user.
SELECT pg_get_userbyid(datdba) FROM pg_database WHERE datname = 'mydb';Confirmed by postgresql.org
How do I stop it coming back?
- Create the application role's schema or grants in your database setup script, not by hand.
- Give each app its own role and schema instead of sharing public.
- After a major upgrade, compare \dn+ public on old and new databases so surprises show up in staging.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix