The migration that added a table and forgot RLS

What actually happens

In a Supabase project the anon and authenticated roles hold broad grants on the public schema by design. That is what makes PostgREST useful out of the box — and it means grants are not your safety net. The only mechanism bounding what an unauthenticated caller can read is Row Level Security per table.

So the failure sequence is short. A table lands in public without the RLS line. PostgREST exposes it, because exposing tables in public is its job. The first request carrying the anon key returns every row. Your own frontend made that request shape look normal months ago; nobody reading logs distinguishes "the server sent rows" from "the database judged this caller may see rows".

The catalog stores the switch as relrowsecurity on pg_class, and only an explicit ENABLE ROW LEVEL SECURITY statement sets it. Creating a table defaults the flag to false, and nothing else — no grant, no schema change, no PostgREST setting — substitutes for it. That is why the defect never appears in application diffs: the absence lives entirely in database metadata, one boolean per table, invisible to every tool that reads code rather than catalogs.

How to check the last migration yourself

The catalog answer takes thirty seconds. Any false in relrowsecurity below is exposed to the anon key right now. Run it after every migration batch rather than only when something looks wrong: the query reads metadata alone, costs nothing measurable, and its output is the ground truth that dashboards summarise.

SELECT c.relname AS table_name, c.relrowsecurity AS rls_enabled
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname = 'public'
ORDER BY c.relrowsecurity, c.relname;

Fixing it properly

Enable, force, then grant. FORCE matters because migrations and psql sessions run as the table owner, and owners bypass policies unless you say otherwise. Scope the policies to whatever column actually expresses ownership in your schema:

ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY "owners_select_invoices"
  ON public.invoices FOR SELECT TO authenticated
  USING (user_id = (SELECT auth.uid()));

CREATE POLICY "owners_insert_invoices"
  ON public.invoices FOR INSERT TO authenticated
  WITH CHECK (user_id = (SELECT auth.uid()));

Variations you will meet

Partitioned tables split the problem in two. Enabling RLS on the partitioned parent governs queries arriving through the parent, while each partition carries its own relrowsecurity flag for direct access. A migration that enables the parent and creates partitions afterwards leaves those partitions bare, and a lookup addressed to a partition by name never touches the parent policy.

Generated migrations also fail messily. A patch that creates the table, seeds it, and appends the policy step can die between steps, leaving a populated table with no policies and a deploy marked failed. Re-running the safe parts and skipping the failed tail is exactly how created-but-unprotected tables survive a red pipeline.

Verifying the fix from outside

After applying the remediation, ask PostgREST the question an attacker would: fetch the table with the anon key and read the response. Before the fix you get 200 with rows; after it you should see an empty array or a permissions error, depending on whether a narrow policy admits the anonymous role, and a request carrying a real user token should return exactly that user's rows. The catalog states what is configured; this two-request check proves what actually happens.

Keeping the next migration honest

Fixing one table proves little about the next sprint. RowShield records the finding on each scan and diffs against the previous run, so a table that ships without RLS alerts as created within minutes of its migration landing — hourly on Indie, every fifteen minutes on Team, daily on Free. If the same hole reappears after someone fixed it, the alert says regressed rather than created, which is the word that gets process changed.

Remediation SQL comes generated from your real column names, FORCE included, one-click copy. RowShield reads pg_catalog metadata only and is an independent product, unaffiliated with Supabase.

Frequently asked

Why did no linter catch the missing line?

Static linters lint what passes through them; ad-hoc SQL in dashboard editors, console sessions and generated patches routinely bypasses migration files entirely. A scheduled catalog scan checks what actually exists in the database instead of what was supposed to be written somewhere, which is why it sees what the linter structurally cannot.

Is an internal-only table still a risk?

Yes — internal is an application concept PostgREST does not know. If the table lives in the public schema with RLS disabled, the anon key reads it regardless of what your service layer intended. Naming conventions, omitted links and absent screens provide no protection; move genuinely internal tables to a non-exposed schema or give them real policies.

Does enabling RLS break existing queries?

Enabling denies every row until a policy grants access, so write the policies in the same change rather than switching on first and intending to follow up. Generate them from your real columns, stage both statements together, and the break window shrinks to a single deploy instead of an open-ended incident.