SolidJS で Supabase の Row Level Security を試してみた 第 33 回 PostgreSQL アンカンファレンス@オンライン　 2022/5/31 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ○ Aurora MySQL v1 → v3 移行中 ■ https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book ■ https://speakerdeck.com/hmatsu47/aurora-mysql-v1-nil-v3-falseyi-xing-diao-c ha-ji-hua-bian 2

前回（第 29 回）の発表ネタ ● Flutter で地図アプリを作ってみた ● その中で Supabase の PostgreSQL を試してみた ○ 単純クエリはクエリビルダを使って問い合わせ ○ クエリビルダでは関数が書けない→ PostGIS を使う場合はスト アドファンクションを RPC で呼び出す ● https://speakerdeck.com/hmatsu47/fluttertesupabasefalsepostgresql- with-postgiswoshi-sitemita 3

今回の発表ネタ ● SolidJS から Supabase を使ってみた ○ SolidJS : React ライクで仮想 DOM を使わない UI ライブラリ ● Row Level Security（以降 RLS と表記）を使ったデータ行 のアクセス制御を試してみた ○ 公式ドキュメント Quickstart のサンプルを改造 ■ https://supabase.com/docs/guides/with-solidjs ● 課題・問題点 4

関連記事 ● https://zenn.dev/hmatsu47/articles/solidjs-suid-sample ○ SolidJS & SUID（Material-UI）を中心に ● https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea ○ RLS を中心に 5

SolidJS とは？（今回のメインではないので軽めに） ● リアクティブ JavaScript（以降 JS と表記）ライブラリ https://www.solidjs.com/ ● React ライク ○ 通常は JSX（TSX）で記述 ● 仮想 DOM を使わない ○ 軽量・高速 6

Supabase とは？（おさらい） 7

Supabase とは？（おさらい） ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ ○ Database ← PostgreSQL が使われている／API 自動生成 ○ Authentication ○ Storage ○ Functions（Coming soon : 2022/8/1） 8

Supabase とは？（おさらい） ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ ○ Database ← PostgreSQL が使われている／API 自動生成 ○ Authentication ← 今回はここも使う ○ Storage ← 今回はここも使う ○ Functions（Coming soon : 2022/8/1） 9

今回のサンプル（1）ログイン（メール・GitHub） a. メールで送信したマジックリンク をクリック b. GitHub 認証 ● いずれの場合もメールアドレスで ユーザを識別 ○ GitHub アカウントでユーザ登録する とどちらの認証方法も使える 10

今回のサンプル（2）プロフィール編集 ● 名前と Web サイト（ホームペー ジ）URL の登録が可能 ● アバター画像のアップロードも可 能 ○ Storage に保存 11

今回のサンプル（3）短文投稿（RLS で表示制御） ● 一覧表示は RLS で制御 ○ アプリケーションでは表示／非表示 の制御をしていない ● 編集・削除アイコンはアプリケー ションで表示制御 ○ 投稿ユーザが他ユーザに与える許可 を選択（無／RO／RW）※削除除く ○ 実行可否は RLS で制御 12

JS から Supabase の PostgreSQL を使う ● Supabase を設定 ○ SQL editor でテーブル作成＆設定 ● JS からクエリビルダを使ってアクセス ○ クライアントライブラリとして supabase-js を使う ■ https://github.com/supabase/supabase-js ○ サービスでは内部的に PostgREST を使用 ■ https://postgrest.org/en/stable/ 13

JS から Supabase の Authentication を使う ● 公式マニュアルの日本語訳によると ● https://www.supabase.jp/docs/guides/auth#%E4%BB%95%E7%B5%84%E3%81%BF 14 仕組み 1. ユーザーがサインアップします。Supabaseは、auth.usersテーブルに新しいユーザーを作成します。 2. Supabaseは、ユーザーのUUIDを含む新しいJWTを返します。 3. データベースへのすべてのリクエストに、JWTも一緒に送信します。 4. PostgresはJWTを検査して、リクエストを行ったユーザーを特定します。 5. ユーザーのUIDは、行へのアクセスを制限するポリシーで使用できます。 Supabaseは、JWTからユーザーのUIDを抽出するPostgresの特別な関数、auth.uid()を提供しています。これは特に ポリシーを作成する際に便利です。

テーブル①（プロフィール情報） ● auth.users は認証済みユーザ情報の Supabase 組み込みテーブル create table profiles ( id uuid references auth.users not null, updated_at timestamp with time zone, username text unique, avatar_url text, website text, primary key (id), unique(username), constraint username_length check (char_length(username) >= 3) ); 15

テーブル①の RLS 設定 ● auth.uid() は JWT に含まれるユーザ ID（UID）を返すヘルパー関数 alter table profiles enable row level security; create policy "Public profiles are viewable by everyone." on profiles for select using ( true ); create policy "Users can insert their own profile." on profiles for insert with check ( auth.uid() = id ); create policy "Users can update their own profile." on profiles for update using ( auth.uid() = id ); 16

テーブル②（投稿情報） ● テーブル結合するときは外部キー制約が必要 create table articles ( id bigint generated by default as identity, updated_at timestamp with time zone, title text not null, note text, note_type int not null default 1, userid uuid not null, primary key (id), constraint title_length check (char_length(title) > 0) ); alter table articles add foreign key (userid) references profiles; 17

