はじめに
にゃーん。
最近、ばたばたしていたPostgreSQL 14の調査をサボってましたが、また見てみようかなと。
今日は、PostgreSQL 14で追加された定義済みロール1について調べてみました。
定義済みロール
定義済みロールは、PostgreSQL 9.6から導入された権限分掌強化のための仕組みです。以降、各メジャーバージョンアップごとにさり気なく増えてます。
それぞれの定義済みロールがどのバージョンで増えたのかは以下の表を見てください。
| ロール名 | 9.6 | 10 | 11 | 12 | 13 | 14 |
|---|---|---|---|---|---|---|
| pg_signal_backend | ● | ● | ● | ● | ● | ● |
| pg_read_all_settings | - | ● | ● | ● | ● | ● |
| pg_read_all_stats | - | ● | ● | ● | ● | ● |
| pg_stat_scan_tables | - | ● | ● | ● | ● | ● |
| pg_monitor | - | ● | ● | ● | ● | ● |
| pg_read_server_files | - | - | ● | ● | ● | ● |
| pg_write_server_files | - | - | ● | ● | ● | ● |
| pg_execute_server_program | - | - | ● | ● | ● | ● |
| pg_read_all_data | - | - | - | - | - | ● |
| pg_write_all_data | - | - | - | - | - | ● |
| pg_database_owner | - | - | - | - | - | ● |
PostgreSQL 14で増えたデフォルトロール
PostgreSQL 14では以下の3つのデフォルトロールが追加されました。
| ロール名 | 概要 |
|---|---|
| pg_read_all_data | すべてのテーブル、ビュー、シーケンスに対する読み取り権限を与える。 |
| pg_write_all_data | すべてのテーブル、ビュー、シーケンスに対する書き込み権限を与える。 |
| pg_database_owner | データベース所有者に一致する定義済みロール。 テンプレートデータベースで有用。 |
これらのロールはログイン権限を持っていないので、それ単独でログイン用のユーザとしては使えません。他のロール（ユーザ）にこのロールをメンバとして持つようにGRANT文で設定が必要です。
今回の記事では、上記のうちpg_read_all_dataとpg_write_all_dataについてPostgreSQL 14 beta3で動作確認した内容を紹介します。
検証モデル
今回の検証では、最初に以下の3つの一般ユーザ（ログイン権限をもったロール）を作ります。
ユーザと付与するロール
| ユーザ名 | このユーザの役割 | 付与するロール |
|---|---|---|
| db_owner | データベース所有者。そのデータベースに対してなんでもできる。ローカルな神 | なし（データベース作成時に所有者に指定する） |
| data_admin | 業務データの参照/更新を行うユーザ | pg_read_all_data pg_write_all_data |
| viewer | 業務データの参照のみを行うユーザ | pg_read_all_data |
作成するデータベース/スキーマ/テーブル
- testdb というデータベースを作成し、所有者を db_owner に設定する。
- db_ownerがtestdb データベースの下にtestスキーマを作成する。
- db_owverがtestスキーマ内にfooテーブルを作成する。
data_admin/viewerユーザの挙動
上記の設定により、data_admin/viewerユーザは以下のように動作する。
| 操作 | data_admin | viewer |
|---|---|---|
| testスキーマ上に任意のテーブルを作成する | 権限エラー | 権限エラー |
| fooテーブルのSELECT | 成功 | 成功 |
| fooテーブルのUPDATE | 成功 | 権限エラー |
| fooテーブルのDELETE | 成功 | 権限エラー |
| fooテーブルのTRUNCATE | 権限エラー | 権限エラー |
ちょっと面白いことに、pg_write_all_dataを付与したdata_adminは、DELETEはできるけど、TRUNCATEはできない。
また、今回はdata_adminにはpg_write_all_dataとpg_read_all_dataを付与しているが、pg_write_all_dataしか付与しない場合は、INSERTはできるけどUPDATEもDELETEも権限エラーになる。
動きを考えればまあ、そうかとなるんだけど（UPDATEもDELETEもWHERE句での評価を行う以上、対象の行を読めなければいけない）、PostgreSQL 14 beta3文書のTable 22.1を見ると
Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly.
と書いてあって、一見するとpg_write_all_dataロールだけ付与すれば、INSERT/UPDATE/DELETEが出来るようにも読めちゃうのよなあ。にゃーん。
おわりに
ということで、PostgreSQL 14のpg_write_all_dataとpg_read_all_dataをうまく使うと、データ更新をさせたいユーザと参照のみしかさせないユーザの管理を簡単にできるかも？という話でした。
参考
以前のバージョンの定義済みロール（デフォルトロール）については、以前自分が調べた記事も参考にしてください。
-
PostgreSQL 13まではデフォルトロール（Default Role）と呼ばれていました。呼び方自体、PostgreSQL 14で変えたみたい。 ↩