SolidJS で Supabase の Row Level Security を試してみた

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

• https://qiita.com/hmatsu47
• https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
• https://speakerdeck.com/hmatsu47/aurora-mysql-v1-nil-v3-falseyi-xing-diao-cha-ji-hua-bian

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

• https://speakerdeck.com/hmatsu47/fluttertesupabasefalsepostgresql-with-postgiswoshi-sitemita

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

• https://supabase.com/docs/guides/with-solidjs

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

• https://zenn.dev/hmatsu47/articles/solidjs-suid-sample
• https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea

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

• https://www.solidjs.com/

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

• https://github.com/supabase/supabase-js
• https://postgrest.org/en/stable/

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

• https://www.supabase.jp/docs/guides/auth#仕組み

テーブル①（プロフィール情報） 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) ); ● auth.users は認証済みユーザ情報の Supabase 組み込みテーブル 15

テーブル①の RLS 設定 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 ); ● auth.uid() は JWT に含まれるユーザ ID（UID）を返すヘルパー関数 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

ストレージに関する定義 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' ); ● Database と同じようにアクセス制御を定義 ● この例では認証前のユーザにもアップロードを許可している ■ 公式サンプルの実装のまま 19

JS で Supabase 接続
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!);

● API の URL と匿名キーを使って接続
○

https://www.supabase.jp/docs/guides/api#api-url%E3%81%A8%E3%82%AD%E3
%83%BC

20

• https://www.supabase.jp/docs/guides/api#api-urlとキー

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

• https://www.supabase.jp/docs/guides/auth/auth-magic-link

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

• https://www.supabase.jp/docs/guides/auth/auth-github

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

.eq() で絞り込まなくても
SELECT 権限があるデータ
だけ取得可能

23

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

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

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

JS で DB へデータを挿入・更新（INSERT・UPDATE）
const { data, error } = await (isInsert ? (
z

supabase
.from('articles')
.insert(updates)
) : (
supabase
.from('articles')
.update(updates)
.match({ id: props.article!.id }))

RLS 違反の場合はエラーに

);

※削除（DELETE）は省略
27

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

テーブル②の RLS 設定等修正 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); RLS とは別に CHECK 制約も追加 ● 結合対象の投稿者行がないとエラーに ○ note_type だけを変えようとしてもエラーに 32

投稿情報の INSERT後に投稿者を INSERT
if (isInsert) {
// 新規投稿→投稿者を登録
const author = {
id: data![0]?.id!,
updated_at: new Date(data![0]?.updated_at),
userid: props.session.user!.id
};
addAuthor(author);

※addAuthor() の内容および削除処理の実装は省略

33

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

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