Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT fails with permission denied for sequence

ErrorERROR: permission denied for sequence cities_id_seq

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, psql
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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"
  2. 2Match what you see, then jump to its fix

    1. 1INSERT works for the owner but fails for the app roleThe role was granted the table but not its sequenceFix
    2. 2The first tables worked, a table added in a later migration failsThe sequence was created after the grantFix
    3. 3The error appears on a restore, seed script or ALTER SEQUENCE reset, not on normal insertssetval needs UPDATE, not USAGEFix

What does the error look like?

  • ERROR: permission denied for sequence cities_id_seq
  • permission 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.