（ネタとしてはもう古い・・・）

本記事はPostgreSQL AdventCalenderの16日目の記事です。

はじめに

本記事はPostgreSQL 15develを対象に確認した結果を使用しています。開発中のものであるため、リリース時には挙動が変わっている可能性がある点にご注意ください。

元々はROLEの設計について書こうと思っていたのですが、定義済みロールに見かけない子（pg_database_owner）がいたので気になって調べていたら、皆様ご存知のPUBLICロールへのREVOKEがPostgreSQL 15からは不要になるという話があったので確認してみたという話になります。
（割とインパクトのある変更なので、既にご存知の方も多いかもですが汗。）

環境

CentOS 7
PostgreSQL
- 14.1
- 15devel（2021/12/8時点）

定義済みロール（Predifined Roles　旧：デフォルトロール）とは？

PostgreSQLでは、特権ユーザしか許可されていない操作やデータがいくつか存在します。
ただ、そういった操作やデータにアクセスしたい場合に全て特権ユーザ権限を付与するのでは、影響が大きくなってしまいます。
そこで、PostgreSQL9.6以降、一部の権限を一般ユーザに持たせることができるように権限群が提供されるようになったものが定義済みロールです。（PostgreSQL13まではデフォルトロールという名前でしたが、14から名称が変更されました。）

定義済みロールの種類は年々増えており、2021/12/8時点では12種類がマニュアル(devel)に掲載されています。

ROLE名	9.6	10	11	12	13	14	15	説明
pg_read_all_data						○	○	全てのスキーマへのUSAGE権限、全てのテーブル、ビュー、シーケンスに対してSELECT権限と同等のアクセスを許可する。BYPASSRLS属性は持たない。
pg_write_all_data						○	○	全てのスキーマへのUSAGE権限、全てのテーブル、ビュー、シーケンスに対してINSERT、UPDATE、DELETE権限と同等のアクセスを許可する。BYPASSRLS属性は持たない。
pg_read_all_settings		○	○	○	○	○	○	全ての設定変数の参照を許可する。
pg_read_all_stats		○	○	○	○	○	○	全てのpg_stat_*ビューの参照を許可し、統計関連の拡張機能を使用を許可する。
pg_stat_scan_tables		○	○	○	○	○	○	長時間シェアロックを取得する可能性があるモニタリング関数の実行を許可する。
pg_monitor		○	○	○	○	○	○	pg_read_all_settings、pg_read_all_stats、pg_stat_scan_tablesを包括して許可する。
pg_database_owner						○	○	暗黙のうちに現在のデータベース所有者のメンバとして構成される。
pg_signal_backend	○	○	○	○	○	○	○	他のバックエンドへの、クエリのキャンセルやセッションの終了を許可する。
pg_read_server_files			○	○	○	○	○	サーバ上でアクセスできる場所からファイルの読み込みを許可する。
pg_write_server_files			○	○	○	○	○	サーバ上でアクセスできる場所へのファイルの書き込みを許可する。
pg_execute_server_program			○	○	○	○	○	データベースを実行するユーザで、サーバ上のプログラムを実行することを許可する。
pg_checkpointer							○	CHECKPOINTコマンドの実行を許可する。

PostgreSQLでは、ROLEとはユーザやグループ（権限の集まり）を包括したものとなっているため、混乱しがちな要素です。
Web上にはROLEに関しての説明が多く掲載されているので、詳細はそちらをご確認ください。

pg_database_owner？知らない子ですね。

定義済みロールの一覧を見ていくと大体どういうときに使うものかは想像ができたのですが、「pg_database_owner」は説明を読んでもどういうタイミングで使うのかわからなかったです。

ということで調べてみました。

説明だけではよくわからないけど、GRANTで与えたらデータベース所有者のメンバとして動作する？っていう理解でいいのかな？

とりあえず、pg_database_ownerを一般ユーザのロールにGRANTしてみる

一般ユーザを作成して、postgresデータベースでスキーマを作成してみる。

$ psql postgres -h localhost -c "CREATE ROLE ippan15 LOGIN"
CREATE ROLE
$ psql postgres -h localhost -U ippan15 -c "CREATE SCHEMA newschema"
ERROR:  permission denied for database postgres

データベースの所有者ではないし、postgresデータベースへのCREATE権限を付与されているわけではないので、当然データベース内にオブジェクトを作成することはできません。これは想定通りの動作。

では、一般ユーザにGRANTでpg_database_ownerに付けてみると？

$ psql postgres -h localhost -U postgres -c "GRANT pg_database_owner TO ippan"
ERROR:  role "pg_database_owner" cannot have explicit members

