Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Postgres permission denied for sequence: GRANT USAGE and nextval

Sequences carry their own ACL, so table grants never cover nextval. The USAGE grant, default privileges, and why identity columns behave differently.

~5 min readSQL
#PostgreSQL#Permissions#Sequences#Migrations
3D illustration of a database cylinder beside a counter block blocked by a red shield

Introduction & Quick Summary

Symptom. An application role can SELECT, INSERT and UPDATE its tables, but every INSERT into a table with a serial primary key fails with ERROR: permission denied for sequence orders_id_seq. Reads work, updates work, the application user is not postgres, and \dp orders shows exactly the grants you issued. Sometimes the error appears only in production, right after a schema migration created a new table.

Cause. In PostgreSQL a sequence is an independent object with its own ACL. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public does not touch sequences, so nextval('orders_id_seq') — called implicitly by the serial column default — is checked against the sequence's ACL and refused. Nothing about the table grant implies a sequence grant.

Fix. Grant USAGE (which covers nextval and currval) plus SELECT on the sequence to the application role, then make it automatic for future sequences with OWNED BY and ALTER DEFAULT PRIVILEGES. Check with has_sequence_privilege(), which shows the effective privilege including group membership and inherited roles.

Core Mechanics, Signatures & Reproducible Setup

Sequence privileges in PostgreSQL are three, and they are checked by different functions:

  • USAGE — required by nextval() and currval().
  • SELECT — required to read last_value (and by some tools that inspect the sequence).
  • UPDATE — required by setval().

A reproducer that shows the exact failure, run as a superuser in one session:

sql
CREATE ROLE app_user LOGIN PASSWORD 'secret'; CREATE TABLE orders ( id serial PRIMARY KEY, total numeric NOT NULL ); GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
sql
SET ROLE app_user; INSERT INTO orders (total) VALUES (10); -- ERROR: permission denied for sequence orders_id_seq

The fix, back as superuser or the table owner:

sql
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user; -- make it automatic for every sequence this role creates later ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user; -- and for sequences that already exist GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES applies to objects created by the role that runs it (or by the role named in FOR ROLE), in that schema, from that moment on. A migration that runs as postgres therefore needs FOR ROLE postgres, or the grant must be issued by postgres itself.

Key Comparison Table

The grant that fixes the table does not fix the sequence, and the object type decides the privilege name.

Object typePrivilege checkedBy which operationGRANT ALL ON ALL TABLES covers it?
TableSELECT / INSERT / UPDATESELECT, INSERT, UPDATEYes
SequenceUSAGEnextval, currvalNo
SequenceUPDATEsetvalNo
SequenceSELECTlast_value readsNo
SchemaUSAGEResolving any name in itNo
ViewSELECT on the view, plus underlyingSELECTYes for the view only

serial and identity columns behave differently, which is the source of most confusion:

Column definitionHow the default runsSequence privilege neededNotes
serial / bigserialColumn default calls nextval(...) as the invoking userUSAGE on the sequenceACL is checked; this is the error case
GENERATED BY DEFAULT AS IDENTITYInternal nextval attached to the tableNot checked separately; table INSERT sufficesPreferred for new schemas
GENERATED ALWAYS AS IDENTITYSame, rejects explicit values without OVERRIDING SYSTEM VALUENot checked separatelyStrongest default
Application-managed idYour SQL calls nextval() directlyUSAGE, or wrap in a SECURITY DEFINER functionExplicit grants belong in the migration

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Which sequence is the default actually using

The error names a sequence, but a restored schema can have a default pointing at a differently named one, or at a sequence in another schema:

sql
SELECT pg_get_serial_sequence('public.orders', 'id') AS seq; SELECT d.column_default FROM information_schema.columns d WHERE d.table_schema = 'public' AND d.table_name = 'orders' AND d.column_name = 'id';

pg_get_serial_sequence() returns the qualified name to grant on, which avoids guessing when \d orders is unavailable (for example through a pooler that restricts meta-commands).

2. Verifying the grant the way PostgreSQL checks it

