🐘

PostgreSQL 16でのロールに関する変更点

2023/04/24に公開

PostgreSQL

Database

tech

はじめに

本記事は、第41回 PostgreSQLアンカンファレンス@オンラインで発表したものと同様のものとなります。

2023年4月がPostgreSQL 16のFeature Freezeでしたね。本記事では、PostgreSQL 16でのロールに関する変更点を紹介したいと思います。
この記事で紹介する機能は正式リリースまでの間に変更となる可能性があることにご注意ください。

動作環境

Rocky Linux 9.1
PostgreSQL 15.2
PostgreSQL 16devel (2434d60a2ae977a460eda401fd9f99cf619f4743)

紹介する機能

CREATEROLE属性に関する変更
定義済みロールの追加
GUCパラメータの追加

CREATEROLE属性に関する変更

まずはおさらいですが、あるロールAの実行できる操作は以下の要素から決まります。

ロールAの属性
- LOGIN/SUPERUSER/CREATEDB/CREATEROLE/REPLICATION/PASSWORD/INHERIT/BYPASSRLS/CONNECTION LIMIT
ロールAに付与された権限
- スキーマやテーブルなどのデータベースオブジェクトの権限
ロールAに付与されたINHERIT属性をもつロール（メンバシップ）から継承した権限
- INHERIT属性を持たない場合はそのロールにSET ROLEすることで権限を行使可能
- ADMIN OPTION付きで付与されたロールは、他のロールに再付与可能

そして、PostgreSQL 15までのCREATEROLE属性は、SUPERUSER属性をもつロールに関する操作、SUPERUSER/REPLICATION/BYPASSRLS属性の操作を除く、ロールに関する操作全般を行うことができました。

PostgreSQL 15

=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=# SET ROLE role_a ;
SET
=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 shinya       | role_a
(1 row)

=> -- 自身が持たないCREATEDB属性をもち、pg_execute_server_programのメンバシップをもつロールを作成できる
=> CREATE ROLE role_b LOGIN CREATEDB IN ROLE pg_execute_server_program ;
CREATE ROLE
=> -- REPLICATION属性をもつロールはスーパーユーザしか作成できない
=> CREATE ROLE role_c REPLICATION ;
ERROR:  must be superuser to create replication users
=> -- 任意のロールを任意のロールに付与できる
=> GRANT role_b TO role_a ;
GRANT ROLE
=> -- スーパーユーザの属性は変更できない
=> ALTER ROLE shinya NOLOGIN ;
ERROR:  must be superuser to alter superuser roles or change superuser attribute
=> -- スーパーユーザのロールを他のロールに付与できない
=> GRANT shinya TO role_a ;
ERROR:  must be superuser to alter superusers

このCREATEROLE属性の挙動がPostgreSQL 16から変更されます。
具体的には、自身が持つ属性、ADMINT OPTION付きで付与されたロールに関する操作のみを行うことができるようになります。

PostgreSQL 16devel

=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=# SET ROLE role_a ;
SET
=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 shinya       | role_a
(1 row)

=> -- role_aはCRATEDB属性をもたないので、CREATEDB属性をもつロールを作成できない
=> CREATE ROLE role_b CREATEDB ;
ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEDB attribute may create roles with CREATEDB.
=> -- role_aはpg_execute_server_programのメンバシップをもたない（かつADMIN OPTIONもない）ので、pg_execute_server_programのメンバシップをもつロールを作成できない
=> CREATE ROLE role_c IN ROLE pg_execute_server_program ;
ERROR:  permission denied to grant role "pg_execute_server_program"
DETAIL:  Only roles with the ADMIN option on role "pg_execute_server_program" may grant this role.
=> CREATE ROLE role_d REPLICATION ;
ERROR:  permission denied to create role
DETAIL:  Only roles with the REPLICATION attribute may create roles with REPLICATION.
=> RESET ROLE ;
RESET
=# -- CREATEDB属性をもつロールを作成
=# CREATE ROLE role_e LOGIN CREATEROLE CREATEDB ;
CREATE ROLE
=# -- REPLICATION属性をもつロールを作成
=# CREATE ROLE role_f LOGIN CREATEROLE REPLICATION ;
CREATE ROLE
=# -- pg_execute_server_programのメンバシップをもつロールを作成。ADMIN OPTIONなし
=# CREATE ROLE role_g LOGIN CREATEROLE IN ROLE pg_execute_server_program ;
CREATE ROLE
=# -- pg_execute_server_programのメンバシップをもつロールを作成。ADMIN OPTIONあり
=# CREATE ROLE role_h LOGIN CREATEROLE ;
CREATE ROLE
=# GRANT pg_execute_server_program TO role_h WITH ADMIN OPTION ;
GRANT ROLE
=# \du
                                            List of roles
 Role name |                         Attributes                         |          Member of          
