permission denied for sequence in PostgreSQL

ERROR: permission denied for sequence invoices_id_seq (SQLSTATE 42501) on an INSERT means the table’s id column is a serial (or has a default nextval(…)), and the role that inserts has INSERT on the table but no right to draw a number from the sequence behind it. A sequence is a separate object with its own privileges, and the grants people usually run — GRANT ALL ON ALL TABLES IN SCHEMA — do not include it. The fix is GRANT USAGE on the sequence; the lasting fix is a default privilege, or identity columns, whose inserts need no sequence grant.

We measured every case below on PostgreSQL 17.11, and repeated the privilege matrix, the identity case and the ON ALL grants on 15.19 with identical results. The setup is the usual one for a SaaS application: a role migrate owns schema core and its tables, and the runtime role app has USAGE on the schema and SELECT, INSERT, UPDATE, DELETE on the tables.

Which privilege does each sequence function need?

USAGE is the one the application needs: with only USAGE, INSERT … RETURNING id, currval and lastval() all worked, and setval and reading last_value were refused — which is how it should be for a runtime role. The message names the sequence without its schema (permission denied for sequence seq, also when the sequence was other.seq), and the LOCATION line names the function that refused (nextval_internal, currval_oid, lastval, do_setval; aclcheck_error for a plain SELECT).

An insert into an identity column needs no sequence privilege. For generated by default as identity and generated always as identity, app inserted rows, also with COPY, without any privilege on the sequence; the insert draws the number on the table’s behalf. Reading the number back is a different matter: lastval() and currval(pg_get_serial_sequence(…)) after that insert were refused with the same 42501, so take the id from INSERT … RETURNING id. Resetting the counter is not for the runtime role either: setval needs UPDATE, and alter table … alter column id restart answered must be owner of table. If you are choosing between serial and identity for a new table, the insert alone is one more reason for identity.

One leftover to know about: after converting a serial column with drop default and add generated by default as identity, the old sequence stayed owned by the column, and pg_get_serial_sequence returned that unused sequence instead of the identity sequence. Drop it in the same migration, or the setval recipes in duplicate key value violates unique constraint move the wrong counter.

Why does GRANT ALL ON ALL TABLES not fix it?

Because ON ALL TABLES covers every relation except sequences: tables, partitioned tables and their partitions, views, materialized views and foreign tables all got app=arwdDxtm from one grant all on all tables, the insert into the serial table still failed, and the sequences’ relacl stayed empty. Three other grants behaved differently, and the second one is the trap:

So for a migration role that creates the tables, the pair is: grant usage on all sequences in schema core to app once, for what exists, and alter default privileges for role migrate in schema core grant usage on sequences to app, for everything migrate creates later. A table created by anyone else — a superuser running a hotfix, a second migration tool — is not covered. The same rule for tables, and why ALTER DEFAULT PRIVILEGES so often seems to do nothing, is measured in ALTER DEFAULT PRIVILEGES not working.

Does an INSERT that inserts nothing still need the sequence?

Yes. insert into t (email) values ('a@x.nl') on conflict do nothing, on a row that already existed, was refused with the same 42501 when app had no privilege on the sequence; on conflict do update too. The default is evaluated before the conflict is detected. With USAGE granted, four such inserts returned INSERT 0 0 and moved last_value from 1 to 5: every attempt uses up a number. An insert that passes an explicit id needed no sequence privilege at all.

How do you find the sequences a role cannot use?

This lists every column whose default names a sequence, on a table the role may insert into, where the role has neither USAGE nor UPDATE on the sequence. It also catches a default that calls a sequence the column does not own — a hand-written default nextval('…'), in the same schema or another — for which pg_get_serial_sequence returns NULL:

-- psql -v role=app -f sequences.sql
with defaults as materialized (
  select ad.adrelid as table_oid, ad.adnum as attnum, d.refobjid as seq_oid
  from pg_attrdef ad
  join pg_depend d on d.classid = 'pg_attrdef'::regclass and d.objid = ad.oid
                  and d.refclassid = 'pg_class'::regclass
  join pg_class s  on s.oid = d.refobjid and s.relkind = 'S'
)
select df.table_oid::regclass as table_name,
       a.attname              as column_name,
       df.seq_oid::regclass   as sequence_name
from defaults df
join pg_attribute a on a.attrelid = df.table_oid and a.attnum = df.attnum
where has_any_column_privilege(:'role', df.table_oid, 'INSERT')
  and not has_sequence_privilege(:'role', df.seq_oid, 'USAGE')
  and not has_sequence_privilege(:'role', df.seq_oid, 'UPDATE')
order by 1, 2;

On our test database:

     table_name     | column_name |       sequence_name
--------------------+-------------+---------------------------
 core.t_dp_postgres | id          | core.t_dp_postgres_id_seq
 core.t_oc2         | id          | core.t_oc2_id_seq
 core.t_other       | id          | other.seq

The materialized matters: without it, the planner applied has_sequence_privilege to dependencies that were not sequences and the query failed with "t_serial" is not a sequence. It only sees defaults that name the sequence directly: a default written as nextval('…'::text), a default that calls a function of yours, and nextval() in a trigger or in application code are not listed, and they failed the same way. Identity columns are left out because their inserts need no grant. And it follows role membership like the insert does: a role that gets USAGE only through a group it does not inherit from is listed, and its insert failed until it ran set role.

A sequence in another schema has one more twist. A table in core with default nextval('other.seq'): without any privilege, the insert said permission denied for sequence seq, not for the schema. With USAGE on the sequence but none on schema other, the insert worked — the default refers to the sequence by its id — while select nextval('other.seq') typed by hand got permission denied for schema other. Which privilege the other statements need on tables and schemas is in permission denied for table.

What we did not measure

How drivers report it. Supabase, whose roles and default grants are set up by the platform. pg_dump and restore of sequence privileges.

All measurements are our own, on PostgreSQL 17.11 and 15.19 in containers, on 21 October 2026.