Supabase Row Level Security Without the Foot-Guns

Supabase hands the browser a database connection. That is the whole product idea and it is genuinely good — but it means the only thing standing between a client-side query and every row in your table is a policy you wrote.

Which is fine when the policies are right, and a public API when they are not. I have opened projects where a table had row level security enabled, one policy reading USING (true), and a team who believed the table was protected because the toggle was green.

This is how I set it up on the products where Supabase is the database — the same Supabase and Neon decision that goes into the stack templates, with the parts that bite spelled out.

What does enabling RLS actually do?#

It flips the default from "everything is visible" to "nothing is visible", and then policies add back specific visibility. That order matters and it is the opposite of how most access control is written.

With no policies at all, a table with row level security enabled returns zero rows to everyone except the roles that bypass it. That is a safe failure and it is why enabling first and adding policies second is the right sequence — the window between the two is closed rather than open.

The second row is the one to aim for while building. A feature that returns nothing is a bug you will find in a minute; a table that returns everything is a bug you may not find at all.

Which key is the client actually using?#

This is the question that decides whether any of your policies matter, and it has exactly two answers.

• The anon key is public by design. It ships in the browser bundle, it is meant to be there, and every query it makes is evaluated against your policies.
• The service role key bypasses row level security entirely. Every policy is ignored. It exists for trusted server-side work and it must never reach the browser.

The service role key in a client bundle is the single worst mistake available in a Supabase project, and it is easy to make because the fix for a policy problem that is blocking you at 2am is to switch keys and move on.

// Browser. Anon key only. Policies apply.
export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
);

// Server only. This file must never be imported by a client component.
import 'server-only';
export const admin = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!, {
  auth: { persistSession: false },
});

The server-only import is worth adding on the first day. It turns an accidental client import into a build error rather than a deployed secret, which is the difference between a failed build and a key rotation.

A NEXT_PUBLIC prefix on a service key is a shipped secret#

Anything prefixed for public exposure is inlined into the bundle at build time. There is no runtime check that catches this and no error — the application works perfectly, which is exactly why it survives. Grep for the service key name across everything prefixed public before every release, or better, have CI do it.

Server code that uses the admin client must do its own checks#

This is the corollary people miss. The moment a route handler uses the service role key, the database has stopped protecting you and every permission decision is back in application code. That is fine, but it has to be deliberate — an admin client used casually for convenience removes the backstop the whole design depends on.

What does a correct policy look like?#

Specific about the operation, specific about the role, and expressed against auth.uid() rather than against anything the client sends.

alter table documents enable row level security;

create policy "read own documents"
  on documents for select
  to authenticated
  using ( owner_id = (select auth.uid()) );

create policy "insert own documents"
  on documents for insert
  to authenticated
  with check ( owner_id = (select auth.uid()) );

Three details in there do real work. The policy names the operation rather than using for all, so a read rule cannot accidentally grant writes. It names the authenticated role, so anonymous visitors are not evaluated against it at all. And it compares against auth.uid(), which comes from the verified JWT and cannot be set by the caller.

USING and WITH CHECK are not the same thing#

USING filters rows that already exist — it decides what a select, update or delete can see. WITH CHECK validates rows being written. An update policy needs both, and the second is what stops a user updating their own row into somebody else's ownership.

create policy "update own documents"
  on documents for update
  to authenticated
  using       ( owner_id = (select auth.uid()) )   -- may touch this row
  with check  ( owner_id = (select auth.uid()) );  -- and may not reassign it away

Without the with check, a user can take a row they legitimately own and set its owner to another account — or, more usefully to an attacker, set a role or status column that the application treats as authoritative. Column-level control is a separate mechanism; the policy only decides which rows.

for all is a trap in a hurry#

One policy covering every operation is shorter, and it means your read rule is also your delete rule. Those are almost never the same rule in a real product — plenty of things a user may see are things they may not remove.

How do you handle organization membership?#

With a helper function marked security definer, so the policy does not have to join through a table the user cannot read.

The obvious policy joins to a memberships table. That produces a recursion problem the moment the memberships table has its own policy referencing the same helper, and the error message when it happens is not obvious.

