Error: Permission Denied For Sequence cities_id_seq Using Postgres

The PostgreSQL error: “the permission denied for sequence cities_id_seq” means that the user doesn’t have permission to access or modify the sequence. This happens when the user doesn’t have the right to add data to the table or make some changes in the sequence.

Let’s explore this in the blog.

• Steps to Resolve Error: permission denied for sequence cities_id_seq using Postgres
  • Step 1: Identify the user and Check Ownership
  • Step 2: Grant Correct Permissions to the User
    • Grant Permission for Use/Select – if applicable
    • Grant permission for INSERT/UPDATE- if applicable
    • Grant all privileges  – if necessary
• Conclusion
• FAQs

Steps to Resolve Error: permission denied for sequence cities_id_seq using Postgres

Step 1: Identify the user and Check Ownership

Confirm the user, who has to implement the query. You can check the current user by SQL command:

Example

SELECT current_user;

Output :

current_user

---------------

Postgres

(1 row)

Note: If the sequence is owned by someone else, you should make this user as the new owner of the sequence. By using an ALTER SEQUENCE, you can change the ownership.

ALTER SEQUENCE cities_id_seq OWNER TO your_user;

The actual output depends on the user who logged in and running the query.

Step 2: Grant Correct Permissions to the User

A few permissions can be missing for the user due to which the user might be facing this error. You should try granting the below-mentioned user permissions.

1. Grant Permission for Use/Select – if applicable

If the user doesn’t have permission to use the sequence, you can give permission using the GRANT command in PostgreSQL. The following command can be used to allow the user to use and select from the sequence.

Example:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO your_user;

Output:

GRANT

The output confirms the permission. This indicates that the permission for sequence cities_id_seq has been granted to the user.

2. Grant permission for INSERT/UPDATE- if applicable

When the user wants to add new data in the table where the sequence is being used, the user gets permission by the UPDATE command. If the user wants to add some data and needs extra permission, you use the DEFAULT/NEXTVAL() command.

Example:

GRANT INSERT, UPDATE ON TABLE your_table TO your_user;

Output

GRANT

This indicates that the INSERT and UPDATE permissions have been successfully granted to the user.

3. Grant all privileges  – if necessary

All privileges on the sequence can be given, if necessary. You can use the following command.

Example

GRANT ALL PRIVILEGES ON SEQUENCE cities_id_seq TO your_user;

Output

GRANT

This means the privileges such as USAGE, SELECT, and UPDATE on the sequence are successfully granted to the user. If the command runs without an error, PostgreSQL will just return GRANT to confirm the permission.

After completing the above process, check if the issue has been resolved.

Conclusion

The error: permission denied for sequence cities_id_seq in PostgreSQL occurs when the query of the user doesn’t have all permissions. Identify the user, grant the permission, and grant the privileges if necessary. And check for the ownership. You can do this with the above-mentioned commands in this blog.

FAQs

ALTER SEQUENCE cities_id_seq OWNER TO your_user_name;