共通鍵暗号で暗号化されたデータはパスワードが漏洩すると復号される危険が高まるのに対し、公開鍵暗号で暗号化されたデータは秘密鍵とパスワードの二つが漏洩しないと復号できません。
APサーバとDBサーバ通信のような2ホスト間にそれぞれ公開鍵と秘密鍵を分散させることで、悪意のある第三者が復号を試みた際のクラック難度向上が期待できます。
pgcrypto推奨の「GnuPG（Gnu Privacy Guard）」を使って、
- 鍵ペア（公開鍵と秘密鍵）を作成
- 鍵ペアをファイルにエクスポート
- PostgreSQL + pgcrypto拡張で鍵ファイルを使用
- 公開鍵で暗号化したデータを秘密鍵で復号
- Java JDBC（秘密鍵保持）、PostgreSQL（公開鍵保持）でデータのやり取り
の流れで検証、簡単なJDBCプログラムで接続出来る構成になるように進めて行きます。
作業環境
- CentOS 7.6
- PostgreSQL 11.4
pgcryptoのインストールと有効化は共通鍵暗号の回で終わらせている状態です。
GnuPGはデフォルトインストール済みの/usr/bin/gpgを使用。
$ gpg --version
gpg (GnuPG) 2.0.22事前準備
GnuPGで鍵ペアを作成
公式ドキュメントの「F.25.3.9. GnuPGを使用したキーの生成」を参照しながらやっていきます。
（＊）留意点としてGnuPG（gpgコマンド）はsu -したパスワード無しユーザでの鍵ペア生成を許してくれない為、シェルログインパスワードをデフォルトで持たないpostgresユーザでは簡単には鍵ペアを作れません。
かといってpostgresユーザでダイレクトにシェルが取れる状態にはしたくないので、他の一般ユーザで鍵ペアを生成します。
以下記事で作ったOS、Postgresユーザdevelで以降の作業を行います。
「gpg –gen-key」コマンドで鍵ペアを生成。質問は全てデフォルトで回答。
$ whoami
devel
$ gpg --gen-key
gpg (GnuPG) 2.0.22; Copyright (C) 2013 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
gpg: ディレクトリ「/home/devel/.gnupg」が作成されました
gpg: 新しいコンフィグレーション・ファイル「/home/devel/.gnupg/gpg.conf」ができました
gpg: *警告*: 「/home/devel/.gnupg/gpg.conf」のオプションはこの実行では、まだ有効になりません
gpg: 鍵リング「/home/devel/.gnupg/secring.gpg」ができました
gpg: 鍵リング「/home/devel/.gnupg/pubring.gpg」ができました
ご希望の鍵の種類を選択してください:
(1) RSA と RSA (デフォルト)
(2) DSA と Elgamal
(3) DSA (署名のみ)
(4) RSA (署名のみ)
あなたの選択は? 1
RSA 鍵は 1024 から 4096 ビットの長さで可能です。
鍵長は? (2048) 2048
要求された鍵長は2048ビット
鍵の有効期限を指定してください。
0 = 鍵は無期限
<n> = 鍵は n 日間で期限切れ
<n>w = 鍵は n 週間で期限切れ
<n>m = 鍵は n か月間で期限切れ
<n>y = 鍵は n 年間で期限切れ
鍵の有効期間は? (0)0
(null)は無期限です
これで正しいですか? (y/N) y
GnuPGはあなたの鍵を識別するためにユーザIDを構成する必要があります。
本名: devel
電子メール・アドレス: devel@localhost
コメント:
次のユーザIDを選択しました:
"devel <devel@localhost>"
名前(N)、コメント(C)、電子メール(E)の変更、またはOK(O)か終了(Q)?OK(O)を選択するとcursesで作られたレトロ感溢れるパスワード選択画面に入ります。
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
x パスフレーズを入力 x
x x
x x
x パスフレーズ ________________________________________
x x
x <OK> <Cancel> x
mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqjここで入力したパスフレーズは、暗号データを復号する際の秘密鍵ピンコードになります。本番では複雑なものにしておきますがここでは簡単の為「devel」にしておきます。
（＊）パスワードを考えるのが面倒な場合はmkpasswdコマンドを使えば良い感じのパスフレーズを作ってくれます。
# mkpasswd -l 15 -d 3 -C 5
2s$jBR8Dx9CMvbcgpgキーストア（gpg用語では「keyring」）に作成された公開鍵と秘密鍵を確認してみます。
まだ一回しかgpg –gen-keyをしていないので、それぞれ一つの鍵しかキーリングに入っていない状態です。
// 公開鍵の一覧を表示
$ gpg -k
/home/devel/.gnupg/pubring.gpg
------------------------------
pub 2048R/092CFCF5 2019-09-26
uid devel <devel@localhost>
sub 2048R/59F6142A 2019-09-26
// 秘密鍵の一覧を表示
$ gpg -K
/home/devel/.gnupg/secring.gpg
------------------------------
sec 2048R/092CFCF5 2019-09-26
uid devel <devel@localhost>
ssb 2048R/59F6142A 2019-09-26「本名」で入力したdevelがUIDになり、公開鍵と秘密鍵のペアが作られていることを確認できました。
GnuPG keyring内の鍵ペアをファイルにエクスポート
pgcryptoから使えるようにdevelユーザUIDの公開鍵と秘密鍵をファイルにエクスポートしておきます。
gpgでエクスポート出来るフォーマットには、
- バイナリ形式
- ASCII Armor形式（CRC付きのBase64亜種テキスト形式）
の2種類があります。
バイナリかテキストかは運用方針で決めるとして今回は両方使ってみます。
テキスト（ASCII Armor）形式でエクスポート。
$ gpg -a --export devel > public.key
$ gpg -a --export-secret-keys devel > secret.keyバイナリ形式でエクスポート。
$ gpg --export devel > public.key.bin
$ gpg --export-secret-keys devel > secret.key.bin計4ファイルエクスポートされました。
$ ls -l
合計 16
-rw-rw-r--. 1 devel devel 1707 9月 28 12:27 public.key
-rw-rw-r--. 1 devel devel 1175 9月 28 12:28 public.key.bin
-rw-rw-r--. 1 devel devel 3574 9月 28 12:27 secret.key
-rw-rw-r--. 1 devel devel 2553 9月 28 12:28 secret.key.binpsqlを使って暗号、復号を動作確認
鍵ペアファイルが出来たのでpsqlを使って暗号、復号処理を動かしてみます。
postgresスーパーユーザで手っ取り早く試し打ちしてみる
まずはテーブルを作らずに暗号化、復号をやってみます。
作成したバイナリ鍵ファイルの方を、PostgreSQLのdataディレクトリにコピーしておきます。
$ ls
public.key public.key.bin secret.key secret.key.bin
# su
# cp *.bin /var/lib/pgsql/11/data
# cd /var/lib/pgsql/11/data
# chown postgres.postgres ./public.key.bin
# chown postgres.postgres ./secret.key.bin（＊）postgresスーパーユーザはpg_****_****()のようなシステム管理関数を使うことでdataディレクトリ内の資源に簡単にアクセスすることが出来ます。
ここではpg_read_binary_file()を利用してpsqlからdata/鍵ファイルにアクセスします。
（システム管理関数の詳細は公式ドキュメント参照）
‘暗号化する文字列’という文字列を公開鍵で暗号化してみます。postgresユーザでPostgreSQLに入り、以下のSQLを実行。共通鍵暗号の「pgp_sym_encrypt()」関数に対し、公開鍵暗号は「pgp_pub_encrypt()」関数を使います。
シグネチャ
pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
SELECT pgp_pub_encrypt(‘暗号化する文字列’, pg_read_binary_file(‘public.key.bin’));
# su - postgres
-bash-4.2$ psql
psql (11.4)
"help" でヘルプを表示します。
postgres=# SELECT pgp_pub_encrypt('暗号化する文字列', pg_read_binary_file('public.key.bin'));
pgp_pub_encrypt
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
\xc1c04c03bcf3309f59f6142a0108008479e8f47ab253567444e890bcb418758c15c2e1686239ae740c56c453a59527d75b7437521b6253731dcf85026126f10b3800e3be2
71d9d1984d07127e732a6adc21490047c1a12e15390dab5f0bb5e98b7235e0b740633a717e5cf180177f594a16686bd82c8d7bde476864a88e2cd8eb694ad81e56a43b3f7c12
6332865879535049fdb7dcdb46efe70fb9279e5673b01ff676ef4318a94708ff666543daf9ea0f518b30954ee8b613b88d07b5f2b68851d137c041e4acdc46c521a311d68e9d
4c19310949b7e052c3b0b6a861574305da4a6cfa71c009989dbf9ee4ccd88091ce5da4c3e037b115c9cb440f82a909875bc95fa6f94672b3cba7bb675d271d24901555cf1af6
441370dc91a6d545c836635a0ada2b06bc1d8b7e6cbbe0ece96dd50fa458c1d32ec5feb1f5cfa907c602983078740cd6a1a876d058bd1ef2031c7f1ba81ae2f4013d420
(1 行)暗号化出来ました。秘密鍵で復号出来るか確認してみます。復号には「pgp_pub_decrypt」関数を使います。
シグネチャ
pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
SELECT pgp_pub_decrypt(pgp_pub_encrypt(‘暗号化する文字列’, pg_read_binary_file(‘public.key.bin’)), pg_read_binary_file(‘secret.ke
y.bin’), ‘devel’);
postgres=# SELECT pgp_pub_decrypt(pgp_pub_encrypt('暗号化する文字列', pg_read_binary_file('public.key.bin')), pg_read_binary_file('secret.ke
y.bin'), 'devel');
pgp_pub_decrypt
------------------
暗号化する文字列
(1 行)暗号と復号を一行でやっているので長くて見づらいですが、”暗号化する文字列”に戻りました。大丈夫そうですね。
公開鍵による暗号、秘密鍵による復号の動作確認が出来ました。
ただ、残念ながらこの方法は以下の理由で実戦投入が出来ません。
- システム稼働時に使う一般PostgreSQLユーザではシステム管理関数が使えない
- 公開鍵と秘密鍵が同じ場所にあるのはリスクがある
次はもう少し実戦を想定した実装をしてみます。
公開鍵と秘密鍵の場所を分散させる
以下の方針でやってみます。
- 一般PostgreSQLユーザ（devel）のDB、テーブルで暗号、復号を行う
- 公開鍵はDBにデータ登録、秘密鍵は任意のディレクトリパスにファイル配置
ついでに鍵データフォーマットは先程使わなかったASCIIアーマーの方を使ってみます。
公開鍵をテーブルに保存する
develでPostgreSQLに接続して公開鍵を保存するpubkeyテーブルを作成します。
公開鍵データはASCIIアーマーフォーマットで入れるのでtext型にしておきます。
$ psql
psql (11.4)
"help" でヘルプを表示します。
devel=> CREATE TABLE pubkey(id integer, key text);
CREATE TABLE
devel=> \d pubkey
テーブル "public.pubkey"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
-----+---------+----------+---------------+------------
id | integer | | |
key | text | | |public.keyファイルを読み込んでpsql内の変数PUBKEYに格納。
devel=> \set PUBKEY `cat /home/devel/public.key`
// 読み込めたか確認
devel=> select :'PUBKEY';
?column?
------------------------------------------------------------------
-----BEGIN PGP PUBLIC KEY BLOCK----- +
Version: GnuPG v2.0.22 (GNU/Linux) +
+
mQENBF2M1IEBCADHgeMNWGmFXjFQKUv/bQI0uX3XT8lnEfW4QOr/LlCLcM7VFcjq+
EVVMgRKJtyhXjzSibwFPkjrBRv8CcCE7TBrbwBxhzGhpZaXJz9BldGncpWF0J/01+
(snip)PUBKEY変数の内容をテーブルにINSERT。
devel=> INSERT INTO pubkey VALUES(0, :'PUBKEY');
INSERT 0 1ASCIIアーマー形式で公開鍵をテーブルに保存出来ました。
devel=> SELECT * FROM pubkey;
id | key
----+------------------------------------------------------------------
0 | -----BEGIN PGP PUBLIC KEY BLOCK----- +
| Version: GnuPG v2.0.22 (GNU/Linux) +
| +
| mQENBF2M1IEBCADHgeMNWGmFXjFQKUv/bQI0uX3XT8lnEfW4QOr/LlCLcM7VFcjq+
| EVVMgRKJtyhXjzSibwFPkjrBRv8CcCE7TBrbwBxhzGhpZaXJz9BldGncpWF0J/01+
(snip)（＊）pgp_pub_encrypt()やpgp_pub_decrypt()へ渡す鍵データはバイナリである必要があるので、実際に鍵を使う際はdearmor()関数を使ってバイナリに戻す必要があります。
dearmor()関数はpgcryptoを有効化すると使えるようになる拡張関数です。
// ASCIIアーマーテキストをバイナリに戻す
devel=> SELECT dearmor((SELECT key FROM pubkey WHERE id = 0));暗号化する際、公開鍵をハードディスク上のファイルからではなく、テーブルから持ってくる準備が出来ました。
暗号化したデータを保存するテーブルを作成
名前と暗号化されたパスワードを保存するmembersテーブルを作成します。
CREATE TABLE members(id serial, name varchar(20), crypted_password bytea, PRIMARY KEY(id));
devel=> CREATE TABLE members(id serial, name varchar(20), crypted_password bytea, PRIMARY KEY(id));
CREATE TABLE
devel=> \d members
テーブル "public.members"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------------------+-----------------------+----------+---------------+-------------------------------------
id | integer | | not null | nextval('members_id_seq'::regclass)
name | character varying(20) | | |
crypted_password | bytea | | |
インデックス:
"members_pkey" PRIMARY KEY, btree (id)
暗号化しながらINSERT
山田太郎のパスワード「yamada#123」を暗号化しながらINSERT。
pubkeyテーブルからとってきたASCIIアーマー形式の公開鍵をdearmor()でバイナリ化することも忘れずに。
INSERT INTO members(name, crypted_password) VALUES(‘山田太郎’, pgp_pub_encrypt(‘yamada#123’, dearmor((SELECT key FROM pubkey WHERE id = 0))));
devel=> INSERT INTO members(name, crypted_password) VALUES('山田太郎', pgp_pub_encrypt('yamada#123', dearmor((SELECT key FROM pubkey WHERE id = 0))));
INSERT 0 1暗号化されてdevel#123であることは分からなくなっています。
devel=> SELECT * FROM members;
id | name | crypted_password
1 | 山田太郎 | \xc1c04c03bcf3309fd16e72d274a75108bb60f79f8b7ad......
(snip)復号しながらSELECT
復号用の秘密鍵も公開鍵と同じように変数で取り込んでおきます。
devel=> \set SECKEY `cat /home/devel/tmp/secret.key`変数「SECKEY」に入れた秘密鍵をdearmor()でバイナリ化しながら復号。
SELECT name, pgp_pub_decrypt(crypted_password, dearmor(:’SECKEY’), ‘devel’) FROM members;
devel=> SELECT name, pgp_pub_decrypt(crypted_password, dearmor(:'SECKEY'), 'devel') FROM members;
name | pgp_pub_decrypt
----------+-----------------
山田太郎 | yamada#123
(1 行)テーブルに保存した公開鍵で暗号化 → HDD上のファイルから読み込んだ秘密鍵で復号することが出来ました。
ただ、秘密鍵は\setで一時的に変数に読み込んだだけで恒久的に使えないですし、公開鍵と秘密鍵が同じホスト上に存在していて、万が一このホストに侵入されたら復号されてしまうかも知れません。
次は秘密鍵を別のホストに移してJavaプログラムからロードするようにします。
他ホストのJavaに秘密鍵を持たせる
WindowsをAPサーバと見立て、Eclipseから構築してきたPostgreSQLに接続してみます。
本番ではMyBatis3なりSpring JDBCなり使うとして、今回は一枚っぺらのJDBCで簡単に接続。
新規Mavenプロジェクト作成
- 「ファイル」→「新規」→「Mavenプロジェクト」→「maven-archtype-quickstart」
- プロジェクト右クリック→「プロパティ」→「ビルドパス」でsrc/main/resourcesを「フォルダーの追加」
- secret.keyをscpなりでCentOSから持ってきてresourcesに配置
pom.xmlにPostgreSQL用JDBCドライバを追加
mvnrepositoryから最新のjarファイルを選択。
https://mvnrepository.com/artifact/org.postgresql/postgresql
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.8</version>
</dependency>
実装
新規に佐藤一郎をINSERT。パスワードはリモートPostgreSQLで暗号化され、SELECT時は渡した秘密鍵で復号されて返ってきます。
package smpl;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PgcryptoSmpl {
public PgcryptoSmpl() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:postgresql://192.168.0.4:5432/devel";
String user = "devel";
String password = "devel#123";
String seckey = loadSecretKey();
try{
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
// 新規ユーザを追加
String sql = "INSERT INTO members(name, crypted_password) " +
"VALUES('佐藤一郎', pgp_pub_encrypt('sato#123', dearmor((SELECT key FROM pubkey WHERE id = 0))));";
stmt = conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
stmt.close();
// パスワードを復号しながらユーザ一覧をSELECT
sql = "SELECT name, pgp_pub_decrypt(crypted_password, dearmor('" + seckey + "'), 'devel') FROM members;";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
String name = rs.getString(1);
String plainPassword = rs.getString(2);
System.out.println(name + ", " + plainPassword);
}
} catch (SQLException e){
e.printStackTrace();
} finally {
try {
if(rs != null)rs.close();
if(stmt != null)stmt.close();
if(conn != null)conn.close();
}
catch (SQLException e){
e.printStackTrace();
}
}
}
private String loadSecretKey() {
InputStream in = this.getClass().getClassLoader().getResourceAsStream("secret.key");
InputStreamReader reader = new InputStreamReader(in);
StringBuilder builder = new StringBuilder();
char[] buffer = new char[512];
int read;
try {
while (0 <= (read = reader.read(buffer))) {
builder.append(buffer, 0, read);
}
} catch (IOException e) {
e.printStackTrace();
}
String seckey = builder.toString();
return seckey;
}
public static void main(String[] args) {
new PgcryptoSmpl();
}
}
実行結果
EclipseコンソールにはPostgreSQLで復号された平文パスワードが表示。
山田太郎, yamada#123
佐藤一郎, sato#123
A5 Mk2等でテーブルを確認するとパスワードは暗号化されている状態です。
公開鍵と秘密鍵を別ホストで持ちながら暗号、復号することが出来ました。
まとめ
pgcryptoで暗号、復号するタイミングはテーブルに保存（INSERT）、取得（SELECT）される時です。
ブラウザ等のクライアント→APサーバ→DBサーバでの通信では平文でデータが流れるのでHTTPSでネットワーク暗号化をしっかりしておく必要があります。
