INSERT fails with permission denied for sequence
ERROR: permission denied for sequence cities_id_seqShort answer
The role has INSERT on the table but not on the sequence behind its id column. Table grants do not cover sequences. Grant USAGE on the sequence to the role (and SELECT if it reads currval), and add default privileges so sequences created later get the same grant.
Fix it fast
1Find out which cause you have
Find the sequence name and who owns and may use it. The aclitem list in the last column shows the roles that have access.
psql -d mydb -c "\dp cities_id_seq"2Match what you see, then jump to its fix
- 1
INSERT works for the owner but fails for the app roleThe role was granted the table but not its sequenceFix - 2
The first tables worked, a table added in a later migration failsThe sequence was created after the grantFix - 3
The error appears on a restore, seed script or ALTER SEQUENCE reset, not on normal insertssetval needs UPDATE, not USAGEFix
- 1
What does the error look like?
ERROR: permission denied for sequence cities_id_seqpermission denied for sequence when calling nextval
Cause 1 of 3
The role was granted the table but not its sequence
1Check
The manual says granting a privilege on a table does not extend to sequences used by the table, including sequences of SERIAL columns; sequence permissions are set separately. nextval requires USAGE or UPDATE on the sequence.
psql -d mydb -U app_user -c "SELECT nextval('cities_id_seq');"2Fix
Grant USAGE on the sequence. Add SELECT too if the app calls currval or lastval.
GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO app_user;
-- or every sequence in the schema:
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;3Verify
Repeat the INSERT as the app role.
INSERT INTO cities (name) VALUES ('test') RETURNING id;Confirmed by postgresql.org
Cause 2 of 3
The sequence was created after the grant
1Check
GRANT ... ON ALL SEQUENCES applies to the sequences that exist at that moment. ALTER DEFAULT PRIVILEGES sets what objects created in the future receive and does not touch existing ones.
psql -d mydb -c "\\d+ newtable"2Fix
Set default privileges for the role that runs your migrations, in the schema the tables live in.
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;3Verify
Create a table with a serial column as the migration role, then insert into it as app_user.
CREATE TABLE t_probe (id serial PRIMARY KEY);
GRANT INSERT ON t_probe TO app_user;Confirmed by postgresql.org
Cause 3 of 3
setval needs UPDATE, not USAGE
1Check
The sequence functions page lists the privilege for each call: nextval needs USAGE or UPDATE, currval and lastval need USAGE or SELECT, and setval needs UPDATE.
SELECT setval('cities_id_seq', 1000);2Fix
Run reset scripts as the owner, or grant UPDATE for the job that has to call setval.
GRANT UPDATE ON SEQUENCE cities_id_seq TO seed_user;3Verify
The setval call from the check now succeeds for that role.
SELECT setval('cities_id_seq', (SELECT max(id) FROM cities));Confirmed by postgresql.org
How do I stop it coming back?
- Put the sequence grants in the same migration as the table grants.
- Set default privileges once per schema for the role that runs migrations.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix