Victor's room

Quick Summary: The error ERROR: permission denied for schema public occurs in PostgreSQL 15 and newer when a non-superuser role attempts to run CREATE TABLE, run database migrations, or initialize an ORM schema inside the default public schema. Starting with PostgreSQL 15, the default CREATE privilege on the public schema was revoked from the PUBLIC pseudo-role to harden database security.

Understanding the Root Causes

In PostgreSQL 14 and earlier, every database user could create objects in the public schema by default. In PostgreSQL 15+, only the database owner or a superuser holds CREATE privileges on public unless explicitly granted. Common triggers include:

• PostgreSQL 15+ Default Security Hardening: Upgrading a local or Docker PostgreSQL instance from 14 to 15, 16, or 17 breaks legacy migration scripts that assume non-owner roles can create tables in public.
• Database Owner Mismatch: Creating a database as the postgres superuser without assigning OWNER to your dedicated application role.
• Missing Schema-Level Grants: Granting ALL PRIVILEGES ON DATABASE does not automatically cascade USAGE or CREATE permissions to schemas inside that database.

Step 1: Quick Fix – Grant Schema Ownership or Privileges as Superuser

Connect to your target database as the postgres superuser and grant schema privileges to your application user:

# 1. Connect to the specific database as superuser
psql -U postgres -d my_database

# 2. Option A: Grant USAGE and CREATE on schema public to your role
GRANT USAGE, CREATE ON SCHEMA public TO app_user;

# 3. Option B: Make your application user the owner of the public schema
ALTER SCHEMA public OWNER TO app_user;

# 4. Also ensure the user owns the database itself (recommended for migrations)
ALTER DATABASE my_database OWNER TO app_user;

Step 2: Grant Table and Sequence Permissions for Existing Objects

If tables or sequences already exist in the public schema (for example, after restoring a dump), grant access to both existing and future objects:

-- Grant privileges on all existing tables and sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Set default privileges for any future tables created by postgres
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO app_user;

Step 3: Use a Dedicated Application Schema (Production Best Practice)

Instead of opening the shared public schema to all roles in multi-tenant or production environments, isolate your application into its own dedicated schema:

-- Create a dedicated schema owned by your application user
CREATE SCHEMA app_schema AUTHORIZATION app_user;

-- Set the default search_path for the user so queries resolve automatically
ALTER ROLE app_user IN DATABASE my_database SET search_path TO app_schema, public;

Verification & Testing Steps

Reconnect as the application role and verify that table creation and schema inspection succeed without permission errors:

# Connect as the application user
psql -U app_user -d my_database

# Test creating and dropping a temporary table
CREATE TABLE permission_test (id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ DEFAULT NOW());
DROP TABLE permission_test;

# Inspect current schema privileges
\dn+ public

Troubleshooting Summary Comparison

Scenario	Primary Root Cause	Recommended Fix
Local Dev / Docker (PG 15+)	CREATE revoked from PUBLIC on public schema	GRANT USAGE, CREATE ON SCHEMA public TO app_user;
ORM / Migration Failure	Database owned by postgres instead of app role	ALTER DATABASE mydb OWNER TO app_user;
Post-Dump Restore Errors	Existing tables/sequences lack role grants	GRANT ALL ON ALL TABLES/SEQUENCES IN SCHEMA public
Multi-App Production DB	Shared public schema isolation needed	CREATE SCHEMA app_schema AUTHORIZATION app_user;