TLDR: Supabase auto-enables Row Level Security (RLS) on every new table — no policy = deny-all, and supabase-js returns { data: [], error: null } without a single warning.

The Setup

I was building out a supply chain management app for an ecommerce business — tracking vendors, per-product lead times, reorder points, the whole thing.

Migration 027 was the big one: a vendor_products join table to replace the old supplier_contact_id column with a proper many-to-many setup.

It was a clean migration.

  • ✅ Created the table
  • ✅ Backfilled 7 rows from the old columns
  • ✅ Recreated report_forecast() with the new join logic

I applied it through Arc CDP (my browser automation tool for driving the Supabase SQL editor), verified the columns existed in information_schema, watched report_forecast() return the right reorder_point_days for a test product — 162 days, exactly correct — and shipped the code.

The Diagnosis

Opened the vendor assignment dialog in the live app.

"No vendors assigned yet."

…which was strange, because I had just confirmed 7 rows plus one freshly-inserted vendor assignment existed in the table.

Back to the SQL editor. Queried vendor_products directly.

Rows there. All 7. Healthy.

Why does the app not see them?

SELECT relname, relrowsecurity,
  EXISTS (SELECT 1 FROM pg_policies WHERE schemaname='public' AND tablename=relname) AS has_policy
FROM pg_class WHERE relname = 'vendor_products';

relrowsecurity = true. has_policy = false.

There it was.

The Actual Trap

Supabase auto-enables RLS on every newly-created table in public. The moment CREATE TABLE runs, relrowsecurity flips to true — before your migration sets any policy.

With RLS enabled and no policy in place, Postgres defaults to deny-all.

So supabase-js returns { data: [], error: null }.

No error. No warning. Just silence. An empty array that looks exactly like a table with no rows.

The SQL editor works because it runs as a superuser, which bypasses RLS. And report_forecast() worked because it's a SECURITY DEFINER function (a Postgres mode that runs as the function's owner — also bypasses RLS). So both verification steps I ran looked PERFECT.

That's the really nasty part. Two green checks pointing directly at a broken setup.

The Fix

Migration 028. One policy, roughly:

CREATE POLICY "Authenticated full access" ON vendor_products
  FOR ALL USING (auth.role() = 'authenticated');

(My note: the exact policy name and auth check varied — match whatever pattern your project already uses for public tables.)

App worked immediately. THAT'S IT.

The Checklist (Add This to Every Migration)

Every time you CREATE TABLE in a Supabase public schema, run this before you ship:

SELECT relname, relrowsecurity,
  EXISTS (SELECT 1 FROM pg_policies WHERE schemaname='public' AND tablename=relname) AS has_policy
FROM pg_class WHERE relname = 'your_new_table';

relrowsecurity = true + has_policy = false = zero rows, zero errors, zero clues.

Add the policy in the same migration. Don't let it leave without one.

Why This Stuck With Me

The full data flow is: Postgres → RLS policy → supabase-js → server component → React render.

A SQL editor query skips the RLS hop. A SECURITY DEFINER function skips it too. Only the actual interactive UI exercises the full chain.

My verification steps weren't wrong — they just didn't cover the right hop.

One query against pg_policies on every new table before pushing. That's the habit now. Took one invisible "No vendors assigned yet" to earn it.