Ayobami ZenthosGet in touch
All insights

One business, many branches: permissions in the database

How Zenthos Gym lets a receptionist see only their own branch while the manager sees everything, using one function and Row Level Security.

Live check-in at the front deskMember home screenManager overview

A gym with three branches has a simple rule nobody writes down: the receptionist in one branch should not be scrolling through another branch’s members, payments and takings. The manager should see all of it.

In most apps that rule lives in the interface. The receptionist’s screen just doesn’t show the other branches. But anyone who can open the browser’s network tab can ask the database directly, and the database will happily answer.

In Zenthos Gym the rule lives in Postgres, so it holds no matter how the data is requested.

One question, asked everywhere

Every permission check comes down to the same question: does the person asking cover this branch? That question is one function:

create or replace function public.staff_covers(p_branch uuid)
returns boolean language sql stable security definer set search_path = public as $$
  select coalesce((
    select role = 'admin'
        or (role = 'receptionist' and (p_branch is null or p_branch = branch_id))
      from profiles where id = auth.uid()
  ), false)
$$;

A manager covers every branch. A receptionist covers their own branch. Anyone else covers nothing, which is what the coalesce(..., false) guarantees when the person has no profile at all.

The p_branch is null case is deliberate. Members who signed up online without choosing a branch are visible to every desk, so nobody falls through a gap between branches.

Policies read like the rule

With that function in place, each table’s policy reads almost like the sentence it enforces. A check-in is visible to the member who made it, or to staff who cover that branch:

create policy checkins_read on check_ins for select to authenticated
  using (user_id = auth.uid() or public.staff_covers(branch_id));

Memberships and payments use exactly the same shape. Members always see their own records; staff see their branch’s; the manager sees all of it. One definition, applied everywhere, means there is no table where someone forgot the rule.

Updates follow the same line

Reading is only half of it. A receptionist can edit a member’s details, but only members in their branch:

create policy profiles_update on profiles for update to authenticated
  using (id = auth.uid() or public.is_admin() or (role = 'member' and public.staff_covers(branch_id)))
  with check (id = auth.uid() or public.is_admin() or (role = 'member' and public.staff_covers(branch_id)));

The with check matters. Without it, a receptionist could edit one of their members and move them into another branch, carrying the record out of their reach and into someone else’s. Checking the row after the change as well as before closes that door.

role = 'member' matters too: a receptionist can edit members, never another member of staff.

Files are covered too

Members upload transfer receipts as proof of payment. Those files sit in storage, and storage has policies just like tables. Each file lives in a folder named after the member, so the same branch rule applies to the folder:

create policy proofs_owner_read on storage.objects for select to authenticated
  using (bucket_id = 'proofs' and (
    (storage.foldername(name))[1] = auth.uid()::text
    or public.staff_covers_member((storage.foldername(name))[1])
  ));

A receipt can be opened by the member who uploaded it, or by staff who cover that member’s branch. Nobody else, whatever link they have.

Alerts go to the right desk

The same rule decides who hears about things. When a new member pays for the first time, the “New paid member” alert goes to the manager and to the receptionists of that member’s branch, not to every desk in the business.

What I took from it

  • Put the rule where every request has to pass it. The interface hides; the database enforces.
  • Name the question once. A single staff_covers function keeps every policy consistent.
  • Check rows after an update, not just before. with check stops data from being moved out of reach.
  • Cover files as well as tables. Storage is just another place data can leak.
  • Decide the edge cases on purpose. “Members without a branch are visible to every desk” is a business decision, written down in code.
See Zenthos Gym