初めに

Reactの学習で、【2024年最新版】0からReactを勉強するならこのロードマップに従え！の課題4 (https://qiita.com/Sicut_study/items/7d8c6f309dddda1a3961) を挑戦したとき、Supabaseで構築したバックエンドの中間テーブル「card_skill」に設定したRow Level Security (RLS) ポリシーでパフォーマンス指摘を受けました。本記事では、その時の指摘内容と、どのように修正してパフォーマンスを改善したかを忘れないためにまとめます。

SupabaseのRLSは非常に強力で便利な機能ですが、誤ったポリシー設定はパフォーマンスに大きな影響を与える可能性があります。この記事が、同様の課題に直面している方の助けになれば幸いです。

テーブル構成

今回は、名刺を管理するためのアプリになり、名刺情報を管理する cards テーブル、スキル情報を管理する skill テーブル、そして名刺とスキルを紐付ける中間テーブル card_skill の3つのテーブルを使用しています。認証にはSupabaseのデフォルトの auth.users テーブルを利用しています。

auth.users テーブル

認証用のデフォルトテーブルです。id (UUID型) がSupabaseのユーザーIDとして使用されます。

column_name	data_type
id（uid）	UUID
email	text
省略・・・

cards テーブル（作成テーブル）

名刺情報を表示するときに使用するテーブルです。
card_id は新規作成時に名刺IDとして指定できるvarchar型で定義しています。auth_id は auth.users.id に外部キーとして紐付けられており、認証されたユーザーと連携します。認証したユーザーが新規作成・編集・削除などするために使用します。

今回、ユーザーが複数の名刺を作成できる仕様のため、card_id + auth_idを複合キーにしています。

colum_name	data_type	null	other
card_id	varchar	not null	PK
auth_id	UUID	not null	PK, FK（auth.users.id）
name	varchar	not null
description	text	not null
github_id	varchar	null
qiita_id	varchar	null
x_id	varchar	null
create_id	timestamptzone	not null	Asia/Tokyo (デフォルト値として設定)

-- "cards" テーブルを作成します(UIで一つずつ設定するのは手間のため)
CREATE TABLE public.cards (
  -- カラム定義
  card_id     varchar   NOT NULL,
  auth_id     uuid      NOT NULL DEFAULT auth.uid(),
  name        varchar   NOT NULL,
  description text      NOT NULL,
  github_id   varchar, -- NULLを許容する場合は NOT NULL を省略
  qiita_id    varchar,
  x_id        varchar,
  created_at  timestamptz NOT NULL DEFAULT (now() AT TIME ZONE 'Asia/Tokyo'),

  -- 制約定義
  -- card_id と auth_id の組み合わせを主キーに設定（複合主キー）
  CONSTRAINT cards_pkey PRIMARY KEY (card_id, auth_id),

  -- auth_id を auth.users テーブルの id カラムにリンクさせる外部キー制約
  -- ON DELETE CASCADE を付けると、認証ユーザーが削除された際に、そのユーザーのカードも自動的に削除されます
  CONSTRAINT cards_auth_id_fkey FOREIGN KEY (auth_id) REFERENCES auth.users (id) ON DELETE CASCADE

   -- card_id: 4~39文字、半角英数字、ハイフン、アンダーバーのみ
  CONSTRAINT check_card_id_format CHECK (
    card_id ~ '^[a-zA-Z0-9][a-zA-Z0-9_-]{3,38}$'
  ),

  -- name: 1~30文字
  CONSTRAINT check_name_length CHECK (
    length(name) BETWEEN 1 AND 30
  ),

  -- description: 1~500文字
  CONSTRAINT check_description_length CHECK (
    length(description) BETWEEN 1 AND 500
  ),

  -- github_id: nullでない場合、1~39文字、先頭と末尾が英数字、間は英数字とハイフン
  CONSTRAINT check_github_id_format CHECK (
    github_id IS NULL OR github_id ~ '^[a-zA-Z0-9]([a-zA-Z0-9-]{0,37}[a-zA-Z0-9])?$'
  ),

  -- qiita_id: nullでない場合、1~39文字、先頭が英数字、間は英数字とハイフン
  -- (GitHubのルールと似ているが、末尾のルールが少し緩い)
  CONSTRAINT check_qiita_id_format CHECK (
    qiita_id IS NULL OR qiita_id ~ '^[a-zA-Z0-9][a-zA-Z0-9-]{0,38}$'
  ),

  -- x_id: nullでない場合、4~15文字、英数字とアンダーバーのみ
  CONSTRAINT check_x_id_format CHECK (
    x_id IS NULL OR x_id ~ '^[a-zA-Z0-9_]{4,15}$'
  )
);

-- (オプション) テーブルとカラムにコメントを追加して、後から分かりやすくする
COMMENT ON TABLE public.cards IS 'ユーザーが作成する名刺情報';
COMMENT ON COLUMN public.cards.card_id IS 'ユーザーが設定する名刺ID';
COMMENT ON COLUMN public.cards.auth_id IS '所有者を示す認証ユーザーID';

RLSポリシー（RLS有効で認証外部キーの設定も有効になる）
認証ユーザーが自身で作成した名刺情報のみを操作できるように設定しています。

-- SELECT：認証ユーザーが自分のデータのみを閲覧できるようにする
alter policy "Enable users to view their own data only"
on "public"."cards"
to authenticated
using (
   (( SELECT auth.uid() AS uid) = auth_id)
);

    -- INSERT：認証ユーザーのみ挿入を有効にする (自身の認証IDを挿入する)
alter policy "Enable insert for authenticated users only"
on "public"."cards"
to authenticated
with check (
   (( SELECT auth.uid() AS uid) = auth_id)
);

-- UPDATE：認証ユーザーが自分のデータだけを更新できるようにする
alter policy "Enable users to update their own data only"
on "public"."cards"
to authenticated
using (
  (( SELECT auth.uid() AS uid) = auth_id)
)
with check (
  (( SELECT auth.uid() AS uid) = auth_id)
);

--DELETE：認証ユーザーが自分のデータだけを削除できるようにする
alter policy "Enable delete for users based on id"
on "public"."cards"
to authenticated
using (
  (( SELECT auth.uid() AS uid) = auth_id)
);

テストデータの例

INSERT INTO cards (card_id, auth_id, name, description, github_id, qiita_id, x_id) VALUES (
    'sample_id',
    '認証ユーザーのUUID',
    'テスト太郎',
    '<h1>テスト太郎の自己紹介</h1>',
    'your_github_ID',
    'your_Qiita_ID',
    'your_X_ID'
)

skill テーブル

新規名刺登録時に、画面のセレクトボックスにスキル一覧を表示させるために使用します。

column_name	data_type	null	other
skill_id	int8	not-null	PK, Is Identity
name	varchar	not-null	Unique

-- "skill" テーブルを作成します
CREATE TABLE public.skill (
  -- カラム定義
  -- GENERATED BY DEFAULT AS IDENTITY で、データ挿入時に自動で連番が振られます
  skill_id  bigint  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  name      varchar NOT NULL,

  -- 制約定義
  -- skill_id を主キーに設定します
  CONSTRAINT skill_pkey PRIMARY KEY (skill_id),
  
  -- name カラムの値が重複しないようにユニーク制約を設定します
  CONSTRAINT skill_name_key UNIQUE (name),

  -- skill_id: 1 ~ 127
  CONSTRAINT check_skill_id_range CHECK (
    skill_id BETWEEN 1 AND 127
  )
);

-- (オプション) テーブルとカラムにコメントを追加して、後から分かりやすくする
COMMENT ON TABLE public.skill IS 'スキルのマスターデータ';
COMMENT ON COLUMN public.skill.skill_id IS 'スキルを一意に識別するID（自動採番）';
COMMENT ON COLUMN public.skill.name IS 'スキル名（例: React, TypeScript）';

RLSポリシー
認証ユーザーであれば、スキル一覧を閲覧できるように設定しています。

-- SELECT：認証されたユーザーのみ選択を有効にする
alter policy "Enable select for authenticated users only"
on "public"."skill"
to authenticated
using (
  true
);

-- INSERT,UPDATE,DELETE 操作禁止（ポリシーなし）

テストデータの例

INSERT INTO skill (name) VALUES
 ('React'),
 ('TypeScript'),
 ('Github')

card_skill 中間テーブル（作成テーブル）

名刺とスキルを1対多で紐付けるためのテーブルです。
card_id は cards.card_id に、skill_id は skill.skill_id に外部キーとして紐付けられています。

column_name	data_type	null	other
id	int8	not-null	PK, Is Identity
card_id	varchar	not-null	PK, FK (public.cards.card_id)
skill_id	int8	not-null	PK, FK (public.skill.skill_id)

RLSポリシー ※以下の設定で後ほどパフォーマンスの指摘をうける。

/* 
    using　→　既存のデータが対象。using句内の返り値はブール値にする。
    WITH CHECK　→　挿入される新しいデータや更新後のデータが対象。WITH CHECK句内の返り値はブール値にする。
    以下のポリシーを簡易的に説明すると、「中間テーブルのcard_idとcardsテーブルのcard_idが一致し、
    さらにcardsテーブルのauth_idが現在のauth.uid()と一致するレコード」のみ操作を許可する
*/

-- SELECT：認証ユーザーが自分のデータのみを閲覧できるようにする
alter policy "Enable users to view their own data only"
on "public"."card_skill"
to authenticated
using (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
);

-- INSERT：認証ユーザーのみ挿入を有効にする(自身の認証IDを挿入する)
alter policy "Enable insert for authenticated users only"
on "public"."card_skill"
to authenticated
with check (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
);

-- UPDATE：認証ユーザーが自分のデータのみを更新できるようにする
alter policy "Enable users to update their own data only"
on "public"."card_skill"
to authenticated
using (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
)
with check (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
);

-- DELETE：認証ユーザーが自分のデータのみを削除できるようにする
alter policy "Enable delete for users based on user_id"
on "public"."card_skill"
to authenticated
using (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
);

テストデータの例

INSERT INTO card_skill (card_id, skill_id) VALUES
 ('sample_id', 1)

パフォーマンス指摘内容

上記の設定で、SupabaseのPerformance Advisorから「Auth RLS Initialization Plan」に関する警告が複数表示されました。警告の詳細は、card_skill テーブルのポリシーで「current_setting() および auth.() がRLSポリシー内で各行に対して不必要に再評価されている」と記載してありました。（unnecessarily re-evaluated for each row）

なんのこっちゃ！と思い、AIに相談したところ、以下に問題があると指定を受けた↓

-- public.card_skill のRSLポリシー（EXISTSサブクエリ内に問題がある）
/* 
    こののポリシーは、card_skillテーブルの各行をチェックする際に、
    わざわざpublic.cardsテーブルをEXISTSサブクエリで検索しに行っています。
*/

using (
  (EXISTS ( SELECT 1
     FROM cards
     WHERE (
       ((cards.card_id)::text = (card_skill.card_id)::text) AND 
       (cards.auth_id = auth.uid())
     )
  ))
);

問題点1. EXISTSサブクエリ内の非効率な結合:

RLSポリシーの目的は、このcard_skill.card_idが現在認証しているユーザーのIDであるかを確認することです。

現在のポリシーでは、cardsテーブルに対してEXISTS句でサブクエリを実行し、その中でcard_skill.card_idとcards.card_idを結合し、さらにcards.auth_idとauth.uid()を比較しています。

この多段階の参照と結合がPostgreSQLのクエリプランナーにとって複雑になり、card_skillテーブルの行ごとに繰り返し評価される可能性が生じます。

テキストだけではわかりづらいので、さらにAIに例を聞いてみた↓

例：card_skillテーブルにデータが複数ある場合

id	card_id	skill_id
1	'card_A'	101
2	'card_B'	102
3	'card_A'	103
4	'card_C'	104
5	'card_B'	105

例：cards テーブル

auth_id	card_id	name	・・
'auth_uuid_of_user_A'	card_A'	太郎	・・
'auth_uuid_of_user_B'	card_B'	花子	・・
'auth_uuid_of_user_C'	card_C'	次郎	・・

現在、「auth_uuid_of_user_A」としてユーザーAがログインしているとします。
PostgreSQLは、card_skillテーブルの各行をフィルタリングするために、次のような内部的なチェックを行ごとに繰り返す可能性があります。

-- card_skill の1行目 (id=1, card_id='card_A') をチェック
SELECT 1 FROM cards 
WHERE (cards.card_id = 'card_A') AND (cards.auth_id = 'auth_uuid_of_user_A')

結果：見つかる（EXISTS は TRUE）。この行は許可されます。

-- card_skill の2行目 (id=2, card_id='card_B') をチェック
SELECT 1 FROM cards
WHERE (cards.card_id = 'card_B') AND (cards.auth_id = 'auth_uuid_of_user_A')

結果：見つからない（EXISTS は FALSE）。この行は許可されません。

...というように、card_skillテーブルの行数が増えれば増えるほど、その行ごとに cards テーブルへのサブクエリが繰り返し実行されてしまいます。

問題点2. 冗長な ::text キャスト:

((cards.card_id)::text = (card_skill.card_id)::text) のように、card_idが両方ともvarchar型であるにも関わらず、::textキャストを行っています。これは不要な型変換であり、SQLの実行オーバーヘッドをわずかながら増加させ、インデックスの使用を妨げる可能性もあります。varchar型同士は直接比較できます。
（これに関しては、Supabaseが自動で::text付与していた）

今回の修正対応

EXISTSを使用しないSQLであれば良いのでは？思ってAIに以下を相談しました。

using (
  card_skill.card_id IN (
    SELECT c.card_id
    FROM public.cards c
    WHERE c.auth_id = auth.uid()
  )
)

INを使った場合でも、概念的には行ごとに評価が発生するみたいです。結果、INや結合を用いたSQLでも恐らくパフォーマンスの警告を改善できないみたいです。

そのため今回はcard_skill テーブルに auth_id UUID 列を追加し、その列を auth.users.id に外部キーとして参照させ、RLSポリシーを (auth_id = auth.uid()) とする方法で対応することしました。

ただ、データの非正規化（auth_idが2つのテーブルに存在する）や将来的にauth_idを変更するような操作があった場合、両方のテーブルを同時に更新する必要があるデメリットもありますが、デメリットも踏まえて以下のようにテーブル構造とポリシーを変更することにしました。

card_skill 中間テーブル（修正後）

※PKFKは複合外部キーの略

column_name	data_type	null	other
auth_id	uuid	not-null	PK, FK (auth.users.id),PKFK(public.cards.auth_id)
card_id	varchar	not-null	PK, PKFK(public.cards.card_id)
skill_id	int8	not-null	PK, FK (public.skill.skill_id)

-- "card_skill" 中間テーブルを修正します（UIから事前にテーブル削除後）
CREATE TABLE public.card_skill (
  -- カラム定義
  auth_id   uuid    NOT NULL DEFAULT auth.uid(),
  card_id   varchar NOT NULL,
  skill_id  bigint  NOT NULL, -- int8 は bigint に対応します

  -- 制約定義
  -- 3つのカラムの組み合わせを主キーに設定（複合主キー）
  -- これにより、(auth_id, card_id, skill_id) の組み合わせの重複が自動的に防がれます
  CONSTRAINT card_skill_pkey PRIMARY KEY (auth_id, card_id, skill_id),

  -- auth_id を auth.users テーブルの id カラムにリンクさせる外部キー制約
  -- ON DELETE CASCADE: 認証ユーザーが削除されたら、この中間テーブルの関連行も自動削除
  CONSTRAINT card_skill_auth_id_fkey FOREIGN KEY (auth_id) REFERENCES auth.users (id) ON DELETE CASCADE,
  
  -- card_id,auth_id を cards テーブルの card_id,auth_id カラムにリンクさせる外部キー制約
  -- ON DELETE CASCADE: cardsテーブルの行が削除されたら、この中間テーブルの関連行も自動削除
  -- この外部キーは、cardsテーブルの(card_id, auth_id)という複合主キー全体を参照しています
  CONSTRAINT card_skill_card_id_fkey FOREIGN KEY (card_id, auth_id) REFERENCES public.cards (card_id, auth_id) ON DELETE CASCADE,

  -- skill_id を skill テーブルの skill_id カラムにリンクさせる外部キー制約
  -- ON DELETE CASCADE: skillテーブルの行が削除されたら、この中間テーブルの関連行も自動削除
  CONSTRAINT card_skill_skill_id_fkey FOREIGN KEY (skill_id) REFERENCES public.skill (skill_id) ON DELETE CASCADE
);

-- (オプション) テーブルとカラムにコメントを追加
COMMENT ON TABLE public.card_skill IS 'カードとスキルの関連を定義する中間テーブル';
COMMENT ON COLUMN public.card_skill.auth_id IS '所有者を示す認証ユーザーID';
COMMENT ON COLUMN public.card_skill.card_id IS '関連するカードのID';
COMMENT ON COLUMN public.card_skill.skill_id IS '関連するスキルのID';

card_skill ポリシー（修正後。cardsテーブルとほぼ同じ）

-- SELECT：認証ユーザーが自分のデータのみを閲覧できるようにする
alter policy "Enable users to view their own data only"
on "public"."card_skill"
to authenticated
using (
    (( SELECT auth.uid() AS uid) = auth_id) -- 修正
);

-- INSERT：認証ユーザーのみ挿入を有効にする(自身の認証IDを挿入する)
alter policy "Enable insert for authenticated users only"
on "public"."card_skill"
to authenticated
with check (
    (( SELECT auth.uid() AS uid) = auth_id) -- 修正
);

-- UPDATE：認証ユーザーが自分のデータのみを更新できるようにする
alter policy "Enable users to update their own data only"
on "public"."card_skill"
to authenticated
using (
    (( SELECT auth.uid() AS uid) = auth_id) -- 修正
)
with check (
    (( SELECT auth.uid() AS uid) = auth_id) -- 修正
);

-- DELETE：認証ユーザーが自分のデータのみを削除できるようにする
alter policy "Enable delete for users based on user_id"
on "public"."card_skill"
to authenticated
using (
    (( SELECT auth.uid() AS uid) = auth_id) -- 修正
);

テストデータの例

INSERT INTO card_skill (auth_id, card_id, skill_id) VALUES
 ('認証されたUUID','sample_id', 1)

新規名刺登録後、中間テーブルを更新するデータベース関数 (RPC)

Name of function：create_card_with_skill
Schema ：public
Return type ：bool
Arguments ：params: json
Security ：Invoker

/* 
　GUI（SQL Editorでない場合）、Begin～endまでを記述だが、その場合セキュリティ警告がでた↓↓
　「Detects functions where the search_path parameter is not set.」
 　そのため、SQL Editorで「SET search_path TO public」を指定
*/

/*
　security Invokerではなく、もしdefinerを設定した場合、RLSを一時的にバイパスして関数が書き込みが可能。
　その場合、一時的管理者権限になるため、理由がない限りは「Invoker」で。
  SQLインジェクション対策はバックエンド側でバリデーション（妥当性検査）を行い、
　PL/pgSQLでプレースホルダとして扱うようにする。
*/

-- この関数はReact側から名刺データとスキルIDを受け取り、関連テーブルに書き込む
CREATE OR REPLACE FUNCTION public.create_card_with_skill(params json)
RETURNS boolean
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path TO public
AS $$
begin
  -- 1. cardsテーブルに新しい名刺情報を挿入 
  -- 静的SQLのため、->>でJSONから取り出した値もプレースホルダと同様に安全に扱われる
  -- ON CONFLICT を追加し、同じユーザーが同じ名刺を複数回登録できないようにする
  -- CONFLICTの場合、INSERTしないので、複合キー「card_id,auth_id」重複制約エラーにならない
  INSERT INTO public.cards (card_id, name, description, github_id, qiita_id, x_id)
  VALUES (
    params->>'card_id',
    params->>'name',
    params->>'description',
    params->>'github_id',
    params->>'qiita_id',
    params->>'x_id'
  )
  ON CONFLICT (card_id, auth_id) DO NOTHING; -- コンフリクト

  -- 2. 複合キー「card_id,auth_id」重複の場合（コンフリクトしたい場合）、何も登録なしでfalseを返す
  IF NOT FOUND THEN
  -- 直前のINSERTで何も挿入されなかった場合
  RETURN false;
  END IF;

  -- 3. card_skill中間テーブルにマッピングを挿入
  -- 複合キー「auth_id, card_id, skill_id」のため、同じスキルの登録はできない
  INSERT INTO public.card_skill (card_id, skill_id)
  VALUES (
    params->>'card_id',
    (params->>'skill_id')::int8
  ); -- 「auth_id, card_id, skill_id」重複の場合、制約エラーで処理はなしになる（トランザクション処理）

  RETURN true; -- React側でエラーハンドリングしたいので返り値設定
end;
$$;

念のためSQL Editorでリフレッシュ

NOTIFY pgrst, 'reload schema';

typeファイルも更新

npx supabase gen types --lang=typescript --project-id "project-idを入力" --schema public > database.types.ts

Reactプロジェクトからデータベース関数 (RPC) を呼び出す

type FormData = {
  card_id: string; // 4~39文字、先頭半角英数字、ハイフン、アンダーバー
  name: string; // 1~30文字
  description: string; //  1~500文字
  skill_id: number; //1 ~ 127
  github_id: string | null; // 1~39文字、先頭と末が半角英数字、ハイフン
  qiita_id: string | null; // 1~39文字、先頭が半角英数字、ハイフン
  x_id: string | null; // 4~15文字、アンダーバー
};

export async function createUserWithSkill(req: FormData) {
  // DB側の制約対策。""とnullは異なるため
  // 制約で正規表現を使用しているため、""の場合エラーになる
  const params = {
    ...req,
    // もし空文字なら null に、そうでなければ元の値のままにする
    github_id: req.github_id === "" ? null : req.github_id,
    qiita_id: req.qiita_id === "" ? null : req.qiita_id,
    x_id: req.x_id === "" ? null : req.x_id,
  };

  const { data, error } = await supabase.rpc("create_card_with_skill", {
    params //params: paramsの意味
  });

  if (error != null) {
    return {
      success: false,
      message: `RPC Error: ${error}`,
    };
  }

  if (data) {
    return {
      success: data,
      message: "",
    };
  } else {
    return {
      success: data,
      message: `既にその名刺IDで登録されております`,
    };
  }
}

おわりに

今回の開発を通じて、特に以下の点を深く学ぶことができました。

フロントエンドのバリデーションだけではなく、データベースで適切な制約をすること
RLSはSupabaseの強力な機能だが、ポリシーの内容を知って初めて真価を発揮すること
複雑な書き込み処理は、DB関数（RPC）にまとめることで、シンプルかつ整合性を保つことができる

また、AIとのペアプログラミングしてみて

事前に必須情報を共有する大切や、質問を具体的に伝えないと（上司または部下に報告するように）ほしい情報が得らないこと
違和感に対し深掘って質問することで、これは鵜呑みしてはいけないなど

AIの凄さを再実感しました。非常に長い記事になりましたが、最後までお読みいただきありがとうございました。この記事が、同じようにReactとSupabaseで奮闘している誰かの助けになれば幸いです。