ERROR: permission denied for sequence cities_id_seq using Postgres

Question

I'm new at Postgres (and at database info systems all in all). I ran following SQL script on my database:

create table cities (

id serial primary key,

name text not null

);

create table reports (

id serial primary key,

cityid integer not null references cities(id),

reportdate date not null,

reporttext text not null

);

create user www with password 'www';

grant select on cities to www;

grant insert on cities to www;

grant delete on cities to www;

grant select on reports to www;

grant insert on reports to www;

grant delete on reports to www;

grant select on cities_id_seq to www;

grant insert on cities_id_seq to www;

grant delete on cities_id_seq to www;

grant select on reports_id_seq to www;

grant insert on reports_id_seq to www;

grant delete on reports_id_seq to www;

When, as the user www, trying to:

insert into cities (name) values ('London');

I get the following error:

ERROR: permission denied for sequence cities_id_seq

I get that the problem lies with the serial type. That's why I grant select, insert and delete rights for the *_id_seq to www. Yet this does not fix my problem. What am I missing?

Answer 1

If you are using, PostgreSQL 8.2 then use the below code:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

Here, GRANT USAGE - is used for sequences, this allows the use of the currval and nextval functions.

You can grant permissions to all the sequences in the schema with:

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

Answer 2

This kind of errors are raised due to authorization restrictions 

Grant Privileges 

Become superuser such as postgres or owner of the sequence: 

psql -U postgres -d your_database 

Grant the following privileges to a user/role if he/she should use this sequence. Just replace your_user with their actual login username: 

If the user still can't access the sequence, make sure they are the owner of it, or if the sequence is not owned by another user: 

ALTER SEQUENCE cities_id_seq OWNER TO your_user; 

Check Role Permissions 

Be sure the user has rights on the schema too: 

GRANT USAGE ON SCHEMA public TO your_user;