-- security definer runs as the owner, so the policy on memberships
-- does not re-enter while we are evaluating a policy that needs it.
create function auth.org_ids() returns setof uuid
language sql security definer stable
as $$ select org_id from memberships where user_id = auth.uid() $$;

create policy "read org documents"
  on documents for select to authenticated
  using ( org_id in (select auth.org_ids()) );

stable matters as much as security definer. It tells Postgres the function returns the same result within a statement, so it is evaluated once rather than once per row — which is the difference between a policy that scales and one that turns a thousand-row query into a thousand function calls.

Wrap auth.uid() in a select#

The (select auth.uid()) form rather than a bare auth.uid() is not stylistic. The subquery form is evaluated once per statement; the bare call can be re-evaluated per row. On large tables this is the single largest performance difference in the whole policy, and it costs six characters.

Security definer functions need a locked search path#

A function running as the owner with a caller-controlled search path is a privilege escalation waiting to happen. Set search_path explicitly on every security definer function, and keep them small enough to read in one go — this is code running with elevated rights and it deserves the same scrutiny as anything else that does.

Why did the query get slow?#

Because a policy is a predicate added to every query, and an unindexed predicate is a sequential scan wearing a security costume.

The policy above compares org_id to a set. If org_id is not indexed, every query against that table now filters the whole table before returning anything — including the queries that were fast yesterday, because the policy applies to all of them.

-- Index whatever the policy filters on. Filter column first, sort second.
create index documents_org_created_idx
  on documents (org_id, created_at desc);

This is the same composite index rule as tenant-scoped queries generally, and it applies with more force here because the filter is now invisible in the application code. A developer reading the query sees no org_id condition at all, so the missing index has no obvious cause.

Read the plan with the policy applied#

explain analyze run as the table owner does not show you what the client sees, because the owner bypasses policies. Set the role and the JWT claims first, or the plan you are optimizing is not the plan that runs.

A policy that calls a function per row is the usual culprit#

If a table got dramatically slower after policies went on, look for a volatile function or a bare auth.uid() in the policy before looking anywhere else. Marking the helper stable and wrapping the call in a select routinely takes a query from seconds back to milliseconds.

Which policy pattern fits which table?#

Most tables in a product fall into one of five shapes, and knowing which one you are looking at saves inventing a policy from scratch each time.

The last row is worth calling out because it looks like a mistake. A table with row level security enabled and no policies returns nothing to any client, which is exactly right for data only the server should ever touch — the service role reaches it and nothing else does, without anyone having to remember not to query it from the browser.

The third row is the one that gets written carelessly. A public read policy is legitimate for published content, but it applies to every column, so a profiles table with a public select policy has published every column on it including the ones added later by someone who did not know. Either restrict the grant to specific columns or keep the public shape in its own table.

What about storage and realtime?#

Both have their own access model, and both are commonly left open on a project where the tables are locked down properly.

Storage buckets have policies too#

Files live in storage.objects, which is an ordinary table with row level security. A public bucket serves anything in it to anyone with the URL, and object paths are frequently guessable. Make buckets private and write policies against the path prefix — the organization id as the first path segment, checked the same way the table policies check it.

create policy "read own org files"
  on storage.objects for select to authenticated
  using (
    bucket_id = 'documents'
    and (storage.foldername(name))[1] in (select auth.org_ids()::text)
  );

Realtime broadcasts what the policy allows#

A subscription to table changes is evaluated against the same policies, which is the good news. The bad news is that this is easy to assume rather than verify, and a table with a permissive read policy broadcasts every change to every subscribed client in real time — a leak that is both continuous and invisible in any request log.

Views do not inherit policies the way you expect#

A view created by a superuser runs with the creator's rights, so it can expose rows the underlying policies would deny. Create views with security_invoker = true so the caller's policies apply, and treat any pre-existing view as unprotected until checked.

How do you test policies?#

By running the queries as a user rather than as the owner, and by asserting on what is denied.

Almost every policy bug survives testing because the test ran as the service role or in the SQL editor, both of which bypass the thing under test. A policy test has to impersonate an actual authenticated user or it proves nothing.

