LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@NatumeSousaku

Supabase RLSの循環参照問題をSecurity Definer関数で解決する方法

Posted at

最近話題になっているSupabaseのセキュリティ問題。自分も触ってみてこんなポストをしました。

このポストに対して共感する意見も多かったのですが、Supabaseの開発をしているエンジニアのタイラーさんからこんなリプライをいただきました。

今回はこれを実際に実装して循環参照を解消してみたいと思います。

そのまえにまず前提となる知識を示します

Database Function（データベース関数）とは

PostgreSQLでは、SQLやPL/pgSQLのコードをデータベース上に保存し、
サーバー側で実行できる関数のことを指します。Supabaseでも利用可能です。

SECURITY DEFINER の役割

SECURITY DEFINER を付けると、関数は呼び出し元ではなく、
関数を作成したユーザーの権限で実行されます。

これにより、通常のユーザー権限ではできない処理を、
関数内で安全に実行できます。

注意点

強力な機能ですが、関数内で適切な権限チェックをしないと、
不正アクセスのリスクがあるため注意が必要です。

前提知識は以上となります！それでは実際に実装してみましょう

実装してみる

テーブル定義

スクリーンショット (122).png

✅ 要件

  1. ユーザーは 自分が owner の secret のみ追加できる。
    （→ secrets テーブルの INSERT に対する条件）

  2. ユーザーは 自分が owner の secret に対してのみ secret_viewers を追加できる。
    （→ secret_viewers テーブルの INSERT に対する条件）

  3. ユーザーは 自分が owner または viewers に含まれる secret のみ閲覧可能。
    （→ secrets テーブルの SELECT に対する条件）

  4. ユーザーは 自分が owner の secret または自分自身が対象の secret_viewers のみ閲覧可能。
    （→ secret_viewers テーブルの SELECT に対する条件）

⚠️ 本来であれば DELETE や UPDATE に対しても RLS を設定すべきですが、
本記事では説明の簡潔さを重視し、これらのポリシーは割愛しています。

🔑 この要件で最も重要なポイント

owner はその secret に紐づくすべての viewers を確認できますが、
viewer 側は「自分が viewer として追加されていること」はわかっても、
他に誰が viewer なのかは一切わからないようにしたい という要件が存在します。

この非対称な閲覧権限を実現することが、RLS（行レベルセキュリティ）ポリシーの設計において最も難しい点でした。

RLS の基本は「ユーザーごとに見える行を制御する」ことですが、
今回のように 同じテーブルに対して複数の立場（owner / viewer）で異なる可視性を持たせる 場合、
相互参照や条件分岐が複雑になりやすく、循環参照やアクセス漏れに注意が必要です。

🔁 試してみる 3 種類の RLS 実装パターン

  1. 循環参照が発生する RLS
  2. 循環参照を回避したが、要件を満たしていない RLS
  3. Database Function を使った RLS（SECURITY DEFINER）

見ていきましょう！

1. 循環参照が発生する RLS

  • secrets テーブルの INSERT ポリシー：
alter policy "Allow INSERT based on owner_id"
on "public"."secrets"
to authenticated
with check (
  (( SELECT auth.uid() AS uid) = owner_id)
);
  • secret_viewers テーブルの INSERT ポリシー
alter policy "Allow INSERT only if user owns the secret"
on "public"."secret_viewers"
to authenticated
with check (
  -- 1. ユーザーがその secret の owner であるか確認
  EXISTS (
    SELECT 1
    FROM secrets
    WHERE secrets.id = secret_viewers.secret_id
      AND secrets.owner_id = auth.uid()
  )
);

この INSERT ポリシーは、次に紹介する 2 種類の RLS パターンでも共通で使用するため、以後は省略します。

  • secrets テーブルの SELECT ポリシー
alter policy "Allow SELECT if owner or listed viewer"
on "public"."secrets"
to authenticated
using (
  -- 1. ユーザーが owner の場合は閲覧可能
  owner_id = auth.uid()
  -- 2. そうでない場合、secret_viewers テーブルに viewer として登録されているか確認
  OR EXISTS (
    SELECT 1
    FROM secret_viewers
    WHERE secret_viewers.secret_id = secrets.id
      AND secret_viewers.viewer_id = auth.uid()
  )
);
  • secret_viewers テーブルの SELECT ポリシー
create policy "Allow SELECT if viewer or secret owner"
on public.secret_viewers
as permissive
for select
to authenticated
using (
  -- 1. 自分自身が viewer の場合
  viewer_id = auth.uid()
  -- 2. または、該当する secret の owner である場合
  OR EXISTS (
    SELECT 1
    FROM secrets
    WHERE secrets.id = secret_viewers.secret_id
      AND secrets.owner_id = auth.uid()
  )
);

以上が4つの条件をそのままSQLに落とし込んだポリシーです。
一見すると要件をしっかり満たしているように見えます。

しかし、実際にデータを挿入・参照しようとすると、以下のようなエラーが発生します。

スクリーンショット (110).png

これは、secrets テーブルのポリシー内で 無限ループ（循環参照）が発生している ことを示しています。

なぜこの問題が起きるのか？

  • secrets の SELECT ポリシー内で secret_viewers テーブルを参照しています。
  • 一方で、secret_viewers の SELECT ポリシー内でも逆に secrets テーブルを参照しています。

つまり、こういう構図です

  1. secrets の閲覧権限を確認するために secret_viewers をチェックしたい。
  2. しかし secret_viewers のポリシーを評価するには secrets テーブルも参照が必要。
  3. その結果、ポリシー評価の際に secrets と secret_viewers が互いに依存しあい、無限ループに陥る。

