I run a production app on Supabase with eight user roles: field reps, team leads, business leads, key account managers, admins, directors, accounts and logistics. Each sees a different slice of the same tables. The schema has 64 tables and 163 row-level security policies, and the client is never trusted with any of it.
This is the checklist I now apply to every table and function. It's ordered by how badly things break if you skip it.
1. RLS on every table. No exceptions, no "internal" tables
alter table public.vehicle_inspections enable row level security;
A table without RLS is readable by any signed-in user through the auto-generated API. "Nobody queries that table from the app" doesn't matter, because anybody can call the API directly.
Add a check to your migrations or CI that fails if any table in public has RLS disabled:
select tablename from pg_tables
where schemaname = 'public' and not rowsecurity;
-- must return zero rows
2. Policies target authenticated. Nothing is granted to anon
If your app has no public pages, anon should have no policies at all. Write policies to authenticated explicitly rather than relying on the default public role, which includes anon.
Also turn off public sign-ups in Supabase Auth if accounts are created by admins. Otherwise anyone can create an authenticated user and inherit every policy written for "any signed-in user."
3. Role checks live in the policy, not in the app
create policy "accounts validate payments"
on public.payments for update to authenticated
using (
exists (select 1 from public.users u
where u.id = auth.uid() and u.role in ('ACCOUNTS','ADMIN'))
);
Hiding the "Validate" button from field reps is good UX. It isn't security. The policy is.
4. Users must not be able to change their own scope
This is the most common hole I see. If your policies read users.role or users.region_label, and users can update their own row, a rep can promote themselves to director with one API call.
Lock it down with column grants:
revoke update on public.users from authenticated;
grant update (full_name, phone) on public.users to authenticated;
Then role and scope changes go through an admin-only function.
5. SECURITY DEFINER functions do their own authorisation
Definer functions run with the owner's rights and bypass RLS. That's why you use them (aggregates across regions, dashboards), and also why they're dangerous.
create or replace function public.region_summary(p_region text)
returns table (...)
language plpgsql
security definer
set search_path = public -- stops search_path hijacking
as $$
begin
if not exists (select 1 from users where id = auth.uid()
and role in ('TL','BL','ADMIN','DIRECTOR')) then
raise exception 'not allowed';
end if;
...
end $$;
Two rules apply every time: pin search_path, and check the caller's role inside the function body.
6. Keep secrets out of tables users can read
Our push-notification webhook shares a secret with an Edge Function. It sits in an app_settings table, but the policy for that table excludes the secret row from normal users. Assume any row a user can select will eventually be selected.
7. Never put the service_role key in the client
That one key bypasses every policy above. It belongs in Edge Function secrets and nowhere else: not in .env files that ship with the app, not in a config file "just for testing."
8. Test permissions as each role, in SQL, on every change
The checklist only works if it stays true. I keep a SQL test file that impersonates each role and tries the forbidden things:
-- as a field rep, try to read another region's payments
set local role authenticated;
set local request.jwt.claims = '{"sub": "<rep-uuid>"}';
select count(*) from payments where region_label <> 'North';
-- expect 0
A script spins up a temporary Postgres, applies the full schema, runs a functional smoke test and the security suite (33 checks today), and prints ALL SECURITY CHECKS PASSED. Any migration that adds a permission rule adds a test in the same commit.
The short version
| Check | Why |
|---|---|
| RLS enabled on every table | Missing RLS = readable by any signed-in user |
Policies to authenticated, nothing to anon
|
Default role includes anonymous users |
| Public sign-ups off when admins create accounts | Otherwise anyone becomes authenticated
|
| Role checks inside policies | UI hiding is cosmetic |
Column grants on users
|
Stops self-promotion |
Definer functions pin search_path and check role |
They bypass RLS |
| Secrets excluded from readable rows | Readable means read |
service_role never in the client |
It bypasses everything |
| SQL tests per role, every change | Permissions decay silently |
This schema is part of Beatplan field sales app, a field sales system I now license as full source code. Buyers get the migrations, all 163 policies and the security test suite, so they can verify everything above themselves before they trust it with their team's data.
Top comments (0)