こちらは PostgreSQL Advent Calendar 2022 12 日目の記事です。
昨日は take_3 さんの PostgreSQL用高機能ページャ pspg で表を見やすくする でした。
そしてこの記事は、
の続きです。
やってみた（けれど微妙だった）
以前こちらの記事で作ったサンプルアプリのプロフィール画面に、「秘密の情報」として 1 項目、透過的列暗号化を使う項目を追加してみました。
GitHub リポジトリ
サンプル画面
使い方がよくわからないまま試行錯誤してみたのですが、結果的に 実用レベルの実装はできなかった です。
もちろんこれは 使い方がよくわからないまま試行錯誤した結果 なので、使い方が間違っているのが原因、の可能性もあります。
また、今後（場合によっては近日中？）のアップデートで実用レベルになる可能性もあります。
問題点
- 2 つ以上のテーブルで暗号化の設定ができない
- これは前回の記事に書いたとおり
- 更新時の処理が期待どおりに走らない
-
upsert()で処理を書くと更新できたりできなかったり不安定 -
select()の結果でinsert()かupdate()かを分岐して処理する場合、update()の処理が動かない（リクエストは受け付けられるが更新されない） -
select()後、delete()とinsert()の処理を分けて行う場合、delete()のリクエストは受け付けられるが行が消されない
-
- （認証済みユーザーに対して使う場合）
authenticatedに対して広めの権限を付けないといけない -
publicスキーマに復号用ビューを参照するビューを（二重に）作らないと使いづらい - Supabase CLI で TypeScript の型定義を出そうとすると、pgsodium が生成するビューの権限の問題でエラーになる
テーブル定義など
こんな感じで作りました（今回の実装に関係する追加分のみ）。
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制約を外しました。
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 );
security label for pgsodium
on column privates.secret_note
is 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userid) NONCE nonce';
TCE よりも先に RLS の設定をしておく必要があるようです（TCE を先にすると RLS の設定時にエラーになりました）。
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（関連部分のみ）
const [secretNote, setSecretNote] = createSignal<string>("");
秘密の情報用の Signal（ステート）です。
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（プロフィール情報読み取り）の中で呼び出しています。
const note = await getPrivate();
if (note) {
// @ts-ignore
setSecretNote(note.decrypted_secret_note);
} else {
setSecretNote("");
}
行が取れなかったときは空文字を Signal に入れています。
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（プロフィール情報書き込み・更新）の中で呼び出しています。
await updatePrivate();
<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 の型定義を出そうとしたもののエラーになり、うまく型情報を適用できなかったことが原因です。
% 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.
こちらの記事を参考にして試しましたがダメでした。
エラーの後、手作業でそれらしく作ろうとしてみたのですが、アドベントカレンダーの担当日に間に合いませんでした。
というわけで
（もちろん、やり方が悪かった可能性もありますが）実利用にはまだ早いようです。
現状では透過的に暗号化するのではなく、コード側で明示的に暗号化するほうが楽なようです。
また、現状では鍵のローテーションを支援するような機能が実装されているようには見えないので、よりそのような印象を受けました。
明日は yohsita_ さんです。