はじめに
みなさんこんにちは。NTTテクノクロス 原田です（いつもはぬこ＠横浜名義でQiitaに書いてますが・・・）。
これはNTTテクノクロス Advent Calendar 2018の4日目の記事です。今回は初めて会社員としてアドベントカレンダーを書くことになりました。
私は普段はPostgreSQLに関する検証・調査や、社内でのPostgreSQL支援、OSSクラウド基盤トータルサービスあたりに関わってますが、趣味としてもPostgreSQLはちょこちょこ使っています。
今回の記事、弊社の他のアドベントカレンダーの記事と比べると小粒で地味な感じですが、よろしくおねがいします。
お題
今回はPostgreSQLのロールに関するちょっとした話を書いてみます。
DBMSというのは何らかの権限管理機能を持っています。PostgreSQLももちろん権限管理機能を持っています。まあ、こんなこと書いてますが普段、自分の検証用環境だと、ほとんど使わないんですよね（PostgreSQLの新機能の調査やちょっと性能測ったりするときって、だいたいpostgresユーザで済ませたりするし）。なので、備忘の意味で、今日は以下の3題について書いてみることにします。なお、この記事はPostgreSQL 11での動作を元にして書かれています。
- ロール is 何？
- ロールがアクセス可能なデータベースオブジェクトを表示する
- ロールの継承関係を表示する
ロール is 何？
最初にPostgreSQLのロールについて、簡単におさらいしておきます。
ロール(ROLE)は名前のとおり、データベースシステムにおける役割を示す概念です。PostgreSQLでは、従来は「ユーザ」と「グループ」という2つの概念に分かれていましたが、PostgreSQL 8.1以降は「ロール」という概念に統一されました。データベースの「ユーザ」も「ロール」の一部です。すごくざっくり言ってしまうと、PostgreSQLで「ユーザ」というのは「ログイン権限」をもつ「ロール」をさします。
またロールに対しては、
- ロール自身の属性
- データベースオブジェクトに対するアクセス権限
を設定することができます。
ロール自身の属性
ロール自身の属性は多いですが、良く使われるものには以下の属性があります。
- そのロールがログインできるかどうか。
- そのロールが特権ロールかどうか
- そのロールがデータベースを作成できるかどうか
- そのロールが他のロールを作成できるかどうか
ロール自身の属性の詳細については、PostgreSQL文書のDatabase Rolesや、CREATE ROLEやALTER ROLEを参照してください。
また、そのロールでログインしたときのPostgreSQLパラメータのデフォルト値を設定することもできます。例えば、以下は特権ユーザ(postgres)でログインしたときの操作を全てサーバログに出力させる設定をALTER ROLEコマンド設定する例です。1
ALTER ROLE postgres SET log_statement = 'all';
データベースオブジェクトに対するアクセス権限
PostgreSQLのテーブルやシーケンス、自作したSQL関数などは全て「データベースオブジェクト」として扱われます。あるロールがこれらのデータベースオブジェクトに対してアクセス可能かどうかをアクセス権限といいます。
たとえば、あるロールXはテーブルXには参照も更新もできるけど、テーブルYには参照しかできない、テーブルZには参照も更新もできない、といったアクセスの制限をかけることができます。
オブジェクトに対するアクセス権限の設定はGRANTコマンドやREVOKEコマンドを使います（PostgreSQL文書を見ると、GRANTコマンドやREVOKEコマンドで設定可能なデータベースオブジェクトの種類の多さにげんなりしますが）。
ロールの継承
PostgreSQLではロールの能力をGRANTコマンドで別のロールに与えたり、REVOKEコマンドで取り上げたりすることができます。自分で作成したロールの能力を他に与えるような複雑なロール制御をするケースは、あんまりないかもしれませんが、PostgreSQL 9.6から登場した「デフォルトロール」の能力を、自分で作成したロールに付与するケースは結構あると思います。
デフォルトロール
ちょっと脇道に逸れますが、せっかくなのでデフォルトロールについても簡単に書いておきます。
デフォルトロールというのは、PostgreSQL 9.6から導入された機能です。この機能は、近年、PostgreSQL開発コミュニティが取り組んでいる、権限分掌強化の一つです。
デフォルトロールが誕生した背景は
- PostgreSQLの特権ユーザ(postgres)が何でもできてしまう、強力すぎる権限を持つこと
- PostgreSQLの特定の操作のために、postgresユーザでなければならなかった
といった問題がありました。
たとえば、pg_signal_backendというデフォルトロールは、クエリ処理実行中のプロセス（バックエンドプロセス）に対して終了シグナルを送信する（SQL関数を発行する）という能力を持ちますが、PostgreSQL 9.5まではこの操作を行うために、特権ユーザ（postgres）で操作する必要がありました。もちろん、こういった操作がどんなユーザでも可能なのは問題です。しかし、終了シグナルを送信するためだけに強力すぎる特権ユーザでログインして操作させる、というのも問題です。
この問題の解決法として、特定の操作を可能とするロールを最初から用意し、そのロールの能力をもつユーザを作成して、そのユーザで終了シグナルを送信できるようにしました。そのユーザに他の権限を与えなければ、データベース内の情報の不正な参照や更新はできなくなります。このような権限分掌のために開発されたのが、デフォルトロールです。
PostgreSQL 11でサポートされているデフォルトロールはのDefault Rolesを参照してください。（PostgreSQL 10までのデフォルトロールについては、去年書いたQiitaの記事 監視用デフォルトロール でも説明しているので、そちらを参照してください。PostgreSQL 11では、pg_read_server_files, pg_write_server_files, pg_execute_server_programという3つのロールが追加されています）
サンプル
こんな感じの環境を作って試してみることにします（postgres以外のデフォルトロールは省略）。
表にするとこんな感じ。
| ロール名 | 継承元のメンバ | アクセス権限 |
|---|---|---|
| postgres | なし | 特権ロール。すべてのオブジェクトのフルコントロール |
| dbowner | なし | そのデータベース内のオブジェクトのフルコントロール |
| usr_a | なし | table_aに対する読み込みのみ |
| user_a_w | user_a | table_aに対する読み書き |
| usr_b | なし | table_bに対する読み込みのみ |
| user_b_w | user_b | table_bに対する読み書き |
| user_c | user_a user_b |
table_aに対する読み込みのみ table_bに対する読み込みのみ |
この設定のためのDDLはこんな感じ。2
ロールの作成とロール間の関係の定義
-- ロールの作成
CREATE ROLE dbowner CREATEDB LOGIN;
CREATE ROLE user_a LOGIN;
CREATE ROLE user_a_w LOGIN;
CREATE ROLE user_b LOGIN;
CREATE ROLE user_b_w LOGIN;
CREATE ROLE user_c LOGIN;
CREATE ROLE monitor LOGIN;
-- ロール間の関係
GRANT user_a TO user_a_w;
GRANT user_b TO user_b_w;
GRANT user_a TO user_c;
GRANT user_b TO user_c;
GRANT pg_monitor TO monitor;
テーブル定義とテーブルに対するアクセス権限の定義
-- テーブル作成
CREATE TABLE table_a (id int, data text);
CREATE TABLE table_b (id int, data text);
-- GRANT TABLES
GRANT SELECT ON table_a TO user_a;
GRANT INSERT, UPDATE, DELETE ON table_a TO user_a_w;
GRANT SELECT ON table_b TO user_b;
GRANT INSERT, UPDATE, DELETE ON table_a TO user_a_w;
ロールがアクセス可能なデータベースオブジェクトを表示する
たくさんのロールやテーブル等を作成して、細かくデータベースオブジェクトへのアクセス権限を指定したあとに、「はて、正しくアクセス権限を設定できたんだっけ？」と確認したくなることもあるでしょう。
しかし、これを確認するのはちょっと面倒です。
ロールの一覧はpg_rolesというシステムカタログに格納されてますが、そのシステムカタログにはロール自身の属性の情報しかなく、アクセス権限に関する情報はありません。
$ psql -U postgres -c "\d pg_roles"
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | | |
oid | oid | | |
$
アクセス権限に関する情報は、対象となるデータベースオブジェクト（例えばテーブルを管理するpg_classのrelaclや列、を管理するpg_attribute内のattacl列などのaclitem[]型で管理されています。
参考までに、aclitem[]型をもつシステムカタログはこれだけあります。うんざりしますね。
| システムカタログ名 | 内容 |
|---|---|
| pg_attribute | 列の情報 |
| pg_class | 表の情報 |
| pg_database | データベースの情報 |
| pg_default_acl | 新規作成オブジェクトの初期権限 |
| pg_foreign_data_wrapper | 外部データラッパの情報 |
| pg_foreign_server | 外部サーバの情報 |
| pg_init_privs | システム内のオブジェクトの初期権限 |
| pg_largeobject_metadata | ラージオブジェクトメタデータの情報 |
| pg_namespace | 名前空間（スキーマ）の情報 |
| pg_pltemplate | 手続き言語の「テンプレート」に関する情報 |
| pg_proc | SQL関数の情報 |
| pg_tablespace | テーブルスペースの情報 |
| pg_type | 型の情報 |
さて、先にあげたサンプルだと、table_a, table_bの2つのテーブルがありましたが、そこにaclitem[]型のrelaclという列が存在します。内容を見るとこんな感じ。
test=# SELECT relname, relacl FROM pg_class c JOIN pg_namespace ns ON (ns.oid = c.relnamespace) WHERE ns.nspname = 'public';
relname | relacl
---------+-----------------------------------------------------------------
table_a | {dbowner=arwdDxt/dbowner,user_a=r/dbowner,user_a_w=awd/dbowner}
table_b | {dbowner=arwdDxt/dbowner,user_b=r/dbowner}
(2 rows)
relacl列のところに謎の文字列が設定されていますね。これがロール毎のアクセス制限内容になります。1つ1つの設定はカンマで区切られ、以下のような形式で記述されています。
ロール名=アクセス制御文字列/所有者のロール名
例えば、table_aに対するuser_aロールのアクセス制御文字列は r となっています。
さて、これを見ると以下の2点の疑問が。
- 特権ロールpostgresロールや、データベース所有者のdbownerロールはフルアクセスできるはずなのに、ここに現れないのは何故？
- user_c の情報は表示されないの？
- user_aとuser_bの能力は継承されている。
どうやら、relacl列に表示されるのは、明示的にGRANTで指定された権限情報のみのようですね。
postgresやデータベースオーナであるdbownerはともかく、user_cロールのアクセス情報はどこを見ればわかるのやら。
アクセス権限照会関数
幸いにして、PostgreSQLにはAccess Privilege Inquiry Functions（アクセス権限照会関数）という関数群が用意されているので、その関数を使ってどのロールがどういった権限をもっているのか確認することができます。アクセス権限照会関数の種類はとても多いのですが、今回はその関数のうち、has_table_privilege(user, table, privilege)というSQL関数を使ってどのロールがテーブルに対して権限を持つか表示してみます。
この関数の第1引数にはロールのoid、第2引数にはテーブルのoid、第3引数には、確認したい権限を指定します（この権限の指定方式についてPostgreSQL文書には細かく書いていないのがツラい）。
例えば、user_aロール(oid=16398)がtable_aテーブル(oid=16417)に対して、SELECT権限があるか、またUPDATE権限があるかを確認する例を以下に示します。
test=# SELECT has_table_privilege(16398, 16417, 'SELECT');
has_table_privilege
---------------------
t
(1 row)
test=# SELECT has_table_privilege(16398, 16417, 'UPDATE');
has_table_privilege
---------------------
f
(1 row)
あとはこれを応用してSQLを書いてみます。
SELECT r.oid, r.rolname, c.relname,
has_table_privilege(r.oid, c.oid, 'SELECT') AS "SELECT",
has_table_privilege(r.oid, c.oid, 'INSERT') AS "INSERT",
has_table_privilege(r.oid, c.oid, 'UPDATE') AS "UPDATE",
has_table_privilege(r.oid, c.oid, 'DELETE') AS "DELETE"
FROM pg_roles r, pg_class c
WHERE c.relkind = 'r'
AND r.rolcanlogin = true
AND relnamespace IN
(SELECT oid
FROM pg_namespace
WHERE nspname NOT IN ('pg_catalog','information_schema','pg_toast'))
ORDER BY r.rolname, c.relname
;
WHERE句は以下のように設定しています。
- pg_classの種別は'r'に設定してテーブルのみ対象にする。
- ログイン可能なロールのみ指定
- システムカタログ等を含まないようにする('pg_catalog','information_schema','pg_toast'を除外)
そして、そのクエリを実行するとこんな感じで、ロール名/テーブル名に対して、SELECT/INSERT/UPDATE/DELETEの権限があるかを表示します。
$ psql -U postgres test -f table_acl.sql
oid | rolname | relname | SELECT | INSERT | UPDATE | DELETE
-------+----------+---------+--------+--------+--------+--------
16397 | dbowner | table_a | t | t | t | t
16397 | dbowner | table_b | t | t | t | t
16403 | monitor | table_a | f | f | f | f
16403 | monitor | table_b | f | f | f | f
10 | postgres | table_a | t | t | t | t
10 | postgres | table_b | t | t | t | t
16398 | user_a | table_a | t | f | f | f
16398 | user_a | table_b | f | f | f | f
16399 | user_a_w | table_a | t | t | t | t
16399 | user_a_w | table_b | f | f | f | f
16400 | user_b | table_a | f | f | f | f
16400 | user_b | table_b | t | f | f | f
16401 | user_b_w | table_a | f | f | f | f
16401 | user_b_w | table_b | t | f | f | f
16402 | user_c | table_a | t | f | f | f
16402 | user_c | table_b | t | f | f | f
(16 rows)
おまけ：CRUDぽくしてみる
もうちょっと凝ると、WHEN CASEやGROUP BYを使ったクエリの結果をpsqlの\crosstabviewメタコマンドを使って、行をロール名、列をテーブル名に展開して、CRUD(C:INSERT, R:SELECT, U:UPDATE, D:DELETE)を表示させることもできます。
$ cat table_acl_v2.sql
--
-- table_acl_v2.sql (Linux/psql only)
--
\o /dev/null
SELECT rolname, relname, crud
FROM (SELECT r.oid, r.rolname, c.relname,
((CASE WHEN has_table_privilege(r.oid, c.oid, 'INSERT') THEN 'C' ELSE ' ' END) ||
(CASE WHEN has_table_privilege(r.oid, c.oid, 'SELECT') THEN 'R' ELSE ' ' END) ||
(CASE WHEN has_table_privilege(r.oid, c.oid, 'UPDATE') THEN 'U' ELSE ' ' END) ||
(CASE WHEN has_table_privilege(r.oid, c.oid, 'DELETE') THEN 'D' ELSE ' ' END)) AS crud
FROM pg_roles r, pg_class c
WHERE c.relkind = 'r'
AND r.rolcanlogin = true
AND relnamespace IN
(SELECT oid
FROM pg_namespace
WHERE nspname NOT IN ('pg_catalog','information_schema','pg_toast'))
) t
ORDER BY rolname, relname
;
\o
\crosstabview rolname relname
で、これを実行すると、各ロール毎のCRUDっぽい表示が出せます。
$ psql -U postgres test -f table_acl_v2.sql
rolname | table_a | table_b
----------+---------+---------
dbowner | CRUD | CRUD
monitor | |
postgres | CRUD | CRUD
user_a | R |
user_a_w | CRUD |
user_b | | R
user_b_w | | R
user_c | R | R
(8 rows)
どやぁ。
ロールの継承関係を表示する
PostgreSQLではロール間の継承の自由度は高いです。
一つのロールを複数のロールに継承させることもできるし、複数のロールを1つのロールに継承させることもできます。唯一のルールは循環させてはならない、ということだけですね。なので滅多矢鱈にロール間の継承を設定すると、わけがわからんことになりがちです。
なのでロールの継承関係を表示するSQLを書いてみました。
ロールの継承関係はpg_auth_membersというシステムカタログで管理されています。先ほど示したサンプルの図の場合、こんな感じでpg_auth_membersシステムカタログに登録されています。
postgres=# TABLE pg_auth_members;
roleid | member | grantor | admin_option
--------+--------+---------+--------------
3374 | 3373 | 10 | f
3375 | 3373 | 10 | f
3377 | 3373 | 10 | f
16398 | 16399 | 10 | f
16400 | 16401 | 10 | f
16398 | 16402 | 10 | f
16400 | 16402 | 10 | f
3373 | 16403 | 10 | f
(8 rows)
roleidやmemberに表示されているのは、そのロールのoidです。なのでこれを見やすくするためには、pg_rolesシステムビューと結合します。また、ロールの継承は何度でも可能なので、再帰的に処理する必要があります。
はい、みんな大好き(私は苦手)CTE：Common Table Expressions＋recursiveの登場です。
こんな感じのクエリを書くと
WITH RECURSIVE t AS (
SELECT oid as roleid, 0 as level, oid::text as sortkey
FROM pg_roles
WHERE rolname IN (SELECT rolname FROM pg_roles)
UNION ALL
SELECT member as roleid, t.level + 1 , ((sortkey || '.' || pam.member)::text) AS sortkey
FROM pg_auth_members pam JOIN t ON (pam.roleid= t.roleid)
) SELECT (repeat(' ', level) || pr.rolname) as rolename
FROM t JOIN pg_roles pr ON (t.roleid = pr.oid)
ORDER BY sortkey ;
こんな感じでロール間の関係を表示してくれます。継承したロールは空白でインデントをつけてそれっぽく見せています。
$ psql -U postgres postgres -f roles.sql
rolename
---------------------------
postgres
monitor_user
dbowner
user_a
user_a_w
user_c
user_a_w
user_b
user_b_w
user_c
user_b_w
user_c
monitor
pg_monitor
monitor
pg_read_all_settings
pg_monitor
monitor
pg_read_all_stats
pg_monitor
monitor
pg_stat_scan_tables
pg_monitor
monitor
pg_signal_backend
pg_read_server_files
pg_write_server_files
pg_execute_server_program
(28 rows)
このクエリの改善の余地はいろいろありそうだけど、とりあえずこんな感じで。
おわりに
久々にきちんとロールやアクセス権限制御を使ってみました。たまに使わないとすぐに忘れちゃうからなあ。
あと、今回記事を書くときにしばしば「ロール」を「ロース」とtypoしてしまったので、ロースかつを食べに行かねばという気持ちになっています。