ERROR: permission denied for sequence cities_id_se

问题:

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?

回答1:

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;

回答2:

Since @Phil has a comment getting a lot of upvotes which might not get noticed, I'm using his syntax to add an answer that will grant permissions to a user for all sequences in a schema (assuming your schema is the default 'public')

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

回答3:

@Tom_Gerken, @epic_fil and @kupson are quite correct with their statements to give permissions to work with existing sequences. However, the user will NOT get access rights to sequences created in the future. To do that, you have to combine the GRANT statement with an ALTER DEFAULT PRIVILEGES statement, like so:

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

This only works on PostgreSQL 9+, of course.

This will append to existing default privileges, not overwrite them, so is quite safe in that regard.