本番環境のシステム運用を考えたとき、データベースの中身を参照せざるを得ないことは多くあります。

データは貴重な情報資産であるため、運用者に対してすら、そのデータ参照を厳しく制限せねばなりません。例えば要配慮個人情報を運用者に対して簡単に参照できるようにしてしまうと、情報漏洩のリスクが上がるとともに、運用者もリスクに晒してしまいます。

一方で、問題が発生した時に迅速に対応できることは、システム運用において極めて重要です。必要な時にはデータを迅速に参照できるようにしていないと、サービスレベルは下がり、ユーザに不便を与えてしまいます。

データの参照という点において焦点を合わせると、以下のようなことを実現したく、その方法を検討してみました。

機密性の高い情報は、簡単には参照させたくない
機密性の低い情報は、迅速に参照させたい

具体例
権限設定
検証する
- 環境
- 実検証
  - 単純なカラム参照
  - JOINを含む検証
まとめ

具体例

例えば、以下のような親子を表現するテーブル群があったとします。

この中のnameカラムの情報は個人情報ですから、管理者の承認無しには運用者が参照できないようにしたい。一方で、システム運用上、他のデータにはアクセスを許容したい。このようなケースでどう対応すべきか。

運用者に対し、これらのテーブルに対する権限を持たせないというのが簡単な方法です。しかし、その場合は親子関係に関するエラーや問い合わせがあった時、トラブルシュートのアジリティを下げそうです。この中では、nameの参照を避けたいわけなので、そのカラム情報に対する権限さえ持たせなければ良いのではないでしょうか。

PostgreSQLはカラムレベルでの権限設定が可能です。これを試してみましょう。

The GRANT command has two basic variants: one that grants privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, large object, configuration parameter, schema, tablespace, or type), and one that grants membership in a role.

PostgreSQL: Documentation: 15: GRANT

権限設定

operatorというDBユーザとその権限を以下のように定義してみました。「スキーマは以下の全シーケンスに対する権限を付与」までは、Postgresqlに対するアプリケーション用DBユーザーの作成と権限を参照ください。今回の焦点は、その後のREVOKE、GRANTです。

CREATE USER operator NOINHERIT NOBYPASSRLS PASSWORD 'postgres';
ALTER USER operator SET search_path TO my_schema;

-- スキーマを利用する権限を付与
GRANT USAGE ON SCHEMA my_schema TO operator;
-- スキーマ配下の全テーブルに対する権限を付与
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_schema TO operator;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL PRIVILEGES ON TABLES TO operator;

-- スキーマ配下の全シーケンスに対する権限を付与
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA my_schema TO operator;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL PRIVILEGES ON SEQUENCES TO operator;

-- parents テーブルは氏名 (name) 以外は参照等可能
REVOKE ALL PRIVILEGES ON TABLE parents FROM operator;
GRANT ALL PRIVILEGES (id) ON TABLE parents TO operator;

-- children テーブルも氏名 (name) 以外は参照等可能
REVOKE ALL PRIVILEGES ON TABLE parents FROM operator;
GRANT ALL PRIVILEGES (id, father_id, mother_id) ON TABLE parents TO operator;

PostgreSQLの権限モデルでは、例えカラムに対する権限を持っていなくても、そのカラムを持つ「テーブルに対する権限」を持っていれば参照等が可能です。従って、機密情報を持つテーブルレベルの権限をまずREVOKEし、その後で「当該テーブル上で機密レベルの低いデータを持つカラム」の権限をGRANTする、というやや面倒な方法になりそうです。

Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.

PostgreSQL: Documentation: 15: GRANT

検証する

それでは実際に検証してみます。

環境

$ psql --version
psql (PostgreSQL) 15.3 (Homebrew)
$ psql -h localhost -U operator -d kiririmode
kiririmode=> select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 行)

実検証

まずはoperatorユーザでPostgreSQLにログインし、parentsテーブルを参照してみます。

単純なカラム参照

kiririmode=> select * from children;
ERROR:  permission denied for table children

これは想定通り、権限エラーになりました。では、name以外のカラムのみ抽出してみます。

kiririmode=> select id, father_id, mother_id from children;
 id | father_id | mother_id
----+-----------+-----------
  1 |         1 |         2
(1 行)

このように、name以外の参照は可能になっています。素晴らしい。

JOINを含む検証

nameにさえアクセスしなければ、当然JOINも可能です。

kiririmode=> select c.id, f.id, m.id from children c
kiririmode->   inner join parents f on (c.father_id = f.id)
kiririmode->   inner join parents m on (c.mother_id = m.id);
 id | id | id
----+----+----
  1 |  1 |  2

一方で、nameにアクセスすると、想定通り権限エラーになりますね。

kiririmode=> select c.id, f.id, m.id from children c
  inner join parents f on (c.father_id = f.id)
  inner join parents m on (c.mother_id = m.id)
  where c.name = 'hoge';
ERROR:  permission denied for table children