テーブル②の RLS 設定 alter table articles enable row level security; create policy "Users can view their own articles or disclosed articles." on articles for select using ( ( auth.uid() = articles.userid ) or ( note_type between 2 and 3 ) ); create policy "Users can insert their own articles." on articles for insert with check ( auth.uid() = articles.userid ); create policy "Users can update their own articles or free-updatable articles." on articles for update using ( ( auth.uid() = articles.userid ) or ( note_type = 3 ) ); create policy "Users can delete their own articles." on articles for delete using ( ( auth.uid() = articles.userid ) ); 18

ストレージに関する定義 ● Database と同じようにアクセス制御を定義 ● この例では認証前のユーザにもアップロードを許可している ■ 公式サンプルの実装のまま insert into storage.buckets (id, name) values ('avatars', 'avatars'); create policy "Avatar images are publicly accessible." on storage.objects for select using ( bucket_id = 'avatars' ); create policy "Anyone can upload an avatar." on storage.objects for insert with check ( bucket_id = 'avatars' ); 19

JS で Supabase 接続 ● API の URL と匿名キーを使って接続 ○ https://www.supabase.jp/docs/guides/api#api-url%E3%81%A8%E3%82%AD%E3 %83%BC import { createClient } from '@supabase/supabase-js'; const supabaseUrl = import.meta.env.VITE_SUPABASE_URL; const supabaseAnonKey = import.meta.env.VITE_SUPABASE_ANON_KEY; export const supabase = createClient(supabaseUrl!, supabaseAnonKey!); 20

JS で Supabase 認証①（メールでマジックリンク送信） ● https://www.supabase.jp/docs/guides/auth/auth-magic-link ● 届いたメールのリンクをクリックするとログインできる const { error } = await supabase .auth .signIn({ email: email() }); 21

JS で Supabase 認証②（GitHub 認証） ● https://www.supabase.jp/docs/guides/auth/auth-github ● GitHub の Applications → Authorized OAuth Apps で設定が必要 const { error } = await supabase .auth .signIn({ provider: provider }); 22

JS で DB からデータを取得（SELECT） const { data, error, status } = await supabase .from('articles') .select(` id, updated_at, title, note, note_type, userid, profiles ( username, avatar_url ) `) .order('updated_at', { ascending: false }); 23 .eq() で絞り込まなくても SELECT 権限があるデータ だけ取得可能

例えばこんなユーザがいて、 24

こんなデータが入っていると、 25

こんな感じで hmatsu47 ユーザに表示される 26

JS で DB へデータを挿入・更新（INSERT・UPDATE） ※削除（DELETE）は省略 const { data, error } = await (isInsert ? ( z supabase .from('articles') .insert(updates) ) : ( supabase .from('articles') .update(updates) .match({ id: props.article!.id })) ); 27 RLS 違反の場合はエラーに

ただしこのサンプルの RLS 設定には問題が ● note_type=3（他ユーザ読み取り＆編集可）の場合、投稿者以外が 編集したときに userid を上書きできてしまう ● userid の変更と同時に、note_type も変更できてしまう ● アプリケーション側で制限するしかないが、ブラウザの開発者ツール で容易に回避可能 create policy "Users can update their own articles or free-updatable articles." on articles for update using ( ( auth.uid() = articles.userid ) or ( note_type = 3 ) ); 28

というわけで、対策（注：一つの例です） ● 投稿者テーブルを作って投稿情報の INSERT 時に一緒に INSERT ● 投稿情報の UPDATE 時に結合対象となる投稿者テーブル 行の存在確認を RLS で定義 29

テーブル③（投稿者） create table authors ( id bigint not null, updated_at timestamp with time zone, userid uuid not null, primary key (id) ); alter table authors add foreign key (id) references articles; 30

テーブル③の RLS 設定 alter table authors enable row level security; create policy "Authenticated Users can view all article-authors." on authors for select using ( auth.role() = 'authenticated' ); create policy "Users can insert their own article-authors." on authors for insert with check ( auth.uid() = authors.userid ); create policy "Users can delete their own article-authors." on authors for delete using ( ( auth.uid() = authors.userid ) ); 31 読み取りを認証済みのユーザに限定 UPDATE 権限は付与しない

テーブル②の RLS 設定等修正 ● 結合対象の投稿者行がないとエラーに ○ note_type だけを変えようとしてもエラーに alter policy "Users can update their own articles or free-updatable articles." on articles using ( ( ( auth.uid() = articles.userid ) or ( articles.note_type = 3 ) ) and ( articles.userid = ( select userid from authors where articles.id = authors.id) ) ); alter table articles add constraint note_type_range check (note_type between 1 and 3); 32 RLS とは別に CHECK 制約も追加

投稿情報の INSERT後に投稿者を INSERT ※addAuthor() の内容および削除処理の実装は省略 if (isInsert) { // 新規投稿→投稿者を登録 const author = { id: data![0]?.id!, updated_at: new Date(data![0]?.updated_at), userid: props.session.user!.id }; addAuthor(author); 33

一件落着…してなかった ● 一連の INSERT・DELETE はトランザクション処理すべき ● Supabase では、クエリビルダを使ってトランザクション 処理できない ○ ストアドファンクションに処理内容を記述して RPC で呼び出す 必要がある ■ PostGIS のときと同じ 34

まとめ ● Supabase で RLS を使うと、比較的簡単にアクセス権限 のガードレール的なものを作ることができる ○ ただし、よく考えないと抜け穴ができる ● RLS で実装すべきはデータへのアクセス制御そのもの ○ ガードレールではない ● ストアドファンクション＆ RPC のお世話にならざるを得 ないケースもある 35