How to Fix PostgreSQL 'Permission Denied for Sequence' Error on INSERT

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 5 mins

---

TL;DR

• What broke: Your application role has INSERT on the table but is missing USAGE (and often SELECT) on the underlying sequence (users_id_seq), so Postgres rejects every row insert that needs a next serial/identity value.
• How to fix it: GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO your_app_role; — or fix DEFAULT PRIVILEGES so future sequences inherit the grant automatically.
• Shortcut: Use our Client-Side Sandbox above to paste your schema and role definitions — it auto-generates the exact GRANT and ALTER DEFAULT PRIVILEGES block without sending your credentials anywhere.

---

The Incident (What Does the Error Mean?)

ERROR:  permission denied for sequence users_id_seq
CONTEXT:  SQL function "nextval" during execution

Postgres serial columns and IDENTITY columns call nextval('users_id_seq') internally on every INSERT. That nextval call is a separate privilege check against the sequence object — it is entirely independent of INSERT on the table. Your app role passed the table-level check and failed the sequence-level check. The transaction is aborted. Every new user registration, every job enqueue, every audit log write — all dead until this is patched.

---

The Attack Vector / Blast Radius

This is a least-privilege misconfiguration, not an exploit vector — but the blast radius is severe:

• Total write outage on any table backed by the affected sequence. If users is your auth table, signups and login-session creation fail simultaneously.
• Silent failures in ORMs — SQLAlchemy, ActiveRecord, and GORM often swallow this as a generic IntegrityError or 500, masking the root cause in application logs.
• Migration-time regression — this surfaces most often after a pg_dump/restore, a database clone, or a role rebuild during a cloud migration (RDS, Aurora, Cloud SQL). The table owner changes; the sequence owner does not follow automatically.
• Privilege escalation surface — the inverse problem: over-granting UPDATE on a sequence lets an unprivileged role manipulate setval(), resetting the counter and causing primary key collisions or ID enumeration attacks.

---

How to Fix It (The Solution)

Basic Fix — Grant Directly on the Sequence

- -- Missing: app_role can INSERT on users but cannot call nextval
- GRANT INSERT ON TABLE users TO app_role;

+ -- Correct: explicit sequence privileges alongside table privileges
+ GRANT INSERT ON TABLE users TO app_role;
+ GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_role;

USAGE = permission to call nextval(). SELECT = permission to call currval(). You need both for typical ORM behavior.

---

Enterprise Best Practice — Fix DEFAULT PRIVILEGES So This Never Recurs

Direct grants are fragile. Every new table with a serial column creates a new sequence that inherits nothing. The correct fix is schema-level default privileges, set in the context of the owning role (typically your migration user):

- -- Anti-pattern: granting per-sequence after each migration
- GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_role;
- GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_role;
- GRANT USAGE, SELECT ON SEQUENCE events_id_seq TO app_role;

+ -- Enterprise fix: set default privileges once, scoped to the schema
+ -- Run this AS the role that owns the tables (e.g., 'migrator')
+ ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
+     GRANT USAGE, SELECT ON SEQUENCES TO app_role;
+
+ -- Backfill all existing sequences in the schema
+ GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

Critical detail: ALTER DEFAULT PRIVILEGES only applies to objects created by the specified role going forward. If your CI pipeline runs migrations as migrator but your Terraform provisioned the schema as postgres, the grants will not fire. Audit with:

SELECT sequencename, schemaname
FROM pg_sequences
WHERE schemaname = 'public'
AND sequencename NOT IN (
    SELECT object_name FROM information_schema.role_usage_grants
    WHERE grantee = 'app_role' AND object_type = 'SEQUENCE'
);

Any row returned is a sequence your app role cannot use.

---

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

1. Checkov / Terraform (if provisioning RDS via IaC)

If you manage Postgres roles via terraform-provider-postgresql, add a Checkov custom check asserting that every postgresql_grant resource for INSERT on a table has a corresponding postgresql_grant for USAGE on sequences in the same schema.

2. Post-migration smoke test (add to your deploy pipeline)

# Run after every schema migration in CI
psql $DATABASE_URL -c "
  SET ROLE app_role;
  INSERT INTO users(email) VALUES ('[email protected]');
  DELETE FROM users WHERE email = '[email protected]';
" || { echo 'SEQUENCE PRIVILEGE CHECK FAILED'; exit 1; }

This runs as app_role and will catch missing sequence grants before the deploy hits production.

3. OPA / pgaudit policy

Enable pgaudit with pgaudit.log = 'ddl'. Pipe logs to your SIEM. Alert on CREATE SEQUENCE events that are not followed within 60 seconds by a GRANT USAGE ON SEQUENCE to your application role. This catches the gap introduced by automated migration tools (Flyway, Liquibase) that create sequences but do not re-run the default privilege block.

4. Least-privilege role separation (non-negotiable in prod)

Role	Table Privs	Sequence Privs
app_role	SELECT, INSERT, UPDATE, DELETE	USAGE, SELECT
readonly_role	SELECT	SELECT
migrator	ALL	ALL
app_role	❌ DROP, TRUNCATE	❌ UPDATE (blocks setval)

Never grant UPDATE on sequences to application roles. setval() in the wrong hands resets your primary key counter and causes silent data collisions.