14

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?
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
moooeeeep
  • 243
  • 1
  • 2
  • 7

2 Answers2

18

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:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
0
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;

For all sequences.

Jhonny Ramirez Zeballos
  • 139
  • 3