🚨 PostgreSQL Error Guide: 'permission denied for table' Privilege and GRANT Failures

Exact Error Message

ERROR:  permission denied for table orders

Older or related phrasings include permission denied for relation orders and, for schema-level issues, permission denied for schema app. The SQLSTATE is 42501 (insufficient_privilege).

What the Error Means

PostgreSQL enforces object-level privileges. To run SELECT, INSERT, UPDATE, or DELETE on a table, the connecting role must have been granted that specific privilege on that specific table — and must also have USAGE on the schema that contains it. When the role lacks the required privilege, PostgreSQL rejects the statement with permission denied for table.

This is purely an authorization problem, not an authentication one: the role connected successfully, but it is not allowed to perform the requested action on the object. The two most common surprises are (1) GRANT is per-table, so granting on existing tables does nothing for tables created later, and (2) table privileges are useless without USAGE on the schema. Understanding the difference between owning an object, having explicit grants, role membership, and default privileges is the key to fixing it cleanly.

Common Causes

• No GRANT on the table. The role was never granted SELECT/INSERT/etc. on this table.
• Missing USAGE on the schema. Even with table grants, a role without USAGE on the containing schema cannot access objects in it.
• Tables created after the GRANT. GRANT ... ON ALL TABLES IN SCHEMA only affects tables that existed at grant time; new tables are not covered unless ALTER DEFAULT PRIVILEGES was set.
• Wrong owner / object created by another role. A table created by a migration role is owned by that role; the app role has no rights unless explicitly granted.
• Role membership not inherited. Privileges granted to a group role are not used by a member that has NOINHERIT, or the member did not SET ROLE.
• Row-level security blocking access. With RLS enabled and no matching policy, access can be denied (though RLS usually returns no rows rather than this exact message).

How to Reproduce the Error

Create a table as one role and access it as another with no grant:

-- as the owner
CREATE TABLE orders (id int);

-- as a different, ungranted role
SELECT * FROM orders;
-- ERROR:  permission denied for table orders

Or grant table access but omit schema USAGE:

GRANT SELECT ON app.orders TO app_reader;
-- as app_reader, with no USAGE on schema app:
SELECT * FROM app.orders;
-- ERROR:  permission denied for schema app

Diagnostic Commands

Show the privileges granted on the table (read-only):

psql -c "SELECT grantee, privilege_type FROM information_schema.role_table_grants \
WHERE table_name = 'orders';"

Identify the table owner and its schema:

psql -c "SELECT n.nspname AS schema, c.relname, pg_get_userbyid(c.relowner) AS owner \
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'orders';"

Check whether the role has USAGE on the schema:

psql -c "SELECT has_schema_privilege('app_reader', 'app', 'USAGE') AS has_usage;"

Check a specific table privilege for a role directly:

psql -c "SELECT has_table_privilege('app_reader', 'app.orders', 'SELECT') AS can_select;"

Inspect role membership and inheritance:

psql -c "SELECT r.rolname, r.rolinherit, m.rolname AS member_of \
FROM pg_roles r LEFT JOIN pg_auth_members am ON am.member = r.oid \
LEFT JOIN pg_roles m ON m.oid = am.roleid WHERE r.rolname = 'app_reader';"

Find the error in the log:

sudo journalctl -u postgresql --no-pager | grep "permission denied" | tail -20

Step-by-Step Resolution

1. Identify the exact missing privilege. Use has_table_privilege and has_schema_privilege to pinpoint whether it is the table grant, the schema USAGE, or both that is absent.

2. Grant schema USAGE first. Table grants are inert without it. GRANT USAGE ON SCHEMA app TO app_reader;

3. Grant the table privileges. Grant exactly what the role needs: GRANT SELECT, INSERT, UPDATE ON app.orders TO app_reader; Avoid over-granting; follow least privilege.

4. Cover future tables with default privileges. So new tables created by the migration role are automatically accessible: ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app GRANT SELECT, INSERT, UPDATE ON TABLES TO app_reader; Note this only applies to tables created after the statement and by the specified creating role.

5. Fix role membership/inheritance if using group roles. Ensure the application role is a member of the group role that holds the grants and has INHERIT (the default), or have it SET ROLE to the group role.

6. Re-run the failing statement. Verify with has_table_privilege returning t before retrying the application query.

Prevention and Best Practices

• Use a group/role hierarchy: grant privileges to a role like app_rw, then make application logins members of it, so access is managed in one place.
• Always pair table grants with schema USAGE; they are useless apart.
• Configure ALTER DEFAULT PRIVILEGES for the role that creates objects so newly created tables inherit the right grants automatically.
• Have migrations run as a role whose default privileges are set up, avoiding the “new table, no access” surprise.
• Audit privileges with information_schema.role_table_grants as part of deployment checks.
• For triage, the free incident assistant can turn a permission-denied log block into the precise missing GRANT.

Related Errors

• permission denied for schema app — missing USAGE on the schema, the prerequisite for table access.
• permission denied for sequence orders_id_seq — INSERT needs USAGE on the backing sequence too.
• must be owner of table orders — DDL like ALTER TABLE requires ownership, not just grants.
• password authentication failed for user — an authentication failure, not an authorization one.

Frequently Asked Questions

I granted SELECT but still get permission denied. Why? The most common reason is a missing USAGE privilege on the schema containing the table. Table grants do nothing if the role cannot use the schema. Grant USAGE ON SCHEMA first, then the table privileges.

Why do new tables lack access even though I ran GRANT ON ALL TABLES? GRANT ... ON ALL TABLES IN SCHEMA is a one-time operation affecting only the tables that exist at that moment. Tables created afterward are not covered. Use ALTER DEFAULT PRIVILEGES to grant automatically to future tables created by a given role.

Does the table owner need a GRANT to access their own table? No. The owner has all privileges on objects they own implicitly. Grants are for other roles. If the app role is not the owner and was never granted access, it is denied.

How is this different from “password authentication failed”? That error happens at connection time when credentials are wrong (authentication). permission denied for table happens after a successful connection when the role lacks rights on the object (authorization). They are entirely separate stages.

Could row-level security cause this message? RLS typically filters rows (returning fewer or zero rows) rather than raising permission denied for table. However, if a role lacks the base table privilege entirely, you get this error before RLS policies are even evaluated. Check grants first, then RLS policies.