栽培管理アプリを作ってます⑫（余談）：権限管理実装で大苦戦した記録

2026年2月11日

はじめに

本編（栽培管理アプリに権限管理を実装した話）では、実装の全体像をサクッと紹介しました。

でも実際は…めちゃくちゃ苦戦しました。

RLS設定したら「ようこそ未設定さん」
循環参照で何も表示されない
SECURITY DEFINER関数って何？

この記事は、そんな試行錯誤の記録です。
正直、備忘録に近い内容ですが、同じ問題にハマった人の助けになれば嬉しいです。

2章の余談：最初の設計ミス

出荷アプリとの違い

実は、以前作った出荷管理アプリでは、もっとシンプルな設計でした：

出荷アプリの設計（シンプル）

users
├─ id
├─ email
└─ company_id  ← 全員が同じ会社IDを持つ

データ取得:
SELECT * FROM shipments WHERE company_id = user.company_id

栽培アプリの設計（複雑）

users
├─ id
├─ email
└─ organization_name  ← 代表のみ

cult_workers  ← 別テーブルで役割を管理
├─ user_id
├─ organization_id  ← 所属農園
└─ role (owner/staff)

データ取得:
1. cult_workers から organization_id を取得
2. organization_id でデータをフィルタ

なぜ複雑になったのか？

栽培アプリでは owner_id という概念を使ったため、以下のような感じに：

スタッフは users テーブルに company_id や organization_name を持たない
cult_workers という別テーブルで管理
毎回「このユーザーはどこの農園に所属しているか？」を調べる必要がある

最初から出荷アプリと同じ company_id パターンを使うべきだったかな？と思ったけど、今後の勉強のためにもと思って、そのまま進行。

3章の余談：Edge Functionの5つの問題とServer Actionへの移行

最初の設計（Edge Function）

Supabase の Edge Function という機能を使って、こんな仕組みを作りました：

フロー:

ユーザーが「suzuki」と入力
Edge Function が「suzuki のメールアドレスは何？」とデータベースに問い合わせ
「suzuki@tanaka-farm.local ですよ」と返事
そのメールアドレスでログイン

一見、うまくいきそうですよね？

でも、これは危険でした

実装後に色々AIとやり取りしていると、この設計には問題があることが分かりました。

❌ 問題1: 誰でもアクセスできる「公開API」になっていた

Edge Function は、URLさえ分かれば誰でも叩けるAPIです。

悪用例:

攻撃者「試しに "admin" って入力してみるか」
→ Edge Function「admin@company.co.jp ですよ」
攻撃者「よし、次は "tanaka" で...」

総当たりで試せば、農園にいるスタッフ全員のメールアドレスが分かってしまいます。

❌ 問題2: どのサイトからでも叩ける設定だった

技術的には「CORS が *」という設定ミスでした。

何が起きるか:

攻撃者が自分のWebサイトを作る
そこから Edge Function を自動で何百回も呼び出す
ユーザー名リストとメールアドレスのリストが手に入る

つまり、自分のアプリ以外からもアクセスできてしまう状態でした。

❌ 問題3: メールアドレスが丸見え

Edge Function が、こんな形で返事をしていました：

{
  "email": "tanaka@example.com"
}

これがブラウザ（クライアント側）に送られます。

問題:

メールアドレスはフィッシング詐欺に使われる
個人情報の漏洩になる
本来は「ログインに使うだけ」なのに、わざわざ外に出す必要がない

❌ 問題4: ユーザー名が存在するかバレる

エラーメッセージでこんな区別をしていました：

ユーザー名が見つからない → 「ユーザー名が見つかりません」
ユーザー名が見つかった → メールアドレスを返す

攻撃者の行動:

"admin" → 「見つかりません」 → 存在しない
"tanaka" → メールが返ってくる → 存在する
"suzuki" → メールが返ってくる → 存在する

スクリプトを組めば、数分で「存在するユーザー名一覧」が作れます。これをユーザー列挙攻撃と言います。

改善後の実装：Server Action

これらの問題を解決するため、Next.js の Server Action という仕組みに変更しました。

