PostgreSQL の poor man's 透過的暗号化

透過的暗号化テーブルの作成

-- 暗号化モジュールpgcryptoの登録
CREATE EXTENSION pgcrypto;

-- 暗号化したユーザデータを格納するテーブルを作成
CREATE TABLE users_internal
  (id BIGINT PRIMARY KEY, name BYTEA, addr BYTEA);

-- ユーザデータのテーブルにアクセスするためのビューを作成
-- ユーザ名nameと住所addrの各カラムを暗号化
-- 暗号化キーにはapplication_nameの設定値を使用
CREATE OR REPLACE VIEW users AS
  SELECT
    id,
	pgp_sym_decrypt(name, current_setting('application_name')) "name",
	pgp_sym_decrypt(addr, current_setting('application_name')) addr
  FROM
    users_internal;

-- ビューに対する操作をテーブルに対する操作に変換するトリガ関数の作成
CREATE OR REPLACE FUNCTION users_func() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO users_internal VALUES(
	  NEW.id,
	  pgp_sym_encrypt(NEW.name, current_setting('application_name')),
	  pgp_sym_encrypt(NEW.addr, current_setting('application_name')));
	RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE users_internal SET
	  name = pgp_sym_encrypt(NEW.name, current_setting('application_name')),
      addr = pgp_sym_encrypt(NEW.addr, current_setting('application_name'))
    WHERE id = OLD.id;
	RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM users_internal WHERE id = OLD.id;
	RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- トリガをビューに設定
CREATE TRIGGER users_trigger
  INSTEAD OF INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE PROCEDURE users_func();

透過的暗号化の確認

-- 暗号化キーをmykeyに設定
SET application_name TO 'mykey';

-- ビューに対してユーザデータを挿入・更新・削除
INSERT INTO users VALUES (1, '佐藤太郎', '東京都江東区');
INSERT INTO users VALUES (2, '鈴木花子', '京都府京都市左京区');
INSERT INTO users VALUES (3, '田中二郎', '愛知県名古屋市中区');
UPDATE users SET addr = '東京都港区' WHERE id = 1;
DELETE FROM users WHERE id = 3;

-- 期待どおりの検索結果となることを確認
SELECT * FROM users ORDER BY id;
 id |   name   |        addr        
----+----------+--------------------
  1 | 佐藤太郎 | 東京都港区
  2 | 鈴木花子 | 京都府京都市左京区
(2 rows)

-- 暗号化キーが正しくないと、ユーザデータは適切に復号化されないことを確認
SET application_name TO 'yourkey';
SELECT * FROM users ORDER BY id;
ERROR:  Wrong key or corrupt data

application_nameを暗号化キーに使う利点・課題

application_nameは、PostgreSQLのクライアントツールやライブラリからPostgreSQLサーバに透過的に値を渡す仕組み(JDBCドライバのApplicationName接続パラメータやPGAPPNAME環境変数など)が既に用意されている。このため、特別なSQLやコマンドの実行なく暗号化キーをクライアントからサーバに指定でき、クライアントアプリケーションを透過的暗号化のために改修する必要がない。
ただし、application_nameはpg_stat_activityから容易に漏洩するため対策が必要となる。

暗号化キー漏洩対策

## 対策のためにpg_cheat_funcsをインストール
$ cd /tmp
$ wget --no-check-certificate https://github.com/MasaoFujii/pg_cheat_funcs/archive/master.zip
$ unzip master
$ rm -f master
$ cd pg_cheat_funcs-master
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql-X.Y.Z/bin/pg_config
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql-X.Y.Z/bin/pg_config install

## 漏洩対策のためのパラメータ設定
$ emacs $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_cheat_funcs'
pg_cheat_funcs.hide_appname = on

## 設定変更を有効化するためPostgreSQL再起動
$ pg_ctl -D $PGDATA restart

pg_cheat_funcsにより、application_nameの値(暗号化キー)はpg_cheat_funcs.hidden_appnameにコピーされ、application_name自体は空文字にリセット。hidden_appnameを参照できるのはそのセッション自身のみのため、他セッションに暗号化キーは漏洩しない。また、application_nameは空文字のため、pg_stat_activityから暗号化キーが漏洩することもない。
pg_cheat_funcs.hidden_appnameの暗号化キーを使うように、透過的暗号化テーブルの定義を改修する。

CREATE OR REPLACE VIEW users AS
  SELECT
    id,
	pgp_sym_decrypt(name, current_setting('pg_cheat_funcs.hidden_appname')) "name",
	pgp_sym_decrypt(addr, current_setting('pg_cheat_funcs.hidden_appname')) addr
  FROM
    users_internal;

CREATE OR REPLACE FUNCTION users_func() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO users_internal VALUES(
	  NEW.id,
	  pgp_sym_encrypt(NEW.name, current_setting('pg_cheat_funcs.hidden_appname')),
	  pgp_sym_encrypt(NEW.addr, current_setting('pg_cheat_funcs.hidden_appname')));
	RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE users_internal SET
	  name = pgp_sym_encrypt(NEW.name, current_setting('pg_cheat_funcs.hidden_appname')),
      addr = pgp_sym_encrypt(NEW.addr, current_setting('pg_cheat_funcs.hidden_appname'))
    WHERE id = OLD.id;
	RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM users_internal WHERE id = OLD.id;
	RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

暗号化キーが漏洩していないことの確認

## 漏洩対策後は、暗号化キーは接続時にapplication_nameに設定されていなければならない
## 接続後のSETコマンドによる指定はNG
$ psql -d "application_name=mykey"

-- 漏洩対策後は、application_nameは空文字にリセット
SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid();
 application_name 
------------------
 
(1 row)

-- 漏洩対策後も透過的暗号化テーブルは利用可能
SELECT * FROM users ORDER BY id;
 id |   name   |        addr        
----+----------+--------------------
  1 | 佐藤太郎 | 東京都港区
  2 | 鈴木花子 | 京都府京都市左京区
(2 rows)

スーパーユーザによる漏洩対策無効化への対策

スーパーユーザは、ALTER SYSTEM SETやCOPY PROGRAMコマンドにより、shared_preload_lilbrariesやpg_cheat_funcs.hide_appnameを設定変更して漏洩対策を無効化し、暗号化キーを入手できる可能性がある。
スーパーユーザにも暗号化データを参照させたくない場合は、pg_disallow_utilityでALTER SYSTEM SETとCOPY PROGRAMを実行NGに設定する。

$ cd /tmp
$ wget --no-check-certificate https://github.com/MasaoFujii/pg_disallow_utility/archive/master.zip
$ unzip master
$ rm -f master
$ cd pg_disallow_utility-master
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql-X.Y.Z/bin/pg_config
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql-X.Y.Z/bin/pg_config install

$ emacs $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_cheat_funcs, pg_disallow_utility'
pg_disallow_utility.alter_system = on
pg_disallow_utility.copy_program = on

$ pg_ctl -D $PGDATA restart