-----------+------------------------------------------------------------+-----------------------------
 role_a    | Create role                                                | {}
 role_e    | Create role, Create DB                                     | {}
 role_f    | Create role, Replication                                   | {}
 role_g    | Create role                                                | {pg_execute_server_program}
 role_h    | Create role                                                | {pg_execute_server_program}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=# SET ROLE role_e ;
SET
=> -- CREATEDB属性をもつロールは、CREATEDB属性をもつロールを作成できる
=> CREATE ROLE role_i CREATEDB ;
CREATE ROLE
=> SET ROLE role_f ;
SET
=> -- REPLICATION属性をもつロールは、スーパーユーザでなくてもREPLICATION属性をもつロールを作成できる
=> CREATE ROLE role_j REPLICATION ;
CREATE ROLE
=> SET ROLE role_g ;
SET
=> -- pg_execute_server_programのメンバシップをもつがADMIN OPTIONはないため、pg_execute_server_programのメンバシップをもつロールは作成できない
=> CREATE ROLE role_k IN ROLE pg_execute_server_program ;
ERROR:  permission denied to grant role "pg_execute_server_program"
DETAIL:  Only roles with the ADMIN option on role "pg_execute_server_program" may grant this role.
=> SET ROLE role_h ;
SET
=> -- pg_execute_server_programのメンバシップをADMIN OPTIONありでもつので、pg_execute_server_programのメンバシップをもつロールは作成できる
=> CREATE ROLE role_l IN ROLE pg_execute_server_program ;
CREATE ROLE

また、ロールAがロールBを作成したときにロールBのメンバシップが自動的にロールAに付与されるようになります。これにより、自身が作成したロールに関する操作は自動的に行える状態になることができます。

PostgreSQL 16devel

=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=# SET ROLE role_a ;
SET
=> CREATE ROLE role_b ;
CREATE ROLE
=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {role_b}
 role_b    | Cannot login                                               | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

しかし、デフォルトではロールBにSET ROLEすることはできなくなります。また、ロールBの権限を継承する際の挙動が変わります。以降で、新たな追加されたSET/INHERIT OPTIONについて説明します。

SET OPTION

ロールAがロールBを作成したときに、ロールAにロールBのメンバシップが自動的に付与されますが、ロールAはロールBにSET ROLEすることができません。

PostgreSQL 16devel

=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 role_a       | role_a
(1 row)

=> CREATE ROLE role_b ;
CREATE ROLE
=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {role_b}
 role_b    | Cannot login                                               | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=> -- role_aはrole_bメンバシップをもっているがSET ROLEできない
=> SET ROLE role_b ;
ERROR:  permission denied to set role "role_b"

PostgreSQL 16からは、SET ROLEするためには、ロールを付与するときにSET OPTIONを指定してGRANTする必要があります。SET TRUEはSET OPTIONと同義であり、明示的にSET ROLEできないようにするにはSET FALSEを指定します。

PostgreSQL 16devel

=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {role_b}
 role_b    | Cannot login                                               | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 role_a       | role_b
(1 row)

=> GRANT role_b TO role_a WITH SET OPTION ;
GRANT ROLE
=> \du
                                      List of roles
 Role name |                         Attributes                         |    Member of    