Server Action とは？

簡単に言うと：

ブラウザ（クライアント）ではなく、サーバー側で実行される処理
URLが公開されない（外部から叩けない）
データのやり取りが全てサーバー内で完結する

従来（Edge Function）:

ブラウザ → Edge Function（公開API）→ データベース
          ↑
     ここが危険だった

改善後（Server Action）:

ブラウザ → Next.jsサーバー（Server Action）→ データベース
          ↑
     外部からアクセス不可

何が良くなったのか？

✅ 改善1: 公開APIではなくなった

Server Action は Next.js アプリの内部でしか呼べません。外部から直接叩くことができません。

✅ 改善2: メールアドレスを返さない

サーバー内で：

ユーザー名 → メールアドレスに変換
そのままログイン処理を実行
ログイン成功したら Cookie を発行

ブラウザには「成功」か「失敗」しか返しません。メールアドレスは一切送りません。

✅ 改善3: エラーメッセージを統一

全てのエラーで同じメッセージを返すようにしました：

「メールアドレス/ユーザー名 または パスワードが正しくありません。」

これで、ユーザー名が存在するかどうか判別できなくなりました。

✅ 改善4: Service Role を安全に使う

Service Role Key（マスターキー）は：

server-only というパッケージで保護
絶対にブラウザ側に送られないようにする
1つのファイルだけで使う（他のファイルでは使えないようにする）

新しいログインフロー

ユーザー視点:

ユーザー名「suzuki」とパスワードを入力
ログインボタンを押す
成功 → ハウス一覧画面へ

裏側で起きていること:

Server Action が実行される
ユーザー名「suzuki」→ メールアドレスに変換（サーバー内）
そのメールアドレスでログイン（サーバー内）
Cookie を発行（サーバー内）
成功のみブラウザに返す

メールアドレスは一度も外に出ません。

スタッフ追加機能も同じ仕組みで

代表者が新しいスタッフを追加する機能も、Server Action で実装しました：

フロー:

代表者が「ユーザー名・作業者名・パスワード」を入力
Server Action が実行される
サーバー内で：

Supabase Auth にアカウント作成
データベースに登録

ブラウザには「成功」だけ返す

まとめ：セキュリティ対策の違い

項目	最初の実装（Edge Function）	改善後（Server Action）
外部からアクセス	❌ 誰でも叩ける	✅ 不可能
メールアドレス	❌ ブラウザに送る	✅ サーバー内で完結
ユーザー列挙攻撃	❌ 可能	✅ 防止（統一エラー）
強力な権限の保護	❌ 公開APIで使用	✅ サーバー内のみ

※Edge Function 自体が危険というわけではありませんが、
　認証処理をブラウザから直接呼び出す設計では攻撃面が広がりやすいため、今回は Server Action を採用しました。

4章の余談：owner_id設計とgetOwnerIdFromUser関数の実装

このセクションでやりたいこと（全体像）

代表とスタッフが 同じ農園データ を扱える
データの持ち主は常に「代表」
作業記録には 「誰が作業したか」 を残したい

問題：普通にやると失敗する

【ダメな例】
代表AAA → ハウスデータ(owner_id: AAA)
スタッフBBB → ハウスデータ(owner_id: BBB)
スタッフCCC → ハウスデータ(owner_id: CCC)

❌ データがバラバラで共有できない！

解決策：IDを2つに分ける

【良い例】
代表AAA → ハウスデータ(owner_id: AAA)
スタッフBBB → ハウスデータ(owner_id: AAA) ← 代表のIDで保存
スタッフCCC → ハウスデータ(owner_id: AAA) ← 代表のIDで保存

✅ 全員が同じデータを見れる！

そのために、以下の2つを 分けて考える 必要があります：

ログインしている人のID（user.id）
農園（代表）のID（ownerId）

ユーザーと農園を紐付けるテーブル

ユーザーの役割を管理するために、別途テーブルを作りました。

テーブルの構造（テーブル名：cult_workers）

