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?
Since PostgreSQL 8.2 you have to use:
GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;
GRANT USAGE - For sequences, this privilege allows the use of the currval and nextval functions.
Also as pointed out by @epic_fil in the comments you can grant permissions to all the sequences in the schema with:
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO www;