PostgreSQL の ALTER DEFAULT PRIVILEGES には OWNER 属性があるよ

はじめに

PostgreSQL 内のロールを利用元に応じて適切な権限に設定する対応を検討する際に自分がハマったポイントがあったので、記事化しました。

結論としては、タイトルにもある通り、

ALTER DEFAULT PRIVILEGES には OWNER 属性があるから、実行するロールとオブジェクト（テーブルやシーケンス）を生成するロールは同じにしないといけない

ということです。

そもそも、ALTER DEFAULT PRIVILEGES とは？

機能を一言で言うと、「将来作成されるオブジェクトに対して、デフォルトの権限を事前に定義しておく機能」 です。
この SQL は、標準 SQL にはなく、PostgreSQL 独自のものです。他の DBMS だと、権限管理の考え方や粒度が異なるので、同等の機能に相当するものを用いたり、いくつか機能を組み合わせたりして実現できます。

参考

以下が公式ドキュメントです。

https://www.postgresql.org/docs/17/sql-alterdefaultprivileges.html

https://www.postgresql.jp/document/17/html/sql-alterdefaultprivileges.html

きっかけ

冒頭にも書きましたが、今回、権限の整理を行おうと考えておりました。
ここでは、当初の検討イメージや手順を記載し、当初考えていた方法だと何が問題があったのかを説明します。

前提

以下のような立て付けで、利用用途に合わせてロールを使い分けるように考えていました。

ロール	役割
マスター	全てを司るもの。 デフォルトだと postgres という名前で作成されるロールに相当する。
アプリケーション	アプリケーションが DB にアクセスする際のロール。 基本的な操作（CRUD） ができればよい。
マイグレーション	データベースのテーブルやカラムを変更するロール。 Flyway や Liquibase などが使用する。 CRUD だけでなく、ALTER TABLE などの実行もおこなう。

設定の流れ・手順

以下の流れで実現しようとしていました。^[1]

1. マスターロールでログインする

psql -h <ホスト> -p <ポート> -U <マスター> -d <データベース名>

2. アプリケーション、マイグレーションのロールを追加

ロールを追加する。^[2]

CREATE ROLE <アプリケーション> WITH LOGIN PASSWORD '<アプリケーション用のパスワード>';
CREATE ROLE <マイグレーション> WITH LOGIN PASSWORD '<マイグレーション用のパスワード>';

3. 既存のオブジェクトにも有効になるようにアプリケーション、マイグレーションへの権限追加

-- アプリケーション
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <スキーマ> TO <アプリケーション>;

-- マイグレーション
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <スキーマ> TO <マイグレーション>;
GRANT USAGE, CREATE ON SCHEMA <スキーマ> TO <マイグレーション>;

4. 将来的に生成されるオブジェクトにも有効になるようにアプリケーション、マイグレーションへのデフォルト権限追加

-- アプリケーション
ALTER DEFAULT PRIVILEGES IN SCHEMA <スキーマ> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <アプリケーション>;

-- マイグレーション
ALTER DEFAULT PRIVILEGES IN SCHEMA <スキーマ> GRANT ALL PRIVILEGES ON TABLES TO <マイグレーション>;

するとどうなった？

結果として、上記の手順対応より後に作成されたテーブルに対して、アプリケーションからアクセスできない事象が発生しました。
つまり、設定したデフォルト権限がうまく機能していませんでした。^[3]

なぜ、機能しなかったのか？

冒頭に書いたとおり、ALTER DEFAULT PRIVILEGES 自体にも OWNER 属性があります。
今回の場合、ロール作成やデフォルト権限を設定した SQL を実行したロールが「マスター」でした。
その一方、対応以降でテーブルを作成するロールは「マイグレーション」だったので、無論、それ以降のテーブルの OWNER は「マイグレーション」です。
つまり、デフォルト権限の OWNER とテーブルの OWNER が異なるので、「アプリケーション」に対して意図した権限が付与されませんでした。

ちなみに、この時点で \ddp コマンドを実行して、確認すると以下のようになります。^[4]

\ddp
                Default access privileges
  Owner    | Schema   | Type  |  Access privileges               
-----------+----------+-------+------------------------------------
 <マスター> | <スキーマ> | table | <アプリケーション>=arwd/<マスター>   +
           |          |       | <マイグレーション>=arwdDxt/<マスター>

上記の通り、設定したデフォルト権限が「マスター」で作成されたものしか適用されないことがわかります。

自分が勘違いしていたポイントとしては、権限を付与したり、剥奪したりする GRANT や REVOKE で OWNER の概念がなく、それに関連する ALTER DEFAULT PRIVILEGES がグローバルな設定と思い込んでいたという点でした。

では、どうすればいいのか？

この Owner が一致していればいいので、手順「4. 将来的に生成されるオブジェクトにも有効になるようにアプリケーション、マイグレーションへのデフォルト権限追加」で実行した SQL を本当であれば「マイグレーション」ロールで実行するべきでした。
そうすることで、デフォルト権限の OWNER が「マイグレーション」になり、テーブルの OWNER と一致するので意図した状態になります。

まとめ

今回の話で、以下を学びました。

• デフォルト権限についても OWNER の概念があること
• デフォルト権限が適用されるのは、OWNER が一致したオブジェクトのみであること
• デフォルト権限の状態を確認するには、\ddp で確認できること
• 権限に関しては、ローカルなどの非本番環境で検証を実施すること（当たり前ですね！）

権限に関する設定はセキュリティ観点でとても大事です。
不必要に強力な権限を使いまわさず、最小権限にとどめた運用をしていきたいですが、DBMS ごとに固有の仕様や癖があるのも事実です。
思い込みや勘違いなどを避けるためにも、一つ一つ検証を通して、理解して取り組んでいきたいです。

もし誰かのお役に立てられれば幸いです！

脚注

1. 簡単のため、シーケンスに関する権限付与は割愛しています。

2. CREATE USER でも問題ない。

3. 名誉のために書いておきますが、もちろん、本番ではなく、検証中のローカルでの発生です。

4. arwd や arwdDxt は ACL 短縮形です。参考：PostgreSQL 日本語ドキュメント：ACL 短縮形