はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事は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が絡むあたり）は、まだ自分も完全に理解しきれている自信がないです・・・。