10

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?

2 Answers 2

12

You most probably need:

GRANT USAGE ON SEQUENCE my_table_id_seq TO writer;

The manual:

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.

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.

Since Postgres 10, we can use IDENTITY columns instead of serial. Those use bound sequences without separate permissions internally. See:

2
  • Don't we need UPDATE ON SEQUENCE for this? Dec 1, 2016 at 12:44
  • @anshupitlia: Nope. As explained above. Dec 1, 2016 at 14:39
-1
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;

For all sequences.

1
  • 1
    That isn't at all what the question is about.
    – mustaccio
    Jun 15 at 20:52

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.