Two Ways I Use Supabase Row Level Security: Deny-All Behind a Server, or Per-Owner Policies

Supabase publishes the public schema through its Data API. A table there without Row Level Security can be read and written by anyone holding the project's public anon key, and that key is designed to be public. So every table needs RLS enabled, whether or not your app ever uses the Data API. What differs is what RLS is for.

In the projects I have built on Supabase, it does one of two jobs. Either it closes the Data API completely and a trusted server does all the reading and writing, or it is the mechanism that decides which rows belong to which signed-in user. This article compares the two using the actual migrations, for developers deciding which one their app needs.

rukon-link, my link page with contact requests and an admin dashboard, is the clearest example. Its migration states the model at the top: RLS enabled on every table with no policies, and all privileges revoked from anon and authenticated. Browsers cannot read or write these tables at all. The Next.js server validates each request, then uses the service-role key.

alter table public.link_analytics_events enable row level security;
alter table public.link_contact_requests enable row level security;
alter table public.link_settings        enable row level security;
alter table public.link_daily_stats     enable row level security;

revoke all on public.link_analytics_events, public.link_contact_requests,
              public.link_settings, public.link_daily_stats from anon, authenticated;

revoke all on function public.link_dashboard(date, date, text) from public, anon, authenticated;
grant execute on function public.link_dashboard(date, date, text) to service_role;

Revoking privileges as well as enabling RLS is belt and braces: even a policy added by mistake later would find no table privilege to act on. The database functions get the same treatment, callable only by service_role. On the application side, the service-role client lives in a module that imports server-only, so a build fails if a client component ever imports it.

RPOMS AI does the same thing a different way. The app connects to Postgres directly as the table owner through a connection string, which RLS does not restrict. It never uses the Data API. So one migration loops over every table in public and enables RLS with no policies, closing the API path without affecting the application. Its comment says exactly that: a table without RLS is readable and writable by anyone with the anon key, and this app never uses that API.

Pattern A fits when there is no per-user data in the browser's hands: a public page with an admin area, an internal tool whose server authenticates people itself, or anything where the server is the only client. Its weakness is that isolation between users, if you have users, lives entirely in application code.

ResearchForge, an AI research-paper assistant with private libraries, needed the opposite. Every paper, analysis and review belongs to exactly one account, and "remember to filter by owner" is not a guarantee. So the rule moved into the database:

CREATE POLICY papers_owner_all ON papers
    FOR ALL USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

USING decides which rows a query can see; WITH CHECK stops anyone writing a row they would not own. The part that makes it work is which credential the backend uses. The FastAPI backend calls PostgREST with two headers: apikey carries the public key, which only routes the request to the project, and Authorization carries that user's own access token. Postgres then evaluates auth.uid() as that person, and a forgotten filter returns nothing rather than everything. The repository class requires the token as a constructor argument with no default, because an optional token would make "no token" a silent, valid state.

The native apps use the same pattern. DriveKeep's iOS sync writes to tables whose policies limit every row to its owner; it exists but is off by default. SpenDrop's optional cloud backup uses Supabase Auth, Storage and Postgres with RLS policies. I want to be precise about SpenDrop: its policies have been tested against a simulated server, not yet run on a live Supabase project.

ResearchForge is instructive because it started as Pattern A. Its first migration enabled RLS with no policy and let the backend use the service-role key, which bypasses RLS, so the API worked while the database stayed closed to the browser. The per-user policies were written early, ready for authentication, with a comment explaining why they were harmless at that stage: auth.uid() is null for the service-role key, and user_id = NULL is never true.

The transition is where it went wrong. The policies were correct, but while the backend still held a key that bypasses RLS they enforced nothing, and every automated test passed because the tests modelled RLS with a stub. That defect and how it was found are described in Every Policy Was Correct, and Every Policy Was Inert. The lesson for this comparison: moving from Pattern A to Pattern B is not done when the policies exist. It is done when the server stops holding the key that bypasses them for user requests.

Two practical notes from running them. First, whichever pattern you choose, enable RLS on every table in public; a loop over pg_tables in a migration, as RPOMS AI does, makes that hard to forget. Second, if two apps share one Supabase project, as rukon-link shares DriveKeep's, prefix every table and function (link_ there) so the boundary between them is visible in every query and every policy list.

The project pages for rukon-link, ResearchForge and RPOMS AI describe the systems these migrations belong to.