TECH BLOG
技術ブログ

ARTICLE

  • 2024-10-20 PostgreSQL

    PostgreSQLの定義済みロールとPUBLICロール

今回も引き続き PostgreSQL のロールについての記事となります。今回は、定義済みロールと PUBLIC ロールについて説明していきます。

定義済みロール

PostgreSQL には予め用意されているロール(定義済みロール)が存在します。新しくロールを作成して権限を管理する前に、定義済みロールを利用することで実現できるかどうかを確認するのが推奨となります。
定義済みロールは、PostgreSQL のバージョンが上がる際によく追加されますので、PostgreSQL のバージョンアップを行った際にも、既存のロールを定義済みロールに置き換えられるかどうかを検証するのが推奨となります。
PostgreSQL 文書に定義済みロールの一覧がありますので、詳細はそちらを参照してください。

【 PostgreSQL 文書 16.4 - 22.5. 定義済みロール - 】
https://www.postgresql.jp/document/16/html/predefined-roles.html

以下では、pg_read_all_data ロールおよび pg_database_owner ロールを test_user に付与して、挙動を確認しています。

pg_read_all_data ロール

pg_read_all_data ロールを付与されたロールは、すべてのスキーマのテーブル、ビュー、シーケンスに対して参照できるようになります。
下記では test_user に対して pg_read_all_data ロールを付与し、statsrepo.snapshot テーブルを参照できるようにしています。

-- test_user に pg_read_all_data を付与する前に statsrepo.snapshot を参照
$ psql -U test_user -d postgres -c "SELECT * FROM statsrepo.snapshot LIMIT 3"
ERROR:  permission denied for schema statsrepo
LINE 1: SELECT * FROM statsrepo.snapshot LIMIT 3

test_user には、statsrepo スキーマに対する SELECT 権限がないため、statsrepo.snapshot テーブルを SELECT しようとしても上記の通り、「ERROR: permission denied for schema statsrepo」という権限不足エラーが発生します。

-- test_uesr に pg_read_all_data を付与
$ psql -U postgres -c "GRANT pg_read_all_data TO test_user;"
GRANT ROLE

-- test_user で statsrepo.snapshot を参照
$ psql -U test_user -d postgres -c "SELECT * FROM statsrepo.snapshot LIMIT 3"
 snapid | instid |             time              | comment |    exec_time    | snapshot_increase_size | xid_current 
--------+--------+-------------------------------+---------+-----------------+------------------------+-------------
      1 |      1 | 2024-09-03 06:30:00.082054+00 |         | 00:00:00.172181 |                 221184 |         755
      2 |      1 | 2024-09-03 07:00:00.044034+00 |         | 00:00:00.154523 |                   8192 |         763
      3 |      1 | 2024-09-03 07:30:00.076401+00 |         | 00:00:00.113315 |                      0 |         773
(3 rows)

pg_read_all_data ロールが付与されたことによって test_user ユーザで statsrepo.snapshot テーブルを参照できるようになりました。
pg_read_all_data ロールを利用することで、参照だけを行う運用者用ユーザなどを簡単に作成できます。pg_read_all_data ロールには更新処理やスーパーユーザ権限が必要なコマンドなどを実行する権限はないため、オペレーションミスによる誤った更新などを抑止できます。

pg_database_owner ロール

pg_database_owner ロールは、PostgreSQL 15 から導入された定義済みロールで、その名の通りデータベースの所有者を表すロールとなっています。また、PostgreSQL 15 以降では public スキーマの所有者が、この pg_database_owner ロールになります(14 以前は postgres でした)。
データベースの所有者に対して権限を変更したい場合、14 以前ではデータベースの所有者となっているロールに対して権限を変更する必要がありましたが、15 以降では pg_database_owner の権限を変更することで実現できます(所有者のロールの権限を変更しなくてもよいというのがメリットです)。

また、pg_database_owner は、別のロールに付与できないロールとなっています。

$ psql -U postgres -c "GRANT pg_database_owner TO test_user;"
ERROR:  role "pg_database_owner" cannot have explicit members

データベースを複数のユーザで所有する、というのは PostgreSQL では想定されていないようです。

PUBLIC ロール

