Why We Moved Authorization to Postgres: Supabase RLS in Production

When our team started building a multi-tenant dashboard on Supabase, the default instinct was to handle authorization in the API layer — middleware checks, scoped queries, the usual pattern. But Supabase's architecture changes the calculus: the frontend talks to Postgres through PostgREST, and every extra hop through a custom backend adds latency and surface area. We decided to push row-level authorization into the database itself using PostgreSQL Row Level Security (RLS). Six months in, here's what that decision actually looks like in practice.

The mental model shift

RLS isn't a Supabase feature — it's core Postgres. What Supabase provides is the plumbing: the JWT from Supabase Auth gets passed to Postgres for each request, and the auth.uid() function extracts the user ID from that token. A policy comparing auth.uid() to a column gives you per-row filtering without the client ever seeing a user_id parameter.

Two behaviors catch people off guard. First, enabling RLS on a table denies all access until a policy explicitly allows it — there's no default-allow. Second, superusers and any role with BYPASSRLS skip policies entirely. The service_role key used by server-side jobs has this attribute, which is exactly why it must never reach a browser.

A policy set that survived code review

We started with a tasks table. The policies below are the minimal set for a private-per-user resource. Run them in the SQL Editor as a role with CREATE privileges:

create table tasks (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  user_id uuid references auth.users(id) not null,
  created_at timestamptz default now()
);

alter table tasks enable row level security;

create policy tasks_select_own on tasks
  for select using (auth.uid() = user_id);

create policy tasks_insert_own on tasks
  for insert with check (auth.uid() = user_id);

create policy tasks_update_own on tasks
  for update using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create policy tasks_delete_own on tasks
  for delete using (auth.uid() = user_id);

create index tasks_user_id_idx on tasks (user_id);

The index isn't decorative. Every RLS-filtered query carries the policy predicate, and without an index on user_id the planner falls back to a sequential scan as the table grows. We learned this the hard way on a staging dataset of ~200k rows — a missing index turned a 2ms query into 400ms.

After this, select * from tasks from an authenticated client behaves as though where user_id = auth.uid() were appended. The insert policy's with check rejects rows claiming someone else's ID. The client never supplies user_id; the database enforces it.

Verification that isn't theater

Policies that look correct can still be wrong. Three checks catch most mistakes before they ship:

1. Sign in as two different users and query the same table from each. Each should see only its own rows. The Supabase client libraries make this straightforward; swapping JWTs manually against PostgREST works too.
2. Query the table without a session. With the policies above, an anonymous request returns zero rows rather than an error, because no policy grants access to the anon role.
3. Run explain analyze select * from tasks where user_id = auth.uid(); as the authenticated role and confirm the plan shows an index scan on tasks_user_id_idx rather than a sequential scan.

Critical caveat: the SQL Editor runs as a privileged role, so queries there bypass RLS and show every row. That's expected, and it's also why "it works in the editor" is not evidence that policies are correct.

Where RLS gets expensive

Two limitations shaped how we use RLS in production.

First, policies that join other tables per row can be slow. A policy like using (exists (select 1 from memberships m where m.team_id = tasks.team_id and m.user_id = auth.uid())) re-evaluates that subquery for every candidate row unless the planner rewrites it well. For team-scoped access, we denormalized team_id onto the row and indexed it, or put team membership into JWT claims via a custom claim hook. Both beat a per-row join.

Second, RLS hides authorization failures. A blocked read returns an empty set, not a 403, so debugging means inspecting policies rather than reading an error message. We keep coarse-grained business rules — "only admins can archive a project" — in application code where they're readable, and use RLS for the row-level isolation it's good at.

What we'd tell a team starting today

Enable RLS on every table a client can reach. Write one policy per operation you actually need. Index the columns those policies filter on. Verify with two real user sessions before shipping. Keep the service_role key server-side. That combination gives you direct client-to-database access without trusting the client to filter its own rows — and without building a custom API layer just to append WHERE user_id = ?.