PostgreSQL Sequence Permission Denied Fixer

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.

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

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 infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

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