Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A role cannot create tables in the public schema after upgrading to PostgreSQL 15

ErrorERROR: permission denied for schema public

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

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1Migrations 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. 2You do not want shared write access to public, or the database has several usersThe role has no schema of its ownFix
    3. 3You expect to manage public as the person who created the database, but you are a different roleThe role is not the database ownerFix

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+ public

2Fix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.