Supabase RLS, explained properly

The one-sentence mental model

A row-level-security policy is a WHERE clause the database appends to every query on your behalf — one you wrote once, that the client cannot remove, rewrite or forget. Everything else about RLS is detail on top of that sentence.

This matters on Supabase more than on vanilla Postgres for one reason: your database is reachable from the browser. The anon key in your JavaScript bundle is not a secret — it is a username. PostgREST will happily execute whatever a visitor asks of any table the API exposes, and RLS policies are the part where you say what they are allowed to get back.

Enabling RLS (and what breaks)

Two things happen immediately. First, the table stops answering to the anon and authenticated roles entirely — RLS with no policies is default-deny, not default-allow. Second, nothing visibly errors: selects return empty arrays, inserts fail politely, and an app with no error handling looks “mysteriously logged out”. If you have ever enabled RLS and watched the app go blank, you did not break anything — you just have not written the policies yet.

New tables created through the Supabase dashboard get RLS enabled by default; tables created by raw SQL migrations do not. That difference is how a fast-moving project ends up with the Supabase linter finding rls_disabled_in_public — a public-schema table with RLS off, readable and writable by anyone holding the anon key. lumioguard runs the same detection continuously as RLS disabled on a table.

auth.uid(), auth.jwt() and the three roles

Supabase gives your policies a request context extracted from the caller’s JWT:

• auth.uid() — the caller’s user id (uuid), or null for anonymous requests.
• auth.jwt() — the whole verified token as jsonb, including any custom claims you set.
• Three Postgres roles carry the requests: anon (no session), authenticated (valid session), and service_role — which bypasses RLS entirely and must never leave a server. If that key is in client code, no policy on this page matters: that is the finding service-role key exposed in client-side code.

The policy cookbook

Copy these, rename the tables, keep the shape. Every policy names its command and its role — resist for all and role-less policies; being explicit is what makes the next migration reviewable.

Users see and manage only their own rows

using filters what a query can see; with check constrains what a write may create. An update policy wants both — using decides which rows can be touched, with check stops the update from reassigning the row to someone else.

Multi-tenant: rows visible to members of the org

The membership subquery is the standard shape for tenancy. Index org_members (user_id, org_id) or this policy is a per-row lookup on every read. A policy that only checks “is logged in” on a multi-tenant table is the exact over-share lumioguard flags as over-permissive RLS policy.

Public read, owner write

Admin override via a custom claim

Set the claim from a Custom Access Token hook or your own auth server — never from the client. Policies are additive: a row is visible if any policy allows it, so an admin policy sits cleanly beside the own-rows one.

Storage: scope objects to their owner’s folder

Buckets are just rows in storage.objects — they obey RLS like everything else, and a bucket with no policy is the storage variant of the same hole: no RLS policy on storage objects.

FORCE ROW LEVEL SECURITY

ENABLE applies policies to other roles; the table’s owner still bypasses them. FORCE closes that gap and makes the owner obey its own policies. On Supabase your day-to-day API traffic never runs as the owner, so FORCE mostly matters for SQL you run as the table owner — security definer functions, cron jobs, and migrations that read data. Turn it on for tables where “the app’s own code leaked the wrong rows” would be a real incident, and remember the corollary: with FORCE on and no policies, even the owner is locked out — which is how RLS enabled but no policy happens to internal tooling.

Performance: policies that don’t melt

• Wrap auth functions in a subselect. (select auth.uid()) is evaluated once per query as an InitPlan; bare auth.uid() can be evaluated per row. On a 100k-row scan that is the difference between one function call and 100,000.
• Index every column a policy filters on. user_id, org_id, published — the policy is a WHERE clause; it needs what any WHERE clause needs.
• Keep membership lookups narrow. The exists subquery should hit a covering index, not join three tables. If tenancy needs real logic, put it in a security definer function that returns the caller’s org ids, and index for it.
• Never “fix” slowness by dropping to the service role in app code. That trades a query plan problem for a security incident.

The errors, decoded

“new row violates row-level security policy for table …”

An insert or update produced a row that no with check clause accepts. Nine times out of ten the client forgot to set user_id to the caller (or set it to someone else). Set it server-side with a column default instead of trusting the client: user_id uuid not null default auth.uid().

Selects return empty — no error at all

That is using doing its job with no policy granting visibility. Check which role the request actually used (a missing Authorization header silently downgrades to anon) before rewriting policies that were never consulted.

The linter says rls_disabled_in_public

A public-schema table has RLS off entirely. Enable it and write policies — the finding, its risk and the fix are on the check page.

“query would be affected by row-level security policy”

Raised to the table owner when FORCE is on and no policy permits the operation — usually a migration or a security definer function that predates the policies. Decide whether that code should have a policy of its own or genuinely needs service_role.

Testing policies before shipping

Policies are code; test them like code. In SQL — the editor, a migration test, or CI — impersonate each role and assert what it can see:

set local keeps the impersonation inside the transaction, so the test cleans up after itself. Write one block per role per table that matters — anon sees nothing, user A cannot read user B, admin sees all. It is boring, which is the point. lumioguard re-runs the equivalent assertions against your live project on every scan, and watches for RLS denial spikes that mean a deploy changed the answer.

What RLS cannot do

• It cannot protect what bypasses it. The service-role key, security definer functions, and owner connections all step around policies — audit those paths separately.
• It does not filter columns. A visible row is entirely visible. Sensitive columns want a separate table, a view, or column privileges.
• It is not rate limiting, and it is not validation. A policy decides whose rows, not how often or whether the data makes sense.
• It only guards the database. An unauthenticated edge function holding the service key hands out everything RLS was protecting.

Six of lumioguard’s 46 Supabase checks exist purely to keep RLS true over time — because the failure mode is rarely the policy you wrote, and usually the table someone added on a Friday.