Supabase で TCE（透過的列暗号化）を試してみた

Supabase で TCE（透過的列暗号化）を試してみた 第 37 回 PostgreSQL アンカンファレンス@オンライン 2022/12/20 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ○ Aurora MySQL v1 → v3 移行完了 ■ https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book ■ https://zenn.dev/hmatsu47/books/aurora-mysql-do-book 2

• https://qiita.com/hmatsu47
• https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
• https://zenn.dev/hmatsu47/books/aurora-mysql-do-book

今回の発表ネタ ● 第 33 回「SolidJS から Supabase を使ってみた」発表時 のアプリケーションに TCE（透過的列暗号化）を使った 機能を追加 ○ プロフィール画面に「秘密の情報」項目を追加 3

注意 ● 内容は 2022/12/1 のブログ記事を参考に試したもの ○ 2022/12/7 〜 11 頃にテスト ■ https://supabase.com/blog/transparent-column-encryption-with-postgres ● その後実装が追加・変更されている可能性がある ○ 2022/12/16 のブログ記事には一部追加情報が ■ https://supabase.com/blog/vault-now-in-beta#transparent-column-encryptiontce ■ ただしこの記事 が示す項目は見つけられず（Project が古いから？） 4

• https://supabase.com/blog/transparent-column-encryption-with-postgres
• https://supabase.com/blog/vault-now-in-beta#transparent-column-encryption-tce

関連記事（PostgreSQL Advent Calendar 2022） ● https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0 ○ Supabase で TCE（透過的列暗号化）を軽く試してみた ● https://qiita.com/hmatsu47/items/d3cf24f0e462628cd700 ○ Supabase で TCE（透過的列暗号化）をアプリケーションから 使ってみた 5

• https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0
• https://qiita.com/hmatsu47/items/d3cf24f0e462628cd700

Supabase とは？（おさらい） ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ（それぞれの機能は以前よりも増えている） ○ Database ← PostgreSQL が使われている ○ Authentication ○ Storage ○ Edge Functions 6

Supabase とは？ ● 「Realtime」が別記されて 5 つ並ぶことも ○ https://supabase.com/docs/guides/realtime 7

• https://supabase.com/docs/guides/realtime

Supabase の TCE（透過的列暗号化）とは？ ● ざっくり ○ Extension「pgsodium」を使用 ■ pgsodium : libsodium を使って暗号化 ○ text / bytea 列を暗号化可能 ○ データを暗号化してログ（WAL）に漏らさない ○ ユーザーに行レベルの暗号化を提供 ○ おそらくベータテスト中の機能 8

サンプル画面（今回追加分） ● 「秘密の情報」欄を今回追加 9

手順 1. pgsodium 有効化 ● ● ● ● ● ● スキーマ「pgsodium」を指定（記事に合わせて） 10

手順 2. 鍵生成・鍵 ID 取得（記事に合わせて） select * from pgsodium.create_key(); ● SELECT 毎に生成される 11

手順 3. テーブル作成（関連分） create table privates ( note_id bigint generated by default as identity, updated_at timestamp with time zone, secret_note text not null, key_id uuid not null default '【準備2.で出たid】'::uuid, nonce bytea default pgsodium.crypto_aead_det_noncegen(), userid uuid not null, ); primary key (note_id) ● secret_note : 暗号化対象の列 ● key_id : 鍵 ID ● nonce : 行毎のランダム値（ナンス） 12

手順 4. RLS 設定（関連分） alter table privates enable row level security; create policy "Users can view their own private profile." on privates for select using ( auth.uid() = userid ); create policy "Users can insert their own private profile." on privates for insert with check ( auth.uid() = userid ); create policy "Users can update their own private profile." on privates for update using ( auth.uid() = userid ); ● RLS は TCE よりも前に設定しておく 13

手順 5. TCE（透過的列暗号化）設定 security label for pgsodium on column privates.secret_note is 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userid) NONCE nonce'; ● ● userid 列の値を関連付けて暗号化 ○ ブログ記事の 4 番目の方法 ○ https://supabase.com/blog/transparent-column-encryption-with-postgres#one-key-i d-per-row-with-associated-data ● 2 つ以上のテーブル・列に対して設定しようとするとエラー 14

• https://supabase.com/blog/transparent-column-encryption-with-postgres#one-key-id-per-row-with-associated-data

手順 6. 復号用ビューを作成 create view decrypted_privates as select note_id, userid, decrypted_secret_note from pgsodium_masks.privates where auth.uid() = userid order by userid asc, note_id desc limit 1; ● ブログ記事どおりなら同名の復号ビューが自動作成されるはず ○ https://supabase.com/blog/transparent-column-encryption-with-postgres#using-anencrypted-table ○ 作成されなかったので手動で作成 ○ 内容はオリジナル（おそらく本来のものとは違う） 15

• https://supabase.com/blog/transparent-column-encryption-with-postgres#using-an-encrypted-table

手順 7. 権限追加 grant select on pgsodium.valid_key to authenticated; grant execute on all functions in schema pgsodium to authenticated; ● permission denied for view valid_key ● permission denied for function crypto_aead_det_decrypt のエラーが発生しないように 16

コード 1. 書き込み部分（supabase-js v2 使用）
const updatePrivate = async () => {
// プロフィール秘密情報更新（DB へ）
const { user } = props.session;
// UPSERT は使わない
const note = await getPrivate();
const data = {
userid: user.id,
secret_note: secretNote(),
updated_at: new Date(),
};
const { error } = await supabase.from("privates").insert(data);
if (error) {
throw error;
}
// 実は削除はできない（API は受け付けるが…）
// （削除部分のコードは省略）

};

17

書き込み時 UPSERT / UPDATE は NG ● UPSERT : 不安定 ● UPDATE : 暗号化されない→エラーに 18

コード 2. 読み取り部分（supabase-js v2 使用）
const getPrivate = async () => {
// プロフィール秘密情報読み取り（DB から）
const { user } = props.session;
// @ts-ignore
const { data, error, status } = await supabase
.from("decrypted_privates")
.select(`decrypted_secret_note, note_id`)
.eq("userid", user.id)
.single();
if (error && status !== 406) {
throw error;
}
return data;

};

● 型定義は未割り当て（// @ts-ignore で警告を回避）
19

登録データ ● secret_node が暗号化されている ● decrypted_private ビューでは復号されている ○ where auth.uid() = userid が指定されている→ Table Editor では表示できない 20

問題点いろいろ ● 複数列の暗号化ができず ○ TCE 設定時にエラーが発生 ● ブログ（12/16）の記述どおりの設定項目が表示されず ● ブログの記述どおりの復号用ビューが自動作成されず ● UPSERT が不安定で UPDATE が未対応 ● DELETE → INSERT / INSERT → DELETE ができず ○ DELETE は API で正常に受け付けられるが行削除されない 21

まとめ ● 今のところはベータテスト中（おそらく） ○ うまく動かない部分がある ● 正常に動くようになったら手軽に列暗号化／復号が可能 ● ただし完全な「透過」ではない ○ アプリケーションのデータ参照側の修正が必要（復号用ビュー参照） ○ 修正コストに対して得られるメリットが大きければ「使える」 ■ 素直に暗号化と復号をアプリケーションに実装するほうが良いケースも 22