Supabase RLS isn't working / returns empty / rejects INSERT: a complete troubleshooting guide by cause

The worst thing to do when debugging RLS (row-level security) is "set the policy to using (true) for now and see if it works." That's the same as "the key won't open so I removed the front door" — it looks like it works but publishes all data. The principle of troubleshooting is one — don't fiddle with policies by guessing; first identify 'where it stops' with evidence.

This article is a practical guide to isolating and fixing the three big symptoms hit with Supabase + PostgreSQL RLS, by cause.

1. SELECT returns empty (should be readable but 0 rows)
2. INSERT rejected with new row violates row-level security policy
3. RLS isn't working and data leaks (rows that shouldn't be visible are)

The subject is operational knowledge from the real-time match-recording app (all-RLS on 69 tables, 280 policies). The content is faithful to Supabase official and PostgreSQL official (as of June 2026).

The premises of RLS (enabling, USING/WITH CHECK, roles) are in RLS for beginners, and the Next.js-specific "empty returns from a client-creation mistake" is in the App Router integration guide. This article concentrates on DB-side isolation.

0. First obtain: the 4 diagnostic queries

Before looking at the symptom, prepare the tools to collect evidence. These four lay bare "the state of RLS."

Diagnostic 1: is RLS enabled? Is there owner bypass?

select relname,
       relrowsecurity   as rls_enabled,   -- enable row level security 済みか
       relforcerowsecurity as rls_forced  -- 所有者にも強制しているか
from pg_class
where oid = 'public.matches'::regclass;

• If rls_enabled = false, RLS isn't working in the first place (the most frequent cause of symptom 3).
• If rls_enabled = true, rls_forced = false, connecting as the table owner bypasses it.

Diagnostic 2: what policies are attached?

select policyname, cmd, roles, qual as using_expr, with_check as check_expr
from pg_policies
where schemaname = 'public' and tablename = 'matches';

You can see the contents of qual (USING) and with_check (WITH CHECK), the target cmd (SELECT/INSERT/...), and roles (the TO clause) in a list. "0 policies" means default deny and everything is rejected.

Diagnostic 3: who am I now? What does auth.uid() return?

select current_user,                          -- 接続ロール（anon/authenticated/...）
       auth.uid()       as uid,               -- ユーザーID（未認証ならnull）
       auth.jwt()->>'role' as jwt_role;        -- JWTのrole

If uid is null, you're treated as unauthenticated, and (select auth.uid()) = user_id is forever false.

Diagnostic 4: impersonate an authenticated user to reproduce

The dashboard's SQL editor usually runs as service_role (RLS bypass), so it can't reproduce the bug. Impersonate a specific user.

begin;
  set local role authenticated;
  set local request.jwt.claims =
    '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';

  -- ここで本番と同じクエリを投げ、症状を再現する
  select * from public.matches;
rollback; -- 状態を変えない（冪等）

With these four, all that's left is to see "which diagnostic is abnormal" per symptom.

1. Symptom A: SELECT returns empty (0 rows when it should be readable)

The most frequent worry. The cause converges to the following four. Crush them in order with diagnostics 1–4.

The order of isolation

1. See the policies in diagnostic 2. If 0, the answer is out — you enabled but there's no SELECT policy, and default deny erases everything.
2. See uid in diagnostic 3. If null, unauthenticated. Can the app send the token (for Next.js, client creation, middleware)?
3. Impersonate an authenticated user in diagnostic 4. If rows return correctly here, the problem isn't the DB but the app's auth path. Conversely, if it's empty even in diagnostic 4, the policy predicate is wrong.
4. The typical predicate mismatch: what's in user_id wasn't auth.users.id but another ID, the type was text vs uuid needing a cast, the tenant_id value was off — see the real data with select user_id from matches limit 5 and match it against the auth.uid() value.

The isolation you must not do: connecting as service_role, confirming "the data exists," and being satisfied. service_role bypasses RLS, so it only tells you "the data exists" and isolates nothing about the RLS problem. Always reproduce as authenticated with diagnostic 4.

2. Symptom B: new row violates row-level security policy

This error on INSERT/UPDATE (SQLSTATE 42501) is a WITH CHECK violation. The meaning is clear — "the new row being created doesn't satisfy the write policy's predicate."

Per the PostgreSQL official definition, WITH CHECK is evaluated against the proposed new content of the row, not its original content, and is rejected if false (PostgreSQL official). This is a sign that RLS is doing its job correctly, not a bug, so look at "why it isn't satisfied" rather than "silencing it."

Three common causes

-- 想定ポリシー
create policy "insert own" on public.matches for insert
to authenticated
with check ( (select auth.uid()) = user_id );

1. Forgot to set user_id (most frequent)

   tsCopyCopy code

   // ❌ user_id が null → (select auth.uid()) = null は偽 → 弾かれる
   await supabase.from("matches").insert({ opponent: "A", score: "3-1" });
   // ✅ user_id を明示する
   await supabase.from("matches").insert({ opponent: "A", score: "3-1", user_id: user.id });
   

   Setting a DB default value of user_id default auth.uid() structurally prevents the forgetting.