怒られました。pg_database_ownerは明示的なメンバーをもつことができない？

該当のソースのコメントは以下のとおり。

src/backend/commands/user.c

    /*
     * The charter of pg_database_owner is to have exactly one, implicit,
     * situation-dependent member.  There's no technical need for this
     * restriction.  (One could lift it and take the further step of making
     * pg_database_ownercheck() equivalent to has_privs_of_role(roleid,
     * ROLE_PG_DATABASE_OWNER), in which case explicit, situation-independent
     * members could act as the owner of any database.)
     */
    if (roleid == ROLE_PG_DATABASE_OWNER)
        ereport(ERROR,
                errmsg("role \"%s\" cannot have explicit members", rolename));

暗黙的に状況に依存するメンバであると・・・（よくわからない。）

コミュニティでの議論等を追ってみると、どうやら、pg_database_ownerはpublicスキーマのACL（アクセスコントロール）のために設けられた定義済みロールで一般的な用途で使用するものではなさようです。

というわけで、publicスキーマについて確認してみます。

PostgreSQL 14.1のpublicスキーマの所有者

$ psql postgres -h localhost -U postgres -c "SELECT pn.nspname, pr.rolname as nspowner, pn.nspacl FROM pg_namespace pn, pg_roles pr WHERE pn.nspowner =  pr.oid"
      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 pg_toast           | postgres | 
 pg_catalog         | postgres | {postgres=UC/postgres,=U/postgres}
 public             | postgres | {postgres=UC/postgres,=UC/postgres}
 information_schema | postgres | {postgres=UC/postgres,=U/postgres}
(4 rows)

PostgreSQL 15develでのpublicスキーマの所有者

$ psql postgres -h localhost -U postgres -c "SELECT pn.nspname, pr.rolname as nspowner, pn.nspacl FROM pg_namespace pn, pg_roles pr WHERE pn.nspowner = pr.oid"
      nspname       |     nspowner      |                            nspacl                             
--------------------+-------------------+---------------------------------------------------------------
 information_schema | postgres          | {postgres=UC/postgres,=U/postgres}
 pg_catalog         | postgres          | {postgres=UC/postgres,=U/postgres}
 pg_toast           | postgres          | 
 public             | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}
(4 rows)

おぉ！たしかにpublicスキーマの所有者がpg_database_ownerに変わっているぞ。

特に注目してほしいのがpublicスキーマのnspacl部分です。

# ～PostgreSQL 14
      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 public             | postgres | {postgres=UC/postgres,=UC/postgres}

# PostgreSQL 15devel
      nspname       |     nspowner      |                            nspacl                             
--------------------+-------------------+---------------------------------------------------------------
 public             | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}

これはPUBLICロールへのACLの情報が書かれているのですが、日本語にするとこんな感じです。（UとかCとか短縮された文字が何を意味するかはPostgreSQL文書をご確認ください。）

PostgreSQL 14（=UC/postgres）
- postgresロールによって、publicスキーマへのCREATE権限、USAGE権限がPUBLICロールに付与された。
PostgreSQL 15devel（=U/pg_database_owner）
- pg_database_ownerロールによって、publicスキーマへのUSAGE権限がPUBLICロールに付与された。

ってことは、PostgreSQL15以降ではpublicスキーマへのREVOKEが不要！？

ようやくタイトルの内容を回収できます。

pg_database_ownerを調べているうちにROLE系の話から離れてしまいましたが、いい勉強になりました。

publicスキーマのACLについて確認してみると、こちらのコミットログ（Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
）が見つかりました。

皆さんご存知のとおり、全てのロールはPUBLICロールのメンバに暗黙的に所属しているため、PUBLICロールからCREATE権限を剥奪しないとpublicスキーマでオブジェクト作成ができてしまいます。（デフォルトがこの動作になるので、セキュリティ的には微妙だった。）

この挙動がPostgreSQL 15からは変わるようです。ようやくpublicスキーマの扱いを気にする必要はなくなるようなので、今後、PostgreSQL 15でロール設計（権限設計）をする方は少し幸せになりますね。

以下、例としてpublicスキーマに対してCREATEを実行したらどうなるかをお見せします。

publicスキーマへのCREATEの挙動確認（PostgreSQL 14.1）

$ psql postgres -h localhost -U postgres -c "CREATE ROLE ippan14 LOGIN"
CREATE ROLE
$ psql postgres -h localhost -U ippan14 -c "CREATE TABLE public.foo(id int)"
CREATE TABLE
$ psql postgres -h localhost -U postgres -c "CREATE DATABASE newdb"
CREATE DATABASE
$ psql newdb -h localhost -U ippan14 -c "CREATE TABLE public.foo(id int)"
CREATE TABLE

