The problem with Supabase RLS

Here is the bug that ate a weekend on Even Steven, the expense-splitting app I am building. I logged into a test build, tapped Add Expense, typed in fifty euros for dinner, and got back a 403: new row violates row-level security policy for table "expenses". I was logged in. I had created the group myself a few seconds earlier. By every rule I could think of, I was allowed to do this. The database disagreed, and it would not tell me why.

The short version: a Postgres WITH CHECK policy that has to read a second table to decide whether a write is allowed can end up evaluating auth.uid() as NULL, so the check fails even for a user who should pass. After four migrations trying to fix the policy, I stopped fixing it and moved the write into a function instead. The interesting part is everything in between, so let me walk through it.

What is a WITH CHECK policy supposed to do?

Row Level Security, or RLS, is Postgres letting you attach a rule to a table that decides which rows a given user can see or change. You turn it on, and every query the user runs gets your rule silently stapled onto it. A USING clause filters reads. A WITH CHECK clause validates writes: if the new row does not satisfy the expression, the insert is rejected.

Think of WITH CHECK like a bouncer who inspects every row trying to get into the table and turns away the ones that do not match the guest list. For my expenses table the guest list rule was simple in English: you can insert an expense into a group only if you are an active member of that group. The membership lives in a different table, group_members, so the policy had to go look over there. That cross-table lookup is where everything went wrong.

So why did every insert fail?

My first few guesses were wrong, and they were wrong in instructive ways, so they are worth naming. I thought Postgres was caching the membership helper across rows and computing it once with a stale, empty auth.uid(). That is a real class of RLS bug, so I rewrote the helper to not be cacheable. Same 403. Then I thought the helper function was the problem, so I deleted it and inlined the membership lookup straight into the policy. Same 403.

The thing I had missed is that the lookup itself triggered more RLS. When the WITH CHECK on expenses reads from group_members, the SELECT policy on group_members fires too, and that policy calls the same membership helper, which calls auth.uid() again. So I had RLS nested inside RLS. To find out where it broke, I dropped a log line inside the check that printed both auth.uid() and the raw JWT claim it reads from.

Both came back NULL. Not one, both. Inside the WITH CHECK evaluation the user's identity simply was not being threaded through, so the membership lookup compared against nobody and always came up empty. That is why every clever rewrite produced the exact same 403: I kept fixing the wording of a question that was being asked with the user's name left blank.

I did get one half-fix to work. I moved the membership check into a BEFORE INSERT trigger, which runs in a normal execution context where auth.uid() is populated, and reduced the WITH CHECK to a stub. The insert finally succeeded. But now the table's policy was a lie: the policy said one thing, the trigger enforced another, and the real rule lived in two places that did not agree. That is the moment I wrote a line in a migration comment that ended the whole chase: if I have to fight the policy this hard and the result is a half-policy plus a trigger, the policy is the wrong place to put this rule.

What finally worked?

I stopped using a policy for the write and used a function instead. The pattern is a SECURITY DEFINER function, which is Postgres for "run this as the user who defined it, not the user who called it." The defining user is the database owner, so the function runs as a superuser and RLS does not apply inside it. Think of it like an employees-only door: the customer cannot walk through, but the staff member behind it can fetch what they asked for. The catch is that with the bouncer gone, the function has to check the guest list itself.

Here is the function that replaced the policy. It runs as the owner so there is no nested RLS to break auth.uid(), it calls auth.uid() in a context where the JWT is actually there, and it checks membership by hand before writing anything.

-- 20260523000017_create_expense_rpc.sql
CREATE OR REPLACE FUNCTION public.create_expense(
  p_group_id    uuid,
  p_title       text,
  p_amount      numeric,
  p_payer_id    uuid,
  p_splits      jsonb
  -- ... other columns elided
)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_user_id    uuid;
  v_expense_id uuid;
  v_split      jsonb;
