13.1K Views
May 31, 22
スライド概要
第 33 回 PostgreSQL アンカンファレンス@オンライン 2022/5/31
Qiita や Zenn でいろいろ書いてます。 https://qiita.com/hmatsu47 https://zenn.dev/hmatsu47 MySQL 8.0 の薄い本 : https://github.com/hmatsu47/mysql80_no_usui_hon Aurora MySQL v1 → v3 移行計画 : https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
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/fluttertesupabasefalsepostgresqlwith-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 を使う ● 公式マニュアルの日本語訳によると 仕組み 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
テーブル①(プロフィール情報) 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
JS で Supabase 認証①(メールでマジックリンク送信) const { error } = await supabase .auth .signIn({ email: email() }); ● https://www.supabase.jp/docs/guides/auth/auth-magic-link ● 届いたメールのリンクをクリックするとログインできる 21
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
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