ERROR: permission denied for sequence cities_id_seq using Postgres

Question

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?

Granting insert/delete on a sequence does not make sense to me. I'm surprised that it even works. — , Feb 17 '12 at 13:02

score 563 · Accepted Answer · edited Apr 26 '21 at 07:15

563

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;

Note: Don't forget to choose the database (\c <database_name>) before executing the privilege grant commands

edited Apr 26 '21 at 07:15

ThangLeQuoc

2,272
2
19
30

answered Feb 17 '12 at 08:43

kupson

6,738
1
18
14

Thank you. This one worked. Just wondering if the way I'm granting rights to these two table is of the norm or is there a more accepted standard somewhere. I just need the www to be able to add, edit and remove rows. – Tõnis Ojandu Feb 17 '12 at 08:55
@Vampnik: In that case you need to add UPDATE to all tables. For sequences the `USAGE, SELECT` should be enough. – A.H. Feb 17 '12 at 09:04
57

FYI, the syntax ". . . ON ALL SEQUENCES IN SCHEMA schema_name" is also supported. – epic_fil Feb 12 '13 at 22:00
5

Interesting. I did a GRANT all on the table that my sequence is in, but this doesn't seem to cover the sequence. This also seems to very by OS. – Kinnard Hockenhull Jun 07 '14 at 15:48
4

`grant all privileges on all sequences in schema public to staging;` – Steve Tauber Nov 07 '14 at 18:29
84

How is this a real thing? When would I ever want to allow a user to insert data into a table but NOT want to allow them to use the the fact that one of the columns is auto-incrementing? – Brett Widmeier Dec 07 '16 at 14:27
1

I had already done GRANT ALL for my db user, and this answer fixed my problem when I had this error: `psycopg2.ProgrammingError: permission denied for sequence django_migrations_id_seq` – Nathan Hinchey May 27 '17 at 18:11
7

IS `SELECT` necessary? Shouldn't `USAGE` cover what's needed? – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 10 '17 at 08:35
4

Same problem: privileges on table, but not sequence. Docs do say _"Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to SERIAL columns. Permissions on sequences must be set separately."_ (See [GRANT documentation page]. (https://www.postgresql.org/docs/9.5/static/sql-grant.html) Though unclear when this is an issue. – user1071847 Mar 23 '18 at 14:54
23

@BrettWidmeier Exactly. It's stupefying to me how this sort of thing is tolerated by the developers. It's like people want to roam around the Internet and read bottomless StackOverflow threads in attempt to fix things that should have been working right out of the box. – milosmns Apr 06 '18 at 15:19
1

@TᴀʀᴇǫMᴀʜᴍᴏᴏᴅ: From documentation on privileges (https://www.postgresql.org/docs/current/ddl-priv.html), `USAGE` "allows use of the `currval` and `nextval` functions" in sequences. So, strictly speaking, that is generally all that's needed, since a typical column default uses `nextval`. But `USAGE` alone would not allow other, reasonable, access to the sequence, like in https://stackoverflow.com/questions/14886048#14886371, hence the convention of granting `SELECT` as well. – Randall Oct 28 '19 at 17:18
What about consequences of granting all sequences? Will this not lead information leaked? What sequence permissions are supposed to do? – Gherman Apr 03 '20 at 09:33
1

From version 10 you can specify identity column instead of serial eg: `id INT GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` so you don't have that issue https://www.postgresql.org/docs/10/sql-createtable.html – Alexis Panagiotopoulos Mar 10 '21 at 10:01

score 91 · Answer 2 · answered Mar 24 '15 at 21:04

91

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;

answered Mar 24 '15 at 21:04

Tom Gerken

2,930
3
24
28

3

Note that this only works in PostgreSQL 9.0 and up, to accomplish the same in 8 you could do something like: SELECT 'GRANT USAGE, SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(relname) || ' TO www;' FROM pg_statio_all_sequences WHERE schemaname = 'public'; – Tom Gerken 2 days ago – Tom Gerken Feb 18 '16 at 17:25
That version 8 workaround was a life saver. I initially struggled to work out how to use it. I had to use that query to produce a list of new queries, which I then had to issue. Is there a trick to do that in one hit? Also I inserted "SEQUENCES " after "ON " – Paul Gardiner Jul 17 '21 at 12:37

Asfand Qazi · Answer 3 · 2016-10-11T12:40:04.080

@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.

score 5 · Answer 4 · answered May 03 '21 at 08:08

5

It is due to permission issue on the SEQUENCES.

Try the following command to resolve the issue,

GRANT USAGE, SELECT ON SEQUENCE sequence_name TO user_name;

Eg:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;

answered May 03 '21 at 08:08

Codemaker2015

12,190
6
97
81

score -4 · Answer 5 · answered Aug 20 '19 at 05:07

Execute the following command in postgres.

login to postgres:

sudo su postgres;

psql dbname;

CREATE SEQUENCE public.cities_id_seq INCREMENT 1
MINVALUE 0
MAXVALUE 1
START 1 CACHE 1; ALTER TABLE public.cities_id_seq OWNER TO pgowner;

pgowner will be your database user.

ERROR: permission denied for sequence cities_id_seq using Postgres

5 Answers5

Linked