BEGIN
  v_user_id := auth.uid();

  IF v_user_id IS NULL THEN
    RAISE EXCEPTION 'Not authenticated' USING ERRCODE = '42501';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM group_members
    WHERE group_id = p_group_id
      AND user_id  = v_user_id
      AND status   = 'active'
  ) THEN
    RAISE EXCEPTION 'Not a member of this group' USING ERRCODE = '42501';
  END IF;

  INSERT INTO expenses (group_id, title, amount, payer_id)
  VALUES (p_group_id, p_title, p_amount, p_payer_id)
  RETURNING id INTO v_expense_id;

  FOR v_split IN SELECT * FROM jsonb_array_elements(p_splits)
  LOOP
    INSERT INTO expense_participants (expense_id, member_id, share_amount)
    VALUES (
      v_expense_id,
      (v_split->>'memberId')::uuid,
      (v_split->>'share')::numeric
    );
  END LOOP;

  RETURN v_expense_id;
END;
$$;

GRANT EXECUTE ON FUNCTION public.create_expense TO authenticated;

The ERRCODE = '42501' is worth a second. PostgREST, the layer Supabase puts in front of Postgres, maps that code to an HTTP 403. The original RLS rejection used the same code, so from the app's point of view nothing changed: a failed write is still a 403. The difference is the message is now mine to write, and there is a real reason behind each one instead of a blanket policy refusal.

On the app side the change is one line. Instead of inserting into the table directly, I call the function by name with .rpc(). Supabase exposes any granted function as a callable endpoint, so this is the only write path the client knows about.

// lib/repos/expenses.ts
export async function createExpense(
  client: SupabaseClient<Database>,
  params: CreateExpenseParams,
  splits: Split[]
): Promise<Expense> {
  const { data: expenseId, error } = await client.rpc('create_expense', {
    p_group_id: params.group_id,
    p_title:    params.title,
    p_amount:   params.amount,
    p_payer_id: params.payer_id,
    p_splits:   splits.map((s) => ({ memberId: s.memberId, share: s.share })),
    // ... other params elided
  });

  if (error) throw error;

  const { data: expense, error: fetchError } = await client
    .from('expenses')
    .select('*')
    .eq('id', expenseId as string)
    .single();

  if (fetchError) throw fetchError;
  return expense as Expense;
}

There is a second read after the RPC because the function returns just the new id, and the rest of the app wants the whole row. That read goes through the normal SELECT policy on expenses, which works fine. I kept the writes in the function and left the reads to RLS, and the two stopped stepping on each other. The same shape later fixed two more tables, invite acceptances and push tokens, that had the same membership-gated write and the same broken context.

Things that surprised me

A few of these I had to look up more than once before they stuck:

• Reads were never the problem. The SELECT policies on the same tables worked perfectly the whole time. RLS evaluates USING in a context where the user's identity is present; it was specifically WITH CHECK on a cross-table write that went blank.
• A policy that reads another protected table quietly triggers that table's policy too. Nested RLS is easy to write without noticing, and it is where the identity got lost.
• SECURITY DEFINER turns the security model inside out. Because the function bypasses RLS, every rule the policy used to enforce now has to be re-stated by hand inside the function, including the easy-to-forget "you can only act as yourself" check.
• Moving the write into a function gave me a transaction for free. The expense row and its split rows now insert together or not at all, which two separate client inserts could never promise.
• Raising ERRCODE 42501 keeps the HTTP status identical to the old RLS rejection, so the app's error handling did not need to change at all.

When I reach for this now

I am not down on RLS. For reads it is genuinely good: the SELECT policies in this app compose cleanly, nest through helpers without complaint, and let me keep authorization next to the schema instead of scattered across the app. I would not give that up.

What I would tell someone hitting this: if a WITH CHECK is silently rejecting writes that should be allowed, and the rule depends on a second table, do not write a fourth migration tuning the policy. Move the write into a SECURITY DEFINER function, check the rule by hand, and grant it to authenticated. It is not only a workaround for the broken context. You also get an atomic multi-table write, error messages you control, and a single thing to grant per operation. The day RLS WITH CHECK starts threading identity through reliably for cross-table writes, I will be glad. Until then, the function is where I put the write, and I have not missed the policy once.

Resources

The Supabase Row Level Security guide covers USING and WITH CHECK and the auth helper functions. The Supabase database functions guide shows how to define a function and call it with .rpc(). And the Postgres CREATE FUNCTION reference is the canonical word on what SECURITY DEFINER actually changes.