#はじめに
PostgreSQLでpgcryptoを使って暗号化する方法です。
透過的暗号化（TDE）ではなく、INSERTやSELECTに暗号化関数を組み込む方法になります。
この方法では既存のSQLを修正する必要があるため、透過的暗号化を用いたいところですが、PostgreSQL自体は透過的暗号化の機能を持っていません。
透過的暗号化をしたい場合、NECのTDEツール(GPLv3)を使うか、自前で実装しましょう。
自前で実装する方法は @fujii_masao さんの PostgreSQL の poor man's 透過的暗号化 が参考になるかと思います。
（自前で実装する場合も、pgcryptoを使うことになりますが）
#環境
PostgreSQL 9.4.11
#準備
まずは、pgcryptoをDBに導入します。
CREATE EXTENSION PGCRYPTO;
次に、暗号化対象となるテーブルを作成します。
pgcryptoを使って暗号化すると、データがbytea型になるので、暗号化するカラムをbytea型にしておく必要があります。
CREATE TABLE member (id int, name bytea); -- nameが暗号化対象のカラム
#データの暗号化
データの暗号化にはpgp_sym_encrypt(data text, psw text [, options text ]) returns byteaを使います。
dataに暗号化する文字列を指定し、pswに暗号化用のパスワードを指定すればOKです。パスワードは復号時にも使用します。
オプションで暗号アルゴリズムなどが指定できますが、基本的にはデフォルトで問題ありません。
INSERT INTO member VALUES ( 1, pgp_sym_encrypt('佐藤', 'my_pass'));
暗号化したデータを普通にSELECTすると、暗号化されたバイト配列が取得されます。
SELECT id, name FROM member;
| id | name |
|---|---|
| 1 | \xc30d0407030271dd3f52e8cac05e68d23701df040194148ccd404b117b348b0e7baa83f89b49bb743e03b413376652a90db11582cf01b4af29c7cfd283216165d0c729f1254ef3aa |
#データの復号
データの復号にはpgp_sym_decrypt(msg bytea, psw text [, options text ]) returns textを使います。
msgに復号するバイト配列を指定し、pswに暗号化時に使用したパスワードを指定します。
SELECT id, pgp_sym_decrypt(name, 'my_pass') FROM member;
| id | name |
|---|---|
| 1 | 佐藤 |
パスワードが間違っているなど、復号に失敗するとエラーになります。
SELECT id, pgp_sym_decrypt(name, 'wrong_pass') FROM member;
-- ERROR: Wrong key or corrupt data
#パスワードの管理
とりあえず暗号化/復号は出来ましたが、SQLにパスワードを直接埋め込むのは望ましくありません。SQLがエラーとなった場合、ログに表示されてしまう危険性がありますし、パスワード変更時の修正個所も多くなります。パスワードはどこかで一括管理したいですよね。
方法として以下2つが考えられます。（他にもあるかも知れません）
##postgresql.confにパスワードを設定
postgresql.confにパスワード設定用のパラメータを定義し、current_setting関数でそのパラメータを取得します。
# ユーザ定義クラス
custom_variable_classes = 'my_class'
# パスワード用のkey,valueを設定
my_class.password='my_pass'
postgresql.confを編集したら、pg_ctl reloadで有効化します。
暗号化/復号時にcurrent_setting関数を呼び出します。
-- 暗号化して登録
INSERT INTO member VALUES ( 1, pgp_sym_encrypt('佐藤', current_setting('my_class.password')));
-- 復号して参照
SELECT id, pgp_sym_decrypt(name, current_setting('my_class.password')) FROM member;
##PL/pgSQLでパスワードを返す関数を作る
単純に平文のパスワードを返すだけの関数を用意します。
CREATE FUNCTION get_pass() RETURNS text AS $$ SELECT cast ('my_pass' as text); $$ LANGUAGE SQL IMMUTABLE;
この方法だと、関数定義を参照するとパスワードが分かってしまうので、一般ユーザのpg_procへの参照権限をREVOKEする必要があります。
あとは、current_setting関数と同じですね。
-- 暗号化して登録
INSERT INTO member VALUES ( 1, pgp_sym_encrypt('佐藤', get_pass()));
-- 復号して参照
SELECT id, pgp_sym_decrypt(name, get_pass()) FROM member;
#速度について
暗号化をすると、当然SQLの速度は低下します。
暗号化強度や圧縮率に依りますが、デフォルト設定だと以下のような応答時間でした。
| 復号せずに参照 | 復号して参照 |
|---|---|
| 0.5563ms | 13.4605ms |
| ※テストデータ10件に対して、単純に全件取得した際の平均値です。厳密な計測とも言えないので、参考程度に留めてください） |
暗号化強度等は、暗号化関数であるpgp_sym_encryptのオプションで指定できます。
詳しくは公式ドキュメントを参照してください。
#参考