PostgreSQL Grant Permissions to Update Sequence – Is It Necessary?

permissionspostgresqlsequence

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?

Best Answer

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.

Related Question