PG16：Add USER SET parameter values for pg_db_role_setting

はじめに

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

この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 18日目の記事です。
今回はpg_db_role_settingに追加された列について書いてみます。

概要

項目	内容
タイトル	Add USER SET parameter values for pg_db_role_setting
Topic	Server Features
ステータス	commited
Last Modified	2022-12-09
概要	pg_db_role_settingにsetuser列が追加された

変更内容

pg_db_role_settingとは

PostgreSQL 14文書のpg_db_role_settingのページから引用します。

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

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

PostgreSQL 15の例

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

PostgreSQL 15のpg_db_role_settingの定義は以下になります。

 ~/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パラメータを設定した場合の例を示します。

=# 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になっていることが確認できます。

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

$

ロールに対する設定

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

=# 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を設定します。

=# 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を設定します。

=# 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の値を確認します。

$ ~/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)

$

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

PostgreSQL 16での変更

追加された列

PostgreSQL 16のpg_db_role_settingの定義を確認します。

 ~/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を見てみると、

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のページを見ると、以下のように構文が一部変更されていました。

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データベースにログインします。

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

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

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

この状態ではpg_db_role_settingには何も設定されていません。

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

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

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に値をセットします。

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

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

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オプションなしで設定しようとしますが、

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``を実行します。

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を参照します。

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)になっているのが確認できます。

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

おわりに

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

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