\dp orders_id_seq shows the ACL, but it does not show inheritance or PUBLIC grants. has_sequence_privilege() answers the question the server actually asks:

sql
SELECT has_sequence_privilege('app_user', 'public.orders_id_seq', 'USAGE') AS can_nextval, has_schema_privilege('app_user', 'public', 'USAGE') AS can_resolve, pg_get_userbyid(relowner) AS owner FROM pg_class WHERE oid = 'public.orders_id_seq'::regclass;

If can_nextval is true and the insert still fails, look at can_resolve and at the table's RLS policies instead of the sequence.

3. Migrations that create objects under another role

When migrations run as postgres and the application connects as app_user, default privileges must be declared for the migrating role:

sql
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Run this in the same database that later migrations target. A staging restore that omits pg_dumpall --roles keeps the tables but loses the role-level defaults, so the first new sequence after the restore breaks production and staging differently.

4. Restores and --no-acl

pg_dump includes ACL entries by default; --no-acl omits them, and a dump restored into a different owner produces tables that app_user can read (through PUBLIC defaults on some providers) but not insert into. Compare the ACL after a restore rather than the data:

sql
SELECT c.relname, c.relkind, pg_get_userbyid(c.relowner) AS owner, coalesce(array_to_string(c.relacl, ' '), '(default)') AS acl FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relkind = 'S' ORDER BY c.relname;

A blank ACL on a sequence means "owner only", which is exactly the state that produces the error for every other role.

5. Sequences reached through a non-default search_path

nextval('orders_id_seq') resolves through search_path. If the sequence lives in app while the session's search_path is public, the call fails with relation "orders_id_seq" does not exist before permissions are even considered. Qualify it in the default, and grant on the qualified name:

sql
ALTER TABLE app.orders ALTER COLUMN id SET DEFAULT nextval('app.orders_id_seq'); GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user; GRANT USAGE ON SCHEMA app TO app_user;

Poolers that reset search_path per transaction (PgBouncer in transaction mode, most serverless Postgres proxies) make this the more common failure after a deployment.

6. setval in seed scripts

Seed and fixture scripts commonly call setval() to align a sequence with hand-inserted ids. That requires UPDATE on the sequence, which USAGE does not include:

sql
GRANT UPDATE ON SEQUENCE orders_id_seq TO app_user; -- only for seeding roles SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));

Keep this grant on a migration role, not on the runtime role; a runtime user that can setval can also create duplicate-key storms.

Common Compilation/Runtime Mistakes & Version Gotchas

  • GRANT ALL ON ALL TABLES as a blanket fix. It covers tables, views and foreign tables in the schema — never sequences. Sequelize, Prisma and Django all emit serial/bigserial in older migrations, so this remains a live failure in 2026.
  • Granting on the table and expecting nextval to inherit. A column default is an expression evaluated with the invoking user's privileges unless it is inside a SECURITY DEFINER function.
  • ALTER DEFAULT PRIVILEGES without FOR ROLE. It only affects objects the executing role creates. This is the usual reason "the grant is there but the new table still fails".
  • Forgetting USAGE on the schema. Sequence ownership is not enough if the role cannot resolve the schema name; the error then changes to permission denied for schema public.
  • PUBLIC grants that hide the problem locally. Managed providers sometimes grant broad privileges on public for convenience; the same migration then fails on a self-hosted instance where PUBLIC has nothing.
  • GRANT ... ON ALL SEQUENCES IN SCHEMA before the sequence exists. It is not retroactive. Run it after CREATE TABLE in the same migration, or rely on default privileges.
  • Identity columns do not need the grant, and templates still add it. Grants on identity-backed sequences are harmless but they document nothing; preferring GENERATED BY DEFAULT AS IDENTITY in new migrations removes this class of error entirely.

Related tools. Paste the failing migration into the SQL formatter when a multi-statement grant block is hard to read, and confirm the ordering — schema usage, table grants, then sequence grants — before rerunning it; a single misplaced semicolon in a DO $$ … $$ block hides which statement actually executed.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.