PERMISSION DENIED — Diagnosing SQLSTATE 42501 insufficient_privilege

Scenario

After deploying a new application role or a fresh schema migration, the application returns ERROR: permission denied for table orders (SQLSTATE 42501). The same query succeeds as a superuser or the table owner. All queries that were working before the role change continue to work.

How to Identify

Conditions:

• ERROR: permission denied for table|schema|function|sequence in application logs
• Failure is immediate and consistent — not intermittent — every call to the same object fails
• Error appears right after a role change, schema migration, or new environment deployment
• The query succeeds when run as a superuser or the object owner

Analysis Steps

-- 1. Test each privilege layer in order
SELECT has_database_privilege('app_user', current_database(), 'CONNECT');
SELECT has_schema_privilege('app_user', 'public', 'USAGE');
SELECT has_table_privilege('app_user', 'public.orders', 'SELECT');

-- 2. List all grants on the failing object
SELECT grantee, privilege_type, is_grantable
FROM   information_schema.role_table_grants
WHERE  table_name = 'orders'
ORDER  BY grantee, privilege_type;

-- 3. Simulate the application role in psql
SET ROLE app_user;
SELECT * FROM orders LIMIT 1;
RESET ROLE;

-- 4. Check schema ACL
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';

-- 5. Check default privileges (will new objects auto-inherit grants?)
SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS owner,
       n.nspname                                 AS schema,
       CASE d.defaclobjtype
           WHEN 'r' THEN 'TABLE'
           WHEN 'S' THEN 'SEQUENCE'
           WHEN 'f' THEN 'FUNCTION'
       END                                       AS object_type,
       pg_catalog.array_to_string(d.defaclacl, ', ') AS default_acl
FROM   pg_catalog.pg_default_acl d
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace;

-- 6. Check sequence grants (needed for INSERT on serial/identity columns)
SELECT relname, relacl FROM pg_class
WHERE  relkind = 'S' AND relnamespace = 'public'::regnamespace;

Pitfalls

• Do not grant SUPERUSER or pg_read_all_data to fix a permission issue in production — this bypasses row-level security and audit trails.
• GRANT ... ON ALL TABLES IN SCHEMA public only covers tables that exist at that moment. Tables created by future migrations get no grants unless you also run ALTER DEFAULT PRIVILEGES.
• PostgreSQL 15+: REVOKE CREATE ON SCHEMA public FROM PUBLIC is now the default. If your migration scripts rely on implicit CREATE privilege in public, they will fail after upgrading.
• Column-level DENY-equivalent does not exist in PostgreSQL — column-level grants are additive. If a role has no table-level SELECT but has column-level SELECT on one column, it can only read that column.
• Do not confuse role membership with direct grants. A role that is a member of app_readwrite inherits its privileges only if the grant was made to the group, not to the individual.

Resolution Approach

Fix all layers in order, then set default privileges so future objects inherit correctly: