permission denied for table in PostgreSQL: which grant?

ERROR: permission denied for table invoices names the object and leaves out the two things you need: which privilege was missing, and which role the statement ran as. In a multi-tenant application the usual setup is a migration role that owns the tables and an application role that only reads and writes them, and that split produces this error in more ways than a missing GRANT SELECT. An UPDATE … WHERE id = 1 is refused while UPDATE is granted. A table works for months and is refused the morning after a migration. The error names a table the query never mentions.

We set up that split on PostgreSQL 17.11 and ran each statement with one privilege missing at a time, writing down the literal message. The results below are from those runs. Almost none of it is row-level security: a policy that hides rows returns zero rows or raises a different error. The one exception, a policy that reads a second table, is further down.

What does “permission denied for table” mean in PostgreSQL?

It means the role the statement runs as lacks a privilege that this statement needs on that table. Privileges on the tables a statement names are checked when it starts, before a row is read, so the table’s contents play no part; a sequence or a table inside a function is checked when the statement reaches it. The message names the kind of object and its name without the schema, and nothing else. These are the forms we got on PostgreSQL 17.11, each from a role that was missing exactly one thing:

All of the errors in this table carry SQLSTATE 42501, must be owner of table included (the warning in the last row is 01000), so the code tells a driver that a privilege was missing and not which one. A table that does not exist is a different error, 42P01, relation "app.iso2" does not exist: a role without USAGE on the schema is told about the schema, not that the table is missing.

Older servers word the second one differently. The same query on PostgreSQL 10.23 gave permission denied for relation t; a view gave the same wording, and must be owner of relation t took the place of must be owner of table.

The setup

One database, shop, on PostgreSQL 17.11. A role migrator owns the schema app and creates every table in it. A role app logs in for the application and owns nothing. A superuser hands out the grants. The tables are invoices and customers with identity columns, a legacy table with a serial column, and later a few more that the migration role creates halfway through. Every statement ran in its own connection, logged in as the role in question.

Which privilege does each statement need?

More than the name of the statement suggests: a write that reads a column, in a WHERE, in RETURNING, as a conflict target or in amount = amount + 1, needs SELECT as well. The documentation says so in one sentence, about the SELECT privilege: “This privilege is also needed to reference existing column values in UPDATE, DELETE, or MERGE.” We granted the application role one privilege at a time and ran the statements an ORM actually sends:

Every refusal was the same line, permission denied for table invoices. So a write-only role, one that may insert but not read, cannot return a column or name a conflict target, by column or by constraint name, and insert … returning id is what many ORMs send. The privilege is needed on the columns involved, not on the table: with INSERT and a column grant select (id), both returning id and on conflict (id) do nothing worked and returning * was refused. returning 1, which reads no column, worked with INSERT alone. A read-only role cannot run select … for update, and a worker that claims jobs with for update skip locked, as in our job queue per tenant, needs UPDATE on the queue table before it has updated anything.

Two results went the other way: something was allowed that we expected a grant for.

A foreign key check needs no privilege on the referenced table for the role that inserts. A role with only INSERT on a child table inserted a row that references parent, while select count(*) from parent as the same role was refused. The check runs as the owner of the referenced table: when we revoked SELECT on parent from its own owner, the same insert failed with permission denied for table parent and the check’s query in the CONTEXT line. It also means the insert tells a role whether a parent row exists that it cannot read, which is the privilege side of the foreign-key gap in our row-level security article.

And LOCK TABLE is wider than the grants suggest. A role with SELECT and DELETE on a table ran lock table … in access exclusive mode successfully; with SELECT alone it was refused for that mode and allowed for access share. The documentation states the rule: “If the user has MAINTAIN, UPDATE, DELETE, or TRUNCATE privileges on the table, any lockmode is permitted.” An application role with UPDATE on a table can therefore also block every reader of it. A table-level grant carries that with it; with UPDATE granted per column instead, the same updates and select … for update worked and lock table … in access exclusive mode was refused.

What does a column grant change?

It keeps the message and makes it depend on the columns the query touches. With grant select (id, tenant_id) on invoices, a support role ran select id from invoices and select count(*) from invoices. It was refused on select *, on where note = 'x' and on order by amount_cents: a column in a filter or a sort counts as read. The error is still permission denied for table invoices and does not name the column.

Why is a table that the migration just created refused?

Because GRANT … ON ALL TABLES IN SCHEMA covers the tables that exist when it runs and no table created afterwards, and because ALTER DEFAULT PRIVILEGES, the statement that is meant to cover the later ones, applies only to objects created by one specific role. Without FOR ROLE that is the role that ran the statement. The documentation’s parameter list has it as: “Change default privileges for objects created by the target_role, or the current role if unspecified.”

We ran the sequence a deployment runs:

\ddp in psql lists the defaults with the creating role in its first column, “Owner”; after step 2 the only line had postgres there, which does nothing for a table migrator creates. The same miss happens when a member of the migration role creates a table without set role, and sequences need a default of their own; those cases, with the listing, are in ALTER DEFAULT PRIVILEGES not working. Two things from these runs belong here:

• A table that is replaced. Privileges belong to the table, not to its name. With no default privileges in place, a migration that built swap_new, dropped refunds and renamed the new table to refunds left the application role without access, and so did create table … as select. With the defaults of step 3 in place, a dropped and re-created customers was readable straight away.
• The default is as wide as you wrote it. Step 3 gives the application role SELECT, INSERT, UPDATE and DELETE on every table, view and materialized view the migration role creates in the schema from then on, including a backup copy made with create table … as and a lookup table the application should only read. Such a table needs a REVOKE in the migration that creates it.

What raises “permission denied for schema”?

A missing USAGE on the schema, for any statement that touches an object in it, and a missing CREATE on the schema for CREATE TABLE. The schema is checked before the table: with SELECT granted on app.invoices and no USAGE on app, the role got permission denied for schema app, and only after grant usage on schema app did the message become the one about the table.

Two variants send people to the wrong grant:

• GRANT ALL PRIVILEGES ON DATABASE gives no access to any table. A role with that grant on shop still got permission denied for schema app; with grant all on schema app added it got permission denied for table invoices. Database privileges are about connecting, creating schemas and temporary tables. That last one is why create temp table worked for a role that could create nothing else: TEMPORARY is granted to PUBLIC by default.
• permission denied for schema public on CREATE TABLE. In our database, created on 17.11, the application role could not create a table in public; the schema’s privileges read pg_database_owner=UC/pg_database_owner, =U/pg_database_owner, which is usage for everyone and create for the database owner only. This is the default since PostgreSQL 15, whose release notes list “Remove PUBLIC creation permission on the public schema” and add that “Upgrading a cluster or restoring a database dump will preserve public’s existing permissions”. So the same application can work on an upgraded database and fail on a freshly created one. The fix that keeps the new default is a schema of your own, owned by the migration role.

What raises “permission denied for sequence”?

An insert into a table with a serial column, by a role without USAGE (or UPDATE) on the sequence behind it. A table with an identity column does not have the problem: with only INSERT on the table, the insert into invoices (generated always as identity) succeeded, and the insert into legacy (serial) failed with permission denied for sequence legacy_id_seq. Calling nextval('app.invoices_id_seq') directly was refused on the identity sequence too; the insert draws the number without the role needing a privilege on the sequence, a direct call does not.

With USAGE granted, the insert worked; select last_value from app.legacy_id_seq and setval(…) were still refused, since they need SELECT and UPDATE on the sequence.

Why does the error name a table the query never mentions?

Because a view, a function or a row-level security policy reads that table, and in these three cases the read runs with the caller’s privileges:

• A view with security_invoker = true. A reporting role with SELECT on two views over payments, and nothing on payments itself, read the ordinary view and was refused on the security_invoker one, with permission denied for table payments. An ordinary view reads its tables with the privileges of the view’s owner; security_invoker switches that to the caller. It is the setting that makes a view respect the caller’s policies, as we measured in views and row-level security, and it comes with this: every role that reads the view now needs SELECT on the columns the view uses in the tables under it. The reverse failed as well: a view whose owner had no SELECT on the table was refused for its own owner.
• A function. select app.total(1), an ordinary SQL function that sums payments, was refused with permission denied for table payments and the line CONTEXT: SQL function "total" statement 1. The same body as a SECURITY DEFINER function returned its result, for any tenant the caller passed in and past any policy the owner is not subject to: such a function replaces the privilege check and the policy with its own code. EXECUTE on a function is granted to PUBLIC by default, so both functions were callable by every role with USAGE on the schema until we revoked that.
• A policy with a subquery. With the policy using (tenant_id in (select tenant_id from app.memberships where …)) on payments, and SELECT on payments but not on memberships, select count(*) from app.payments failed with permission denied for table memberships. Granting SELECT on memberships fixed it, and let the application role read the whole membership table directly. Moving the subquery into a SECURITY DEFINER function fixed it without that grant: the count returned, and select count(*) from app.memberships was still refused, on a table the application role had no privileges on. Such a function needs a fixed search_path, an owner that is not the role that owns everything, and EXECUTE revoked from PUBLIC; as we left it, any role with USAGE on the schema could call it. Our article on infinite recursion in a policy measures that pattern and what it costs.

Only the function gave a CONTEXT line. For the view and the policy the error arrives bare, and what you have is the server log, where each refusal is followed by a STATEMENT: line with the full query: the table in the error is not in it.

Why is a role refused when the role it belongs to has the grant?

Because the membership does not inherit, which can be set in two places. A group role readers had USAGE on the schema and SELECT on payments, and three login roles were made members of it:

The message is the one for the schema, because that is the first check to fail. select roleid::regrole, member::regrole, inherit_option from pg_auth_members shows the difference: inherit_option was f for both u2 and u3.

Which fixes did not work?

Three things that look like a fix and were not, on our setup, and one more message:

• Revoking from one role what PUBLIC has. With grant select on open_t to public, revoke select on open_t from app succeeded without a message and the application role still read the table. A revoke removes a grant made to that role; it does not subtract from PUBLIC.
• Granting on the partitioned table and querying a partition. With SELECT and INSERT on events, the role read and wrote through the parent, and select count(*) from app.events_1 was refused with permission denied for table events_1.
• Granting in another database. Roles are shared across a cluster, grants are stored per database. A role with USAGE and SELECT granted in a second database read app.invoices there, and got permission denied for table invoices on the table of the same name in shop.
• Reading pg_authid. An ordinary role got permission denied for table pg_authid and permission denied for view pg_shadow. pg_roles has the same rows with the password masked as ******** and was readable.

Is “permission denied for table” a row-level security error?

No. With row-level security enabled and a policy that matches nothing, the application role’s select count(*) returned 0 and no error. A write the policy refuses raises new row violates row-level security policy for table, which we took apart in its own article. The one place where a policy produces permission denied for table is the subquery case above, and there the missing thing is still a grant. If the query returns no rows rather than an error, start from debugging row-level security.

How do you find the missing grant?

Ask PostgreSQL, as a superuser or the owner, naming the role that was refused. has_table_privilege, has_schema_privilege and has_sequence_privilege take the role as their first argument. Run as the refused role itself they stop where the query stopped: without USAGE on the schema, has_table_privilege('z.inv', 'select') failed with permission denied for schema z.

select has_schema_privilege('app', 'app', 'usage')            as schema_usage,
       has_table_privilege('app', 'app.invoices', 'select')   as can_select,
       has_table_privilege('app', 'app.refunds', 'select')    as can_select_refunds;
-- t | t | f

To find every table in a schema where the application role is short of a privilege, with its owner next to it:

select c.relname,
       pg_get_userbyid(c.relowner)              as owner,
       has_table_privilege('app', c.oid, 'select') as sel,
       has_table_privilege('app', c.oid, 'insert') as ins,
       has_table_privilege('app', c.oid, 'update') as upd,
       has_table_privilege('app', c.oid, 'delete') as del
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'app'
  and c.relkind in ('r', 'p', 'v', 'm')
  and not (has_table_privilege('app', c.oid, 'select')
       and has_table_privilege('app', c.oid, 'insert')
       and has_table_privilege('app', c.oid, 'update')
       and has_table_privilege('app', c.oid, 'delete'))
order by 1;

At the point in the runs where we executed it, this returned ten relations. The owner column separated the causes: by_dev was owned by dev, the table created by a member without SET ROLE, and the rest by migrator, created before the default privileges existed or while they were revoked. It lists views and partitions too, where a missing write privilege may be what you want, and it reports a role that has only column-level grants as lacking the privilege; has_any_column_privilege answers for those.

For sequences owned by a column, as a serial column’s is, the ones the role cannot use:

select c.relname as sequence, d.refobjid::regclass as used_by
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_depend d on d.classid = 'pg_class'::regclass and d.objid = c.oid
                and d.refclassid = 'pg_class'::regclass and d.deptype = 'a'
where n.nspname = 'app' and c.relkind = 'S'
  and not has_sequence_privilege('app',
        quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'usage');

deptype = 'a' selects the sequences owned by a column and leaves identity sequences out, which need no grant. A sequence that a column default calls without owning it is not found this way. The sequence is passed by name: with c.oid the query failed on our database with "pg_toast_16488" is not a sequence, because the function was evaluated for rows that the relkind filter had not yet removed.

In psql, \dp app.invoices prints the grants as letters: app=arwd/migrator is INSERT (a), SELECT (r), UPDATE (w) and DELETE (d), granted by migrator. An empty privileges column on a table, as refunds had, means nobody but the owner has been granted anything. And \ddp lists the default privileges, with the creating role in the first column.

What we did not measure

Managed services that replace the superuser with a restricted administrator role; Privileges on foreign tables, large objects, types and languages. MERGE. What a revoked USAGE on a schema does to sessions that are already open; the documentation warns that “existing sessions might have statements that have previously performed this lookup”. Versions other than 17.11, apart from the one message on 10.23.

Sources: PostgreSQL 17 documentation, Privileges, ALTER DEFAULT PRIVILEGES and LOCK, and the PostgreSQL 15 release notes, read on 13 October 2026.