こちらは PostgreSQL Advent Calendar 2022 カレンダー 2 枚目・8 日目の記事です。
もともと 12 日目のネタを書こうとして、1 つの記事にすると長くなりそうだったので分割しようと思い、急遽 2 枚目のカレンダーを作ってしまいました。
PostgreSQL の Extensions（拡張機能）の中に pgsodium というものがあり、Supabase ではこれを使って透過的列暗号化が利用できるようなので、まずは簡単に試してみました（そしてハマりました）。
Supabase は Firebase Alternative(s) の 1 つで、PostgreSQL を利用した BaaS / mBaaS です。
（セルフホスティングも可能です）
ざっくり、どんな機能？
こちら のブログ記事には、
- 任意の数のテーブルの 1 つまたは複数の
text（またはbytea）列を暗号化可能 - データを暗号化してログに漏らさないようにできる
- ユーザーに行レベルの暗号化を提供することが可能
と書かれています。
「ログ」は WAL のことでしょう。
やってみる
まずは、このブログ記事に（概ね）沿って試してみます。
ちょっとだけテーブル名・列名などをアレンジしています。
pgsodium を有効化する
有効になっていない場合は、「Database」→「Extensions」で有効化します。
鍵を生成し、鍵 ID を取得する
「SQL Editor」で生成するための SQL 文を流します。
鍵の導出についての説明は、先のブログ記事をご覧ください。ここでは省略します。
select * from pgsodium.create_key();
SELECTですが何度も実行するとたくさん生成されてしまうので気をつけましょう。
id列の値をコピーします。
対象列について、テーブル内の全行分を 1 つの鍵 ID で暗号化してみる
2022/12/18 追記：
2022/12/16 のブログ記事を見ると、Table Editor から設定できるようになったようです。
（私のプロジェクトでは対象項目が表示されませんでした）
（同ブログ記事の画像を引用）
最も単純なパターンです。
CREATE TABLE credit_cards (
id bigserial primary key,
credit_card_number text,
key_id uuid not null DEFAULT 'f3c621ed-d16c-431d-8c8d-c21904a0cb0f'::uuid
);
SECURITY LABEL FOR pgsodium
ON COLUMN credit_cards.credit_card_number
IS 'ENCRYPT WITH KEY COLUMN key_id';
INSERT INTO credit_cards (credit_card_number) VALUES ('1234567890123456');
INSERT INTO credit_cards (credit_card_number) VALUES ('1234567890123456');
INSERT INTO credit_cards (credit_card_number) VALUES ('1234567890123456');
SECURITY LABELという、見慣れないものが使われています。
試しに検索してみたところ、なんと海外さんの 10 年前のアドベントカレンダー記事が出てきました（笑）。
本題に戻って…INSERTされた行を「Table Editor」から（普通に）見てみます。
暗号化後の列値（credit_card_number）が全行同じですね。
そしてこれを、透過的に復号するビューで確認してみます。
select * from pgsodium_masks.credit_cards;
先のブログ記事ではdecrypted_credit_cardsビューを使うように書かれていますが（2022/12/7 現在）、手元で試した限りではpgsodium_masks.credit_cardsが正しいようです。
2022/12/9 追記：
TABLE Editor で schema としてpgsodium_masksを選択することでcredit_cardsビューを見ることもできます（以降同様）。
decrypted_credit_card_number列に復号後の値が見えますね。
対象列について、1 行ごとに鍵 ID を変えて暗号化してみる
先の例の場合、暗号化した後の文字列が全て同じになりました。
この状態で、ある特定の行の暗号が解読された場合、 同じ（暗号化後の）列値を持つ行が別に存在すると、その行の（暗号化前の）列値も同じである ことが簡単にわかってしまいます。
というわけで、今度は行ごとに鍵 ID を変えて暗号化してみます（NONCEを追加）。
ここで、1 つ目のテーブルを残したまま、2 つ目のテーブルを作ろうと思い、
select * from pgsodium.create_key();
CREATE TABLE credit_cards_2 (
id bigserial primary key,
credit_card_number_2 text,
key_id uuid not null DEFAULT '045d3605-b39f-495c-8b87-4b334837d6ad'::uuid,
nonce bytea default pgsodium.crypto_aead_det_noncegen()
);
SECURITY LABEL FOR pgsodium
ON COLUMN credit_cards_2.credit_card_number_2
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';
INSERT INTO credit_cards_2 (credit_card_number_2) VALUES ('1234567890123456');
INSERT INTO credit_cards_2 (credit_card_number_2) VALUES ('1234567890123456');
INSERT INTO credit_cards_2 (credit_card_number_2) VALUES ('1234567890123456');
としてみたところ、
怒られてしまいました。
SQL Editor に書いた SQL 文のインデントが一部荒ぶっていますが、気にしないでください。以降同様。
使い方が間違っているだけなのかもしれませんが、試した範囲では同時に複数のテーブルに対して暗号化列を作成できませんでした。
というわけで、仕方なく最初のテーブルを消そうとしたら、また怒られました。
他に正しい方法があるかもしれませんが、ワークアラウンド的に先ほどの復号用ビューを先に消してみます。
今度はうまく消せました。
気を取り直して、CREATE TABLE再チャレンジです。
今度は問題なく作成できました。
暗号化後の列値（credit_card_number_2）が行ごとに違う値になっています。
復号用のビューから見てみると、暗号化前の列値が表示されました。
別の列を関連付けて暗号化する
例えば「A さんのカード番号は XXX」のように、人とカード番号の組み合わせを暗号化の処理（鍵 ID）に紐づけることによって、列値の取り違えや（悪意を持った人による）すり替えなどを防ぎたいケースもあるでしょう。
行レベルセキュリティー（RLS）と組み合わせることによってそれを防止することも考えられますが、ここでは暗号化を使うケースで考えます。
もちろん、両者を組み合わせて使うこともできるはずです（というか、通常は RLS が必須で、列暗号化は RLS と組み合わせて使うべきでしょう）。
まずは先ほどと同様に 2 つ目のテーブル（と復号用のビュー）を削除します。
そして新たな鍵 ID を発行します。
これを使って新たなテーブルを作ります。
CREATE TABLE credit_cards_3 (
id bigserial primary key,
credit_card_number_3 text,
account_id integer,
key_id uuid not null DEFAULT '18a31832-6216-4b23-ada6-16b9ac82b16a'::uuid,
nonce bytea default pgsodium.crypto_aead_det_noncegen()
);
SECURITY LABEL FOR pgsodium
ON COLUMN credit_cards_3.credit_card_number_3
IS 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (account_id) NONCE nonce';
INSERT INTO credit_cards_3 (
credit_card_number_3,
account_id
)
VALUES
('1234567890123456', 123);
INSERT INTO credit_cards_3 (
credit_card_number_3,
account_id
)
VALUES
('7890123456789012', 456);
INSERT INTO credit_cards_3 (
credit_card_number_3,
account_id
)
VALUES
('3456789012345678', 789);
ASSOCIATED (account_id)が前回と異なる部分です。
テーブルの中身を見てみます。
復号用のビューを見てみます。
特に問題なく復号できました。
余談
ツイートでぼやいたら、中の人から 「来週またブログ記事とちゃんとしたドキュメント出すね」 というリプをいただきました。
次回、1 枚目のカレンダーの 12 日目の記事で、アプリケーションからの利用を試してみます。