LoginSignup
0

allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

Last updated at Posted at 2022-12-19

はじめに

にゃーん。趣味でポスグレをやっている者だ。

この記事は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」で、VACUUMANALYZEを実行可能な定義済みロール(pg_vacuum_all_tables, pg_analyze_all_tables)について書きました。
そのときに、CLUSTERREINDEXについても試したのですが、それらのコマンドは対応しておらず、特権ユーザでの実行が必要でした。

今回紹介する改善項目により、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_mMenber 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_muser_a,user_bスキーマへのアクセス権限は付与されている。
  • user_muser_,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に対してCLUSTERREINDEXコマンドを、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では、かなり改善されると思います。今後はこうした新機能をきちんと使って、より安全なロール設計をする必要がありますね。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
0