Supabase RLS: how to secure your database with row level security

What does RLS do in Supabase?

Supabase puts an API directly on top of your Postgres database. Your app talks to it with the publishable key (the anon key in older projects), and that key sits in your browser code. Anyone who opens your site can find it and send their own requests to your database.

Row level security (RLS) is the Postgres feature that decides what such a request may do. Per table you write rules, policies, that decide per row whether someone may read, insert, update or delete it. Postgres adds those rules to every query itself. If someone asks for all orders, they only get the rows the policy says yes to.

So in Supabase, RLS is not an extra lock on the door, it is the door. Without RLS, the public key is enough to read an entire table. That is by far the most common finding in apps built with an AI tool.

Prefer the explanation without code first? Read row level security in Supabase explained.

Enabling Supabase RLS, and why you never turn it off

If you create a table in the dashboard's Table Editor, RLS is on by default. If you create it with SQL or a migration, which Lovable, Cursor and Bolt almost always do, RLS stays off until you enable it yourself:

alter table public.todos enable row level security;

-- Which tables in public still have no RLS?
select tablename
from pg_tables
where schemaname = 'public' and rowsecurity = false;

• RLS off: anyone with the public key can do anything in the table, even without logging in.
• RLS on without a policy: nobody gets in through the API, not even your own app. Safe, but your app shows nothing.
• RLS on with policies: whoever is allowed can do exactly what the policy permits, and nobody else.

So do not turn RLS off 'for a moment' to get rid of an error. That error usually means a policy is missing. Without RLS it disappears, together with your security, and because your key is public, 'a moment' is long enough.

The anon and authenticated roles

Every request to your database carries a Postgres role. In a policy, 'to' sets which role the rule applies to:

• anon: a visitor who is not logged in and only uses the public key.
• authenticated: someone who is logged in. Note: that is anyone who can create an account, not just your customers. 'to authenticated using (true)' therefore means: open to anyone who signs up.
• service_role: the role behind the secret key (formerly the service role key). It ignores RLS completely and belongs on the server only, never in your frontend or your repo.

In policies you use auth.uid(): the id of the logged-in user from their token. For a visitor without an account it is empty, so a rule like 'auth.uid() = user_id' keeps logged-out visitors out automatically.

Writing an RLS policy: one rule per operation

Say you have a todos table with a user_id column that points to the user. Each user may only see and manage their own tasks. Then you write one policy per operation:

-- Read: only your own rows
create policy "Read own todos"
on public.todos for select
to authenticated
using ( (select auth.uid()) = user_id );

-- Insert: only rows in your own name
create policy "Insert own todos"
on public.todos for insert
to authenticated
with check ( (select auth.uid()) = user_id );

-- Update: only your own rows, and they stay yours
create policy "Update own todos"
on public.todos for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

-- Delete: only your own rows
create policy "Delete own todos"
on public.todos for delete
to authenticated
using ( (select auth.uid()) = user_id );

• using decides which existing rows someone may see, update or delete.
• with check decides what a new or changed row may look like. Without with check on update, someone can move their task into another user's name.
• An update only works if there is also a select policy: Postgres must be able to read the row first.
• Wrap auth.uid() in (select ...) and put an index on user_id. Postgres then computes the id once instead of per row, which makes a big difference on large tables.

Common mistakes in RLS policies

• using (true). Feels like security, but lets everyone see everything. Always tie a policy to auth.uid() or to a role.
• Roles in the profile table. If 'admin' lives in a table the user may update themselves, someone will make themselves admin. Use a separate roles table.
• Permissions from user_metadata. Users can change that data themselves, so it does not belong in a policy. Use app_metadata or your own table.
• Views without security_invoker. By default a view ignores the RLS of the tables underneath. Create views 'with (security_invoker = true)'.
• Security definer functions in a public schema. Such a function runs with its creator's rights and skips RLS. Put it in a schema the API cannot reach and give it a fixed search_path.

Checking an admin role with a security definer function

If an administrator must be able to see everything, create a separate roles table without policies (so unreachable through the API) and a function that looks up the logged-in user's role. Supabase recommends putting such a function in its own schema:

create schema if not exists private;

-- RLS on and no policies: nobody can reach this through the API
create table public.user_roles (
  user_id uuid primary key references auth.users on delete cascade,
  role text not null check (role in ('user', 'admin'))
);
alter table public.user_roles enable row level security;

create function private.is_admin()
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = (select auth.uid()) and role = 'admin'
  );
$$;

revoke execute on function private.is_admin() from public;
grant usage on schema private to authenticated;
grant execute on function private.is_admin() to authenticated;

create policy "Admins read all todos"
on public.todos for select
to authenticated
using ( (select private.is_admin()) );

Because the function is security definer, it may read the roles table while the user cannot. With search_path = '' you prevent anyone from slipping a different table underneath through a schema of their own. Postgres combines policies for the same operation with 'or': an admin sees everything, a regular user still only their own tasks.

Testing Supabase RLS: how to know it holds

A policy that looks right can still leak. So test in three ways:

• The Security Advisor in your dashboard (under Advisors). It reports, among other things, tables without RLS, RLS without policies, policies that are always true, views that bypass RLS and policies that use user_metadata.
• As a user in SQL. With 'set local role' and 'set local request.jwt.claim.sub' you impersonate a visitor or a logged-in user inside a transaction, and see exactly what they get to see.
• Automatically with pgTAP. The Supabase CLI runs every test in the supabase/tests/database folder with 'supabase test db', so every policy is checked again on every change.

An RLS test with pgTAP

This test checks that a visitor without an account sees nothing and that a logged-in user sees no tasks of others. Put it in supabase/tests/database/todos_rls.test.sql, make sure you have test data with tasks from at least two users, and run supabase test db. You enable pgTAP once with 'create extension if not exists pgtap with schema extensions'.

begin;
select plan(2);

-- Not logged in: no tasks visible at all
set local role anon;
select is_empty(
  'select * from public.todos',
  'anon sees no todos'
);

-- Logged in as test user: no tasks of others
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select is_empty(
  $$ select * from public.todos
     where user_id <> '11111111-1111-1111-1111-111111111111' $$,
  'user sees no todos of others'
);

select * from finish();
rollback;

The test ends with rollback, so it leaves nothing behind in your database.

Beyond RLS: keys, edge functions and storage

RLS protects your tables, but not everything. AI tools often leave these gaps open too:

• The secret key or service role key in your frontend, in a bundled .env file or in a public repo. Search your project for 'service_role' and 'sb_secret_', and rotate the key if it has ever been public.
• Keys in your git history. Removing them from the current file is not enough; only rotating fixes it.
• Edge functions without checks. Check per function whether JWT verification is on and whether the function really validates the caller.
• Public storage buckets. User uploads in a public bucket can be guessed and downloaded by anyone. Make the bucket private; storage also works with RLS policies, on the storage.objects table.

See what to do when a key has leaked if your secret key has been public.

Securing Supabase: the checklist

In short, and in this order:

• Enable RLS on every table in the public schema.
• Give every table one policy per operation, tied to auth.uid() or a role.
• Look for 'using (true)' and 'to authenticated' without an ownership check.
• Move roles out of the profile table and never use user_metadata in a policy.
• Set security_invoker on your views and keep security definer functions in their own schema.
• Keep the secret key out of your frontend and your repo, and rotate it if it was ever public.
• Check every edge function for authentication and make storage buckets private.
• Run the Security Advisor and your pgTAP tests on every change, not once.

Use the Supabase security checklist before you launch and compare a scan or pentest for further checks.