はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 1日目の記事です。
今回はVACUUM/ANALYZEを実行可能な権限をもつ定義済みロールの追加について書いてみます。
今日、別件で、12/19に最新化したソースでpg_rolesを調べたら、定義済みロールpg_vacuum_all_tablesと、pg_analyze_all_tablesが消えてました・・・。
どうやらpg_maintainに統合されたようです。(2022-12-21)
概要
| 項目 | 内容 |
|---|---|
| タイトル | allow granting VACUUM and ANALYZE privileges on relations |
| Topic | Miscellaneous |
| ステータス | commited |
| Last modified | 2022-11-28 |
| 概要 | VACUUM/ANALYZE用の定義済みロールの追加 |
変更内容
PostgreSQL 16では、VACUUMやANALYZEを行う権限を持つ定義済みロールが追加されました。
定義済みロールとは
PostgreSQLには9.6から導入された定義済みロールと呼ばれるロールがあります。これは、特権ユーザ(postgresユーザ)でないとできなかった操作を、別のロールに割当て、そのロールを非特権ユーザに割り当てることで、特権を持たないユーザでも様々な運用を可能にします。
PostgreSQL 9.6～16(devel)までに対応した定義済みロールを以下を示します。
| ロール名 | 対応ver | ロールの機能 |
|---|---|---|
| pg_signal_backend | 9.6 | 他のバックエンドを終了可能 |
| pg_read_all_settings | 10 | 全ての設定変数を読み取る |
| pg_read_all_stats | 10 | 全てのpg_stat_*ビューを読み取る。 |
| pg_stat_scan_tables | 10 | 各種の統計関連のエクステンションを使用できる。 |
| pg_monitor | 10 | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tablesを兼ねる。 |
| pg_read_server_files | 11 | サーバ上のファイルを読める。 |
| pg_write_server_files | 11 | サーバ上のファイルに書ける。 |
| pg_execute_server_program | 11 | サーバ上でのプログラムを実行できる。 |
| pg_read_all_data | 14 | すべてのデータ(テーブル、ビュー、シーケンス)を読み取る。 |
| pg_write_all_data | 14 | すべてのデータ(テーブル、ビュー、シーケンス)に書き込む。 |
| pg_database_owner | 14 | データベース所有者に一致する定義済みロール。 |
| pg_checkpoint | 15 | CHECKPOINTを実行可能なロール |
| pg_vacuum_all_tables | 16 | VACUUMを実行可能なロール |
| pg_analyze_all_tables | 16 | ANALYZEを実行可能なロール |
PostgreSQL 16では上記の表の最後の2行にある、pg_vacuum_all_tablesとpg_analyze_all_tablesが追加されました。
この追加されたロールは、名前のとおり全てのテーブルに対するVACUUMやANALYZEを実行する権限を持ちます。
実行例
検証環境
createuserユーティリティを使って、postgresユーザの権限で以下の非特権ロールを作成します。
| ロール名 | ロール内容 |
|---|---|
| testdb_owner | testdbデータベースの所有者 |
| user_a | user_aスキーマのデータにアクセス可能なロール |
| user_b | user_bスキーマののデータにアクセス可能ロール |
| user_m | VACUUM/ANALYZEのみ可能なロール (PostgreSQL 16のみ使用) |
$ createuser -U postgres testdb_owner
$ createuser -U postgres user_a
$ createuser -U postgres user_b
$ createuser -U postgres user_m
$ psql -U postgres postgres -c "\du"
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testdb_owner | | {}
user_a | | {}
user_b | | {}
user_m | | {}
$
postgresユーザの権限で、検証用のデータベースtest_dbを所有者db_ownerとして作成します。
$ createdb -U postgres -O testdb_owner testdb
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+--------------+----------+---------+-------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C | C | | libc |
template0 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | testdb_owner | UTF8 | C | C | | libc |
(4 rows)
$
psqlを使ってtestdbにログインし、db_owner権限で、user_a用、user_b用のスキーマを作成し、それぞれのスキーマと同名のユーザにALL権限(USAGEとCREATE相当)を与えます。
=> CREATE SCHEMA user_a;
CREATE SCHEMA
=> CREATE SCHEMA user_b;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA user_a TO user_a ;
GRANT
=> GRANT ALL ON SCHEMA user_b TO user_b ;
GRANT
=>
スキーマの権限を確認します。
=> \dnp+
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
user_a | testdb_owner | testdb_owner=UC/testdb_owner +|
| | user_a=UC/testdb_owner |
user_b | testdb_owner | testdb_owner=UC/testdb_owner +|
| | user_b=UC/testdb_owner |
(3 rows)
=>
user_aユーザ、user_bユーザでログインし、それぞれアクセス可能なスキーマ上にtestテーブルを作成します。
$ psql testdb -U user_a -c "CREATE TABLE user_a.test_a (id int primary key, data text)"
CREATE TABLE
$ psql testdb -U user_b -c "CREATE TABLE user_b.test_b (id int primary key, data text)"
CREATE TABLE
$
PostgreSQL 15
PostgreSQL 15上で、このtestdbに対してVACUUMを実行すると以下のようになります。
以下はuser_aロールでVACUUMを実行する例になります。
$ psql testdb -U user_a -c "VACUUM"
| 実行ロール | user_aスキーマ | user_bスキーマ | システムカタログ |
|---|---|---|---|
| user_a | VACUUMされる | スキップされる | スキップ |
| user_b | スキップ | VACUUMされる | スキップ |
| testdb_owner | VACUUMされる | VACUUMされる | データベース内のカタログはVACUUMされる。 データベースにまたがるカタログはスキップされる。 |
| postgres | VACUUMされる | VACUUMされる | VACUUMされる |
PostgreSQL 16
postgresユーザでログインし、GRANTコマンドで、PostgreSQL 16に追加された定義済みロールpg_vacuum_all_tablesとpg_analyze_all_tablesの役割をuser_mに与えます。
=# GRANT pg_vacuum_all_tables TO user_m;
GRANT ROLE
=# GRANT pg_analyze_all_tables TO user_m;
GRANT ROLE
=#
duコマンドで、user_mがpg_vacuum_all_tablesとpg_analyze_all_tablesのメンバに属していることが確認できます。
=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+----------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testdb_owner | | {}
user_a | | {}
user_b | | {}
user_m | | {pg_vacuum_all_tables,pg_analyze_all_tables}
=#
これで準備完了です。PostgreSQL 15と同様に、各ロールでtestdbをVACUUMします。
VACUUMの結果は以下のようになります。
| 実行ロール | user_aスキーマ | user_bスキーマ | システムカタログ |
|---|---|---|---|
| user_a | VACUUMされる | スキップされる | スキップ |
| user_b | スキップ | VACUUMされる | スキップ |
| testdb_owner | VACUUMされる | VACUUMされる | データベース内のカタログはVACUUMされる。 データベースにまたがるカタログはスキップされる。 |
| postgres | VACUUMされる | VACUUMされる | VACUUMされる |
| user_m | VACUUMされる | VACUUMされる | VACUUMされる |
user_mにVACUUM権限が与えられたことを確認できました。
user_mの各スキーマへのアクセス
user_mはVACUUM専用のロールになっているので、user_a, user_bスキーマにアクセスしようとしても権限エラーになります。
$ psql testdb -U user_m -c "TABLE user_a.test_a"
ERROR: permission denied for schema user_a
LINE 1: TABLE user_a.test_a
^
$ psql testdb -U user_m -c "TABLE user_b.test_b"
ERROR: permission denied for schema user_b
LINE 1: TABLE user_b.test_b
^
$
このように、VACUUM/ANALYZE専用のメンテナンスロールを作成することで、
- メンテナンスコマンドは実行できるけど
- データそのものにはアクセスできない
という権限分掌が実現できるようになりました。
素晴らしい！
他のメンテナンスコマンドは？
PostgreSQLのメンテナンスはVACUUMの他にもANALYZE、REINDEX、CLUSTERなどがありますが、それらについても、user_mで動作させたときの結果を確認してみました。
| コマンド名 | 結果 |
|---|---|
| ANALZYE | 実行可能 |
| REINDEX SCHEMA | 権限エラーになる |
| REINDEX | 権限エラーになる |
| VACUUM FULL | 実行可能 |
| CLUSTER | 権限エラーになる |
今回与えられたロールはANALYZEとVACUUM用なので、REINDEX,CLUSTERの実行権限はないようです。実質的にほぼ同じ動作のVACUUM FULLとCLUSTERで挙動が異なってくるのがちょい罠っぽいですね。
psqlタブ補完の改善(2022-12-03追記)
別の改善項目(psqlのタブ補完関連)を調べているときに、以下の改善項目もこの記事に追記すべきと判断して、追記しました。
PostgreSQL 16からは、psqlのタブ補完でGRANT/REVOKEコマンドの直後にVACUUM/ANALYZEキーワードが補完されるようになりました。
PostgreSQL 15
=# GRANT
ALL pg_checkpoint pg_signal_backend TEMPORARY
ALTER SYSTEM pg_database_owner pg_stat_scan_tables testdb_owner
CONNECT pg_execute_server_program pg_write_all_data TRIGGER
CREATE pg_monitor pg_write_server_files TRUNCATE
DELETE pg_read_all_data postgres UPDATE
EXECUTE pg_read_all_settings REFERENCES USAGE
GRANT pg_read_all_stats SELECT
INSERT pg_read_server_files SET
=# GRANT
PostgreSQL 16
=# GRANT
ALL pg_analyze_all_tables pg_read_server_files SELECT
ALTER SYSTEM pg_checkpoint pg_signal_backend SET
ANALYZE pg_database_owner pg_stat_scan_tables TEMPORARY
CONNECT pg_execute_server_program pg_vacuum_all_tables TRIGGER
CREATE pg_monitor pg_write_all_data TRUNCATE
DELETE pg_read_all_data pg_write_server_files UPDATE
EXECUTE pg_read_all_settings postgres USAGE
INSERT pg_read_all_stats REFERENCES VACUUM
=# GRANT
おわりに
今回はPostgreSQLの権限分掌を強化する定義済みロールの追加について紹介しました。
今後も他のメンテナンスコマンド対応のロールや、これらのロールをまとめたメンテナンス用ロールなどが追加されていくのかもしれませんね。