Supabase RLS policies, and the parts that bite

RLS moves authorisation out of your application and into the database, where it applies to every client at once. That is the strength and the reason a mistake in it is total rather than local.

Off by default means public

PostgreSQL ships row level security disabled per table. In a normal backend that is fine, because the only thing holding the connection string is your server. In Supabase the browser talks to PostgREST directly with a key that anyone can read out of the bundle, so a table without RLS is a table on the public internet.

Two statements, and they do different things:

• ALTER TABLE posts ENABLE ROW LEVEL SECURITY; turns it on. With no policies, nothing is visible to anyone, which is the correct default: a table that denies everything is a bug report, a table that allows everything is an incident.
• ALTER TABLE posts FORCE ROW LEVEL SECURITY; makes the policies apply to the table owner as well. Without it, the owner bypasses them, which is why a policy can look broken during testing in the SQL editor and work perfectly from the client.

The Supabase dashboard flags tables that are exposed without RLS, and that warning is worth treating as an outage-level alert rather than a lint. A single unprotected table with a foreign key to your users is enough to enumerate the user list.

USING and WITH CHECK

A policy carries up to two expressions, and the difference between them is where most real holes come from.

USING applies to rows that already exist. It decides what SELECT returns and which rows UPDATE and DELETE are permitted to touch. WITH CHECK applies to rows on the way in: what INSERT may create, and what a row is allowed to look like after an UPDATE.

The hole is an UPDATE policy with only a USING clause. The user may edit rows where user_id = auth.uid(), and nothing constrains the result, so they can set user_id to another user's id and hand the row over. On a billing or documents table that is a real finding. Both clauses are usually the same expression, and writing it twice is the point rather than duplication.

Write one policy per command rather than a single FOR ALL. It is more lines and it is far easier to review, because "who may delete this" is then a question with its own visible answer.

Add TO authenticated or TO anon to every policy. It documents intent, and it also means the planner skips the policy entirely for other roles instead of evaluating an expression that cannot pass.

anon, authenticated, service_role

Supabase maps its keys onto PostgreSQL roles, and the mapping explains what each key can do.

anon is the unauthenticated visitor. The anon key is meant to be public: it identifies the project, not a person, and it is safe in a bundle exactly to the extent that your policies are correct.

authenticated is a signed-in user. The JWT travels with the request, and auth.uid() reads its sub claim while auth.jwt() exposes the rest. Custom claims can drive policies, which is convenient and inherits every property of the token: claims are a snapshot from issue time, so a role revoked five minutes ago is still in a token that lives an hour. That trade-off, and the verification rules that go with it, is the subject of five JWT mistakes.

service_role holds BYPASSRLS. Every policy is ignored. It exists for server-side code that legitimately needs to see everything, and it belongs on a server and nowhere else. The failure mode is specific and common: the service key gets used to make something work during development, then ends up behind a NEXT_PUBLIC_ or VITE_ prefix, and those prefixes compile the value into JavaScript every visitor downloads. If that has happened, rotate first and clean up the repository afterwards, in that order, for the reasons in stop committing secrets.

The recursion trap

Multi-tenant schemas hit this within a day. You want members of an organisation to see each other, so the policy on members checks whether the caller is a member of the same organisation, by querying members. Evaluating the policy requires evaluating the policy, and PostgreSQL stops with infinite recursion detected in policy for relation "members".

Two ways out. The usual one is a SECURITY DEFINER function that performs the lookup with the owner's rights, so the policy is not re-entered:

create function auth.org_ids() returns setof uuid language sql security definer stable set search_path = '' as $$ select org_id from public.members where user_id = auth.uid() $$;

and then a policy of the form org_id in (select auth.org_ids()). Mark it STABLE so the result can be cached within the statement, and always pin search_path, because a SECURITY DEFINER function with a mutable search path is a privilege escalation waiting for someone to create a same-named object in a schema they control.

The other way out is to denormalise: copy the tenant id onto each row so the policy compares two columns and never leaves the table. More writes, no recursion, and much easier to read six months later. On tables that are read constantly and written rarely, we would take the denormalised version every time.

Why your queries got slow

A policy is not a separate access-control layer that runs before the query. It is a predicate the planner adds to the query, so everything you know about slow WHERE clauses applies unchanged.

Three things account for most of the slowdown after enabling RLS:

• Per-row function calls. A bare auth.uid() in a policy can be evaluated for every row examined. Written as (select auth.uid()) the planner can treat it as an InitPlan and run it once. On a table with hundreds of thousands of rows the difference is not subtle, and it is the single most effective RLS optimisation there is.
• Missing indexes. A policy filtering on user_id needs an index on user_id. Without it, every query the policy touches is a sequential scan, and it will not look like a policy problem in your metrics.
• Subqueries in the predicate. A policy joining two other tables runs that join for every query against the protected table. A SECURITY DEFINER helper returning a small set is usually faster than an EXISTS the planner has to re-derive.

The diagnosis is ordinary: EXPLAIN ANALYZE as the authenticated role, and look for the policy expression appearing as a filter with a large row count above it. Our SQL query optimizer takes a query plus your DDL and names the index that is missing, which works the same way whether the predicate came from your WHERE clause or from a policy, and the SQL formatter makes a generated PostgREST query readable enough to reason about in the first place.

Where RLS does not reach

Policies apply to tables. Several things that look like tables are not.

Views. A view has historically run with its owner's privileges, so a view over a protected table returns every row to anyone who can select from the view. PostgreSQL 15 added security_invoker = true, which makes the view run as the caller and respect their policies. Create views with it, and audit any view created before you knew this.

SECURITY DEFINER functions. They run as their owner by design, which is what makes them useful for breaking recursion and what makes them a bypass. Keep them small, keep them few, and pin search_path on every one.

Storage and realtime. Supabase Storage objects and realtime subscriptions have their own policies. A file bucket left open is exactly as public as a table left open, and it is easy to forget because the buckets are configured elsewhere.

Anything reached by the service key. Server-side code with service_role must reimplement the authorisation your policies express, because the database will not do it for you there. An edge function using the service key to "just fetch the user's data" is where tenant isolation quietly ends.

Column visibility. RLS filters rows, not columns. If a row contains a field the user must not see, use column privileges or a view with security_invoker, and remember that PostgREST will happily return any column the role can select.

Testing a policy properly

Policies fail open, quietly, and only for other people's data. Test them the way you would test authorisation code, because that is what they are.

Impersonate the role and the claims in a transaction, then roll back:

begin; set local role authenticated; set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111"}'; select * from posts; rollback;

Then write the negative assertions, which are the ones that matter: user B cannot select user A's rows, cannot update them, cannot reassign one to themselves by changing the owner column, and cannot delete them. Keep those in the migration test suite so the next policy change has to pass them.

A short review checklist that catches most of it:

• RLS enabled on every table reachable by the anon key, and FORCE where the owner also connects.
• Every UPDATE policy has a WITH CHECK, not just a USING.
• Every policy names a role with TO.
• auth.uid() wrapped in a subquery, and the filtered column indexed.
• Views created with security_invoker = true.
• No service_role key anywhere a browser can reach.

When you are writing the client-side queries these policies govern, the shape of a PostgREST filter is its own small language, and our Supabase query generator turns SQL into supabase-js calls with joins expressed as embeds. The policy reference itself is in the Supabase row level security documentation, which is one of the better pieces of writing on the underlying PostgreSQL feature regardless of which client you use.

Questions about row level security