How to fix Permission denied for sequence in PostgreSQL

Open a terminal and connect to your PostgreSQL database as the postgres superuser:

psql -U postgres -d your_database_name

Or if using a remote connection:

psql -h your_host -U postgres -d your_database_name

From the error message, note the exact sequence name (e.g., "user_id_seq"). You can also list all sequences in a schema:

\ds public.*

This shows all sequences in the public schema. Replace "public" with your schema name if needed.

Grant the necessary privileges to your application user. Replace "sequence_name" with your actual sequence name and "app_user" with your database user:

GRANT USAGE, SELECT, UPDATE ON SEQUENCE sequence_name TO app_user;

Breakdown of privileges:
- USAGE: Allows use of currval() and nextval() functions
- SELECT: Allows reading the current sequence value
- UPDATE: Required for nextval() to advance the sequence

If your application user needs access to multiple sequences, grant permissions on all sequences in a schema in one command:

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Replace "public" with your actual schema name. This approach is often cleaner than granting on individual sequences.

To ensure new sequences automatically get the correct permissions, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_user;

This ensures any sequences created in the future will have the proper permissions for app_user.

Test that the user can now insert data into the affected table:

SET ROLE app_user;
INSERT INTO your_table (name) VALUES ('test');
SET ROLE postgres;

If the INSERT succeeds, permissions are correctly configured. If it still fails, ensure you have granted permissions on all affected sequences.