2 つ以上のテーブルで暗号化の設定ができない
- これは前回の記事に書いたとおり
更新時の処理が期待どおりに走らない
- upsert()で処理を書くと更新できたりできなかったり不安定
- select()の結果でinsert()かupdate()かを分岐して処理する場合、update()の処理が動かない（リクエストは受け付けられるが更新されない）
- select()後、delete()とinsert()の処理を分けて行う場合、delete()のリクエストは受け付けられるが行が消されない
（認証済みユーザーに対して使う場合）authenticatedに対して広めの権限を付けないといけない
publicスキーマに復号用ビューを参照するビューを（二重に）作らないと使いづらい
Supabase CLI で TypeScript の型定義を出そうとすると、pgsodium が生成するビューの権限の問題でエラーになる

テーブル定義など

こんな感じで作りました（今回の実装に関係する追加分のみ）。

鍵IDを生成

select id from pgsodium.create_key();

秘密の情報用テーブルを作成

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 '【↑で出たid】'::uuid,
  nonce bytea default pgsodium.crypto_aead_det_noncegen(),
  userid uuid not null,

  primary key (note_id)
);

当初はuseridを主キーにして作成しましたが、duplicated keyエラーに悩まされたため（後述）、別の ID（note_id）を用意しました。
また、useridをユニークキーにする実装も同様の問題が生じたのでunique制約を外しました。

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 );

TCEを設定

security label for pgsodium
  on column privates.secret_note
  is 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userid) NONCE nonce';

TCE よりも先に RLS の設定をしておく必要があるようです（TCE を先にすると RLS の設定時にエラーになりました）。

復号用ビューを参照するビューをpublicスキーマ側に作成

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;

pgsodium_masksスキーマのprivatesビューを直接使おうとすると、デフォルトのpublicスキーマに対するクライアントと同時にpgsodium_masksスキーマへのクライアントも 認証済みの状態で 接続しておく必要があるようで、そのような面倒な処理を避けるためにpublicスキーマ側に復号用のビューを作ります。

権限追加

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のエラーが発生しました。

コード

まずは、ライブラリを supabase-js v2 に（ついでに SolidJS と SUID も）バージョンアップして実装を…と思ったのですが、Supabase CLI で TypeScript の型定義を出力して適用する部分だけは（試しているとアドベントカレンダーの担当日に間に合わなくなりそうだったため）先送りしました。

そうしたら前述のとおりハマりました（先にやっておけば引っ掛からなかったかもしれませんが、TCE を実装するタイミングでやっぱりハマっていたかもしれません）。

`Account.tsx`（関連部分のみ）

Signal 追加部分

Account.tsx（Signal追加部分）

  const [secretNote, setSecretNote] = createSignal<string>("");

秘密の情報用の Signal（ステート）です。

DB からの読み取り部分

Account.tsx（秘密の情報読み取り部分）

  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;
  };

decrypted_privatesテーブルからuseridをキーにして復号後のデータを読み取っています。

この例では該当しませんが、テーブルとビューを結合してデータを取りたいケースがあると思います。
結合してネストの形でデータを取る場合、Supabase では外部キーを定義する必要があり、ビューではそれができません。
残念ながら TCE では結合してネストの形でデータを取ることができないようです。

同・呼び出し部分

そしてこれをgetProfile（プロフィール情報読み取り）の中で呼び出しています。

Account.tsx（プロフィール情報読み取りからの呼び出し部分）

      const note = await getPrivate();

      if (note) {
        // @ts-ignore
        setSecretNote(note.decrypted_secret_note);
      } else {
        setSecretNote("");
      }

行が取れなかったときは空文字を Signal に入れています。

DB への書き込み（更新）部分

Account.tsx（DBへの書き込み部分）

  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 は受け付けるが…）
    if (note) {
      const { error } = await supabase
        .from("privates")
        .delete()
        // @ts-ignore
        .eq("note_id", note.note_id);
      if (error) {
        throw error;
      }
    }
  };

こちらはビューではなくテーブルprivatesに直接データを書き込んでいます。

