こんにちは。プラットフォーム事業部のHです。
本稿ではPostgreSQLでデータを暗号化する方法について紹介させていただきます。

pgcryptoを使ったデータ暗号化について

pgcrypto拡張とは、PostgreSQLに標準で用意されている暗号機能を提供する拡張モジュールです。
個人情報や機密情報を扱うシステムでは、データを平文のまま保存せず、暗号化して保護したいという要件が求められることがあります。

pgcrypto拡張を有効化する

pgcrypto拡張を利用するために、以下のSQLで拡張を有効化します。

CREATE EXTENSION IF NOT EXISTS pgcrypto;

データを暗号化する

暗号化用のテーブルを作成します。
暗号化対象のカラムはbytea型として定義します。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  encrypted_name bytea
);

データを登録する際に、pgp_sym_encrypt を使って 暗号化した状態でDBに保存します。

INSERT INTO users VALUES (1, pgp_sym_encrypt(‘山田太郎’, ‘pass’));

このとき、DB上には文字列ではなく、バイナリ形式の暗号化データ（bytea）で保存されます。
そのため、SQLで直接参照しても内容を読むことはできません。

SELECT encrypted_name FROM users where id = 1;

既存データを更新する場合も、
INSERT時と同様に 更新値を暗号化してから保存します。

UPDATE users SET encrypted_name = pgp_sym_encrypt(‘佐藤花子’, ‘pass’) WHERE id = 1;

データを復号化する

正しく更新されているかは、pgp_sym_decrypt を使って確認できます。

SELECT id, pgp_sym_decrypt(encrypted_name, ‘pass’) AS decrypted_name FROM users WHERE id = 1;

暗号化オプション（s2k-mode）について

s2k-mode=1 は 暗号化時（pgp_sym_encrypt）に指定するオプションです。

UPDATE users SET encrypted_name = pgp_sym_encrypt(‘佐藤花子’, ‘pass’, ‘s2k-mode=1’) WHERE id = 1;

s2k-mode=1 を指定すると、パスワードから鍵を生成する処理が簡略化されるため、暗号化・復号の処理が高速になる場合があります。 一方で、鍵生成の強度はデフォルト設定よりもやや下がるため、性能とセキュリティのバランスを考慮して使用することが望まれます。

補足

pgp_sym_encrypt / pgp_sym_decrypt で使用するパスワードはソースコードに直接記載するのではなく、外部から安全に参照する形で管理することが望まれます。環境変数や外部設定ファイルなどを利用し、使用する環境に応じて適切に管理する方法が一般的です。

まとめ

今回はpgcrypto拡張を使用したPostgreSQLの暗号化について紹介させていただきました。
最後までお読みいただき、ありがとうございました。