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;