Supabase Row Level Security check: test and fix your tables

You need owner access to your own Supabase project, the project URL and the public key (shown as the publishable key, formerly called the anon key) from the API settings page, a terminal with curl, and two test accounts in your app. Only run these checks against a project you own.

At the end you will have a list of every table, view and bucket in your project with its protection status, a set of policies that match what your app actually does, and a short script that fails loudly if one user can read or change another user's data.

AI coding tools create tables and queries quickly, and the app works in the preview because your own session sees everything. Nothing in the interface tells you a table is open to the world. A generated migration that creates a table and forgets to enable RLS looks identical to one that does it correctly.

The public key is meant to be in the browser. What protects your data is not hiding that key, it is RLS. Supabase's own documentation says that once RLS is enabled, no data is accessible through the API with a publishable key until you create policies. The reverse is the danger: a table in an exposed schema with RLS off can be read and written through the auto-generated API by anyone holding your project URL and public key, and both are visible in your app's JavaScript.

Open the SQL editor for your project and run the query below. It lists every ordinary table in the public schema with its RLS flag. Any row where rowsecurity is false is a table to fix. If your app exposes other schemas through the API, repeat the query for those.

Expected output: one row per table, false rows sorted first. You have finished this step when you can name the reason for every false row, for example a lookup table that is deliberately public.

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

A table with RLS enabled and no policies denies everything through the API. That is safe but usually means a feature is broken, and it tempts people to paste in a permissive policy to make the error go away. List these tables so you write a real policy instead.

select n.nspname as schema, c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and c.relrowsecurity
  and not exists (select 1 from pg_policy p where p.polrelid = c.oid)
order by c.relname;

Open a terminal that has no session with your app and call the REST endpoint for each table using only the public key. Replace the placeholders with your own values.

If the response is a JSON array with rows in it, an anonymous visitor can read that table. If you get an empty array or a permission error, the table is not readable without a policy. An empty array on a table you know contains data is the result you want. Repeat for every table from Step 1, not just the ones that look sensitive.

curl "https://<project-ref>.supabase.co/rest/v1/<table>?select=*&limit=1" \
  -H "apikey: <publishable-key>" \
  -H "Authorization: Bearer <publishable-key>"

Enable RLS first. That closes the table, and you then open only what each feature needs. Name the role in every policy with a to clause so the logic does not run for roles you did not intend. Supabase notes that auth.uid() returns null for signed-out requests, so state the signed-in requirement explicitly, and wrapping the call in a select lets Postgres evaluate it once per statement instead of once per row.

The using clause filters rows that already exist, and with check validates the row being written. An update policy usually needs both, otherwise a user could change a row's owner column to someone else's id. Add an index on the column your policy filters on, here user_id, so policy checks stay fast as the table grows.

alter table public.orders enable row level security;

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

create policy "Users create their own orders"
  on public.orders for insert
  to authenticated
  with check ((select auth.uid()) = user_id);

create policy "Users update their own orders"
  on public.orders for update
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create index if not exists orders_user_id_idx on public.orders (user_id);

Views run with their owner's permissions by default, which can bypass the RLS on the tables underneath. On Postgres 15 and later you can make a view respect the caller's policies. List your views, then set the option on each one that exposes user data.

Storage has its own protection. Buckets marked public serve every file to anyone with the URL, and private buckets are controlled by policies on the storage.objects table. List your buckets and confirm anything holding user documents is not public.

select viewname from pg_views where schemaname = 'public';

alter view public.my_orders_view set (security_invoker = true);

select id, name, public from storage.buckets;

Anonymous tests do not catch the most common policy bug, which is a policy that only checks that someone is signed in. Sign in as your second test account with the public key and try to read and change the first account's rows. Both calls should return empty results, not the first user's data.

Run this from a local Node script with the values in environment variables, never with a secret key. If the update returns the modified row, your update policy is too loose.

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_PUBLISHABLE_KEY);

await supabase.auth.signInWithPassword({
  email: process.env.USER_B_EMAIL,
  password: process.env.USER_B_PASSWORD,
});

const read = await supabase.from("orders").select("id").eq("user_id", process.env.USER_A_ID);
console.log("read", read.data, read.error);

const write = await supabase
  .from("orders")
  .update({ status: "tampered" })
  .eq("user_id", process.env.USER_A_ID)
  .select();
console.log("write", write.data, write.error);

• Policies written as using (true) on insert, update or delete. That grants the action to everyone the role covers and is equivalent to having no protection for it.
• Trusting a check on a column the user controls, such as a role or owner field sent from the client, instead of auth.uid().
• Forgetting the with check clause on update, which lets a user reassign a row to another user.
• Putting the secret key (formerly service_role) in browser code or a public environment variable. Supabase states it bypasses every RLS policy and must never be in a browser, a shipped app or source control.
• Testing only while logged in as the account that created the data, so every table looks fine.
• Assuming an internal-looking table is safe because no screen uses it. The API exposes it regardless of what your interface shows.

You are done when all of the following hold, and you have seen each result yourself rather than assumed it.

Each new table or migration your AI tool generates can add an unprotected table or a permissive policy. Re-run the Step 1 and Step 2 queries after every schema change, re-run the Step 6 script after every policy change, and repeat the full list before each launch. Keep the script and the queries in your repository so the check does not depend on anyone's memory.