はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 20日目の記事です。
今回はメンテナンスコマンド用の新しい定義済みロールに関する話を書きます。
概要
| 項目 | 内容 |
|---|---|
| タイトル | allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX |
| Topic | Miscellaneous |
| ステータス | commited |
| Last Modified | 2022-12-14 |
| 概要 | CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX実行用の定義済みロールの追加 |
変更内容
pg_maintainロール
今回のアドベントカレンダーの1日目「PG16:allow granting VACUUM and ANALYZE privileges on relations」で、VACUUMとANALYZEを実行可能な定義済みロール(pg_vacuum_all_tables, pg_analyze_all_tables)について書きました。
そのときに、CLUSTERやREINDEXについても試したのですが、それらのコマンドは対応しておらず、特権ユーザでの実行が必要でした。
今回紹介する改善項目により、CLUSTER, REFRESH MATERIALIZED VIEW, REINDEXを非特権ユーザで実行可能になりました。
追加された定義済みロールはpg_maintainという名前のロールです。
=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles ;
rolname | rolsuper | rolcanlogin
---------------------------+----------+-------------
postgres | t | t
pg_database_owner | f | f
pg_read_all_data | f | f
pg_write_all_data | f | f
pg_monitor | f | f
pg_read_all_settings | f | f
pg_read_all_stats | f | f
pg_stat_scan_tables | f | f
pg_read_server_files | f | f
pg_write_server_files | f | f
pg_execute_server_program | f | f
pg_signal_backend | f | f
pg_checkpoint | f | f
pg_maintain | f | f
(14 rows)
=#
pg_maintainロールは特権属性もログイン属性もfalseになっています。このため利用時には、ログイン可能な属性をもつ非特権ロールをこのロールに属させます。
ロールの作成とGRANTによる設定
今回は4つの非特権ロールを追加します。
| ロール名 | 役割 |
|---|---|
| testdb_owner | testdbデータベースの所有者 |
| user_a | aテーブルへのアクセスが可能なユーザ |
| user_b | bテーブルへのアクセスが可能なユーザ |
| user_m | メンテナンスコマンド実行用のユーザ |
=# CREATE ROLE testdb_owner LOGIN ;
CREATE ROLE
=# CREATE ROLE user_a LOGIN ;
CREATE ROLE
=# CREATE ROLE user_b LOGIN ;
CREATE ROLE
=# CREATE ROLE user_m LOGIN ;
CREATE ROLE
=# GRANT pg_maintain TO user_m;
GRANT ROLE
=# \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_maintain}
=#
user_mのMenber of列にpg_maintainロールが含まれています。
データベースオブジェクトの作成
また、以下のデータベース・テーブルを作成します。これらのデータベースオブジェクトは全てtestdb_ownerが所有者とします。
| オブジェクト名 | オブジェクト種別 | 内容 |
|---|---|---|
| testdb | データベース | testdb_ownerが所有するデータベース |
| user_a | スキーマ | user_aがアクセスするテーブルを置くスキーマ |
| table_a | テーブル | user_aがアクセスするテーブル |
| mv_a | マテリアライズド・ビュー | table_aの内容をコピーしたマテリアライズド・ビュー |
| user_b | スキーマ | user_bがアクセスするテーブルを置くスキーマ |
| table_b | テーブル | user_bがアクセスするテーブル |
| mv_b | マテリアライズド・ビュー | table_bの内容をコピーしたマテリアライズド・ビュー |
$ createdb -U postgres -O testdb_owner testdb
$ psql -U testdb_owner testdb
psql (16devel)
Type "help" for help.
=>
=> CREATE SCHEMA user_a;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA user_a TO user_a;
GRANT
=> GRANT ALL ON SCHEMA user_a TO user_m;
GRANT
=> CREATE TABLE user_a.table_a (id int primary key, data text);
CREATE TABLE
=> GRANT ALL ON TABLE user_a.table_a TO user_a;
GRANT
=> CREATE MATERIALIZED VIEW user_a.mv_a AS SELECT * FROM user_a.table_a;
SELECT 0
=> GRANT SELECT ON TABLE user_a.mv_a TO user_a;
GRANT
=>
=> CREATE SCHEMA user_b;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA user_b TO user_a;
GRANT
=> GRANT ALL ON SCHEMA user_b TO user_m;
GRANT
=> CREATE TABLE user_b.table_b (id int primary key, data text);
CREATE TABLE
=> GRANT ALL ON TABLE user_b.table_b TO user_b;
GRANT
=> CREATE MATERIALIZED VIEW user_b.mv_b AS SELECT * FROM user_b.table_b;
SELECT 0
=> GRANT SELECT ON TABLE user_b.mv_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_m=UC/testdb_owner |
user_b | testdb_owner | testdb_owner=UC/testdb_owner +|
| | user_b=UC/testdb_owner +|
| | user_m=UC/testdb_owner |
(3 rows)
=>
次に各スキーマ内のテーブル/マテリアライズド・ビューに対する権限を確認します。
=> \dp user_a.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------------------+------------------------------------+-------------------+----------
user_a | mv_a | materialized view | testdb_owner=arwdDxtm/testdb_owner+| |
| | | user_a=r/testdb_owner | |
user_a | table_a | table | testdb_owner=arwdDxtm/testdb_owner+| |
| | | user_a=arwdDxtm/testdb_owner | |
(2 rows)
=> \dp user_b.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------------------+------------------------------------+-------------------+----------
user_b | mv_b | materialized view | testdb_owner=arwdDxtm/testdb_owner+| |
| | | user_b=r/testdb_owner | |
user_b | table_b | table | testdb_owner=arwdDxtm/testdb_owner+| |
| | | user_b=arwdDxtm/testdb_owner | |
(2 rows)
=>
ここで注目すべきポイントは、
-
user_mはuser_a,user_bスキーマへのアクセス権限は付与されている。 -
user_mはuser_,user_bスキーマに作成されたデータベースオブジェクトへのアクセス権限は全く持っていない。
ということです。
この状態で、user_mでログインしたときの挙動を見てみます。
user_m によるアクセス
user_mでログインして、user_aスキーマ上のテーブルにアクセスします。
$ psql testdb -U user_m
psql (16devel)
Type "help" for help.
=> SELECT * FROM user_a.table_a ;
ERROR: permission denied for table table_a
=> SELECT * FROM user_a.mv_a ;
ERROR: permission denied for materialized view mv_a
=>
きちんと権限エラーになります。
ではuser_a.table_aに対してCLUSTERとREINDEXコマンドを、user_a.mv_aに対して、REFRESH MATERIALIZED VIEWコマンドを実行します。
=> CLUSTER user_a.table_a USING table_a_pkey ;
CLUSTER
=> REINDEX TABLE user_a.table_a ;
REINDEX
=> REFRESH MATERIALIZED VIEW user_a.mv_a ;
REFRESH MATERIALIZED VIEW
=>
これらのメンテナンス用コマンドは実行できます。
user_mは、メンテナンス対象となるデータベースオブジェクトの内容をDMLによって参照・更新はできませんが、メンテナンスコマンドは実行できる、という権限を持っていることがわかります。
おわりに
以前のPostgreSQLではこうした権限分掌の機能が弱い、という問題がありましたが、PostgreSQL 16では、かなり改善されると思います。今後はこうした新機能をきちんと使って、より安全なロール設計をする必要がありますね。