PG16：Add USER SET parameter values for pg_db_role_setting

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

この記事は[PostgreSQL 16 全部ぬこ Advent Calendar 2022](https://qiita.com/advent-calendar/2022/pg16-all-nuko) 18日目の記事です。
今回は``pg_db_role_setting``に追加された列について書いてみます。

# 概要

|項目|内容|
|:--|:--|
|タイトル|[Add USER SET parameter values for pg_db_role_setting](https://commitfest.postgresql.org/41/4037/)|
|Topic|Server Features|
|ステータス|commited|
|Last Modified|2022-12-09|
|概要|``pg_db_role_setting``に``setuser``列が追加された|

# 変更内容
## pg_db_role_settingとは
PostgreSQL 14文書の[pg_db_role_setting](https://www.postgresql.jp/document/14/html/catalog-pg-db-role-setting.html)のページから引用します。

> ``pg_db_role_setting``カタログはロールとデータベースの組み合わせ毎に、実行時設定変数に設定されるデフォルト値を記録します。 

このシステムカタログの内容を確認すると、データベースクラスタ生成直後には1行も存在していません。
このシステムカタログには、``ALTER DATABSE``や``ALTER ROLE``によってPostgreSQLパラメータが設定されたときに、行が追加されます。

## PostgreSQL 15の例
PostgreSQL 15での実行例を示します。

PostgreSQL 15の``pg_db_role_setting``の定義は以下になります。
```bash
 ~/pgsql/master/bin/psql testdb -p 15001 -c "\d pg_db_role_setting"
         Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default
-------------+--------+-----------+----------+---------
 setdatabase | oid    |           | not null |
 setrole     | oid    |           | not null |
 setconfig   | text[] | C         |          |
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

$
```

### データベースに対する設定
まずデータベースにPostgreSQLパラメータを設定した場合の例を示します。
```sql
=# CREATE DATABASE testdb ;
CREATE DATABASE
=# ALTER DATABASE testdb SET max_parallel_workers_per_gather = 0;
ALTER DATABASE
=# SELECT d.datname, drs.setconfig FROM pg_database d JOIN pg_db_role_setting drs ON (d.oid = drs.setdatabase);
 datname |              setconfig
---------+-------------------------------------
 testdb  | {max_parallel_workers_per_gather=0}
(1 row)

=#
```

``setconfig``に``max_parallel_workers_per_gather=0``（デフォルト値は2）がセットされています。
この状態で、``testdb``にログインして、SHOWコマンドでパラメータを確認すると0になっていることが確認できます。

```bash
$ ~/pgsql/pgsql-15/bin/psql testdb -c "SHOW max_parallel_workers_per_gather"
 max_parallel_workers_per_gather
---------------------------------
 0
(1 row)

$
```

### ロールに対する設定
ロールに対しても同様の設定ができます。

```sql
=# CREATE ROLE testuser LOGIN ;
CREATE ROLE
=# ALTER ROLE testuser SET log_statement = 'ALL';
ALTER ROLE
=# SELECT r.rolname, drs.setconfig FROM pg_roles r JOIN pg_db_role_setting drs ON (r.oid = drs.setrole);
 rolname  |      setconfig
----------+---------------------
 testuser | {log_statement=ALL}
(1 row)

=#
```

### 【参考】データベースとロールのパラメータ設定の優先度
さて、ここまでデータベースとロールに対するパラメータ設定を説明しました。
では、データベースに対するパラメータ設定とロールに対するパラメータ設定が被ったらどうなるんでしょう？

上の例の続きになりますが、``testuser``に対して、``max_parallel_workers_per_gather=4``を設定します。

```sql
=# CREATE ROLE testuser LOGIN;
CREATE ROLE
=# ALTER ROLE testuser SET max_parallel_workers_per_gather = 4;
ALTER ROLE
=# SELECT r.rolname, drs.setconfig FROM pg_roles r JOIN pg_db_role_setting drs ON (r.oid = drs.setrole);
 rolname  |              setconfig
----------+-------------------------------------
 testuser | {max_parallel_workers_per_gather=4}
(1 row)

=#
```
``testdb``データベースには``max_parallel_workers_per_gather=0``を設定します。
```sql
=# ALTER DATABASE testdb SET max_parallel_workers_per_gather = 0;
ALTER DATABASE
=# SELECT d.datname, drs.setconfig FROM pg_database d JOIN pg_db_role_setting drs ON (d.oid = drs.setdatabase);
 datname |              setconfig
---------+-------------------------------------
 testdb  | {max_parallel_workers_per_gather=0}
(1 row)

=#
```
で、この状態で``testuser``が``testdb``にログインして``max_parallel_workers_per_gather``の値を確認します。

```bash
$ ~/pgsql/pgsql-15/bin/psql -p 15001 -U testuser testdb -c "SHOW max_parallel_workers_per_gather"
 max_parallel_workers_per_gather
---------------------------------
 4
(1 row)

$
```
:::note info
データベースとロールに対して同じパラメータ名を別の値で設定した場合には、ロールの設定が優先されます。
:::

## PostgreSQL 16での変更
### 追加された列
PostgreSQL 16の``pg_db_role_setting``の定義を確認します。

```bash
 ~/pgsql/master/bin/psql testdb -p 16001 -c "\d pg_db_role_setting"
          Table "pg_catalog.pg_db_role_setting"
   Column    |   Type    | Collation | Nullable | Default
-------------+-----------+-----------+----------+---------
 setdatabase | oid       |           | not null |
 setrole     | oid       |           | not null |
 setconfig   | text[]    | C         |          |
 setuser     | boolean[] |           |          |
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

$
```
PostgreSQL 16からは``setuser``という``boolean配列``の列が追加されています。開発中のPostgreSQL Document(12/11現在)の[pg_db_role_setting](https://www.postgresql.org/docs/devel/catalog-pg-db-role-setting.html)を見てみると、

> Values of USER SET flag for every setting in setconfig

と書かれています。``setconfig``ってなんだろう？

### ALTER ROLEコマンドの変更
MLの議論を追っていくと、途中で``ALTER ROLE``への変更に関する話がでてきた。
ということで、現時点の[PostgreSQL devel (2022-12-09 15:44:42 - git commit bad5116957)のALTER ROLE](https://www.postgresql.org/docs/devel/sql-alterrole.html)のページを見ると、以下のように構文が一部変更されていました。

> ``
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | value USER SET | DEFAULT }``

``USER SET``が追加されています。
カスタムPostgreSQL変数に対して、このオプションを付与した``ALTER ROLE``を実行することで、どうやら``setuser``列の配列要素に`t`(=`ture`)が設定されるようです。

## PostgreSQL 16での実行例
ここでは、特権ロール``postgres``と、そこから``SET ROLE``された一般ロール``foo``による、カスタムPostgreSQL変数``prefix.bar``,``prefix.baz``に対する値の設定と、``pg_db_role_setting``システムカタログの``setuser``列の結果を見ていきます。（ちょい長い）

まず``postgres``ロールで``postgres``データベースにログインします。

```sql
$ ~/pgsql/master/bin/psql -p 16001 -a -f ml-test.sql
SELECT current_user;
 current_user
--------------
 postgres
(1 row)
```

一般ロール``foo``を作成します。

```sql
drop role if exists foo;
DROP ROLE
create role foo noinherit;
CREATE 
```

この状態では``pg_db_role_setting``には何も設定されていません。
```sql
TABLE pg_db_role_setting;
 setdatabase | setrole | setconfig | setuser
-------------+---------+-----------+---------
(0 rows)
```

一般ロール``foo``に切り替えます。

```sql
set role to foo;
SET
SELECT current_user;
 current_user
--------------
 foo
(1 row)

TABLE pg_db_role_setting;
 setdatabase | setrole | setconfig | setuser
-------------+---------+-----------+---------
(0 rows)
```

``ALTER ROLE``コマンドで``foo``ロールに対して、``user set``オプションつきで、カスタムPostgreSQLパラメータ``prefix.bar``に値をセットします。

```sql
alter role foo set prefix.bar to 'abc' user set;
ALTER ROLE
```

この状態で``pg_db_role_setting``を参照します。
```sql
TABLE pg_db_role_setting;
 setdatabase | setrole |    setconfig     | setuser
-------------+---------+------------------+---------
           0 |   16472 | {prefix.bar=abc} | {t}
(1 row)
```
``setconfig``列（配列になっている）の第1要素には設定したパラメータ名と値が、``setuser``列（これも配列になっている）の第1要素には``true``が設定されています。

次に、``prefix.baz``を``SET USER``オプションなしで設定しようとしますが、
```sql
alter role foo set prefix.baz to 'xyz';
psql:ml-test.sql:14: ERROR:  permission denied to set parameter "prefix.baz"
```
これは権限エラーになります。``SET ROLE``で一般権限ロールに切り替えた場合には、``SET USER``オプションが必要ということみたいです。

特権ロール``postgres``に切り替え、```foo``ロールに対して、SET USER``オプションなしで``prefix.baz``に値をセットする``ALTER ROLE``を実行します。

```sql
set role to postgres;
SET
SELECT current_user;
 current_user
--------------
 postgres
(1 row)

alter role foo set prefix.baz to 'xyz';
ALTER ROLE
```
これは成功します。

この状態で``pg_db_role_setting``を参照します。

```sql
TABLE pg_db_role_setting;
 setdatabase | setrole |            setconfig            | setuser
-------------+---------+---------------------------------+---------
           0 |   16472 | {prefix.bar=abc,prefix.baz=xyz} | {t,f}
(1 row)
```
``setconfig``列、``setuser``列ともに要素が2つになっています。そして、``setuser``列の第2要素が`f`(=`false`)になっているのが確認できます。

:::note info
``setuser``列は、``ALTER ROLE``コマンドでカスタムパラメータに値を設定するときに``SET USER``オプションを付与していた(``t``)か付与していないか(``f``)を識別する情報が格納されている、ということのようです。
:::

# おわりに
今回は、``ALTER ROLE``コマンドおよび、``pg_db_role_setting``システムカタログへの変更んいついて書いてみました。

PostgreSQLのロール回り（とくに``SET ROLE``が絡むあたり）は、まだ自分も完全に理解しきれている自信がないです・・・。