当初はここをupsert()で記述しましたが、先に「問題点」で記したとおり 更新されたりされなかったりで動作が不安定 でした。

そこで、処理を

getPrivate()で以前の情報があるかどうかをチェック
- ある場合はupdate()
- ない場合はinsert()

に書き換えたのですが、update()で情報が暗号化されずに更新されてしまい、読み取り時に復号不能でエラーが発生。

トリガを見ると（一番上にあるpublicスキーマのprivates_encrypt_secret_trigger）、Events の指定が BEFORE INSERT だけ であり、publicスキーマには更新用のトリガも見当たらないので、update()で更新されなくて当然ですよね。

（前の記事の冒頭でも触れましたが）透過的列暗号化の目的として 「平文のデータをログ（WAL）に書き出さないようにする」 があります。
それなのに、BEFORE UPDATE に対応していないせいで アプリケーション開発者がうっかりupdate()する処理を書いて実行してしまうとデータが平文で漏れる、 というのはちょっといただけないですね。
少なくともエラーを出すなどして、書き出されるのを止めて欲しいものです。

続いて、

getPrivate()で以前の情報があるかどうかをチェック
- ある場合はdelete()
その後insert()

（いわゆる DELETE → INSERT）に書き換えてみたのですが、主キーをuseridにしているとduplicated keyエラーが発生しました。

主キーをnote_idに変えた後も、useridをユニークキーにしていると同様にduplicated keyエラーが発生し続けたのでよくよく確かめてみると、（コメントおよび先に「問題点」として記したとおり）delete()からの削除リクエストは204で正常に受け付けられるものの、実際に削除は行われていませんでした。

最終的に、

getPrivate()で以前の情報があるかどうかをチェック
- 一旦その結果を保持
その後insert()
最後に、以前の情報があった場合はdelete()

に書き換え、かつinsert()からdelete()までの間に数秒スリープを挟んだりしたのですが、やはり以前の情報が削除されることはありませんでした。

同・呼び出し部分

updateProfile（プロフィール情報書き込み・更新）の中で呼び出しています。

Account.tsx（プロフィール情報書き込みからの呼び出し部分）

      await updatePrivate();

入力欄表示部分

Account.tsx（入力欄表示部分）

                <Box sx={{ padding: "20px 0 0 0" }}>
                  <TextField
                    id="secret"
                    label="秘密の情報"
                    helperText="秘密の情報があれば入力してください"
                    type="text"
                    value={secretNote()}
                    onChange={(event, value) => {
                      setSecretNote(value);
                    }}
                    sx={{ width: "100%" }}
                  />
                </Box>

コードの中に// @ts-ignoreが（多数）ありますが、これは先に「問題点」として記したとおり、Supabase CLI で TypeScript の型定義を出そうとしたもののエラーになり、うまく型情報を適用できなかったことが原因です。

CLIでの型情報生成前のlink処理でエラー

% npx supabase link --project-ref dykfxoivrejkrxpfysig                   
Enter your database password: 
Error: ERROR: cannot drop view pgsodium_masks.privates because other objects depend on it (SQLSTATE 2BP01)
Try rerunning the command with --debug to troubleshoot the error.

こちらの記事を参考にして試しましたがダメでした。

SupabaseのTS用の型情報を自動生成する

エラーの後、手作業でそれらしく作ろうとしてみたのですが、アドベントカレンダーの担当日に間に合いませんでした。

というわけで

（もちろん、やり方が悪かった可能性もありますが）実利用にはまだ早いようです。

現状では透過的に暗号化するのではなく、コード側で明示的に暗号化するほうが楽なようです。

また、現状では鍵のローテーションを支援するような機能が実装されているようには見えないので、よりそのような印象を受けました。

明日は yohsita_ さんです。

DBのマイグレーションツールを使ったみた（OracleからPostgreSQL）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Supabase で TCE（透過的列暗号化）をアプリケーションから使ってみた

やってみた（けれど微妙だった）

GitHub リポジトリ

サンプル画面

問題点

テーブル定義など

コード

Account.tsx（関連部分のみ）

というわけで

`Account.tsx`（関連部分のみ）