Supabaseのテーブル設計で詰まったこととその対策

要約

supabaseでRLSを用いてバックエンドを実装せずにWebアプリを構築しようとして詰まったことと、その対策のメモです。

BaaSとRLSについて

BaaS(Backend as a Service)は、通常バックエンドで実装する機能である認証、ユーザセッション管理、DBアクセスなどをAPIとして提供してくれます。supabaseはBaaSの一つです。

さて多くのWebアプリでは、フロントエンドから自由にDBアクセスできてしまうとセキュリティ上の問題があるため、権限に応じたデータのアクセス制御を行う必要があります。
このアクセス制御を実現する方法の一つがRow Level Security(RLS)です。supabaseではDBMSとしてPostgresを使っているため、PostgresのRLS機能を使います。

やったこと

supabaseを使って、クイズアプリのデータベースを作ります。要件は以下の通りです。

• アプリのユーザはsupabaseのユーザに紐づく。
• クイズは問題と答えの組から構成される。
• ユーザは問題と答えの組を入力してクイズを出題することができ、出題したユーザがそのクイズの所有者となる。
• ユーザは他のユーザが作成したクイズの参加者となることができる。
• クイズが未解決の間、参加者は問題のみ見ることができ、答えを見ることはできない。
• クイズが解決済みの場合、参加者は問題と答えを見ることができる。
• 所有者はいつでもクイズの問題と答えを閲覧・更新することができる。

テーブル設計(うまくいっていない)

まずは直感に沿ってテーブル設計してみます。

テーブル定義

create table players(
    id uuid, -- ユーザID
    player_name text not null,
    primary key(id),
    foreign key(id) references auth.users(id)
);
create table quizzes(
    id uuid,
    owner_id uuid, -- 所有者のユーザID
    question text not null, -- 問題
    answer text not null, -- 答え
    solved boolean not null default false, -- true:解決済み false:未解決
    primary key(id),
    foreign key(owner_id) references players(id)
);
create table quiz_players(
    player_id uuid, -- 参加者のユーザID
    quiz_id uuid,
    primary key(player_id, quiz_id),
    foreign key(player_id) references players(id),
    foreign key(quiz_id) references quizzes(id)
);

RLSの定義

alter table quizzes enable row level security;
alter table quiz_players enable row level security;
create policy "クイズ参加者はクイズを参照できる。"
  on quizzes for select
  using (
    exists
    (
      select 1 from quiz_players qp
      where qp.player_id = auth.uid()
      and qp.quiz_id = quizzes.id
    )
  );
create policy "クイズ所有者はクイズを参照、作成、更新、削除できる。"
  on quizzes for all
  with check
  (
    auth.uid() = owner_id
  );

create policy "ユーザは他の人が作成したクイズに参加できる。"
  on quiz_players for insert
  with check
  (
    exists
    (
        select 1 from quizzes q
        where quiz_players.player_id = auth.uid()
        and quiz_players.player_id <> q.owner_id
    )
  );

何がうまくいかなかったか

「クイズが未解決の間、参加者は問題のみ見ることができ、答えを見ることはできない。」に対応するアクセス制御が設定できていません。RLSで「参加者は問題のみ見ることができ」を実現するためにはquizzesの参照を行単位で行う必要があるため、「答えを見ることはできない」と矛盾するからです。
もしバックエンドを書くならquizzes.solved = falseならquizzes.answerを返さない、のようなロジックを実装するところです。
supabaseにはEdge Functionsというサーバレスファンクション(AWS LambdaやGoogle Cloud Functionsに相当)機能が存在するので、ここだけそれを使っても良いのですが、今回はBaaS+フロントエンドで完結させる前提でやりたいので、テーブル設計を見直すことで対策しました。

テーブル設計(修正後)

答えを別テーブルに切り出して、新たにRLSを設定します。

テーブル定義

create table players(
    id uuid, -- ユーザID
    player_name text not null,
    primary key(id),
    foreign key(id) references auth.users(id)
);
create table quizzes(
    id uuid,
    owner_id uuid, -- 所有者のユーザID
    question text not null, -- 問題
    solved boolean not null default false, -- true:解決済み false:未解決
    primary key(id),
    foreign key(owner_id) references players(id)
);
create table quiz_answers( -- テーブルを追加
    quiz_id uuid,
    answer text not null, -- 答え
    primary key(quiz_id),
    foreign key(quiz_id) references quizzes(id)
);
create table quiz_players(
    player_id uuid, -- 参加者のユーザID
    quiz_id uuid,
    primary key(player_id, quiz_id),
    foreign key(player_id) references players(id),
    foreign key(quiz_id) references quizzes(id)
);

RLSの定義

alter table quizzes enable row level security;
alter table quiz_answers enable row level security;
alter table quiz_players enable row level security;
create policy "クイズ参加者はクイズを参照できる。"
  on quizzes for select
  using (
    exists
    (
      select 1 from quiz_players qp
      where qp.player_id = auth.uid()
      and qp.quiz_id = quizzes.id
    )
  );
create policy "参加者は解決済みのクイズの答えを見ることができる。" -- ポリシーを追加
  on quiz_answers for select
  using (
    exists
    (
      select 1 from quizzes q
      where q.id = quiz_answers.quiz_id
      and q.solved = true
    )
  );
create policy "クイズ所有者はクイズを参照、作成、更新、削除できる。"
  on quizzes for all
  with check
  (
    auth.uid() = owner_id
  );
create policy "クイズ所有者はクイズの答えを参照、作成、更新、削除できる。" -- ポリシーを追加
  on quiz_answers for all
  with check
  (
    exists
    (
      select 1 from quizzes q
      where q.owner_id = auth.uid()
      and  q.id = quiz_answers.quiz_id
    )
  );
create policy "ユーザは他の人が作成したクイズに参加できる。"
  on quiz_players for insert
  with check
  (
    exists
    (
        select 1 from quizzes q
        where quiz_players.player_id = auth.uid()
        and quiz_players.player_id <> q.owner_id
    )
  );

何を変えたのか

RLSでデータへのアクセス制御を行うためには、テーブル設計を工夫する必要があります。具体的にはポリシーを設定すべき単位でテーブルを分割すればうまくいきました。
ただし、データのまとまりとしては問題と答えはセットで使うことが多いため、頻繁にテーブルの結合が発生してしまいます。アプリの規模が大きくなってくると、性能との兼ね合いも考える必要があります。