PG16:allow granting VACUUM and ANALYZE privileges on relations

# はじめに

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

この記事は[PostgreSQL 16 全部ぬこ Advent Calendar 2022](https://qiita.com/advent-calendar/2022/pg16-all-nuko) 1日目の記事です。
今回は``VACUUM``/``ANALYZE``を実行可能な権限をもつ定義済みロールの追加について書いてみます。

:::note warn
今日、別件で、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](https://commitfest.postgresql.org/40/3774/)|
|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のみ可能なロール<br>(PostgreSQL 16のみ使用)|

```sh
$ 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として作成します。

```sh
$ 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相当)を与えます。

```sql
=> 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
=>
```
スキーマの権限を確認します。

```sql
=> \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テーブルを作成します。

```sh
$ 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を実行する例になります。

```sh
$ psql testdb -U user_a -c "VACUUM"
```

|実行ロール|user_aスキーマ|user_bスキーマ|システムカタログ|
|:--|:--|:--|:--|
|user_a|VACUUMされる|スキップされる|スキップ|
|user_b|スキップ|VACUUMされる|スキップ|
|testdb_owner|VACUUMされる|VACUUMされる|データベース内のカタログはVACUUMされる。<br>データベースにまたがるカタログはスキップされる。|
|postgres|VACUUMされる|VACUUMされる|VACUUMされる|


## PostgreSQL 16

postgresユーザでログインし、``GRANT``コマンドで、PostgreSQL 16に追加された定義済みロール``pg_vacuum_all_tables``と``pg_analyze_all_tables``の役割をuser_mに与えます。

```sql
=# 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``のメンバに属していることが確認できます。

```sql
=# \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される。<br>データベースにまたがるカタログはスキップされる。|
|postgres|VACUUMされる|VACUUMされる|VACUUMされる|
|user_m|VACUUMされる|VACUUMされる|VACUUMされる|

user_mにVACUUM権限が与えられたことを確認できました。

### user_mの各スキーマへのアクセス

user_mはVACUUM専用のロールになっているので、user_a, user_bスキーマにアクセスしようとしても権限エラーになります。

```sh
$ 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
```sql
=# 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

```sql
=# 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の権限分掌を強化する定義済みロールの追加について紹介しました。
今後も他のメンテナンスコマンド対応のロールや、これらのロールをまとめたメンテナンス用ロールなどが追加されていくのかもしれませんね。