Locking down a CRM with Postgres row-level security

The admissions system I built for a group of schools has one rule that matters more than any feature: a registrar at one campus must never see a parent's phone number from another campus. Not in the UI, not in an API response, not by editing a request in the browser's dev tools.

You can try to enforce that in the front end. It works right up until someone opens the network tab. So the rule lives in the database instead.

Why the database, not the app

The app is Next.js talking to Supabase, which is Postgres underneath. Supabase gives every signed-in user a token, and Postgres can read who that user is on every query. Row-level security (RLS) lets you attach a condition to a table that Postgres checks on every row, for every query, no matter where the query comes from.

That means the app code can ask for "all leads" and still only get the right ones. There is no way to forget the filter, because the filter is not in the app.

The shape of it

Every staff login has a profile with a role and a campus. A lead belongs to a campus. The policy for reading leads says: you can see a row if it belongs to your campus, or if you are a super admin.

alter table leads enable row level security;

create policy "Read own campus leads" on leads
  for select using (
    exists (
      select 1 from profiles p
      where p.id = auth.uid()
        and (p.role = 'super_admin' or p.school_id = leads.school_id)
    )
  );

Notes, the activity log and appointments follow the same pattern, so a lead's history is exactly as private as the lead.

One rule I learned the hard way: keep the policy on profiles itself simple. If a policy on profiles reads profiles again, Postgres can end up recursing, and the error you get is not obvious. Read your own row by auth.uid() and stop there.

A role that can write but not read

Leads also arrive from Facebook and Instagram ads. An outside social media agency exports them from Meta Ads Manager as a CSV, and someone has to get that file into the system.

I did not want that agency to be able to browse leads, phone numbers or notes. So their login gets a socials role with no select policy on leads at all. Without a policy, Postgres returns nothing.

Importing goes through one function instead of a direct insert:

create or replace function import_social_leads(target_school uuid, rows jsonb)
returns integer
language plpgsql
security definer
set search_path = public
as $
begin
  -- check the caller really has the socials role, skip duplicates,
  -- tag each lead as facebook or instagram, then insert
  ...
end;
$;

security definer means the function runs with its owner's rights, so it can insert into a table the caller cannot read. The caller gets back a count of rows imported and nothing else. They can do the one job they need to do, and that is all.

Migrations you can run twice

Every change to the schema is a plain SQL file, and every statement in it is safe to run again: create table if not exists, create or replace function, and drop policy if exists before each create policy.

That sounds fussy, but it means anyone on the team can paste a migration into the Supabase SQL editor without worrying about whether it already ran. In a small team without a release process, that matters more than any tooling.

What I would tell myself at the start

• Put access rules in the database from day one. Retrofitting them is much harder than writing them first.
• Test policies by signing in as each role and trying to break them, not by reading the SQL.
• When a role needs to write data it should not see, give it a function, not a table.