PostgreSQL "permission denied for table" — Which Role, Which Layer is Missing?
Diagnosing permission denied across 4 layers: schema USAGE, table DML, sequence USAGE, and search_path resolution. Breaking changes in PostgreSQL 15 public schema.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
ERROR: permission denied for table siparis
ERROR: permission denied for sequence siparis_id_seq
ERROR: permission denied for schema public
ERROR: permission denied for relation siparisSQLSTATE error code: 42501 (insufficient_privilege).
Four distinct object types represent four distinct authorization layers. Because they all begin with the generic phrase "permission denied", developers frequently resort to sweeping GRANT ALL commands, introducing severe security vulnerabilities while still failing if the underlying sequence layer was overlooked.
CREATE privilege on the public schema was revoked from the PUBLIC pseudo-role. Database migration scripts that operated seamlessly on PostgreSQL 14 immediately fail with permission denied for schema public upon upgrading. This architectural change accounts for the majority of recent occurrences of this error.▸Diagnostic Queries to Confirm Root Cause
Verify the effective runtime role and search path configured by your pooler or ORM:
SELECT current_user, session_user, current_setting('search_path');Next, rather than manually parsing system catalogs, query PostgreSQL's built-in permission evaluation functions:
SELECT has_schema_privilege('app_user', 'public', 'USAGE') AS schema_usage,
has_table_privilege('app_user', 'public.siparis', 'SELECT') AS table_select,
has_table_privilege('app_user', 'public.siparis', 'INSERT') AS table_insert,
has_table_privilege('app_user', 'public.siparis', 'UPDATE') AS table_update,
has_sequence_privilege('app_user', 'public.siparis_id_seq', 'USAGE') AS seq_usage;Any column returning false identifies the exact missing authorization layer.
▸The Four Authorization Layers and Common Traps
1. Schema USAGE Privilege
Without USAGE on the parent schema, no table, view, or sequence contained within can be accessed. Even with GRANT ALL on a table, missing schema USAGE will fail the query:
GRANT USAGE ON SCHEMA public TO app_user;2. Table DML Privileges
SELECT, INSERT, UPDATE, DELETE. Trap: Executing an UPDATE statement requires evaluating WHERE clause column values; granting UPDATE without granting SELECT causes runtime query failure.
3. Sequence Privileges — The Most Overlooked Layer!
When inserting into tables with serial or GENERATED ALWAYS/BY DEFAULT AS IDENTITY columns, table privileges alone are insufficient. The backend must consume the sequence, requiring USAGE, SELECT on the sequence object:
GRANT USAGE, SELECT ON SEQUENCE public.siparis_id_seq TO app_user;4. The search_path Resolution Trap
If identically named tables exist across multiple schemas, PostgreSQL resolves unqualified references using search_path:
SELECT to_regclass('siparis');If this resolves to an unauthorized table earlier in the search path, the issue is not permissions, but schema precedence.
▸Step-by-Step Production Resolution
1. Grant Least-Privilege Access
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.siparis TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;2. Secure Future Objects with Default Privileges
Permissions granted today do not apply to tables created tomorrow by migration tools:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;FOR ROLE app_owner clause is mandatory. Default privileges only take effect when objects are created by that specific role. If migrations execute as the postgres superuser, you must define default privileges FOR ROLE postgres.3. Separate Schema Owners from Application Users
Maintain app_owner for schema creation/DDL and app_user strictly for DML operations. This separation guarantees that application vulnerabilities cannot execute unauthorized DROP TABLE or TRUNCATE operations.
▸Related Error Guides & Hub Links
- •SQL Server Msg 229 Permission Denied (H-39) — Bilateral counterpart in SQL Server
- •SQL Server Cannot Open Database (H-15) — Database-level access restrictions
- •PostgreSQL Hub — PostgreSQL architecture and security best practices
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
PostgreSQL Connection Exhaustion & too many clients Triage
Root causes of FATAL: sorry, too many clients already and remaining connection slots errors, read-only pg_stat_activity diagnosis with privilege boundaries, and two-tier pooling mitigation.
PostgreSQL password authentication failed: Root Cause and Fix
Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.
PostgreSQL connection refused: 8-Step Definitive Triage
Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.