カラム名	説明
user_id	users.id への外部キー（どのユーザーか）
organization_id	所属農園の代表のID
role	‘owner’（代表）または ‘staff’（スタッフ）
username	ログイン用ユーザー名
display_name	画面に表示する名前

データの紐付き方

代表の場合

users.id = AAA
  └─ organization_name: "田中農園"

cult_workers
  ├─ user_id: AAA
  ├─ organization_id: AAA  ← 自分のID
  └─ role: 'owner'

cult_fields（ハウスマスタ）
  └─ owner_id: AAA

ポイント：

代表は organization_id に自分のIDを入れる
ハウスデータの owner_id も自分のID

スタッフの場合

users.id = BBB
  └─ organization_name: NULL

cult_workers
  ├─ user_id: BBB
  ├─ organization_id: AAA  ← 代表のID
  └─ role: 'staff'

cult_logs（作業記録）
  ├─ owner_id: AAA  ← 代表のID（共通）
  └─ worker_id: YYY ← スタッフのcult_workers.id

ポイント：

スタッフは organization_id に代表のIDを入れる
データは全て代表の owner_id で管理
作業記録には「誰がやったか」を worker_id で記録

関数を作成（getOwnerIdFromUser）

この関数の役割

「このユーザーはどこの農園に所属しているか？」を調べる関数です。

全てのページでこの関数を使うことで、代表もスタッフも同じデータを扱えるようにします。

コード

export async function getOwnerIdFromUser(userId: string) {
  const supabase = await createClient();

  const { data: worker } = await supabase
    .from('cult_workers')
    .select('organization_id')
    .eq('user_id', userId)
    .maybeSingle();  // ← 0件または1件だけ取得

  // スタッフの場合は organization_id、代表の場合は自分のID
  return worker?.organization_id || userId;
}

動作の詳細（ステップごと）

// 1. cult_workersテーブルを検索
const { data: worker } = await supabase
  .from('cult_workers')
  .select('organization_id')
  .eq('user_id', userId)
  .maybeSingle();

// 2. 結果によって返す値を変える
return worker?.organization_id || userId;

この || の意味：

// パターン1：スタッフの場合
worker = { organization_id: "AAA" }
return "AAA"  // 代表のIDを返す

// パターン2：代表の場合
worker = { organization_id: "AAA" }
return "AAA"  // 自分のID

// パターン3：cult_workersにデータがない場合
worker = null
return userId  // 自分のIDを返す（代表と判断）

実際の動き

【代表でログイン】
userId = AAA
↓
cult_workers を検索
↓
organization_id = AAA を発見
↓
return AAA

【スタッフでログイン】
userId = BBB
↓
cult_workers を検索
↓
organization_id = AAA を発見
↓
return AAA

✅ どちらも同じ AAA が返る！

全ページでの適用

この関数を使うことで、全員が同じデータを扱えるようにしました。

修正前（間違い）

const { data: fields } = await supabase
  .from('cult_fields')
  .select('*')
  .eq('owner_id', user.id)  // ← スタッフのIDでは取得できない

問題：

スタッフの user.id は BBB
ハウスの owner_id は AAA
一致しない → データが取得できない

修正後（正しい）

// まず農園のIDを取得
const ownerId = await getOwnerIdFromUser(user.id);

// 農園のIDでデータを取得
const { data: fields } = await supabase
  .from('cult_fields')
  .select('*')
  .eq('owner_id', ownerId)  // ← 全員が同じIDで取得

動作：

代表：ownerId = AAA → 自分のハウスを取得
スタッフ：ownerId = AAA → 代表のハウスを取得

✅ どちらも同じデータが取得できる！

これで実現できること

✅ 代表とスタッフが同じデータを見れる
✅ データは代表のIDで一元管理
✅ 作業記録には誰がやったかも残る

5章の余談：RLS設定で大苦戦：循環参照とSECURITY DEFINER関数

段階的な導入

RLSは一気に全テーブルに設定すると、予期しないエラーが出やすいため、段階的に導入しました：

Phase 1: マスタテーブル（農薬・肥料・作業）
Phase 2: 作業記録・収穫記録
Phase 3: ハウス・畝・作

