PostgreSQL で INSERT したら permission denied for sequence になったときすること

PostgreSQL に慣れている人だと当たり前かもしれないが，
最初は戸惑ったので，自分用に書いておく

前提条件

• PostgreSQL を使用
• 当該テーブルに serial 型の列を設定
  • サロゲートキーを作る場合など

結論

serial の列に紐づいている Sequence において，テーブルに INSERT するユーザに USAGE を GRANT する

理屈

• PostgreSQL には， Sequence (シーケンス)という概念がある
  • 整数を順番に重複なく出してくれる
  • シーケンスにも，ロールごとに権限を設定できる
• サロゲートキーのような重複のない連番を serial で作ると，デフォルト値としてシーケンスを利用するようになるらしい
  • 故に， INSERT 時にシーケンスを利用する権限が必要

シーケンスについての詳細は他記事に丸投げ．

例

PostgreSQL 12.6 で検証

• test_db : 作業中のデータベース名
• testuser : INSERT しようとしているロール名

テーブル作成

CREATE TABLE student (
    student_id serial NOT NULL,
    name varchar NOT NULL,
    age int4 NOT NULL,
    CONSTRAINT student_pk PRIMARY KEY (student_id)
);

student_id	name	age

INSERTできない場合

testdb=> INSERT INTO student(name, age) VALUES('HogeHoge', 20);
2021-07-02 11:45:53.359 JST [39624] ERROR:  permission denied for sequence student_student_id_seq
2021-07-02 11:45:53.359 JST [39624] STATEMENT:  INSERT INTO student(name, age) VALUES('HogeHoge', 20);
ERROR:  permission denied for sequence student_student_id_seq

対応

-- GRANT できるユーザで行う
testdb=# \ds
                  List of relations
 Schema |          Name          |   Type   |  Owner
--------+------------------------+----------+---------
 public | student_student_id_seq | sequence | hogehoge
(1 row)

testdb=# GRANT USAGE ON SEQUENCE student_student_id_seq TO testuser;
GRANT

対応後

testdb=> INSERT INTO student(name, age) VALUES('HogeHoge', 20);
INSERT 0 1