全ての記事へ戻る

PostgreSQLの「謎の重さ」を退治せよ！RLS関数を爆速にする3つの秘策

#postgresql#supabase#security#performance#rls

SupabaseやPostgreSQLでRLS（行単位セキュリティ）を使いこなしている皆さん、こんにちは。

「データ件数はたった2,000件弱。クエリも単純。なのに、なぜかデータベースが重い……。pg_stat_activityを覗くと、そこにはまるで渋滞の先頭で立ち往生するクエリの姿が……」

そんな絶望を味わったことはありませんか？実はその原因、**「良かれと思って書いたセキュリティ関数」**にあるかもしれません。今回は、1,800件程度のデータでさえ青息吐息にさせてしまうRLSの罠と、その解決策をユーモアを交えて解説します。

1. 現場検証：なぜ君はそんなに遅いのか？

今回の容疑者は、PostgREST（Supabase）経由で発行されたこんなクエリです。

-- 犯人の姿（イメージ）
SELECT * FROM items WHERE RLSポリシーの壁;

一見普通ですが、裏側では「全件カウント（Exact Count）」と「RLSの関数」が組み合わさり、PostgreSQL内部で**「1行ごとにセキュリティ担当（関数）を呼び出して身分証（auth.uid）を確認させる」**という、超非効率な検問が行われていました。

1,800回も「君、誰？」「あ、この人です」「君、誰？」「あ、この人です」と繰り返していたら、そりゃあクエリも進みません。

2. 改造：セキュリティ関数を「プロフェッショナル」へ

元々の関数は、ピュアな LANGUAGE sql で書かれていました。悪くないのですが、実はもっと「賢く」できます。

改善前：生真面目すぎる関数

CREATE FUNCTION auth.check_role(_role text) RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM staff_roles
    WHERE user_id = auth.uid() AND role = _role -- 毎回auth.uid()を聞きに行く
  );
$$ LANGUAGE sql STABLE;

改善後：デキる関数の3か条

以下の3つの魔改造を施しました。

  1. PL/pgSQLへの転生: DECLARE で auth.uid() の結果を変数にブチ込みます。これで「君、誰？」と聞くのは1回だけで済みます。
  2. SECURITY DEFINERの盾: 「この人が確認してるならOK！」と権限チェックのステップをショートカットします。
  3. スカラーサブクエリの活用: ポリシー側で (SELECT auth.has_role(...)) と書くことで、PostgreSQLに「これ1回計算したら使い回していいよ」と伝えます。

3. 爆速化した関数たちの全貌（汎用版）

特定の業務（病院やグループなど）を連想させないよう、汎用的な「組織・スタッフ」構造にリファクタリングしたコードがこちらです。

-- 【秘伝】RLS高速化テンプレート

-- 1. 基本のキ：役割チェック
CREATE OR REPLACE FUNCTION auth.has_role(_role text)
 RETURNS boolean LANGUAGE plpgsql STABLE SECURITY DEFINER
AS $function$
DECLARE
  _uid uuid := auth.uid(); -- 最初に1回だけ取得するのがコツ
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.staff_roles
    WHERE user_id = _uid AND role = _role
  );
END; $function$;

-- 2. 応用：所属組織の管理権限チェック
CREATE OR REPLACE FUNCTION auth.is_org_manager(_org_id uuid)
 RETURNS boolean LANGUAGE plpgsql STABLE SECURITY DEFINER
AS $function$
DECLARE
  _uid uuid := auth.uid();
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.organizations o
    JOIN public.staff_roles sr ON o.group_id = sr.group_id
    WHERE sr.user_id = _uid
      AND sr.role = 'manager'
      AND o.id = _org_id
  );
END; $function$;

4. 仕上げ：ポリシーの書き方でトドメを刺す

関数を直したら、ポリシーの書き方も一工夫。

-- 修正前
USING ( auth.has_role('admin') )

-- 修正後（カッコで囲んでSELECTするのがポイント）
USING ( (SELECT auth.has_role('admin')) )

この「カッコ」ひとつで、PostgreSQLのオプティマイザは**「おっ、これは行ごとに計算しなくていい固定値だな？」**と気づいてくれます。1,800回の検問が、たった1回の顔パスに変わる瞬間です。

まとめ：パフォーマンスは「気遣い」から

データ件数が少ないのに重い。そんな時は、データベースが裏側で「生真面目すぎる仕事」をしていないか疑ってみてください。

  • auth.uid() を何度も呼ばない
  • SECURITY DEFINER で身内を信じる
  • SELECT のカッコで実行計画を導く

これだけで、あなたのSupabaseアプリは羽が生えたように軽くなるはずです。それでは、快適なバックエンドライフを！