-----------+------------------------------------------------------------+-----------------
 role_a    | Create role                                                | {role_b,role_b}
 role_b    | Cannot login                                               | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=> SET ROLE role_b ;
SET

INHERIT OPTION

ロールAがロールBを作成したときに、ロールAにロールBのメンバシップが自動的に付与されますが、ロールAはロールBの権限を自動的に継承しません。

PostgreSQL 16devel

=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 role_a       | role_a
(1 row)

=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 role_a    | Create role                                                | {role_b}
 role_b    |                                                            | {}
 shinya    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=> \dn+
                                       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                  +| 
        |                   | role_b=UC/pg_database_owner            | 
(1 row)

=> CREATE TABLE public.t (i INT) ;
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.t (i INT) ;
                     ^

PostgreSQL 16からは、自動的に権限を継承するには、ロールを付与するときにINHERIT OPTIONを指定してGRANTする必要があります。INHERIT TRUEはINHERIT OPTIONと同義であり、明示的に自動的に権限を継承しないようにするにはSET FALSEを指定します。

PostgreSQL 16devel

=> GRANT role_b TO role_a WITH INHERIT OPTION ;
GRANT ROLE
=> CREATE TABLE public.t (i INT) ;
CREATE TABLE

`createrole_self_grant`パラメータ

このパラメータは、スーパーユーザではないがCREATEROLE属性を持つロールが、作成したロールに自動的にSET/INHERIT OPTIONを付与するためのものです。
createrole_self_grant = 'set, inherit'のようにカンマ区切りでsetとinheritを指定することができ、それぞれを付与するかどうかを制御できます。デフォルトは空文字列です。

PostgreSQL 16devel

=> SELECT SESSION_USER, CURRENT_USER ;
 session_user | current_user 
--------------+--------------
 role_a       | role_a
(1 row)

=> SET createrole_self_grant TO 'set' ;
SET
=> CREATE ROLE role_b ;
CREATE ROLE
=> SET ROLE role_b ;
SET

CREATEROLE属性の挙動が変わることの恩恵

今まではCREATEROLE属性を持っているだけでロールに関する操作が大体行うことができたのですが、今回の変更によりCREATEROLE属性の権限が弱くなりました。

これによりどのような恩恵があるかというと、以前のPostgreSQLアンカンファレンスで発表したような、CREATEROLE属性を持っているロールがデータベースクラスタを壊したり、スーパーユーザを奪取したりすることができなくなります。

簡単に説明すると、v15まではCREATEROLE属性を持っているロールは、どの定義済みロールも付与することができました。
それにより、pg_execute_server_programロールを付与することで、サーバプログラムを実行することのできるロールを作成することができました。
そして、そのロールを使って色々な悪さをすることができたのです。

v16からは、CREATEROLE属性を持っていても、ADMIN OPTION付きで付与されたpg_execute_server_programが付与されていないと、他のロールにpg_execute_server_programを付与することができなくなりました。

定義済みロールの追加

PostgreSQL 16では、3つの定義済みロールが追加予定です。

ロール	説明
~~`pg_maintain`~~	~~すべてのリレーションに対して、`VACUUM`、`ANALYZE`、`CLUSTER`、`REFRESH MATERIALIZED VIEW`、`REINDEX`、`LOCK TABLE`を実行できる~~
`pg_create_subscription`	`CREATE SUBSCRIPTION`を実行できる
`pg_use_reserved_connections`	`reserved_connections`パラメータで予約した接続スロットを使用できる

pg_maintain最終的にRevertされました

PostgreSQL 15ではpg_checkpointというCHECKPOINT文を実行する権限を与えるための定義済みロールが追加されましたが、pg_maintainもそれと同様のロールになります。
~~pg_maintainを付与することで、スーパーユーザしか実行できないメンテナンス系のSQL文の権限を与えることができるようになります。~~

2022年11月に、このコミットでpg_vacuum_all_tablesとpg_analyze_all_tablesという定義済みロールが追加されましたが、pg_maintainに統合されることになりRevertされています。

