はじめに
久々の投稿になります。そろそろPG12のbeta版が出ますね。
私事ですが最近、ROLE周りをちょっとだけ真面目に考える機会があったのですが、真面目に考えると意外と（というやっぱり）難しくてしばらく頭を悩ませていました。
そんなとき、 pgsql-announce: pg_permission: Finally a simple overview というのが自分のアンテナに引っかかったので、気分転換がてら少し試してみました。
pg_permissionとは
簡単に言うと、DB内に設定されている権限情報をチェックする機能を提供するEXTENSIONです。
主な機能は以下のとおり
- ROLEのあるべき権限状態を定義しておくと現設定とのdiffを出力
- 全ROLEの権限情報を一覧化したVIEWを提供
詳細はgitのソースである以下に書かれていますのでそちらを参照してください。
https://github.com/cybertec-postgresql/pg_permission
手順、実行結果
1. インストール手順
最初にgithubからソースを取得して、make install します。
※注意：make 実行時、pg_configにPATHが通っている必要があります。
# git clone https://github.com/cybertec-postgresql/pg_permission.git
# cd pg_permission/
# source ~/.bashrc
# make install
あとはROLEの権限状態を確認したい対象のDBで CREATE EXTENSION を実行して、pg_permissionを登録します。
# su - postgres
$ pg_ctl -D ~/10/data start
$ psql campdb -U masteruser
psql (10.5)
Type "help" for help.
campdb=> CREATE EXTENSION pg_permissions ;
CREATE EXTENSION
インストール手順はこれだけです。
2. 実行結果
以下の結果は以下の4つのROLEを追加した環境を、pg_permissionで確認したものです。
- superuserライクなROLE：masteruser
- オブジェクト作成等を行う管理系のROLE：dbadmin
- アプリケーションが利用するテーブル操作を許可したROLE：application
- DBの監視用ROLE：dbcheck
2.1. VIEWの確認
2.1.1. databaseに対する権限
DATABASEへのスキーマ追加等はmasteruserのみに許可されており、他のROLEはCONNECTしか許可されていないのがわかります。
campdb=> SELECT * FROM database_permissions WHERE role_name IN ('masteruser','dbadmin','application','dbcheck');
object_type | role_name | schema_name | object_name | column_name | permission | granted
-------------+-------------+-------------+-------------+-------------+------------+---------
DATABASE | masteruser | | | | CREATE | t
DATABASE | masteruser | | | | CONNECT | t
DATABASE | masteruser | | | | TEMPORARY | t
DATABASE | dbadmin | | | | CREATE | f
DATABASE | dbadmin | | | | CONNECT | t
DATABASE | dbadmin | | | | TEMPORARY | t
DATABASE | application | | | | CREATE | f
DATABASE | application | | | | CONNECT | t
DATABASE | application | | | | TEMPORARY | t
DATABASE | dbcheck | | | | CREATE | f
DATABASE | dbcheck | | | | CONNECT | t
DATABASE | dbcheck | | | | TEMPORARY | t
(12 rows)
2.1.2. schemaに対する権限
masteruserを除き、dbadminのみがオブジェクト作成の権限があるのがわかります。
campdb=> SELECT * FROM schema_permissions WHERE schema_name = 'orenoschema' AND role_name IN ('masteruser','dbadmin','application','dbcheck');
object_type | role_name | schema_name | object_name | column_name | permission | granted
-------------+-------------+-------------+-------------+-------------+------------+---------
SCHEMA | masteruser | orenoschema | | | USAGE | t
SCHEMA | masteruser | orenoschema | | | CREATE | t
SCHEMA | dbadmin | orenoschema | | | USAGE | t
SCHEMA | dbadmin | orenoschema | | | CREATE | t
SCHEMA | application | orenoschema | | | USAGE | t
SCHEMA | application | orenoschema | | | CREATE | f
SCHEMA | dbcheck | orenoschema | | | USAGE | f
SCHEMA | dbcheck | orenoschema | | | CREATE | f
(8 rows)
2.1.3. tableに対する権限
スキーマ内のテーブルに対する権限としてSELECTは全ROLEが持っているが、TRUNCATEはmasteruserしか持っていないことが確認できます。
campdb=> SELECT * FROM table_permissions WHERE schema_name = 'orenoschema' AND role_name IN ('masteruser','dbadmin','application','dbcheck') AND permission = 'SELECT' AND granted = 't';
object_type | role_name | schema_name | object_name | column_name | permission | granted
-------------+-------------+-------------+-------------------+-------------+------------+---------
TABLE | masteruser | orenoschema | permission_target | | SELECT | t
TABLE | dbadmin | orenoschema | permission_target | | SELECT | t
TABLE | application | orenoschema | permission_target | | SELECT | t
TABLE | dbcheck | orenoschema | permission_target | | SELECT | t
(4 rows)
campdb=> SELECT * FROM table_permissions WHERE schema_name = 'orenoschema' AND role_name IN ('masteruser','dbadmin','application','dbcheck') AND permission = 'TRUNCATE' A
ND granted = 't';
object_type | role_name | schema_name | object_name | column_name | permission | granted
-------------+------------+-------------+-------------------+-------------+------------+---------
TABLE | masteruser | orenoschema | permission_target | | TRUNCATE | t
(1 row)
2.2. 期待結果との差分チェック permission_diffs()
期待結果との差分を出力させるには、以下の手順が必要になります。
- 期待結果を
permission_targetテーブルに登録する -
permission_diffs()実行する
2.2.1. 正しく権限付与がされている場合（期待結果と一致する場合）
以下のように期待する結果を permission_target テーブルに登録しておきます。
campdb=> select * from permission_target;
id | role_name | permissions | object_type | schema_name | object_name | column_name
----+-------------+----------------+-------------+-------------+-------------+-------------
1 | application | {USAGE} | SCHEMA | orenoschema | |
2 | dbadmin | {CREATE,USAGE} | SCHEMA | orenoschema | |
3 | masteruser | {CREATE,USAGE} | SCHEMA | orenoschema | |
(3 rows)
期待結果とのdiffを取得します。
campdb=> SELECT * FROM permission_diffs();
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-----------+-------------+-------------+-------------+-------------+------------
(0 rows)
何も出なかったけど、これはちゃんと設定できているということなんだろう。
2.2.2. 不正な権限付与がされている場合（期待結果と一致しない場合）
campdb=> update permission_target SET permissions = '{CREATE,USAGE}' WHERE id = 1;
UPDATE 1
campdb=> select * from permission_target;
id | role_name | permissions | object_type | schema_name | object_name | column_name
----+-------------+----------------+-------------+-------------+-------------+-------------
2 | dbadmin | {CREATE,USAGE} | SCHEMA | orenoschema | |
3 | masteruser | {CREATE,USAGE} | SCHEMA | orenoschema | |
1 | application | {CREATE,USAGE} | SCHEMA | orenoschema | |
(3 rows)
期待結果とのdiffを取得すると、missingがtrue（期待結果と差分あり）としてCREATE権限について出力されています。
campdb=> SELECT * FROM permission_diffs();
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-------------+-------------+-------------+-------------+-------------+------------
t | application | SCHEMA | orenoschema | | | CREATE
(1 row)
おぉ！ちゃんと現在の権限の付与状態との差分として検出できている。
なるほど、これはなかなか便利なのかもしれない。
さいごに
pg_permissionを使用することで、期待した権限が付与されているかをチェックすることが可能であること。また、期待結果を定義していなくても、現在の権限の状態を知るだけであれば、十分に利用できるツールであることも確認できました。
そろそろbeta版も出るし、次はPG12ネタで何か書きたいな～