3 · The first policy set

The question this chapter answers: what does a complete, correct policy set for a two-tenant app actually look like?

Time to build the book's example project properly. Everything in this chapter runs top to bottom on a fresh project — schema, seed data, grants, helper functions, and every policy — followed by three proofs that the boundaries hold. Appendix C repeats the whole set as one file you can rebuild from anytime.

Meet TaskHarbor

TaskHarbor is the fabricated two-tenant task app from the front matter: two workspaces, each with projects, each project with tasks. Membership in a workspace is the tenant boundary. All UUIDs are fixed literals so your results match the text exactly.

create schema if not exists private;

create table public.workspaces (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_at timestamptz not null default now()
);

create table public.members (
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  user_id      uuid not null,   -- auth.users(id) in production; left plain so this runs anywhere
  role         text not null default 'member' check (role in ('owner', 'member')),
  primary key (workspace_id, user_id)
);

create table public.projects (
  id           uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  name         text not null
);

create table public.tasks (
  id         uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects(id) on delete cascade,
  title      text not null,
  done       boolean not null default false
);

Seed it while connected as postgres (the SQL Editor default). The inserts succeed despite RLS being enabled below because the owner bypasses its own tables' policies:

alter table public.workspaces enable row level security;
alter table public.members    enable row level security;
alter table public.projects   enable row level security;
alter table public.tasks      enable row level security;

insert into public.workspaces (id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Aster Labs'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Borealis Design');

insert into public.members (workspace_id, user_id, role) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3', 'owner'),  -- ada owns Aster
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', 'owner'),  -- bo owns Borealis
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5', 'member'); -- eve works at Aster

insert into public.projects (id, workspace_id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Brand refresh'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Studio handbook');

insert into public.tasks (project_id, title) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Draft moodboard'),
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Review typography'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'Outline chapter 1');

Grants: say who may do what

Following the current Supabase guidance: revoke broad defaults, then grant each client role exactly what the app needs. Signed-out visitors get nothing; signed-in members get the four commands; membership changes stay owner-only.

revoke all on public.workspaces from anon, authenticated;
grant select, update, delete on public.workspaces to authenticated;

revoke all on public.members from anon, authenticated;
grant select, insert, delete on public.members to authenticated;

revoke all on public.projects from anon, authenticated;
grant select, insert, update, delete on public.projects to authenticated;

revoke all on public.tasks from anon, authenticated;
grant select, insert, update, delete on public.tasks to authenticated;

Note there is deliberately no insert grant on workspaces: new workspaces are created through a server-side function below, so nobody can plant stray rows directly.

Two helpers, written once

Almost every policy asks one question: is the caller a member of this workspace? Writing that subquery into a dozen policies invites a dozen drift points. Write it once instead (pattern per Supabase docs):

create or replace function private.is_member(ws uuid)
returns boolean language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.workspace_id = ws
      and m.user_id = (select auth.uid())
  )
$$;

create or replace function private.is_owner(ws uuid)
returns boolean language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.workspace_id = ws
      and m.user_id = (select auth.uid())
      and m.role = 'owner'
  )
$$;

revoke all on function private.is_member(uuid), private.is_owner(uuid) from public;
grant execute on function private.is_member(uuid), private.is_owner(uuid) to authenticated;

Line by line: security definer makes the function run with its creator's privileges, so reading members does not re-trigger members' own select policy — without it, Postgres aborts with "infinite recursion detected in policy." set search_path = '' plus fully qualified names closes the search-path hijack Chapter 8 explains. The helpers live in the unexposed private schema, so they are callable inside policies but not invokable through the API by clients. revoke ... from public then grant ... to authenticated keeps execution rights deliberate.

The policies

Read each as a sentence. Every one names its command and its role.

-- Workspaces: members read; owners rename or delete. No insert policy: creation is a function call.
create policy "workspaces: members read"   on public.workspaces for select to authenticated using (private.is_member(id));
create policy "workspaces: owners update"  on public.workspaces for update to authenticated
  using (private.is_owner(id)) with check (private.is_owner(id));
create policy "workspaces: owners delete"  on public.workspaces for delete to authenticated using (private.is_owner(id));

-- Members: visible to the workspace; owners add or remove, but cannot remove themselves.
create policy "members: workspace reads"   on public.members for select to authenticated using (private.is_member(workspace_id));
create policy "members: owners add"        on public.members for insert to authenticated
  with check (private.is_owner(workspace_id));
create policy "members: owners remove"     on public.members for delete to authenticated
  using (private.is_owner(workspace_id) and user_id <> (select auth.uid()));

-- Projects: any member manages them; only owners delete.
create policy "projects: members read"     on public.projects for select to authenticated using (private.is_member(workspace_id));
create policy "projects: members create"   on public.projects for insert to authenticated
  with check (private.is_member(workspace_id));
create policy "projects: members update"   on public.projects for update to authenticated
  using (private.is_member(workspace_id)) with check (private.is_member(workspace_id));
create policy "projects: owners delete"    on public.projects for delete to authenticated
  using (private.is_owner(workspace_id));

-- Tasks: reach them only through a project in your workspace.
create policy "tasks: members read"        on public.tasks for select to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members create"      on public.tasks for insert to authenticated
  with check (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members update"      on public.tasks for update to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)))
  with check (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members delete"      on public.tasks for delete to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));

Two details deserve attention. The explicit with check on updates means a member cannot edit a task into another workspace even though they can edit their own tasks. And the members: owners remove condition user_id <> (select auth.uid()) prevents an owner from locking themselves out.

Workspace creation ties both tables together atomically, so a half-created tenant can never exist:

create or replace function private.create_workspace(new_name text)
returns uuid language plpgsql security definer set search_path = ''
as $$
declare new_id uuid;
begin
  insert into public.workspaces (name) values (new_name) returning id into new_id;
  insert into public.members (workspace_id, user_id, role)
    values (new_id, (select auth.uid()), 'owner');
  return new_id;
end
$$;

revoke all on function private.create_workspace(text) from public;
grant execute on function private.create_workspace(text) to authenticated;

Three proofs, before you trust it

Impersonate ada (Aster's owner) and count what she can reach:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3';

select count(*) from public.tasks;    -- 2  (only Aster's)
rollback;

Bo, of Borealis, must find nothing of Aster's:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';

select count(*) from public.workspaces where name = 'Aster Labs';  -- 0
select count(*) from public.tasks
  where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2';       -- 0
rollback;

And eve, an Aster member, must fail to write into Borealis:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';

insert into public.tasks (project_id, title)
values ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'smuggled');
-- ERROR 42501: new row violates row-level security policy for table "tasks"
rollback;

Three checks, three passes. But these were run once, by hand, tonight. Chapter 6 turns them into tests that run forever; Chapter 4 first looks at how sets like this one quietly stop being true.

Check on your project

1. Draw your own ownership chain on paper: which table belongs to which tenant root, and does every child table carry the tenant id directly or reach it through a join?
2. Compare your grant statements against what your client actually calls. Grants for operations no code performs are attack surface awaiting a policy mistake.
3. Find any query your policies answer with a recursive lookup (a policy whose table references itself). If it exists, decide whether a security definer helper belongs in your design.
4. Run the three-proof pattern against your real schema tonight: one count per role across the tenant boundary, one cross-tenant write attempt. Record the results — they become baseline evidence in Chapter 6.