PostgreSQLでpgcryptoを使ったデータ暗号化

#はじめに
PostgreSQLでpgcryptoを使って暗号化する方法です。
**透過的暗号化（TDE）ではなく、INSERTやSELECTに暗号化関数を組み込む方法**になります。
この方法では既存のSQLを修正する必要があるため、透過的暗号化を用いたいところですが、PostgreSQL自体は透過的暗号化の機能を持っていません。
透過的暗号化をしたい場合、[NECのTDEツール(GPLv3)](https://github.com/nec-postgres/tdeforpg/wiki/Manual(JA))を使うか、自前で実装しましょう。
自前で実装する方法は @fujii_masao さんの [PostgreSQL の poor man's 透過的暗号化](https://qiita.com/fujii_masao/items/f2bc564e6b977400b2f5) が参考になるかと思います。
（自前で実装する場合も、pgcryptoを使うことになりますが）

#環境
PostgreSQL 9.4.11

#準備
まずは、pgcryptoをDBに導入します。

``` sql
CREATE EXTENSION PGCRYPTO;
```

次に、暗号化対象となるテーブルを作成します。
pgcryptoを使って暗号化すると、データが`bytea`型になるので、暗号化するカラムを`bytea`型にしておく必要があります。

```sql
CREATE TABLE member (id int, name bytea); -- nameが暗号化対象のカラム
```

#データの暗号化
データの暗号化には`pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea`を使います。
`data`に暗号化する文字列を指定し、`psw`に暗号化用のパスワードを指定すればOKです。パスワードは復号時にも使用します。
オプションで暗号アルゴリズムなどが指定できますが、基本的にはデフォルトで問題ありません。

```sql
INSERT INTO member VALUES ( 1, pgp_sym_encrypt('佐藤', 'my_pass'));
```

暗号化したデータを普通にSELECTすると、暗号化されたバイト配列が取得されます。

```sql
SELECT id, name FROM member;
```

|id|name|
|---|---|
|1|\xc30d0407030271dd3f52e8cac05e68d23701df040194148ccd404b117b348b0e7baa83f89b49bb743e03b413376652a90db11582cf01b4af29c7cfd283216165d0c729f1254ef3aa|

#データの復号
データの復号には`pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text`を使います。
`msg`に復号するバイト配列を指定し、`psw`に暗号化時に使用したパスワードを指定します。

```sql
SELECT id, pgp_sym_decrypt(name, 'my_pass') FROM member;
```

|id|name|
|---|---|
|1|佐藤|

パスワードが間違っているなど、復号に失敗するとエラーになります。

```sql
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関数でそのパラメータを取得します。

```postgresql.conf
# ユーザ定義クラス
custom_variable_classes = 'my_class'
# パスワード用のkey,valueを設定
my_class.password='my_pass'
```

postgresql.confを編集したら、`pg_ctl reload`で有効化します。
暗号化/復号時にcurrent_setting関数を呼び出します。

```sql
-- 暗号化して登録
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でパスワードを返す関数を作る

単純に平文のパスワードを返すだけの関数を用意します。

```sql
CREATE FUNCTION get_pass() RETURNS text AS $$ SELECT cast ('my_pass' as text); $$ LANGUAGE SQL IMMUTABLE;
```

この方法だと、関数定義を参照するとパスワードが分かってしまうので、一般ユーザのpg_procへの参照権限をREVOKEする必要があります。
あとは、current_setting関数と同じですね。

```sql
-- 暗号化して登録
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`のオプションで指定できます。
詳しくは[公式ドキュメント](https://www.postgresql.jp/document/9.4/html/pgcrypto.html)を参照してください。


#参考

- [PostgreSQL 9.4.5文書 - pgcrypto](https://www.postgresql.jp/document/9.4/html/pgcrypto.html)
- [Let's Postgres - pgcrypto](https://lets.postgresql.jp/documents/technical/contrib/pgcrypto)