各Phaseで動作確認してから次に進みました。

Phase 1: user_id カラムの追加

なぜ必要？

元々の設計では、スタッフの判定に email を使っていました：

const { data: worker } = await supabase
  .from('cult_workers')
  .select('organization_id')
  .or(`dummy_email.eq.${userEmail},actual_email.eq.${userEmail}`)
  .maybeSingle();

問題:

❌ RLSポリシー内でこの検索を使うと複雑になりすぎる
❌ ダミーメールと実メールの両方をチェックする必要がある
❌ 後述する「自己参照問題」が発生する

解決策

user_id カラムを追加して、auth.uid() と直接紐付けるようにしました：

-- cult_workers テーブルに user_id を追加
ALTER TABLE cult_workers ADD COLUMN user_id UUID;

-- 外部キー制約
ALTER TABLE cult_workers ADD CONSTRAINT cult_workers_user_id_fkey 
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- ユニーク制約（1人1つのworkerレコード）
ALTER TABLE cult_workers ADD CONSTRAINT cult_workers_user_id_unique 
  UNIQUE (user_id);

これで、getOwnerIdFromUser() がシンプルになりました：

export async function getOwnerIdFromUser(userId: string) {
  const supabase = await createClient();

  const { data: worker } = await supabase
    .from('cult_workers')
    .select('organization_id')
    .eq('user_id', userId)  // ← user_id で直接検索
    .maybeSingle();

  return worker?.organization_id || userId;
}

Phase 2: 大問題発生 – 「ようこそ未設定さん」

user_id カラムを追加して、さあRLSを設定しよう！と思った矢先、衝撃的な問題が発生しました。

何が起きたのか？

マスタテーブル（農薬・肥料・作業）にRLSポリシーを設定したところ：

「ようこそ未設定さん」

スタッフどころか、代表（オーナー）でログインしてもこうなりました。

原因を追う

ヘッダー部分のコードを見てみましょう：

// layout.tsx でヘッダーを表示
const user = await supabase.auth.getUser();

// スタッフ判定：cult_workers を検索
const { data: worker } = await supabase
  .from('cult_workers')
  .select('display_name, role, organization_id')
  .eq('user_id', user.id)
  .maybeSingle();

if (worker) {
  // ヘッダーに表示
  setUserProfile({
    username: worker.display_name,  // ← ここが「未設定」になる
    organizationName: org?.organization_name
  });
}

一見、問題なさそうです。でも、RLSを設定した途端に動かなくなりました。

RLSで何が起きているのか？

問題は、マスタテーブルのRLSポリシーにありました：

-- 農薬マスタのSELECTポリシー
CREATE POLICY "view_own_pesticides" ON cult_pesticides FOR SELECT USING (
  owner_id = auth.uid()
  OR owner_id IN (
    SELECT organization_id FROM cult_workers WHERE user_id = auth.uid()
  )  -- ← ここで cult_workers を参照
);

このポリシーは「cult_workers テーブルから organization_id を取得する」という処理を含んでいます。

でも、cult_workers 自体もRLSで保護されています。

-- cult_workers のSELECTポリシー（最初の設計）
CREATE POLICY "cult_workers_select" ON cult_workers FOR SELECT USING (
  organization_id = auth.uid()  -- 代表：自分の農園のメンバー
  OR user_id = auth.uid()        -- スタッフ：自分自身
);

循環参照の罠

ここで循環参照が発生します：

何が起きているのか？

ヘッダーが cult_workers を読もうとする
↓
cult_workers の SELECT ポリシーがチェックされる
↓
でも、農薬マスタのポリシーも cult_workers を参照している
↓
農薬マスタを読むには cult_workers が必要
↓
cult_workers を読むには...農薬マスタが？？
↓
どっちも読めない！

結果: cult_workers が取得できず、display_name が null → 「未設定さん」になる

試した解決策：USING (true)

最初の解決策として、cult_workers の SELECT を緩くしてみました：

-- 全員が見えるようにする
CREATE POLICY "cult_workers_select" ON cult_workers FOR SELECT USING (
  true  -- ← 制限なし
);

