None, View, Edit: per-module access in Supabase

Instead of fixed roles, store one access level per user per module: none, view, or edit, with owners above it all. Three SQL functions answer every permission question, one helper writes the policies for every table from those functions, and the settings screen edits the same table the database enforces. What the owner sets is exactly what the database allows.

This is part 4 of the build log of the ZS Tech CRM. Part 2 hid money columns; this part decides who reaches each module at all.

Why not roles

The original design had two roles, admin and field. Real companies are not that tidy. A senior technician should tick off installation tasks but not move deals between stages. A salesperson should build quotes but not see the company's bank balance. An accountant should see money but has no business editing the price book. Every new person becomes a new role, and roles multiply until nobody remembers what "field plus" means.

So the CRM has seven modules (Dashboard, Pipeline, Contacts, Projects, Price book, Money, Settings) and each user gets None, View, or Edit on each one. Owners have Edit everywhere and manage users.

One table, three functions

create type public.app_module as enum
  ('dashboard','pipeline','contacts','projects','pricebook','money','settings');
create type public.access_level as enum ('none','view','edit');

create table public.module_access (
  user_id uuid not null references public.profiles(id) on delete cascade,
  module public.app_module not null,
  level public.access_level not null default 'none',
  primary key (user_id, module)
);

create or replace function public.access_level(m public.app_module) returns public.access_level
language sql stable security definer set search_path = public as $$
  select case
    when public.is_owner() then 'edit'::public.access_level
    when not coalesce((select active from public.profiles where id = auth.uid()), false)
      then 'none'::public.access_level
    else coalesce((select level from public.module_access
                    where user_id = auth.uid() and module = m), 'none'::public.access_level)
  end
$$;

-- can_view(m): level in ('view','edit')   can_edit(m): level = 'edit'

Three details in there carry weight. A missing row means none, so a new module is closed until someone opens it. A deactivated user drops to none everywhere without deleting their history. And every function is security definer with a fixed search_path, so a policy can call them without the caller needing access to the access table itself.

One helper writes every policy

Writing four policies by hand for thirty tables is how one of them ends up wrong. A small function, used once in the migration and then dropped, writes them from two expressions:

create or replace function public._apply_rls(tbl text, view_expr text, edit_expr text) returns void
language plpgsql as $$
begin
  execute format('alter table public.%I enable row level security', tbl);
  execute format('create policy %I on public.%I for select to authenticated using (%s)',
                 tbl || '_select', tbl, view_expr);
  execute format('create policy %I on public.%I for insert to authenticated with check (%s)',
                 tbl || '_insert', tbl, edit_expr);
  execute format('create policy %I on public.%I for update to authenticated using (%s) with check (%s)',
                 tbl || '_update', tbl, edit_expr, edit_expr);
  execute format('create policy %I on public.%I for delete to authenticated using (%s)',
                 tbl || '_delete', tbl, edit_expr);
end $$;

select public._apply_rls('contacts', $$public.can_view('contacts')$$, $$public.can_edit('contacts')$$);
select public._apply_rls('payments', $$public.can_view('money')$$,    $$public.can_edit('money')$$);
-- ...thirty tables in all, then:
drop function public._apply_rls(text, text, text);

The whole permission model of the database now fits on one screen of the migration, and reviewing it means reading one line per table.

Shared tables take either module

Some data belongs to more than one module, and the expressions say so plainly:

Mixed rights are the normal case, not an exception. A technician with View on Pipeline and Edit on Projects can open a deal and tick off installation tasks, but an update to the deal itself affects zero rows. There is a test for exactly that sentence.

Functions that change state check again

Moving a deal between stages is a database function, because it does several things at once: history, quote number, tasks, the payment plan. It runs as definer to do that work, which means RLS does not protect it, so it checks first:

if not public.can_edit('pipeline') then raise exception 'no access to pipeline'; end if;

Any security definer function that writes is a hole in the policy layer unless its first line repeats the check. That is the one rule I would put on a poster.

Guarding the dangerous columns

Users may edit their own name and phone. They may not make themselves an owner, reactivate themselves, or change their email to someone else's. A trigger on the profiles table rejects those changes for anyone who is not an owner, even on their own row. New users never sign themselves up: public sign-up is off, owners invite by email from Settings, and a trigger creates the profile when the invited user first appears.

The app layers on top

1. The sidebar shows only modules the user can view.
2. Every page starts with requireModule('pipeline'), which redirects to a no-access page, and edit buttons render only when canEdit is true.
3. Every server action checks edit access again, validates input with zod, and writes with a client scoped to the user.
4. Postgres applies the policies regardless of what the app did.

Layers one to three are for a pleasant interface. Layer four is the security. If someone skipped the app and called the API directly with their own token, they would get exactly what the policies allow and nothing more.

An audit trail for free

One trigger function is attached to nineteen tables, including module access itself. On every insert, update, and delete it records who did it and, for updates, only the fields that changed, as {field: {from, to}}. When an owner asks who changed the discount on a quote, the answer is one query, and when someone's access is widened, that is in the log too.

What to copy

• Model access as module x level, not as named roles.
• Default to none; a missing row is closed.
• Generate policies from one helper so every table follows the same rule.
• Repeat the access check inside every definer function that writes.
• Protect privilege columns with a trigger, not just the interface.
• Log changes field by field; it costs one trigger.

Part 5 is how all of this is proven: tests that sign in as each kind of user and check what they can and cannot do. For a system with this kind of access control, see the CRM and ERP page.