1.8K Views
December 20, 22
スライド概要
第 37 回 PostgreSQL アンカンファレンス@オンライン 2022/12/20
Qiita や Zenn でいろいろ書いてます。 https://qiita.com/hmatsu47 https://zenn.dev/hmatsu47 MySQL 8.0 の薄い本 : https://github.com/hmatsu47/mysql80_no_usui_hon Aurora MySQL v1 → v3 移行計画 : https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
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
今回の発表ネタ ● 第 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
関連記事(PostgreSQL Advent Calendar 2022) ● https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0 ○ Supabase で TCE(透過的列暗号化)を軽く試してみた ● https://qiita.com/hmatsu47/items/d3cf24f0e462628cd700 ○ Supabase で TCE(透過的列暗号化)をアプリケーションから 使ってみた 5
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
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
手順 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
手順 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