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

こちらは **[PostgreSQL Advent Calendar 2022](https://qiita.com/advent-calendar/2022/postgresql)** 12 日目の記事です。

昨日は take_3 さんの **[PostgreSQL用高機能ページャ pspg で表を見やすくする](https://qiita.com/take_3/items/2454fedda7723ea1c436)** でした。

そしてこの記事は、

- **[Supabase で TCE（透過的列暗号化）を軽く試してみた](https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0)**

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

の続きです。

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

以前[こちら](https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea)の記事で作ったサンプルアプリのプロフィール画面に、「秘密の情報」として 1 項目、透過的列暗号化を使う項目を追加してみました。

- **[SolidJS で Supabase の Row Level Security を試してみた](https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea)**

https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea

### GitHub リポジトリ

- **[pgunconf-sample-app](https://github.com/hmatsu47/pgunconf-sample-app)**

https://github.com/hmatsu47/pgunconf-sample-app

### サンプル画面

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/90d16e55-90ac-3403-80b6-7e0f5547e93e.png)

使い方がよくわからないまま試行錯誤してみたのですが、結果的に **実用レベルの実装はできなかった** です。

:::note warn
もちろんこれは **使い方がよくわからないまま試行錯誤した結果** なので、使い方が間違っているのが原因、の可能性もあります。
また、今後（場合によっては近日中？）のアップデートで実用レベルになる可能性もあります。
:::

### 問題点

- 2 つ以上のテーブルで暗号化の設定ができない
  - これは[前回の記事](https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0#%E5%AF%BE%E8%B1%A1%E5%88%97%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A61-%E8%A1%8C%E3%81%94%E3%81%A8%E3%81%AB%E9%8D%B5-id-%E3%82%92%E5%A4%89%E3%81%88%E3%81%A6%E6%9A%97%E5%8F%B7%E5%8C%96%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B)に書いたとおり
- 更新時の処理が期待どおりに走らない
  - `upsert()`で処理を書くと更新できたりできなかったり不安定
  - `select()`の結果で`insert()`か`update()`かを分岐して処理する場合、`update()`の処理が動かない（リクエストは受け付けられるが更新されない）
  - `select()`後、`delete()`と`insert()`の処理を分けて行う場合、`delete()`のリクエストは受け付けられるが行が消されない
- （認証済みユーザーに対して使う場合）`authenticated`に対して広めの権限を付けないといけない
- `public`スキーマに復号用ビューを参照するビューを（二重に）作らないと使いづらい
- [Supabase CLI](https://supabase.com/docs/reference/cli) で [TypeScript の型定義](https://supabase.com/docs/reference/javascript/typescript-support)を出そうとすると、[pgsodium](https://github.com/michelp/pgsodium) が生成するビューの権限の問題でエラーになる

### [テーブル定義など](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/db-create.sql#L117)

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

```sql:鍵IDを生成
select id from pgsodium.create_key();
```

```sql:秘密の情報用テーブルを作成
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)
);
```

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

```sql: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 );
```

```sql:TCEを設定
security label for pgsodium
  on column privates.secret_note
  is 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userid) NONCE nonce';
```

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

```sql:復号用ビューを参照するビューを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;
```

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

```sql:権限追加
grant select on pgsodium.valid_key to authenticated;
grant execute on all functions in schema pgsodium to authenticated;
```

:::note
付けたくなかった権限ですが、前者がないと`permission denied for view valid_key`、後者がないと`permission denied for function crypto_aead_det_decrypt`のエラーが発生しました。
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/774f73b6-a7c6-8bd5-f269-9cde531c16ae.png)

### コード

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

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

#### [`Account.tsx`](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/src/Account.tsx)（関連部分のみ）

- **[Signal 追加部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L34)**

```tsx:Account.tsx（Signal追加部分）
  const [secretNote, setSecretNote] = createSignal<string>("");
```

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

- **[DB からの読み取り部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L50)**

```tsx: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`をキーにして復号後のデータを読み取っています。

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

- **[同・呼び出し部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L88)**

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

```tsx:Account.tsx（プロフィール情報読み取りからの呼び出し部分）
      const note = await getPrivate();

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

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

- **[DB への書き込み（更新）部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L108)**

```tsx: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()`で情報が暗号化されずに更新されてしまい、読み取り時に復号不能でエラーが発生。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/870272a7-d200-a377-e4fa-00fa90e4e4e9.png)

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

:::note
（[前の記事の冒頭](https://qiita.com/hmatsu47/items/8de48e81a660eabe4bf0#%E3%81%96%E3%81%A3%E3%81%8F%E3%82%8A%E3%81%A9%E3%82%93%E3%81%AA%E6%A9%9F%E8%83%BD)でも触れましたが）透過的列暗号化の目的として **「平文のデータをログ（WAL）に書き出さないようにする」** があります。
それなのに、BEFORE UPDATE に対応していないせいで **アプリケーション開発者がうっかり`update()`する処理を書いて実行してしまうとデータが平文で漏れる、** というのはちょっといただけないですね。
少なくともエラーを出すなどして、書き出されるのを止めて欲しいものです。
:::

続いて、

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

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

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

最終的に、

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

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

- **[同・呼び出し部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L159)**

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

```tsx:Account.tsx（プロフィール情報書き込みからの呼び出し部分）
      await updatePrivate();
```

- **[入力欄表示部分](https://github.com/hmatsu47/pgunconf-sample-app/blob/9c2e54765c5186b5452e8053147f50935aedfd00/src/Account.tsx#L268)**

```tsx: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](https://supabase.com/docs/reference/cli) で [TypeScript の型定義](https://supabase.com/docs/reference/javascript/typescript-support)を出そうとしたもののエラーになり、うまく型情報を適用できなかったことが原因です。

```sh: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.
```

:::note
こちらの記事を参考にして試しましたがダメでした。
- **[SupabaseのTS用の型情報を自動生成する](https://zenn.dev/k_kind/articles/supabase-type-generate)**
:::

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

## というわけで

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

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

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

---

明日は yohsita_ さんです。

- **[DBのマイグレーションツールを使ったみた（OracleからPostgreSQL）](https://qiita.com/yohsita_/items/1cd4d5f7bb4564c822fc)**

https://qiita.com/yohsita_/items/1cd4d5f7bb4564c822fc