Slide 1
Slide 1 text
Supabase で
TCE（透過的列暗号化）を試してみた
第 37 回 PostgreSQL アンカンファレンス@オンライン
2022/12/20
まつひさ（hmatsu47）
Slide 2
Slide 2 text
自己紹介
松久裕保（@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
Slide 3
Slide 3 text
今回の発表ネタ
● 第 33 回「SolidJS から Supabase を使ってみた」発表時
のアプリケーションに TCE（透過的列暗号化）を使った
機能を追加
○ プロフィール画面に「秘密の情報」項目を追加
3
Slide 4
Slide 4 text
注意
● 内容は 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-encryption-
tce
■ ただしこの記事 が示す項目は見つけられず（Project が古いから？）
4
Slide 5
Slide 5 text
関連記事（PostgreSQL Advent Calendar 2022）
● https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0
○ Supabase で TCE（透過的列暗号化）を軽く試してみた
● https://qiita.com/hmatsu47/items/d3cf24f0e462628cd700
○ Supabase で TCE（透過的列暗号化）をアプリケーションから
使ってみた
5
Slide 6
Slide 6 text
Supabase とは？（おさらい）
● BaaS（Backend as a Service）の一つ
○ Firebase Alternative
● サービスは 4 つ（それぞれの機能は以前よりも増えている）
○ Database ← PostgreSQL が使われている
○ Authentication
○ Storage
○ Edge Functions
6
Slide 7
Slide 7 text
Supabase とは？
● 「Realtime」が別記されて 5 つ並ぶことも
○ https://supabase.com/docs/guides/realtime
7
Slide 8
Slide 8 text
Supabase の TCE（透過的列暗号化）とは？
● ざっくり
○ Extension「pgsodium」を使用
■ pgsodium : libsodium を使って暗号化
○ text / bytea 列を暗号化可能
○ データを暗号化してログ（WAL）に漏らさない
○ ユーザーに行レベルの暗号化を提供
○ おそらくベータテスト中の機能
8
Slide 9
Slide 9 text
サンプル画面（今回追加分）
● 「秘密の情報」欄を今回追加
9
Slide 10
Slide 10 text
手順 1. pgsodium 有効化
●
●
●
●
●
● スキーマ「pgsodium」を指定（記事に合わせて）
10
Slide 11
Slide 11 text
手順 2. 鍵生成・鍵 ID 取得（記事に合わせて）
● SELECT 毎に生成される
11
select * from pgsodium.create_key();
Slide 12
Slide 12 text
手順 3. テーブル作成（関連分）
● secret_note : 暗号化対象の列
● key_id : 鍵 ID
● nonce : 行毎のランダム値（ナンス）
12
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)
);
Slide 13
Slide 13 text
手順 4. RLS 設定（関連分）
● RLS は TCE よりも前に設定しておく
13
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 );
Slide 14
Slide 14 text
手順 5. TCE（透過的列暗号化）設定
●
● userid 列の値を関連付けて暗号化
○ ブログ記事の 4 番目の方法
○ https://supabase.com/blog/transparent-column-encryption-with-postgres#one-key-i
d-per-row-with-associated-data
● 2 つ以上のテーブル・列に対して設定しようとするとエラー
14
security label for pgsodium
on column privates.secret_note
is 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userid) NONCE nonce';
Slide 15
Slide 15 text
手順 6. 復号用ビューを作成
● ブログ記事どおりなら同名の復号ビューが自動作成されるはず
○ https://supabase.com/blog/transparent-column-encryption-with-postgres#using-an-
encrypted-table
○ 作成されなかったので手動で作成
○ 内容はオリジナル（おそらく本来のものとは違う）
15
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;
Slide 16
Slide 16 text
手順 7. 権限追加
● permission denied for view valid_key
● permission denied for function crypto_aead_det_decrypt
のエラーが発生しないように
16
grant select on pgsodium.valid_key to authenticated;
grant execute on all functions in schema pgsodium to authenticated;
Slide 17
Slide 17 text
コード 1. 書き込み部分（supabase-js v2 使用）
17
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 は受け付けるが…）
// （削除部分のコードは省略）
};
Slide 18
Slide 18 text
書き込み時 UPSERT / UPDATE は NG
● UPSERT : 不安定
● UPDATE : 暗号化されない→エラーに
18
Slide 19
Slide 19 text
コード 2. 読み取り部分（supabase-js v2 使用）
● 型定義は未割り当て（// @ts-ignore で警告を回避）
19
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;
};
Slide 20
Slide 20 text
登録データ
● secret_note が暗号化されている
● decrypted_private ビューでは復号されている
○ where auth.uid() = userid が指定されている→ Table Editor では表示できない
20
Slide 21
Slide 21 text
問題点いろいろ
● 複数列の暗号化ができず
○ TCE 設定時にエラーが発生
● ブログ（12/16）の記述どおりの設定項目が表示されず
● ブログの記述どおりの復号用ビューが自動作成されず
● UPSERT が不安定で UPDATE が未対応
● DELETE → INSERT / INSERT → DELETE ができず
○ DELETE は API で正常に受け付けられるが行削除されない
21
Slide 22
Slide 22 text
まとめ
● 今のところはベータテスト中（おそらく）
○ うまく動かない部分がある
● 正常に動くようになったら手軽に列暗号化／復号が可能
● ただし完全な「透過」ではない
○ アプリケーションのデータ参照側の修正が必要（復号用ビュー参照）
○ 修正コストに対して得られるメリットが大きければ「使える」
■ 素直に暗号化と復号をアプリケーションに実装するほうが良いケースも
22