PostgreSQL "permission denied for schema public": Why It Happens and How to Fix It

The PostgreSQL error "permission denied for schema public" usually appears the first time an application, a migration tool or a developer tries to create a table in a new PostgreSQL 15+ database. Since version 15, ordinary users can no longer create objects in the public schema by default. The fix takes one statement, but which statement you choose decides how secure the database stays.

appdb=> CREATE TABLE orders (id bigint PRIMARY KEY);
ERROR:  permission denied for schema public

The message comes from the privilege check in aclchk.c, with SQLSTATE 42501 (insufficient_privilege). Migration tools like Flyway, Liquibase, Alembic, Django or Rails typically show it wrapped in their own exception, for example when they try to create their own history table on the first run.

What changed in PostgreSQL 15

The PostgreSQL 15 release notes list two related changes:

• PUBLIC lost the CREATE privilege on the public schema. Before 15, every role could create tables, functions and other objects in public. Now only the schema owner can, plus anyone it grants CREATE to. USAGE, which lets roles access objects in the schema, is still granted to everyone.
• The public schema is now owned by pg_database_owner. This predefined role always has exactly one implicit member, the owner of the current database (predefined roles). So whoever owns the database also controls its public schema. Before 15 it belonged to the bootstrap superuser.

Why? It is the secure schema usage pattern the documentation has recommended since CVE-2018-1058. When every user can create objects in a schema that is on everyone's search path, one user can create, for example, a function with the same name as one others call and change what their queries do. The schemas chapter now calls the old setup "never a secure pattern", acceptable only with a single user or a few users who trust each other.

Why it works on one server and fails on another

This is the confusing part. The new defaults apply only to new clusters and to new databases created in them. According to the release notes, upgrading a cluster or restoring a dump preserves the existing permissions and ownership of public. So:

• a production database upgraded from 14 to 16 with pg_upgrade still lets everyone create in public;
• a fresh 16 cluster for staging or a new environment does not;
• the same migration passes in production and fails in the new environment.

Check what you actually have:

-- who owns the database and the schema?
SELECT d.datname, pg_get_userbyid(d.datdba) AS db_owner,
       pg_get_userbyid(n.nspowner) AS schema_owner
FROM pg_database d, pg_namespace n
WHERE d.datname = current_database() AND n.nspname = 'public';

-- can the application role create and use objects there?
SELECT has_schema_privilege('app', 'public', 'CREATE') AS can_create,
       has_schema_privilege('app', 'public', 'USAGE')  AS can_use;

In psql, \dn+ public also shows the schema's owner and access privileges.

Three clean fixes

Pick based on how the database is used.

1. Let the application own its database

If one application owns the database, make its role the database owner. Through pg_database_owner, it then owns public and can create objects there, while other roles still cannot. This is the setup PostgreSQL 15 is designed around.

-- option 1: the application owns its database (and so the public schema)
CREATE DATABASE appdb OWNER app;
-- or, for an existing database:
ALTER DATABASE appdb OWNER TO app;

Keep in mind that a database owner has significant power over the database. The documentation notes that no secure schema usage pattern exists when an untrusted user owns the database. That is fine for an application's own service account, but not for a role shared with people you do not fully trust.

2. Give the application its own schema

This is the pattern the documentation describes as constraining users to private schemas: create a schema owned by the application role and named like it. The default search_path starts with "$user", so the role uses that schema first without further setup. Objects land in app, public stays clean, and permissions are easy to reason about.

-- option 2: a dedicated schema for the application
CREATE SCHEMA app AUTHORIZATION app;
-- default search_path is "$user", public: the role finds its schema first

-- option 3: grant CREATE on public to one role only
GRANT CREATE ON SCHEMA public TO app;

The last line in that block is option 3: if you need to stay in public, for example because the code has public. hardcoded, grant CREATE to the one role that needs it. That is still far tighter than the pre-15 default, because other roles cannot create objects there.

The next error: permission denied for table

Once the app can create tables, a second role, such as reporting or a read-only user, often hits "permission denied for table". That is a different privilege. USAGE on the schema lets a role look into it, and each table still needs its own grant. ALTER DEFAULT PRIVILEGES covers tables created later:

-- read-only role: USAGE on the schema plus SELECT on tables
GRANT USAGE ON SCHEMA app TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting;

-- and for tables the app role creates later
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting;

The default privileges apply to objects created by the role named in FOR ROLE. In our experience, this is where most "it worked yesterday" permission tickets come from: the grants were done once by hand, then a migration created a new table that nobody granted.

What about upgraded databases?

If you upgraded from 14 or earlier, you still have the old, open permissions. Consider adopting the new default deliberately, as the release notes and the schema docs suggest, and as part of a broader security hardening pass:

• find the roles that actually create objects in public and grant them CREATE explicitly, or move them to their own schemas;
• then run REVOKE CREATE ON SCHEMA public FROM PUBLIC;;
• the docs also suggest auditing public for objects named like objects in pg_catalog.

Test this on a staging copy first. A forgotten cron job or tool that creates temporary work tables in public will start failing the moment you revoke.