結果:

✅ 動いた！「ようこそ〇〇さん」と表示されました。

でも、これで良いのか？

USING (true) には問題があります：

⚠️ 他の農園のメンバー情報も見える可能性
⚠️ セキュリティが緩い
⚠️ ベストプラクティスではない

Phase 2（改善版）: SECURITY DEFINER 関数

そこで、SECURITY DEFINER 関数という解決策にたどり着きました。

SECURITY DEFINER って何？

まず、これを理解するために、通常の関数との違いを見てみましょう。

通常の関数（SECURITY INVOKER）

-- 普通の関数
CREATE FUNCTION get_my_org()
RETURNS uuid
AS $$
  SELECT organization_id FROM cult_workers WHERE user_id = auth.uid()
$$;

実行時:

「この関数を呼び出したユーザー」の権限で実行される
つまり、cult_workers の RLS が適用される
→ 循環参照の問題が解決しない ❌

SECURITY DEFINER 関数

-- SECURITY DEFINER 関数
CREATE FUNCTION public.my_org_id()
RETURNS uuid
LANGUAGE sql
SECURITY DEFINER  -- ← ここが重要
SET search_path = public
AS $$
  SELECT organization_id 
  FROM public.cult_workers 
  WHERE user_id = auth.uid()
  LIMIT 1
$$;

実行時:

「この関数を作成したユーザー（通常は管理者）」の権限で実行される
RLS をバイパスできる
→ cult_workers を確実に読める ✅

セキュリティは大丈夫？

SECURITY DEFINER は強力ですが、安全に使えます：

✅ 関数内の処理は限定的

auth.uid() で自分のデータのみ取得

✅ 権限を制限

GRANT EXECUTE ... TO authenticated でログインユーザーのみ

✅ 関数内でフィルタ

WHERE user_id = auth.uid() で自分のレコードのみ

実装：2つの関数を作成

① my_org_id() 関数

役割: ログイン中のユーザーの所属農園IDを取得

CREATE OR REPLACE FUNCTION public.my_org_id()
RETURNS uuid
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT organization_id 
  FROM public.cult_workers 
  WHERE user_id = auth.uid()
  LIMIT 1
$$;

-- 権限設定（重要！）
REVOKE ALL ON FUNCTION public.my_org_id() FROM public;
GRANT EXECUTE ON FUNCTION public.my_org_id() TO authenticated;

この関数の動き:

代表でログイン → 自分のIDを返す
スタッフでログイン → 所属農園の代表のIDを返す

② my_worker_id() 関数

役割: ログイン中のユーザーの worker_id を取得

CREATE OR REPLACE FUNCTION public.my_worker_id()
RETURNS uuid
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT id 
  FROM public.cult_workers 
  WHERE user_id = auth.uid()
  LIMIT 1
$$;

-- 権限設定
REVOKE ALL ON FUNCTION public.my_worker_id() FROM public;
GRANT EXECUTE ON FUNCTION public.my_worker_id() TO authenticated;

この関数の動き:

UPDATE/DELETE で「自分の作業のみ編集可能」を実現するために使用

どう変わった？

RLSポリシーがシンプルかつ安全になりました：

Before: サブクエリで循環参照

CREATE POLICY "view_own_pesticides" ON cult_pesticides FOR SELECT USING (
  owner_id = auth.uid()
  OR owner_id IN (
    SELECT organization_id FROM cult_workers WHERE user_id = auth.uid()
  )  -- ← 循環参照の原因
);

After: 関数を使用

CREATE POLICY "view_own_pesticides" ON cult_pesticides FOR SELECT
TO authenticated
USING (
  owner_id = auth.uid()
  OR owner_id = public.my_org_id()  -- ← 関数使用、循環参照なし
);

メリット

✅ 循環参照を完全に回避
✅ セキュリティが高い（他の農園のデータは見えない）
✅ パフォーマンスが良い（関数の結果がキャッシュされる）
✅ 読みやすい

Phase 3: RLSポリシーの設定

関数を使って、全テーブルにRLSポリシーを設定しました。

