はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事は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では、かなり改善されると思います。今後はこうした新機能をきちんと使って、より安全なロール設計をする必要がありますね。