定義済みロールとは別に特別なロールとして「PUBLIC ロール」というものもあります。PostgreSQL 文書の GRANT のページに

PUBLICは、全てのロールを常に含む、暗黙的に定義されたグループと考えることができます。

という説明がある通り、PUBLIC ロールはすべてのユーザ対して暗黙的に付与されるロールとなっています。

【 PostgreSQL 16.4文書 - GRANT - 】
https://www.postgresql.jp/document/16/html/sql-grant.html

PUBLIC ロールは、内部的なロールとして扱われるようで、pg_roles を確認しても出力されません。

postgres=# SELECT * FROM pg_roles WHERE rolname = 'PUBLIC';
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid 
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-----
(0 rows)

一応、PUBLIC ロールが存在することは information_schema.table_privileges で存在を確認できます。

postgres=# SELECT DISTINCT grantor, grantee FROM information_schema.table_privileges;
 grantor  |      grantee      
----------+-------------------
 postgres | PUBLIC
 postgres | pg_read_all_stats
 postgres | postgres
(3 rows)

postgres=# SELECT DISTINCT grantor, grantee, privilege_type FROM information_schema.table_privileges WHERE grantee = 'PUBLIC';
 grantor  | grantee | privilege_type 
----------+---------+----------------
 postgres | PUBLIC  | SELECT
 postgres | PUBLIC  | UPDATE
(2 rows)

PUBLIC ロールは、postgres ロールによって、SELECT と UPDATE できる権限を付与してもらっているようです。
※ 非常に細かい話ですが、postgres ロールが PUBLIC ロールに対して権限付与を行なっていることから、「PUBLIC ロールは(postgres を除く)すべてのロールに含まれる」という説明になるのでは、と個人的に考えています。ただ、裏付けできるような情報が見つかっていません(そこまで気にしなくてよいという話でもありますが・・)。

また、当然ではありますが PUBLIC ロールは削除できません。

postgres=# DROP ROLE PUBLIC;
ERROR:  cannot use special role specifier in DROP ROLE

PUBLIC ロールは、すべてのロールに対して影響を持つロールですので、安易に PUBLIC ロールに対して権限付与を行わないのが推奨となります。

postgres ユーザ

特別な、といえば postgres ユーザも特別なユーザで、ブートストラップユーザというもの該当します。
ブートストラップユーザについては、過去に記事にしていますので、そちらを参照してください。

【 INSIGHT 技術ブログ - PostgreSQL のブートストラップ・ユーザについて - 】
https://www.insight-ltd.co.jp/tech_blog/postgresql/734/

また、postgres ユーザも PUBLIC ロールと同じく削除できません。

$ psql -U postgres2 -d postgres -c "DROP ROLE postgres"
ERROR:  cannot drop role postgres because it is required by the database system

ですが、名前の変更は可能です。

-- postgres の名前を postgres2 に変更
$ psql -U postgres2 -d postgres -c "ALTER ROLE postgres RENAME TO postgres_old"
ALTER ROLE
-- 変更した結果を確認
postgres=# SELECT oid, rolname, rolsuper FROM pg_roles WHERE rolname LIKE 'postgres%';
  oid  |   rolname    | rolsuper 
-------+--------------+----------
 16410 | postgres2    | t
    10 | postgres_old | t
(2 rows)

問題なく postgres から postgres2 に変更されていることを確認できました。postgres という名前は変更されても oid = 10 であれば問題なし、という扱いかと思います。
一方、PUBLIC ロールの名前変更はできません。

postgres=# ALTER ROLE PUBLIC RENAME TO PUBLIC_old;
ERROR:  role name "public" is reserved
LINE 1: ALTER ROLE PUBLIC RENAME TO PUBLIC_old;

色々と扱いが微妙に違っていて面白いです。

まとめ

今回は PostgreSQL の定義済みロールと、PUBLIC ロール(と少しだけ postgres ユーザ)について説明しました。
ロールに関する知識は、用意された PostgreSQL を操作しているだけであれば必要とされる場面は少ないと思いますが、エラーなく問題なく PostgreSQL を運用管理するためには必要となりますので、しっかり理解しておくのが推奨となります。

CATEGORY

ARCHIVE

PostgreSQLの
ハイパフォーマンスチューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT