PostgreSQL 42501: permission denied
The current role lacks a privilege the statement needs: on a table, schema, sequence, function or database. Grant it, or connect as a role that has it.
- SQLSTATE
42501- Name
insufficient_privilege
What it means
PostgreSQL checks privileges on every object a statement touches. 42501 (insufficient_privilege) names the object type and name, for example permission denied for table orders, permission denied for schema app or permission denied for sequence orders_id_seq. Older versions say relation instead of table.
Privileges are needed at each level: USAGE on the schema, then SELECT, INSERT, UPDATE or DELETE on the table, and USAGE on the sequence behind a serial column for inserts.
Common causes
- The role was never granted the privilege, or the grant was on other tables than the one in use.
- Tables created after a
GRANT ... ON ALL TABLES IN SCHEMA, which only covers tables that existed at the time. - No
USAGEon the schema, or since PostgreSQL 15 noCREATEon thepublicschema for ordinary roles. - Inserting into a table with a
serialcolumn withoutUSAGEon its sequence. - Altering or dropping an object you don't own (
must be owner of table orders, same code).
How to fix it
-
Grant the table and schema privileges the role needs.
GRANT USAGE ON SCHEMA app TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user; GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;
-
Cover tables created later with default privileges (set by the role that creates the tables).
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user; -
Check what a role has.
SELECT has_table_privilege('app_user', 'app.orders', 'INSERT'), has_schema_privilege('app_user', 'app', 'USAGE'); -
On PostgreSQL 15 and later, grant
CREATEonpublicexplicitly if a role must create objects there.GRANT CREATE ON SCHEMA public TO app_user;
Related errors
28000invalid authorization specification42P01relation does not exist0LP01invalid grant operation25006read-only SQL transaction229SQL Server: Permission denied on object1142MySQL: Command denied to user for tableORA-01031Oracle: insufficient privileges
More syntax error or access rule violation errors
42601syntax error at or near42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist42883function does not exist
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.