2. Inserting someone else's / another tenant's ID: putting another user's ID in user_id. This is the case where the policy is correctly preventing it. Fix the app bug.

3. On UPDATE, "the post-update row" crosses the boundary: UPDATE has both USING (existing rows you can update) and WITH CHECK (the post-update row) in effect. An UPDATE that rewrites tenant_id to another tenant is rejected by WITH CHECK. This is also correct behavior.

The "fix" you must never do

-- ❌❌ これは解決ではなく、書き込みバイパスを開く穴
create policy "insert own" on public.matches for insert
to authenticated with check ( true );

with check (true) makes the error disappear, but anyone can create a row with any user_id or any tenant_id. This is a typical write-bypass vulnerability, the worst pattern mass-produced by AI-generated code or short deadlines. Fix the error not by "silencing" it but by "sending data that satisfies the predicate."

3. Symptom C: RLS isn't working and data leaks (the most dangerous)

Symptoms A and B are "too strict" problems, but C is the opposite — too loose, and rows that shouldn't be visible are. This is the worst as an accident. The causes are as follows. Suspect from diagnostic 1 (relrowsecurity).

Forgot enable: bulk-audit all tables

Inspecting one table at a time by eye misses things. List the tables in the public schema with RLS not enabled and make it a CI blocking condition.

-- public スキーマで RLS が無効なテーブル＝潜在的に全公開
select tablename
from pg_tables
where schemaname = 'public'
  and tablename not in (
    select c.relname from pg_class c
    join pg_namespace n on n.oid = c.relnamespace
    where n.nspname = 'public' and c.relrowsecurity = true
  );

If this returns even one row, that table may be fully public within the GRANT's range. "The one table where enable was forgotten" is the most common serious-leakage path in SaaS.

The view pitfall

A view runs by default with the creator's privileges and bypasses RLS. Showing a view like public_matches to anon leaks all rows, slipping past the underlying RLS. In PostgreSQL 15+, security_invoker makes it respect the caller's privileges = RLS.

create view public.match_summary
with (security_invoker = true)   -- ビューでもRLSを効かせる
as select id, opponent, score from public.matches;

The hole in a SECURITY DEFINER function

If you escape complex authorization into a security definer function, that function runs bypassing RLS. Convenient, but if you don't fix search_path and allow outsiders to execute it, it becomes a hole that returns all rows. Harden it with set search_path = '', fully qualified names, and least privilege (details).

4. Isolation flow (keepsake)

A single judgment flow spanning the 3 symptoms. Return here when stuck.

データが「見えない」？
├─ 診断2: ポリシーは在る？ → 無 → SELECTポリシーを書く
├─ 診断3: auth.uid()はnull？ → はい → 認証経路（JWT伝播）を直す
├─ 診断4: 認証済み偽装で返る？ → はい → 問題はアプリ側。DBは正常
│                              → いいえ → USING述語の列/型/値を実データと照合
└─ permission deniedエラー → GRANT不足

INSERT/UPDATEが「弾かれる」？(42501)
└─ WITH CHECK違反 → 送る行の user_id/tenant_id を点検（true で黙らせない）

データが「漏れる」？
├─ 診断1: rls_enabled=false → enableする（全テーブル監査）
├─ 診断3: service_role接続 → anonキーへ
├─ 診断1: rls_forced=false＋所有者接続 → forceする
├─ ビュー経由 → security_invoker=true
└─ definer関数経由 → search_path=''・最小権限

5. After fixing, always: fix both allow and deny with tests

Troubleshooting is the work of rewriting predicates. So at the end, always fix both "what should be allowed is allowed" and "what should be denied is denied" with automated tests. Especially after fixing symptoms B and C, confirm with a deny test that you didn't loosen the predicate and create a "works but leaks" regression.

-- pgTAP例：部外者には1行も見えないことを「拒否」として固定する
begin;
  select plan(1);
  set local role authenticated;
  set local request.jwt.claims = '{"sub":"99999999-9999-9999-9999-999999999999"}';
  select is(
    (select count(*) from public.matches)::int, 0,
    '部外者には他人のmatchesが1行も見えない'
  );
  select * from finish();
rollback;

How to write deny tests and gate them in CI is detailed in the article on protecting RLS with pgTAP. Proving "it's fixed" with a test, not a demo — this is the last move that turns troubleshooting from "whack-a-mole" into "a permanent measure."

Conclusion: don't fix by guessing, fix with evidence

• Debug RLS by identifying "where it stops" with evidence using the 4 diagnostics of relrowsecurity / pg_policies / auth.uid() / set local role before fixing.
• Returns empty = no policy, unauthenticated, insufficient GRANT, predicate mismatch. Don't be satisfied by confirming with service_role.
• new row violates ... (42501) = WITH CHECK violation. Don't silence it with with check(true); fix the data you send.
• Leaks = forgot enable, service_role connection, owner bypass, view/definer-function bypass. First suspect relrowsecurity=false.
• After fixing, fix both allow and deny with pgTAP and don't produce a "works but leaks" regression.

Primary sources (always confirm the latest)

• Supabase: Row Level Security
• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY