A technical deep dive into the mechanics of RLS — the SQL pieces, the USING vs WITH CHECK distinction, JWT integration in Supabase, performance characteristics, and the gotchas that cause production incidents.
How this guide works. This is a deep technical reference, not an introductory tutorial. We’re drawing on the PostgreSQL Row Security Policies documentation and the Supabase RLS guides, and assuming you’ve already written some Postgres SQL.
Most SaaS apps written before 2020 enforce authorization in application code. The pattern looks like this: your API receives a request, your code reads the user ID from the session, and every database query includes a WHERE user_id = $1 clause. The database has no opinion about who is allowed to see which rows; it returns whatever the application asks for. The application is the gatekeeper.
This pattern works until it doesn’t. The day someone forgets the WHERE clause on a single endpoint, you have a cross-tenant data leak. The day a junior engineer writes a query that JOINs across an unfiltered table, the same. The day someone adds a new ORM helper and forgets to scope it, the same. Application-layer auth is centralized in code that, in practice, is enforced everywhere — which means it is enforced nowhere uniformly.
Row Level Security inverts this. Instead of the application telling the database which rows to return, the database itself decides which rows the current connection is allowed to see. The application can issue SELECT * FROM messages without a WHERE clause, and the database silently returns only the rows the current authenticated user is permitted to see, by applying a security policy attached to the table. The mistake of forgetting the WHERE clause becomes architecturally impossible.
This is the conceptual shift. Everything that follows is mechanism.
RLS in Postgres is composed of exactly three primitives. Knowing these three gives you 90% of working knowledge.
You enable RLS on a per-table basis. Until you enable it, the table behaves like any other table — all rows are visible to anyone with SELECT permission.
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;The critical detail: enabling RLS without defining any policies makes the table completely inaccessible to non-superusers. The default-deny stance is intentional. If you enable RLS and then notice your queries return zero rows, this is why.
A policy is a named rule that tells the database which rows a given connection can see (or write). Policies are SQL expressions that return a boolean for each row.
CREATE POLICY user_can_see_own_messages
ON messages
FOR SELECT
USING (user_id = auth.uid());This policy says: when the current session is selecting from messages, only rows where user_id equals the session’s authenticated user ID are visible. The expression in USING is evaluated row-by-row at query time. Rows where the expression returns true are returned; rows where it returns false are silently filtered out.
You can attach multiple policies to the same table for different operations (SELECT, INSERT, UPDATE, DELETE, or ALL). Multiple policies are combined with OR by default, meaning a row is visible if any policy permits it. You can change this to AND with AS RESTRICTIVE, which we’ll cover below.
By default, the table owner bypasses RLS. The reasoning: the owner of a table is presumed to be a privileged maintenance role. In practice, this default is dangerous in SaaS contexts because your application’s migration role is usually also the table owner, and it can accidentally bypass RLS during migrations or maintenance.
ALTER TABLE messages FORCE ROW LEVEL SECURITY;With FORCE enabled, even the table owner is subject to RLS policies. This is the safer default for SaaS production tables. The PostgreSQL docs note this is necessary if you want full enforcement; without it, the owner can read everything by accident.
This is where most founders get tripped up. USING and WITH CHECK look superficially similar but solve different problems, and conflating them is a common source of subtle bugs.
USING filters rows that the current operation can see or act on. For SELECT and DELETE, USING determines which rows are visible/affectable. For UPDATE, USING determines which existing rows can be updated.
WITH CHECK validates rows that the current operation is writing — either a new row from INSERT or the new state of an updated row. WITH CHECK runs against the post-update or post-insert row state and rejects the operation if it returns false.
Concrete example: imagine a posts table where users can only see and edit their own posts.
CREATE POLICY users_select_own_posts
ON posts FOR SELECT
USING (user_id = auth.uid());
CREATE POLICY users_insert_own_posts
ON posts FOR INSERT
WITH CHECK (user_id = auth.uid());
CREATE POLICY users_update_own_posts
ON posts FOR UPDATE
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());Look closely at the UPDATE policy. It has both clauses. USING says “the user can only update rows they currently own.” WITH CHECK says “and the row, after the update, must still belong to them.” Without the WITH CHECK, a user could UPDATE their own post and reassign user_id to someone else, effectively giving themselves write access to that other user’s post going forward (or at minimum, polluting the data).
If you only had USING on UPDATE, the database would let you change user_id to any value as long as the row started out belonging to you. The WITH CHECK closes this loophole.
The shorthand: USING controls what you can touch. WITH CHECK controls what state your changes can leave the row in. If your UPDATE policy doesn’t have both, you have a hole.
The above examples reference auth.uid(). This is not a built-in Postgres function. It’s a Supabase convention, and understanding how it works under the hood is critical because a misconfiguration here breaks RLS in subtle ways.
When a Supabase client makes a request, it includes a JWT in the Authorization header. The Supabase API gateway validates the JWT, then forwards the request to PostgREST (Supabase’s REST layer over Postgres). PostgREST opens a database connection and, before running the user’s query, executes:
SET LOCAL request.jwt.claims = '<the JWT’s claims as JSON>';
SET LOCAL ROLE authenticated;The JWT claims are now available to the SQL session via the standard Postgres current_setting() function. Supabase defines auth.uid() as a wrapper:
CREATE OR REPLACE FUNCTION auth.uid()
RETURNS uuid
LANGUAGE sql STABLE
AS $$
SELECT
coalesce(
nullif(current_setting('request.jwt.claim.sub', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
)::uuid
$$;This function reads the sub claim from the JWT and casts it to a UUID. That UUID is what your RLS policies compare against.
You can also read other claims directly. If your JWT includes a custom claim like workspace_id, you can reference it in policies:
CREATE POLICY workspace_isolation
ON projects FOR ALL
USING (
workspace_id = (
current_setting('request.jwt.claims', true)::jsonb ->> 'workspace_id'
)::uuid
);The implication: your auth provider must include the right claims in the JWT for RLS to work. If you’re using Clerk with Supabase, you have to configure Clerk to issue Supabase-compatible JWTs with the appropriate claims, or your auth.uid() calls will return null and your queries will return zero rows.
The most common performance trap with RLS: founders enable it, write the policies, and notice that previously-fast queries are now slow. The reason is that RLS policies effectively add a WHERE clause to every query, and if the columns referenced in your policies aren’t indexed, you’ve just turned every query into a sequential scan with a filter.
Concrete example: if your policy is user_id = auth.uid() and messages.user_id has no index, Postgres will scan the entire table for every query and filter rows. On a 10-million-row table, this is a multi-second query that used to be milliseconds.
The fix is straightforward but easy to forget:
CREATE INDEX idx_messages_user_id ON messages(user_id);For multi-tenant SaaS, this means every table that has a workspace_id or tenant_id column referenced in RLS policies needs an index on that column. Often a composite index is better: if you typically query WHERE workspace_id = X ORDER BY created_at DESC, the right index is on (workspace_id, created_at DESC), which both serves the RLS filter and the ORDER BY.
Second performance consideration: function calls in RLS policies. auth.uid() is marked STABLE, which means Postgres may evaluate it once per query rather than once per row — but only if the planner can prove the value is constant within the query. Subqueries, joins, and certain plan shapes can defeat this optimization, causing auth.uid() to be evaluated per row. For a query against a million rows, this means a million function calls instead of one.
Mitigation: in performance-critical queries, fetch the user ID once and pass it as a parameter rather than relying on the policy to call auth.uid() for every row. The Supabase team has written about this trade-off in their performance documentation.
The following gotchas have caused real production incidents in real solo SaaS apps. Each is worth memorizing.
Supabase’s service_role key bypasses RLS entirely. This is by design — it’s the equivalent of root access. The danger: if the service role key ever leaks into client-side code, every RLS protection on your database is silently disabled for the attacker.
Service role keys belong on servers only. Never in browser bundles, mobile apps, or anywhere a user can extract them. The day a service role key appears in a public GitHub repo is the day someone enumerates your entire database.
You can CREATE POLICY on a table without first ENABLE ROW LEVEL SECURITY. The policy is created. It does nothing. The table behaves as if RLS weren’t configured.
This silent no-op is one of the most dangerous configurations because everything looks correct in the database schema, all your policies appear to be defined, and yet none of them are enforced. Always check both: policies exist AND relrowsecurity is true on the table:
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'messages';If a policy on table A queries table B, and a policy on table B queries table A, you have a circular dependency. Postgres will detect it and produce a runtime error, but only on the queries that actually hit the recursion. The error can appear weeks after deploying the policies, when a specific query path triggers the loop for the first time.
Mitigation: if a policy needs to look up data in another table, prefer using SECURITY DEFINER helper functions that bypass RLS for the lookup itself, or use a SECURITY INVOKER function that’s carefully scoped. The Postgres docs cover this in detail.
If a policy expression evaluates to NULL (which happens easily with NULL columns), Postgres treats NULL as “not satisfied” for USING and WITH CHECK. This means a row with user_id IS NULL in a policy user_id = auth.uid() will not be returned, even if you’re trying to query for unowned rows. Use explicit IS NULL checks if you need to handle that case.
By default, multiple policies on the same operation are combined with OR — a row is visible if any policy allows it. If you want all policies to be required (AND combined), declare them AS RESTRICTIVE. Most multi-tenant patterns want at least one restrictive policy: “the row must belong to your workspace” combined with a permissive policy: “the user must have at least one of these roles.”
For a more practical multi-tenant walkthrough, see our guide on setting up Supabase RLS for multi-tenant SaaS, and our overview of what is RLS at what is RLS.
Stop thinking of RLS as “a feature you turn on.” Start thinking of it as “a WHERE clause that the database appends to every query you write, based on the current session’s identity.” That mental model gets the performance characteristics right (index the columns referenced in policies), gets the security boundary right (the application can’t leak data because the database itself filters), and gets the gotchas right (service role bypasses; null evaluates to not-satisfied; the policies must be enabled, not just created).
If you’re evaluating whether to use Supabase or another Postgres-based stack, RLS is one of the most compelling reasons to choose Postgres — the same primitive works on any Postgres host, including Neon and self-hosted. We’ve compared the options in Supabase vs Firebase; Firebase’s security rules are conceptually similar but operationally different, and in our opinion less powerful for relational data.
The stack, prompts, pricing, and mistakes to avoid — for solo founders building with AI.