allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

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

この記事は[PostgreSQL 16 全部ぬこ Advent Calendar 2022](https://qiita.com/advent-calendar/2022/pg16-all-nuko) 20日目の記事です。
今回はメンテナンスコマンド用の新しい定義済みロールに関する話を書きます。

# 概要

|項目|内容|
|:--|:--|
|タイトル|[allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX](https://commitfest.postgresql.org/41/4055/)|
|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](https://qiita.com/nuko_yokohama/items/37b197a6f39edb1278f4)」で、``VACUUM``と``ANALYZE``を実行可能な定義済みロール(``pg_vacuum_all_tables``, ``pg_analyze_all_tables``)について書きました。
``そのときに、CLUSTER``や``REINDEXに``ついても試したのですが、それらのコマンドは対応しておらず、特権ユーザでの実行が必要でした。

今回紹介する改善項目により、``CLUSTER``, ``REFRESH MATERIALIZED VIEW``, ``REINDEX``を非特権ユーザで実行可能になりました。

追加された定義済みロールは``pg_maintain``という名前のロールです。

```sql
=# 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|メンテナンスコマンド実行用のユーザ|

```sql
=# 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の内容をコピーしたマテリアライズド・ビュー|

```bash
$ createdb -U postgres -O testdb_owner testdb
$ psql -U testdb_owner testdb
psql (16devel)
Type "help" for help.

=>
```

```sql
=> 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
=>
```
作成されたデータベースオブジェクトに対するアクセス権限を確認します。

まずスキーマに対するアクセス権限を確認します。

```sqk
=> \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)

=>
```
次に各スキーマ内のテーブル/マテリアライズド・ビューに対する権限を確認します。

```sql
=> \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``スキーマ上のテーブルにアクセスします。

```sql
$ 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``コマンドを実行します。

```sql
=> 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では、かなり改善されると思います。今後はこうした新機能をきちんと使って、より安全なロール設計をする必要がありますね。