Turn on row-level security for every table

How to do it

Builders often pair the app with a hosted database, such as Supabase, that the browser can query with a public key. That key is public by design, so only the database policies stand between strangers and your data. The secret service_role key skips every policy, so it never reaches the browser. Keep each customer's data apart lists the roles that bypass policies. Write policies that compare the row's owner with the signed-in user. Insert and update policies need a with check clause too, or users can write rows for someone else. Do the same for file storage buckets. Each policy keeps customers apart as Keep each customer's data apart says. Attack it as Check ownership on every record shows.

Examples

SQL example

alter table orders enable row level security;
create policy "owners read their orders" on orders
  for select to authenticated
  using ((select auth.uid()) = customer_id);
create policy "owners add their orders" on orders
  for insert to authenticated
  with check ((select auth.uid()) = customer_id);

-- Check: must return no rows
select tablename from pg_tables where schemaname = 'public' and not rowsecurity;

How to check

A query lists public tables without row-level security, and it returns no rows. Using only the public key, read each table signed out, then as another user. Both return nothing.

Sources

• OWASP Authorization Cheat Sheet

Related rules

• DAT-7 Keep each customer's data apart in Data and databases
• ACC-77 Check ownership on every record in Accounts and identity
• TDD-11 Test the access rules on the running product in Technical due diligence