Error: permission denied for sequence cities_id_seq using Postgres

sql

database-permissions

postgres-privileges

sql-best-practices

byAlex Kataev·Feb 6, 2025

Fix the "permission denied for sequence cities_id_seq" SQL error by adjusting the user's privileges:

-- It's like saying "Open, Sesame!" to your SQL sequence!
GRANT USAGE, UPDATE ON SEQUENCE cities_id_seq TO target_user;

Here, target_user should be the actual username. This approach allows the cities table to increment the primary key automatically.

For total coverage across all sequences within the public schema, use the following:

-- Make "Open, Sesame!" work for ALL your sequences. Now that's power! 
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO target_user;

Remember, execute these commands after selecting the correct database and as a superuser.

Grant select and usage on individual sequences

One locked sequence? Give it the right key! Grant specific privileges on just the required sequence:

-- Just like giving a spare key for one door in the house
GRANT USAGE ON SEQUENCE cities_id_seq TO target_user;

Permit defaults on new sequences

Be proactive! Establish permissions upfront for future sequences using ALTER DEFAULT PRIVILEGES:

-- Think of it as a VIP Pass for every new sequence that shows up
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO target_user;

Need superuser for grant commands

Don't forget, SQL is a stickler for authority - these commands need to be run by a superuser or the owner of the sequence.

Table permissions: select, insert, and delete

Ensure your user enjoys the full banquet, not just the entrée - check they have rights to SELECT, INSERT, and DELETE on related tables.

The devil in the details: permission nuances

Default schema in play?

Ascertain your commands are running in the 'public' schema, or define the schema explicitly. You wouldn't want to give rights to someone in the wrong room, would you?

All-access pass for several sequences

More sequences to handle? Worry not, just extend the USAGE rights. It's like getting a bulk discount!

-- "In SQL we trust", said no one ever! Keep unlocking everything. :)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO target_user;

Empower users with USAGE privilege

Working with serial types? Or need to tweak sequences often? USAGE privilege is your go-to!

Application functionality - give it a thought

Allocate privileges aligning to the user's role. Too much power can be risky, too little can disrupt application flow.

Look for incomplete permission grants

Half-baked pie isn't tasty, and neither are incomplete grants. Ensure right sequence and table-related permissions have been granted.

Verify schema ownership

The role issuing these commands should be a superuser or should own the target schema. The SQL kingdom doesn't accept unauthorized orders!

Making use of cascading permissions

SQL works like family inheritance. Permissions can be cascaded by granting them to a role that other users inherit from.

explain-codes / Sql / Error: permission denied for sequence cities_id_seq using Postgres

Linked

How to set auto increment primary key in PostgreSQL?



Postgres: upgrade a user to be a superuser?



Insert and set value with max()+1 problems



Create new user in MySQL and give it full access to one database



Mysql - How to grant read only permissions to a user?



Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`



How to insert a record into a table with a column declared with the SERIAL function



Grant select and usage on individual sequences Permit defaults on new sequences Need superuser for grant commands Table permissions: select, insert, and delete The devil in the details: permission nuances Troubleshooting issues related to permissions