ippan14ロールはpostgresデータベースの所有者ではないですが、publicスキーマであれば、こうしてテーブルを作成することが可能です。postgresデータベースはもちろん新たに作成したnewdbデータベースでも同様です。

明示的に権限を設定しなくても、新規に作成したROLEはデフォルトでオブジェクトを作成できてしまうという状態だったので、これまでは対策として以下のようにPUBLICロールからpublicスキーマへのCREATE権限を剥奪する等の対応が必要でした。

$ psql postgres -h localhost -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC"
REVOKE
$ psql newdb -h localhost -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC"
REVOKE

PUBLICロールからCREATE権限をREVOKEをすることで以下のようにpublicスキーマへの操作を抑止することができます。

$ psql postgres -h localhost -U ippan14 -c "CREATE TABLE public.bar(id int)"
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.bar(id int)
$ psql newdb -h localhost -U ippan14 -c "CREATE TABLE public.bar(id int)"
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.bar(id int)

このpublicスキーマへは誰でもCREATEができるという挙動が PostgreSQL 15devel ではどうなっているか・・・

publicスキーマへのCREATEの挙動確認（PostgreSQL 15devel）

$ psql postgres -h localhost -U postgres -c "CREATE ROLE ippan15 LOGIN"
$ psql postgres -h localhost -U ippan15 -c "CREATE TABLE public.foo(id int)"
2021-12-09 01:00:25.329 JST [12452] ERROR:  permission denied for schema public at character 14
2021-12-09 01:00:25.329 JST [12452] STATEMENT:  CREATE TABLE public.foo(id int)
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.foo(id int)
                     ^

事前のREVOKEなしでpublicスキーマへのCREATEが抑止されている！！素晴らしい！！

なお、publicスキーマのACLはpg_database_owner=UC/pg_database_owner となっているので、データベースの所有者であれば、publicスキーマへのCREATE権限が許可されていることになります。

$ psql postgres -h localhost -U postgres -c "ALTER DATABASE postgres OWNER TO ippan15"
ALTER DATABASE
$ psql postgres -h localhost -U ippan15 -c "CREATE TABLE public.foo(id int)"
CREATE TABLE

このように ippan15 を postgres データベースの所有者にしてあげれば、publicスキーマへのCREATEは実行できます。

pg_database_ownerの活用について

publicスキーマのACLを制御するために作成されたもので、一般的な用途はなさそうですが一応考えてみます。

任意に作成したオブジェクトのOWNERをpg_database_ownerに変更できるか

可能です。

pg_database_ownerをグループとして、メンバを追加することはできませんが、publicスキーマと同様にオブジェクトのOWNERをpg_database_ownerに変更することは可能です。

$ psql postgres -h localhost -U postgres -p 5433 -c "CREATE SCHEMA newschema"
CREATE SCHEMA
$ psql postgres -h localhost -U postgres -p 5433 -c "ALTER SCHEMA newschema OWNER TO pg_database_owner"
ALTER SCHEMA
$ psql postgres -h localhost -U postgres -p 5433 -c "\dn" 
        List of schemas
   Name    |       Owner       
-----------+-------------------
 newschema | pg_database_owner
 public    | pg_database_owner
(2 rows)
$ psql postgres -h localhost -U postgres -p 5433 -c "CREATE TABLE newschema.foo(id int)" 
CREATE TABLE
$ psql postgres -h localhost -U postgres -p 5433 -c "ALTER TABLE newschema.foo OWNER TO pg_database_owner" 
ALTER TABLE
$ psql postgres -h localhost -U postgres -p 5433 -c "\dt newschema.foo" 
              List of relations
  Schema   | Name | Type  |       Owner       
-----------+------+-------+-------------------
 newschema | foo  | table | pg_database_owner
(1 row)

任意で作成したオブジェクトのOWNERをpg_database_ownerに変更することで何かうれしいことはあるか？

考えてみましたが、特に思いつかなかったです。

オブジェクトの所有者とは、対象のオブジェクトに対する全権限をもった状態と同義になります。pg_database_ownerは対象のオブジェクトが定義されるデータベースの所有者として扱われるので、むしろ任意で作成するオブジェクトであれば、pg_database_ownerは使用せず、従来のとおり明確にROLEを指定するのがよいと思います。

まとめ

来年、PostgreSQL15のロール設計をしている人がいたら言ってあげてください。

「PostgreSQL 15を使っているのに、PUBLICロールにREVOKEっているやついる？」

おわり。

明日の担当は @kaigai さんです。爆速なPostgreSQLの話が聞けるんでしょうか。楽しみですね。

PUBLICロールにREVOKEってるやついる？