重要なポイント

  • PostgreSQLのRLSは、ポリシー内で参照するテーブルにも同様にRLSが適用されます。
  • したがって、RLSのポリシー内で相互にテーブルを参照する設計は、無限再帰（循環参照）を引き起こす原因となります。

この問題を解決するために、次の章では

  • 循環参照を回避するためのRLS設計（ただし要件は一部満たさない）
  • Database Function（SECURITY DEFINER）を活用したより柔軟な設計

を検討していきます。

循環参照が発生しないように変更したが要件を満たしていない RLS

循環参照の問題を解決するために、私が考えたアプローチを紹介します。

問題の本質は、secrets と secret_viewers の両方の SELECT ポリシーが互いを参照していること にあります。
（INSERT ポリシーは他テーブルを参照することがなく、この問題とは無関係です。）

そこで、どちらか一方のポリシーから相互参照を取り除く必要があります。

secrets テーブルには「秘密にしたいデータ」が含まれているため、
参照を削除するのは secret_viewers テーブル側のポリシーにしました。

以下に、その変更を加えたsecret_viewersのポリシーを示します。
（その他の3つのポリシーは変更せず、従来通りです。）

create policy "Allow SELECT for authenticated users"
on public.secret_viewers
as permissive
for select
to authenticated
using (
  true
);

この変更により、secret_viewers テーブルの SELECT ポリシーは、
using を true とし、認証済みユーザーは誰でもアクセス可能としました。

auth.uid() = viewer_id としなかった理由は、
owner が自分の viewers を閲覧できなくなってしまう問題を避けるためです。

この対応により、循環参照の問題は解消されました。

しかし、別の問題が発生します。
それは、要件で示した以下の条件に反することです。

viewer は自分以外の viewer を見られないようにしたい。

実際にデータを見ると、下図のように複数の viewers が見えてしまうことが確認できます。

スクリーンショット (121).png

つまり、今回の要件の 4番目「viewerが自分以外の viewers を閲覧できない」 を満たしていません。

とはいえ、この RLS が 全く使えないわけではありません。
むしろ4つの要件のうち3つは満たしており、

  • secrets テーブルの secret カラムは viewer 以外から見られない。
  • secret_viewers に不正に viewer が追加されることも防止できる。

したがって、要件やユースケースによってはこの RLS で十分な場合もあります。
実際、viewer 同士が誰がいるか見えても問題ないケースも想定されるでしょう。

しかし今回は、viewer は他の viewers を見られないようにしたい ため、
この RLS では要件を満たしません。

そこで、循環参照が発生しないことを担保しつつ、要件も満たす別の方法へ移ります。

database functionを使ったRLS

まず、SupabaseのSQLエディタを開き、以下のコードを実行して新しいデータベース関数を定義します。

-- 権限チェック用の関数
CREATE OR REPLACE FUNCTION can_access_secret(secret_id uuid, owner_id uuid)
RETURNS boolean
SECURITY DEFINER  -- これによりRLSが無視されるようになります。
AS $$
BEGIN
  -- 所有者チェック
  IF owner_id = auth.uid() THEN
    RETURN true;
  END IF;
  
  -- 閲覧者チェック
  IF EXISTS (
    SELECT 1 FROM secret_viewers 
    WHERE secret_viewers.secret_id = $1 AND secret_viewers.viewer_id = auth.uid()
  ) THEN
    RETURN true;
  END IF;
  
  RETURN false;
END;
$$ LANGUAGE plpgsql;

ポイント：SECURITY DEFINER の重要性

SECURITY DEFINER を付けない場合、通常のデータベース関数は
テーブルへのアクセス時にも RLS が有効となり、
結果として先ほどのような循環参照の問題が再発します。

しかし、SECURITY DEFINER を指定すると、
関数は管理者権限で実行されるため、
RLS を一時的に無視して処理を行うことが可能になります。

⚠️ 注意点
SECURITY DEFINER を使う関数は強力ですが、
関数の中で適切な権限チェックを実装しないと、
悪用されるリスクがあるため慎重に設計してください。

この関数を定義した後、改めて RLS ポリシーの設定を行います。

secrets テーブルの SELECT ポリシー

alter policy "Allow SELECT if owner or listed viewer"
on "public"."secrets"
to authenticated
using (
  can_access_secret(id, owner_id)
);

secret_viewers テーブルの SELECT ポリシー

alter policy "Allow SELECT for owner or viewer"
on "public"."secret_viewers"
to public
using (
  viewer_id = auth.uid()
  OR EXISTS (
    SELECT 1 
    FROM secrets
    WHERE id = secret_id
      AND owner_id = auth.uid()
  )
);

secret_viewers の RLS ポリシーは、
以前の循環参照が発生していたポリシーと同じ内容です。

一方で、secrets の RLS ポリシーは、
先ほど登録した database function を使用しています。

ポイント

  • secret_viewers は RLS が有効な状態で secrets テーブルを参照していますが、
  • secrets 側は SECURITY DEFINER によって RLS が無効化された状態で
    secret_viewers にアクセスしています。

このため、循環参照のエラーが発生しません！

以下のスクリーンショットのように、
意図した通りに viewer は一人だけ表示されています。（実際の DB では二人存在）

スクリーンショット (119).png

この設計により、RLS の循環参照問題を回避しつつ、
正確な権限管理が実現できています。

まとめ

結論：

悪いのはSupabaseではなく、正しくRLSを設定できない開発者です。

タイラーさん、浅い知識で生意気言ってすみませんでした！！！

とはいえ、RLSですべてを完璧に設定できない場合もあります。
そのため、二重の防護策として以下を推奨します。

  • anon keyをフロントに流出させない
  • バックエンド側でもバリデーションを行う

これによりセキュリティの向上が確実に見込めます。

それぞれの特性を理解して、適切に開発していきましょう！

最後まで読んでいただき、ありがとうございました！

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?