`pg_create_subscription`

pg_maintainと同様にスーパーユーザしか実行できないCREATE SUBSCRIPTION文の権限を与えることができるようになります。

`pg_use_reserved_connections`

新たに追加されたGUCパラメータreserved_connectionsと深く関係するため後ほど説明します。

GUCパラメータの追加

PostgreSQL 16では、ロールに関するGUCパラメータが2つ追加予定です。

GUCパラメータ	説明
`reserved_connections`	`pg_use_reserved_connections`ロールを持つロールによる接続のために予約されている接続スロット数を指定。デフォルト値は0
`createrole_self_grant`	上述

`reserved_connections`

reserved_connectionsに確保する接続スロット数を設定し、接続スロットを確保させるロールにpg_use_reserved_connectionsを付与することで、スーパーユーザでないロールのための接続スロットを確保することができます。

イメージ図は以下のようになります。max_connectionsのうちsuperuser_reserved_connections、reserved_connections、その他の接続スロットに分類することができます。

イメージ図

=# \du
                                                    List of roles
       Role name        |                         Attributes                         |           Member of           
------------------------+------------------------------------------------------------+-------------------------------
 role_general           |                                                            | {}
 role_has_reserved_conn |                                                            | {pg_use_reserved_connections}
 shinya                 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

=# SHOW max_connections ;
 max_connections 
-----------------
 15
(1 row)

=# SHOW superuser_reserved_connections ;
 superuser_reserved_connections 
--------------------------------
 3
(1 row)

=# SHOW reserved_connections ;
 reserved_connections 
----------------------
 5
(1 row)

=# SELECT usename, COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY usename ;
        usename         | count 
------------------------+-------
 role_general           |     7
 shinya                 |     1
(2 rows)

max_connections-superuser_reserved_connections - reserved_connections < 一般ロールの接続数、となると接続に失敗します。

$ psql postgres -U role_general
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role

=# SELECT usename, COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY usename ;
        usename         | count 
------------------------+-------
 role_general           |     7
 role_has_reserved_conn |     5
 shinya                 |     1
(3 rows)

reserved_connections < pg_use_reserved_connectionsロールを持つロールの接続数、となると接続に失敗します。

$ psql postgres -U role_has_reserved_conn
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  remaining connection slots are reserved for roles with SUPERUSER

=# SELECT usename, COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY usename ;
        usename         | count 
------------------------+-------
 role_general           |     7
 role_has_reserved_conn |     5
 shinya                 |     3
(3 rows)

superuser_reserved_connections>スーパーユーザの接続数、となると接続に失敗します。

$ psql postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  sorry, too many clients already

まとめ

本記事ではPostgreSQL 16でのロールの変更に関して紹介しました。
最近のPostgreSQLのバージョンでは今までよりも細やかな権限制御ができるようになってきました。しかし、ロールを管理が複雑になりすぎてよくわからない状況になってきました。どのくらいのPostgreSQLユーザがロールを使いこなせているのか気になるところです。

PostgreSQL 16でのロールに関する変更点

はじめに

動作環境

紹介する機能

CREATEROLE属性に関する変更

SET OPTION

INHERIT OPTION

`createrole_self_grant`パラメータ

CREATEROLE属性の挙動が変わることの恩恵

定義済みロールの追加

pg_maintain最終的にRevertされました

`pg_create_subscription`

`pg_use_reserved_connections`

GUCパラメータの追加

`reserved_connections`

まとめ

参考

Discussion

はじめに

動作環境

紹介する機能

CREATEROLE属性に関する変更

SET OPTION

INHERIT OPTION

createrole_self_grantパラメータ

CREATEROLE属性の挙動が変わることの恩恵

定義済みロールの追加

pg_maintain最終的にRevertされました

pg_create_subscription

pg_use_reserved_connections

GUCパラメータの追加

reserved_connections

まとめ

参考

Discussion

`createrole_self_grant`パラメータ

`pg_create_subscription`

`pg_use_reserved_connections`

`reserved_connections`