PostgreSQLで「pgcrypto」拡張機能を有効にすると、PostgreSQLに保存するデータを暗号化出来るようになります。
INSERTする際、共通鍵暗号はpgp_sym_encrypt関数、公開鍵暗号はpgp_pub_encrypt関数を使って、カラムデータ単位で暗号化します。
他のRDBにこれらの関数は無いのでSQLにPostgreSQLの方言が入ることになりますが、SQL標準仕様（SQL99）に暗号化仕様は無いので、どのRDBMSを使った暗号化でも結局は方言になります。致し方ないですね。
今回はPostgreSQL11にpgcryptoを入れ、AES256で共通鍵暗号化したデータをINSERT、復号してSELECTしてみます。
目次
検証環境
- CentOS 7.6
- PostgreSQL 11.4
以下の記事でPostgreSQL11.4のインストール、ユーザ作成を行っている状態です。
PostgreSQLのcontribパッケージをインストール
pgcryptoが内包されるcontribパッケージがOSに入っているか確認します。
[root@spock ~]# rpm -qa | grep postgres
postgresql11-11.4-1PGDG.rhel7.x86_64
postgresql11-libs-11.4-1PGDG.rhel7.x86_64
postgresql11-server-11.4-1PGDG.rhel7.x86_64入っていないのでインストールします。
[root@spock ~]# yum install postgresql11-contrib
(snip)
Running transaction
インストール中 : postgresql11-contrib-11.4-1PGDG.rhel7.x86_64 1/1
検証中 : postgresql11-contrib-11.4-1PGDG.rhel7.x86_64 1/1
インストール:
postgresql11-contrib.x86_64 0:11.4-1PGDG.rhel7
postgresql11-contribが入りました。インストール完了です。
pgcryptoを有効化
PostgreSQLの拡張パッケージは有効化しないと使えません。
postgresユーザで有効化します。
現在有効かどうか確認するにはpsqlから\dxを実行します。
[root@spock ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。
// 拡張パッケージを確認
postgres=# \dx
インストール済みの拡張一覧
名前 | バージョン | スキーマ | 説明
---------+------------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 行)
// pgcrypto拡張を有効化！
postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# \dx
インストール済みの拡張一覧
名前 | バージョン | スキーマ | 説明
----------+------------+------------+------------------------------
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 行)pgcrypto 1.3がpublicスキーマで使えるようになりました。
試し打ちしてみる
共通鍵で暗号化するにはpgp_sym_encrypt関数を使います。詳細は公式サイトを参照。
“暗号化する文字列”という文字列を、
- AES256アルゴリズムで、
- “password”というパスワードを使い、
- zip圧縮しながら
暗号化してみます。
postgres=# select pgp_sym_encrypt('暗号化する文字列', 'password', 'compress-algo=1, cipher-algo=aes256');
pgp_sym_encrypt
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
\xc30d040903021e01465cfe5516d265d251017fa6946f593416046162b481271ee3302448810b8cf6d41adf5ffdc84b4a54c791ce934c01a14d3cd95115e1b7dffc066d2ba
26ea4dc696c5e8dfeff961cee5fe5bbff1bfeb485f8c3fdc7a12eb03072
(1 行)正常に暗号化されているのか、復号して確かめます。
共通鍵暗号の復号にはpgp_sym_decrypt関数を使用します。
postgres=# select pgp_sym_decrypt(pgp_sym_encrypt('暗号化する文字列', 'password', 'compress-algo=1, cipher-algo=aes256'), 'password');
pgp_sym_decrypt
------------------
暗号化する文字列
(1 行)“暗号化する文字列”に戻りました。大丈夫そうです。
テーブルに保存する
PostgreSQLのBLOB型はbytea型です。
postgres=# create table crypt_test(id serial, crypted bytea);
CREATE TABLEcryptedカラム値を暗号化しながら2レコード追加。
postgres=# insert into crypt_test(crypted) values (pgp_sym_encrypt('暗号化する文字列１', 'password'));
INSERT 0 1
postgres=# insert into crypt_test(crypted) values (pgp_sym_encrypt('暗号化する文字列１', 'password'));
INSERT 0 1
postgres=# select * from crypt_test;
id | crypted
----+---------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
1 | \xc30d04070302048da71fd7fb1ffb65d24c01e01b1c1268f8329757ee270b603037b8651de687c104312903531cd9f3bd01c950279a53926a5c6107b679a76c202131
e9fcd4cc94b8676b11e3febb970fd32417bfd519670648e3ebe3b4
2 | \xc30d0407030206d9bb3ea974627963d24c01d9ccab4142eff516ba99aaa054e720adbfe593161c80c3ac4247d55f0cfa3296c00c2236b82a4e6b603fd0c30ba5013d
7a7f8e01b037cced1fd779094982f5c7000844194487629f9ac203
(2 行)復号、出来ましたね。
postgres=# select id, pgp_sym_decrypt(crypted, 'password') from crypt_test where id = 1;
id | pgp_sym_decrypt
----+--------------------
1 | 暗号化する文字列１
(1 行)
postgres=# select id, pgp_sym_decrypt(crypted, 'password') from crypt_test where id = 2;
id | pgp_sym_decrypt
----+--------------------
2 | 暗号化する文字列１
(1 行)同じデータを同じアルゴリズムで暗号化してるのにどうして結果が違うの？
先程、”暗号化する文字列１”という文字列を2件暗号化してINSERTしましたが暗号化した結果が異なっていました。
1 | \xc30d04070302048da71fd7fb1ffb65d24c01e01b1c1268f8329757ee270b603037b8651de687c104312903531cd9f3bd01c950279a53926a5c6107b679a76c202131
e9fcd4cc94b8676b11e3febb970fd32417bfd519670648e3ebe3b4
2 | \xc30d0407030206d9bb3ea974627963d24c01d9ccab4142eff516ba99aaa054e720adbfe593161c80c3ac4247d55f0cfa3296c00c2236b82a4e6b603fd0c30ba5013d
7a7f8e01b037cced1fd779094982f5c7000844194487629f9ac203同じアルゴリズム、同じパスワードなのに何故なんでしょうか。
AESはCBCというモードで暗号化します。CBCは暗号化するデータを固定長ブロックに分け、AES暗号した結果を次のブロックとXORし、XOR結果をAES暗号し・・・を繰り返していきます。
となると、一番最初のブロックは前の暗号化結果が無いのでXORが取れず暗号化出来ません。そこでダミーの先頭データを提供する「IV（Initial Vector）」の登場です。
IVをランダムにすることで最初のXOR値が毎回変わります。この結果この後のXOR値も全て異なることになり、毎回同じデータを同じアルゴリズムで暗号化しても違う結果になる、という仕組みになっています。
IVが毎回同じ値であれば暗号結果も毎回同じになります。pgcryptoは自動でランダムな値を設定してくれますが、自分でIVを設定するようなライブラリの場合はランダムにしないとIVの恩恵が受けられないので毎回変えるようにしましょう。
SQLでパスワード丸見えなんだけど
共通鍵暗号はパスワードがバレたら終わりなのでSQL直書きはNGですね。パスワードを変える時も大変になります。
PostgreSQLの前段にいるJavaやPython等のプログラムからうまいこと渡すか、postgresql.confで外部パラメータとして外出しにするのが一般的かと思います。
/var/lib/pgsql/11/data/postgresql.confの末尾にパスワードを追加して外出しにしてみます。
(snip)
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
# 追加
aes.password='password'rootユーザでpostgresql.conf設定ファイルをポスグレに再読み込みさせます。
# systemctl reload postgresql-11postgresql.confに設定したキーバリュー値はcurrent_setting()関数で持ってこれます。
postgres=# select current_setting('aes.password')
;
current_setting
-----------------
password
(1 行)後はこれをSQLに流用します。
postgres=# select pgp_sym_decrypt(pgp_sym_encrypt('暗号化する文字列', current_setting('aes.password'), 'compress-algo=1, cipher-algo=aes256'), current_setting('aes.password'));
pgp_sym_decrypt
------------------
暗号化する文字列
(1 行)SQLからパスワードが「password」であることは分からなくなりました。
まとめ
PostgreSQLに暗号化されたデータを保存、復号して表示出来るようになりました。
ただ共通鍵よりも公開鍵の方が鍵を物理的に分散出来、利用シーンも多めだと思いますのでまた次回公開鍵暗号、アプリからの連携をやっていきたいと思います。
