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

> Systematically debug the three big symptoms commonly hit with Supabase (PostgreSQL) row-level security (RLS) — 'SELECT returns empty,' 'INSERT rejected with new row violates row-level security policy,' 'RLS isn't working and data leaks' — with a cause-isolation flow and diagnostic SQL (pg_policies, relrowsecurity, auth.uid(), set local role). Official-compliant, a procedure to fix with evidence, not guesses.

- Published: 2026-06-28
- Author: 友田 陽大
- Tags: Supabase, RLS, PostgreSQL, セキュリティ, テスト
- URL: https://tomodahinata.com/en/blog/supabase-rls-troubleshooting-empty-results-insert-violation-not-working-guide
- Category: Databases & RLS
- Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions

## Key points

- Don't fiddle with policies by guessing when debugging RLS. With the 4 diagnostics of relrowsecurity/pg_policies/auth.uid()/set local role, identify 'where it stops' with evidence before fixing.
- The most frequent cause of 'SELECT empty' is one of four — RLS enabled but no policy (default deny), unauthenticated so auth.uid() is null, insufficient GRANT, USING predicate mismatch. Isolate by impersonating an authenticated user with set local role.
- 'new row violates row-level security policy (42501)' is a WITH CHECK violation on INSERT/UPDATE. The row being inserted doesn't satisfy the policy predicate (typically user_id unset or another tenant's id inserted).
- 'RLS isn't working and leaks' is typically forgetting enable, connecting as service_role, table-owner bypass (FORCE unset), or a SECURITY DEFINER function or view bypassing it. First suspect relrowsecurity=false.
- After fixing, always re-verify both allow and deny with pgTAP. Don't create a 'works but leaks' regression by loosening the predicate during troubleshooting.

---

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](/case-studies/realtime-sports-scoring-app) (all-RLS on 69 tables, 280 policies). The content is faithful to [Supabase official](https://supabase.com/docs/guides/database/postgres/row-level-security) and [PostgreSQL official](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) (as of June 2026).

> The premises of RLS (enabling, USING/WITH CHECK, roles) are in [RLS for beginners](/blog/supabase-rls-getting-started-enable-first-policy-guide), and the Next.js-specific "empty returns from a client-creation mistake" is in the [App Router integration guide](/blog/nextjs-app-router-supabase-rls-ssr-server-client-auth-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?

```sql
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?

```sql
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?

```sql
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.

```sql
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.

| Cause | How it appears in the diagnostic | How to fix |
| --- | --- | --- |
| **No policy** (only enabled) | Diagnostic 2 is 0 | Write one SELECT policy |
| **Unauthenticated** (JWT not arriving) | `uid` is `null` in diagnostic 3 | Fix the auth path (Next.js is the [integration guide](/blog/nextjs-app-router-supabase-rls-ssr-server-client-auth-guide)) |
| **Insufficient GRANT** | `permission denied for table` error | `grant select ... to authenticated` |
| **USING predicate mismatch** | 0 even in diagnostic 4 | Match the predicate's column, type, and value against the real data |

### The order of isolation

1. **See the policies in diagnostic 2.** If 0, the answer is out — you `enable`d 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](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)). 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

```sql
-- 想定ポリシー
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)**
   ```ts
   // ❌ 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

```sql
-- ❌❌ これは解決ではなく、書き込みバイパスを開く穴
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](/blog/supabase-rls-with-check-using-write-bypass-guide), 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`).

| Cause | Diagnostic | How to fix |
| --- | --- | --- |
| **Forgot enable** | `rls_enabled = false` in diagnostic 1 | `alter table ... enable row level security` |
| **Connecting as service_role** | `current_user = service_role` in diagnostic 3 | The client to the `anon`/publishable key |
| **Owner bypass** | `rls_forced = false` in diagnostic 1 and owner connection | `alter table ... force row level security` |
| **Leftover `using (true)`** | The USING of diagnostic 2 is `true` | Fix to the correct predicate |
| **A view bypasses RLS** | All rows visible via the view | `security_invoker = true` (PG15+) |
| **A hole in a SECURITY DEFINER function** | All rows returned via the function | Harden with `search_path=''`, least privilege |

### 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.

```sql
-- 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.**

```sql
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](/blog/supabase-security-definer-function-search-path-guide)).

---

## 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.

```sql
-- 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](/blog/supabase-rls-testing-pgtap-policy-regression-guide). **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](https://supabase.com/docs/guides/database/postgres/row-level-security)
- [PostgreSQL: Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [PostgreSQL: CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html)