8

Recently I did create a table as a superuser including a serial id column, e.g.,

create table my_table
(
    id serial primary key,
    data integer
);

As I wanted my non-superuser user to have write access to that table, I granted it permissions:

grant select, update, insert, delete on table my_table to writer;

At a random point in time after doing so, the insertions made by that user started to fail because the user lacked permission to modify the sequence my_table_id_seq associated to the serial column. Unfortunately I can't reproduce that on my current database.

I worked around this by giving the user the required permission, like this:

grant all on table my_table_id_seq to writer;

Can someone help me understand

  • why, at some point, the previously sufficient permissions might start to fail?
  • what is the proper way to grant write permission for a table with a serial column?
8

You most probably need:

GRANT USAGE ON SEQUENCE my_table_id_seq TO writer;

Per documentation:

USAGE
...
For sequences, this privilege allows the use of the currval and nextval functions.

nextval() is the reason you need the USAGE privilege on the sequence for a table with serial column.
Details in this related answer on SO.

Since a sequence is a special kind of table (and for historical reasons) GRANT ... ON TABLE works on sequences, too. But you do not normally need that at all.

  • Don't we need UPDATE ON SEQUENCE for this? – inquisitive Dec 1 '16 at 12:44
  • @anshupitlia: Nope. As explained above. – Erwin Brandstetter Dec 1 '16 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.