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

第 37 回 PostgreSQL アンカンファレンス@オンライン 2022/12/20

Transcript

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

   View Slide

2. 自己紹介
   松久裕保（@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
   

   View Slide

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

   View Slide

4. 注意
   ● 内容は 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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

12. 手順 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)
    );
    

    View Slide

13. 手順 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 );
    

    View Slide

14. 手順 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';
    

    View Slide

15. 手順 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;
    

    View Slide

16. 手順 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;
    

    View Slide

17. コード 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 は受け付けるが…）
    // （削除部分のコードは省略）
    };
    

    View Slide

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

    View Slide

19. コード 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;
    };
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide