insufficient_privilege permission denied for schema … — 42501

Symptoms

A role tried to create or access an object in a schema for which it lacks the required privilege. PostgreSQL reports SQLSTATE 42501 (insufficient_privilege). Since PostgreSQL 15 the public schema no longer grants CREATE to everyone, so this appears more often after upgrades.

• Triggered by CREATE TABLE, CREATE FUNCTION, or referencing objects in a schema.
• The role needs USAGE (to access) and/or CREATE (to create) on the schema.
• Common right after migrating to PostgreSQL 15+.

What the server log shows

ERROR:  permission denied for schema app

Why PostgreSQL raises this — what the manual says

Section 5.10 Schemas (Privileges):

“To allow that, the CREATE privilege on the schema needs to be granted.”

Schema access is governed by two privileges: USAGE (look up objects inside it) and CREATE (make new objects in it). Without the relevant grant the access checker rejects the operation with 42501 before touching any object.

Common causes

• PostgreSQL 15+ removed the default CREATE grant on public.
• An application role was never granted USAGE/CREATE on its target schema.
• Objects live in a schema owned by another role.

How to fix it

1. Grant access: GRANT USAGE ON SCHEMA app TO app_user; and, if needed, GRANT CREATE ON SCHEMA app TO app_user;.
2. For existing objects also grant table/sequence privileges, e.g. GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA app TO app_user;.
3. Set default privileges so future objects are usable: ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_user;.

Diagnostic query

-- What schema privileges does the role hold?
SELECT nspname,
       has_schema_privilege('app_user', nspname, 'USAGE')  AS usage,
       has_schema_privilege('app_user', nspname, 'CREATE') AS create
FROM pg_namespace WHERE nspname = 'app';

Related & next steps

• SQLSTATE 42501 — Insufficient Privilege (full reference)
• Message: permission denied for table …
• Glossary: Privilege

Reference: PostgreSQL 18 Section 5.9 “Schemas”.