PostgreSQL 14から参照・更新ユーザーを簡単に作れるようになりました

データベースの利用者は、主に、参照系、更新系、管理系の3種類が存在します。

PostgreSQLにおいて、管理系は SUPERUSER として作成すれば良いものの、残り2つ、特に、参照系ロールは作成が非常に複雑でした。

PostgreSQL 14からは、参照・更新それぞれに特化したロールが事前に定義されるようになり、スッキリと権限管理できるようになりました。

動作を確認してみたので、紹介します。

参照・更新系ロール

14では、参照系の pg_read_all_data と更新系の pg_write_all_data が追加されています。

※ PostgreSQL: Documentation: 14: 22.5. Predefined Roles から引用

定義済みロールを確認します。

postgres=> \duS pg_*
                                              List of roles
         Role name         |  Attributes  |                          Member of
---------------------------+--------------+--------------------------------------------------------------
 pg_database_owner         | Cannot login | {}
 pg_execute_server_program | Cannot login | {}
 pg_monitor                | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_data          | Cannot login | {}
 pg_read_all_settings      | Cannot login | {}
 pg_read_all_stats         | Cannot login | {}
 pg_read_server_files      | Cannot login | {}
 pg_signal_backend         | Cannot login | {}
 pg_stat_scan_tables       | Cannot login | {}
 pg_write_all_data         | Cannot login | {}
 pg_write_server_files     | Cannot login | {}

この新しい定義済みロールを利用すると、3種類の権限もスッキリ記述できます。

AWS RDS/Aurora だと、次の通りです。

参照ユーザーを作成するときは

CREATE ROLE read_only WITH LOGIN PASSWORD 'password';
GRANT pg_read_all_data TO read_only;

というようにすればよいわけです。

やってみた

実際に、参照・更新ロールの動作を確認します。

環境

AWS RDSのパブリックプレビュー版PostgreSQL 14 を利用しました。

postgres=> select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 14rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

事前準備

スーパーユーザーで foo データベースの app スキーマにテーブル a を作成します。

foo => CREATE SCHEMA app;
foo => create table app.a(id serial, name text);
CREATE TABLE
foo=> set search_path = app;
SET
foo=> \d
            List of relations
 Schema |   Name   |   Type   |  Owner
--------+----------+----------+----------
 app    | a        | table    | postgres
 app    | a_id_seq | sequence | postgres
(2 rows)

定義済みロールで3種類のロールを作成

定義済みロールを利用し

• 参照のみ : read_only
• 更新のみ : write_only
• 参照・更新 : read_write

の3種類のロールを作成します。

CREATE ROLE read_only WITH LOGIN PASSWORD 'password';
GRANT pg_read_all_data TO read_only;

CREATE ROLE write_only WITH LOGIN PASSWORD 'password';
GRANT pg_write_all_data TO write_only;

CREATE ROLE read_write WITH LOGIN PASSWORD 'password';
GRANT pg_read_all_data,pg_write_all_data TO read_write;

それぞれのロールのメンバーを確認します。

foo=> \du
                            List of roles
    Role name    |               Attributes               |           Member of
-----------------+----------------------------------------+-----------------------------------------
 postgres        | Create role, Create DB                +| {rds_superuser}
                 | Password valid until infinity          |
...
 read_only       |                                        | {pg_read_all_data}
 write_only      |                                        | {pg_write_all_data}
 read_write      |                                        | {pg_read_all_data,pg_write_all_data}

read_write ユーザー

データを更新するアプリケーション向けの権限です。

pg_write_all_data には INSERT, UPDATE, DELETE 以外の更新権限はありません。

例えば、テーブル作成やスキーマ変更といったDDL系更新操作は行えません。

foo=> \c foo read_write

foo=> create table app.b(id serial, name text);
ERROR:  permission denied for schema app
LINE 1: create table app.b(id serial, name text);

一方で、データの参照・更新は行えます。

foo=> insert into app.a(name) values('a');
INSERT 0 1
foo=> select * from app.a;
 id | name
----+------
  1 | a
(1 row)

foo=> update app.a set name = 'x' where id = 1;
UPDATE 1
foo=> select * from app.a;
 id | name
----+------
  1 | x
(1 row)

write_only ユーザー

参照系の pg_read_all_data ロールがなく、更新系の pg_write_all_data ロールしかあたっていない write_only ユーザーの挙動は興味深いです。

INSERT に成功し、SELECT に失敗するのは、直感通りです。

foo=> insert into app.a(name) values('b');
INSERT 0 1
foo=> select * from app.a;
ERROR:  permission denied for table a

UPDATE 文を走らせてみましょう。

foo=> update app.a set name = 'y' where id = 2;
ERROR:  permission denied for table a

foo=> update app.a set name = 'y';
UPDATE 2

WHERE 句を含むと permission denied になり、省くと成功します。

WHERE 句内で SELECT が発生し、このユーザーは SELECT 権限がないためです。

A WHERE clause requires SELECT rights on the table/columns referenced and if no SELECT rights were granted then a permission denied error is the correct result, yes. Note that pg_write_all_data, as documented, does not include SELECT rights.

PostgreSQL: Re: New predefined roles- 'pg_read/write_all_data'

PostgreSQL独自の仕様だとおもったら、Oracle DatabaseやMySQLも同じだった。データベース何にもわからない。

— Noriyoshi Shinoda (@nori_shinoda) September 6, 2021

実運用では、更新系ユーザーには参照と更新の両方のロールを与えると思われるため、ハマる機会は稀と思いますが、 pg_write_all_data がどこまで許可されているのか、認識しておいても損はないでしょう。

read_only ユーザー

レポーティングシステムやDBダンプ向けの権限です。

read_only ユーザーは SELECT できるだけです。

foo=> \c foo read_only
foo=> select * from app.a;
 id | name
----+------
  1 | y
  2 | y
(2 rows)

PostgreSQL 14 以前でリードオンリーユーザーを作成したい

PostgreSQL 14の定義済み参照ロールを使わずに、リードオンリーユーザーを作成したい場合、以下の記事を参考にしてください。

文字数の多さから、その複雑さが伝わるかと思います。

• Creating a Read-Only Postgres User
• PostgreSQL ユーザーとロールの管理 | Amazon Web Services ブログ
• How do you create a read-only user in PostgreSQL? - Stack Overflow

最後に

データベースエンジンによらず、権限管理には深い理解が必要です。

PostgreSQL 14から参照・更新それぞれの権限をまとめたロールが予め定義されるようになり、権限管理が大幅に簡略化されました。

PostgreSQL 14にアップグレードの折は、秘伝の権限運用を見直すいい機会かもしれません。

参考

• PostgreSQL: Documentation: 14: 22.5. Predefined Roles
• Finally - a system level "read all data" role for PostgreSQL! - CYBERTEC
• PostgreSQL 14がやってくる(6) - pg_read_all_data/pg_write_all_data - Qiita