PostgreSQL error: permission denied for table / schema

SQLSTATE 42501 (insufficient_privilege) · Updated July 9, 2026

ERROR:  permission denied for table orders

-- variante a livello di schema:
ERROR:  permission denied for schema app

The role you are connected as lacks the privilege the statement needs: SELECT/INSERT/UPDATE/DELETE on a table, USAGE or CREATE on a schema, USAGE on a sequence. (Older PostgreSQL versions phrase the first one as "permission denied for relation".) The fix is always a GRANT — the interesting part is why the grant was missing.

What this error means

PostgreSQL privileges are per-object and do not cascade: to read app.orders you need both USAGE on schema app and SELECT on the table. Owners implicitly hold all privileges on their objects — which is exactly why everything works in development (where the app connects as the owner) and breaks in production (where it doesn't).

Two adjacent variants worth recognizing: permission denied for sequence orders_id_seq means the role can write the table but not draw IDs from its sequence; permission denied for schema public started appearing widely with PostgreSQL 15, which stopped letting every user create objects in public by default.

Common causes

• Objects created by a different role. Migrations run as migrator or an admin; the app connects as app_user; nobody granted app_user anything on the new tables.
• Grants are not retroactive-forward: GRANT ... ON ALL TABLES IN SCHEMA covers existing tables only. Tables created next week are not covered — that's what ALTER DEFAULT PRIVILEGES is for.
• The ALTER DEFAULT PRIVILEGES trap: it applies only to objects created by the role it was set for. Setting it as yourself while migrations run as another role does nothing.
• Missing schema USAGE — table grants are useless if the role can't traverse the schema.
• PostgreSQL 15+: CREATE on schema public is no longer granted to everyone; tools that "just worked" before an upgrade start failing.

How to diagnose it

SELECT current_user;                -- chi sei davvero (occhio ai pooler)

\dp orders                          -- privilegi e proprietario della tabella
\dn+                                -- privilegi sugli schemi

SELECT has_table_privilege('app_user', 'app.orders', 'SELECT');

Read \dp output ruthlessly: an empty "Access privileges" column means only the owner has access. And check who the owner is — that usually explains how the situation arose.

How to fix it

-- Il set minimo tipico per un ruolo applicativo:
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_user;

-- E per le tabelle FUTURE create dal ruolo delle migrazioni:
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- PostgreSQL 15+, se serve creare oggetti in public:
GRANT CREATE ON SCHEMA public TO app_user;

• Note the FOR ROLE migrator: default privileges must be attached to the role that creates the objects.
• Structural alternative many teams adopt: make all objects owned by a dedicated app_owner role, run migrations as it, and grant the runtime role only what it needs.