Testing Supabase Row Level Security Before You Launch

In a typical Supabase app, the browser talks almost directly to the database through an auto-generated API. The anon key that makes this possible is public by design, so the only thing standing between a curious visitor and your users' data is Postgres row level security. If those rules are missing or too loose, the app can look perfectly fine in the UI while every record is readable by anyone. This post walks through how to test RLS properly instead of trusting that it is there.

The model in one paragraph

Each table in the exposed schema (usually public) can have RLS enabled. When it is enabled and no policy matches, access is denied. Policies are SQL expressions evaluated per row: using decides which existing rows a user can see, update or delete, and with check decides which new or changed rows they are allowed to write. Inside a policy, auth.uid() returns the ID of the signed-in user. The service_role key bypasses RLS entirely, which is why it must never be used in client code.

A minimal pair of policies for a table owned by individual users looks like this:

alter table public.notes enable row level security;

create policy "owners read their notes"
  on public.notes for select
  to authenticated
  using (auth.uid() = user_id);

create policy "owners insert their notes"
  on public.notes for insert
  to authenticated
  with check (auth.uid() = user_id);

Step 1: Inventory what is actually protected

Start with facts rather than assumptions. In the SQL editor, list every table in the public schema and whether RLS is on:

select tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by tablename;

Then list the policies themselves:

select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

Flag three patterns for attention: tables with rowsecurity = false, policies whose expression is simply true, and tables that have a select policy but no insert, update or delete policies (which may be intentional, or may mean a feature silently fails). Also check views: a view runs with its owner's privileges by default, so it can expose rows that the underlying table's policies would hide unless it is created with security_invoker = true.

Step 2: Impersonate a user in SQL

You can test policies directly by switching role and setting the JWT claims that auth.uid() reads, inside a transaction that you roll back:

begin;
select set_config('request.jwt.claims',
  '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}', true);
set local role authenticated;

select count(*) from public.notes;               -- should only count this user's rows
update public.notes set body = 'x'
  where user_id <> auth.uid();                   -- should affect 0 rows

rollback;

Replace the UUID with a real test user's ID from the auth users table. Run the same block with set local role anon; and no claims to see what an unauthenticated visitor gets. Write down the expected result for each query before you run it, so you are testing against a specification rather than eyeballing output.

Step 3: Attack through the public API

SQL tests prove the policies; API tests prove the app's real exposure. Create two test accounts, A and B, and give each some data through the normal UI. Then, using only the anon key, request data directly:

curl "https://YOUR-PROJECT.supabase.co/rest/v1/notes?select=*" \
  -H "apikey: YOUR_ANON_KEY" \
  -H "Authorization: Bearer YOUR_ANON_KEY"

An unauthenticated request like this should return an empty array or an error for private tables. Next, sign in as user A in the browser, copy A's access token from a request in the Network tab, and use it as the bearer token. Try to read B's rows by filtering on B's ID, update one of B's rows, and insert a row with user_id set to B. Every one of these should fail or affect nothing.

Do not forget storage. File buckets are governed by policies on storage.objects, and a public bucket serves files to anyone with the URL. Try fetching another user's uploaded file path while signed in as A.

Common failures and what they mean

• Everything returns empty after enabling RLS: no select policy exists, or it references the wrong column.
• Inserts fail with a policy violation: the with check expression does not match the data the client sends; often the client forgets to set user_id.
• Users can change ownership of a row: the update policy has using but no with check, so a user can update a row they own and set user_id to someone else.
• Admin features work for everyone: the check relies on a field the user can edit, such as a role column in their own profile row.

If your audit turns up more than a handful of these, or the app was generated by an AI tool and you are not sure what each table is for, it is reasonable to bring in someone who writes Postgres policies regularly. A database security review is a common, clearly scoped job on the Workli freelance marketplace and elsewhere; giving the developer your inventory queries and failed API tests will shorten it considerably.

Row level security is powerful precisely because it lives in the database, beneath any client code an AI tool might regenerate. But it only protects you if it is tested from the outside, as a stranger and as the wrong user. Make these checks part of every release, and keep the SQL and curl scripts in the repository so the next person can run them too.