PostgreSQLでシーケンス権限不足エラーの解決方法

PostgreSQLでデータを処理する際に、下記のようなエラーが発生することがあります。

ERROR:  permission denied for sequence xxx_id_seq

このエラーは、ロールやユーザーに実行権限が付与されていないことが原因です。
この記事では、この問題の解決方法や導入方法を解説します。

問題の原因

問題が発生した原因として、次のようなことが考えられます。

• シーケンスが標準名前ではない場合、再実行時に新たな名前で作成されている
• GRANT文で、ユーザー名やテーブル名が適切にクォートで囲われていない
• シーケンスの削除をすり抜している

解決方法

さらなるシーケンス不足を解決するため、以下のことを行いましょう。

1. シーケンス削除の追加

DDLでテーブルを削除する際に、シーケンスも同時に削除する処理を追加します。

DO $$
DECLARE
    seq RECORD;
BEGIN
    FOR seq IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema='public' LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS public.' || seq.sequence_name || ' CASCADE';
    END LOOP;
END $$;

2. GRANT文の調整

ユーザー名にハイフンが含まれる場合は、必ずダブルクォートで囲います。

DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
        EXECUTE 'GRANT ALL ON TABLE public.' || quote_ident(tbl.tablename) || ' TO "user-name";';
    END LOOP;
END $$;

DO $$
DECLARE
    seq RECORD;
BEGIN
    FOR seq IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public' LOOP
        EXECUTE 'GRANT SELECT, USAGE ON SEQUENCE public.' || quote_ident(seq.sequence_name) || ' TO "user-name";';
    END LOOP;
END $$;

3. DDLの定期実行スクリプトの要件

シーケンスの再作成により付与権限が消える場合があります。
定期実行スクリプトにこれらの変更を可能な限り追加しましょう。

おわりに

この記事では、PostgreSQLのシーケンス権限問題とその解決方法を解説しました。
テーブルを削除、作成する際にはロールやユーザーの実行権限を確認し、GRANT文があれば見直してみてください。
問題を抱えるデータベース管理者の参考となれば幸いです。

• Original:https://minory.org/postgresql-sequence-error.html
• Source:minory
• Author:管理者