-- Impersonate a real user inside a transaction, then roll it back.
begin;
  select set_config('request.jwt.claims', json_build_object('sub', :user_a)::text, true);
  set local role authenticated;

  select count(*) from documents;                 -- expect: only user A's rows
  select count(*) from documents where id = :b_id; -- expect: 0, not an error
rollback;

The second assertion is the important one. A policy that denies a row correctly returns zero rows rather than an error, so a test asserting "this throws" will fail even when the policy is right — and a test asserting "this returns nothing" is the one that catches a leak.

Seed two accounts, always#

A fixture with a single user cannot detect a missing filter, because there is nothing for it to leak. Two accounts with similar-looking data is the minimum, and it is the same rule that applies to every isolation test you will ever write.

Run them in CI, not in the dashboard#

Policies are schema, so they change in migrations and they break in migrations. Putting the impersonation tests in the same suite that runs on every push is what turns a policy from something you verified once into something that stays verified.

When should the client not talk to the database at all?#

Whenever the operation has rules that a policy cannot express, which is more often than the direct-access model suggests.

Row level security answers "may this user see or write this row". It cannot easily answer "may this user invite a member, given the plan's seat limit and their role and whether billing is current". That is business logic with three inputs, and expressing it in SQL produces a policy nobody can read or change safely.

1. Direct client queries for reads and simple owner-scoped writes. This is where the model earns its keep and removes a whole API layer.
2. A route handler or edge function for anything with a rule, a side effect, or a third-party call. Use the anon key with the user's session so policies still apply as a backstop.
3. The service role only for genuinely trusted operations — webhooks, scheduled jobs, admin tooling — with the permission check written explicitly, because the database is no longer doing it.

The mistake at both ends is treating one of these as the answer for everything. All-direct produces policies encoding business rules; all-API throws away the reason to use Supabase and rebuilds a REST layer over a database that did not need one.

Policies are the floor, not the ceiling. They should make a leak impossible, not express your product's rules.

What does a review of an existing project look like?#

Four checks, in order, and the first two find most of it.

List every table without RLS enabled#

One query against pg_tables returns the list. Anything in the public schema without it is readable by anyone holding the anon key, which is everyone who has loaded your site. This is a five-minute check and it is the highest-value five minutes available.

Find the permissive policies#

Search for true in policy definitions. There are legitimate uses — a public content table genuinely readable by everyone — but each one should be a decision somebody made, not a placeholder from a tutorial that stayed.

Grep for the service role key#

Every use, and specifically every use in a file that could be imported by a client component. Add server-only to the module that constructs it so the question cannot recur.

Check storage bucket visibility#

Public buckets and unguarded object paths are the surface people forget, because the tables felt like the whole job. A private bucket with a path-prefix policy takes ten minutes and closes it.

None of this is difficult work and all of it is easy to postpone. It is also the kind of thing where the cost of postponing is not that it becomes harder — it is that the finding comes from somebody outside the project.

Conclusion#

Enable row level security on every table in the public schema before writing any policy, so the failure state is a broken feature rather than an open table. Then add policies one operation at a time — separate select, insert, update and delete rules, scoped to the authenticated role.

Compare against (select auth.uid()) and never against a value the client supplied, pair every update policy with a with check so a user cannot reassign a row away from themselves, and put membership lookups in a stable security definer function with a locked search path.

Index whatever the policies filter on. The predicate is invisible in your application code, so a missing index there produces a slow query with no visible cause — and the composite index leading with the tenant or owner column is what keeps it fast as the table grows.

Keep the service role key on the server behind a server-only import, and remember that every route using it has opted out of the protection entirely and owes its own permission check. Then extend the same thinking to storage buckets, realtime subscriptions and views, all of which have their own defaults and none of which inherit your table policies.

Finally, test by impersonating a real user in a transaction with two accounts seeded, asserting that the other account's rows are absent rather than that the query errors — and run those tests in CI, because policies live in migrations and break in migrations. If you have a live Supabase project and want the policies reviewed before it grows, that is a short, well-defined piece of work.