マスタテーブル（農薬・肥料・作業）

SELECT: 代表 + スタッフ（閲覧可能）

CREATE POLICY "view_own_pesticides" ON cult_pesticides FOR SELECT
TO authenticated
USING (
  owner_id = auth.uid()
  OR owner_id = public.my_org_id()  -- ← 関数使用
);

INSERT: 代表のみ

CREATE POLICY "insert_pesticides" ON cult_pesticides FOR INSERT
TO authenticated
WITH CHECK (
  owner_id = auth.uid()
);

UPDATE/DELETE: 代表のみ

（同様に設定）

作業記録（cult_logs）

SELECT: 代表 + スタッフ（全データ閲覧可能）

CREATE POLICY "select own cult_logs" ON cult_logs FOR SELECT
TO authenticated
USING (
  owner_id = auth.uid()
  OR owner_id = public.my_org_id()
);

INSERT: 代表 + スタッフ（全員が作業登録可能）

CREATE POLICY "insert own cult_logs" ON cult_logs FOR INSERT
TO authenticated
WITH CHECK (
  owner_id = auth.uid()
  OR owner_id = public.my_org_id()
);

UPDATE: 代表は全て、スタッフは自分のみ

CREATE POLICY "update own cult_logs" ON cult_logs FOR UPDATE
TO authenticated
USING (
  owner_id = auth.uid()
  OR worker_id = public.my_worker_id()  -- ← 関数使用
)
WITH CHECK (
  owner_id = auth.uid()
  OR worker_id = public.my_worker_id()
);

DELETE: 代表は全て、スタッフは自分のみ

CREATE POLICY "delete own cult_logs" ON cult_logs FOR DELETE
TO authenticated
USING (
  owner_id = auth.uid()
  OR worker_id = public.my_worker_id()
);

⚠️ WITH CHECK の重要性

USING は「読み取り権限」のチェック、WITH CHECK は「書き込み内容」のチェックです。

WITH CHECK がないと、スタッフが owner_id を改ざんできてしまいます。

必ず両方設定しましょう。

6章の余談：バグ3つの詳細

RLSを設定して、実際に使ってみると、いくつかの問題が発覚しました。

問題1: スタッフでログインするとデータが見えない

症状

スタッフでログインすると、ハウス一覧が空
マスタも全て空
RLSを無効にしても見えない

原因

フロントエンドで user.id をそのまま使っていた：

// 問題のコード
const { data: fields } = await supabase
  .from('cult_fields')
  .select('*')
  .eq('owner_id', user.id)  // ← スタッフのID

// スタッフのuser.id = BBB
// ハウスのowner_id = AAA
// → 一致しない → 取得できない

解決

getOwnerIdFromUser() を使うように全ページを修正しました。

const ownerId = await getOwnerIdFromUser(user.id);

const { data: fields } = await supabase
  .from('cult_fields')
  .select('*')
  .eq('owner_id', ownerId)  // ← 全員が同じIDで取得

問題2: スタッフが農薬を登録できてしまう

症状

肥料・作業マスタはスタッフが登録できない（正しい）
農薬マスタだけスタッフが登録できてしまう（おかしい）

原因

農薬の新規登録だけ、Client Component で直接 Supabase を呼んでいた：

// 問題のコード (pesticides/new/page.tsx)
"use client";

const handleSubmit = async () => {
  const user = await supabase.auth.getUser();

  // 直接INSERT
  await supabase.from('cult_pesticides').insert({
    owner_id: user.id,  // ← スタッフのIDが入る
    name: name,
  });
};

RLSチェック:

owner_id (スタッフID) = auth.uid() (スタッフID)
→ 一致する → 通過してしまう ❌

解決

Server Action を作成して、ownerId を使うように修正：

// pesticides/actions.ts
"use server";

export async function createPesticideAction(data) {
  const supabase = await createClient();
  const user = await supabase.auth.getUser();
  const ownerId = await getOwnerIdFromUser(user.id);

  await supabase.from('cult_pesticides').insert({
    owner_id: ownerId,  // ← 代表のIDが入る
    name: data.name,
  });
}