PostgreSQL 14から参照・更新ユーザーを簡単に作れるようになりました
データベースの利用者は、主に、参照系、更新系、管理系の3種類が存在します。
PostgreSQLにおいて、管理系は SUPERUSER として作成すれば良いものの、残り2つ、特に、参照系ロールは作成が非常に複雑でした。
PostgreSQL 14からは、参照・更新それぞれに特化したロールが事前に定義されるようになり、スッキリと権限管理できるようになりました。
動作を確認してみたので、紹介します。
参照・更新系ロール
14では、参照系の pg_read_all_data と更新系の pg_write_all_data が追加されています。
| Role | Allowed Access |
|---|---|
| pg_read_all_data | Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
| pg_write_all_data | Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
※ 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種類の権限もスッキリ記述できます。
| Role | Attributes | Member of |
|---|---|---|
| 管理者 | Superuser, Create role, Create DB, ... | {} |
| 更新系 | {pg_read_all_data,pg_write_all_data} | |
| 参照系 | {pg_read_all_data} |
AWS RDS/Aurora だと、次の通りです。
| Role | Attributes | Member of |
|---|---|---|
| 管理者 | Create role, Create DB, ... | {rds_superuser} |
| 更新系 | {pg_read_all_data,pg_write_all_data} | |
| 参照系 | {pg_read_all_data} |
参照ユーザーを作成するときは
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にアップグレードの折は、秘伝の権限運用を見直すいい機会かもしれません。
