The lock that cannot be walked around

Session 6 wrote down who sees what. This session makes the database enforce it. PostgreSQL's row-level security (RLS) lets a table carry policies: rules that decide, for each row and each person, whether that row can be seen, added or changed ^[1]. Supabase, the database you set up in Session 1, is PostgreSQL, and its API passes each signed-in person's identity to the database so policies can use it ^[2].

Because the rule runs inside the database, it holds no matter how the request arrives: from your screens, from a script someone writes next year, from the connector, or from an AI agent. A screen that forgets to filter by line still gets only the rows the policy allows.

Reading a policy

A policy names a table, a command (SELECT, INSERT, UPDATE, DELETE or ALL), the database roles it applies to, and up to two conditions ^[3]:

• USING decides which existing rows the command can see or touch. Rows that fail it are simply invisible.
• WITH CHECK decides which new or changed rows are allowed. A row that fails it is refused with an error.

-- Operators may read downtime events on lines in their scope.
create policy downtime_events_operator_read on public.downtime_events
  for select to authenticated
  using (app.has_role('operator', line_id));

-- Operators may record downtime on lines in their scope, as themselves.
create policy downtime_events_operator_insert on public.downtime_events
  for insert to authenticated
  with check (app.has_role('operator', line_id) and created_by = (select auth.uid()));

Read them aloud: signed-in people may select downtime events where they hold the operator role for that row's line. And: signed-in people may insert a downtime event only if they hold the operator role for its line and record it under their own identity. auth.uid() is Supabase's function for the signed-in person's id ^[2]; app.has_role is a helper chapter 2 generates from the matrix.

How policies combine

A table can have several policies for the same command. Ordinary (permissive) policies combine with OR: a row is allowed if any one of them allows it. Restrictive policies combine with AND: every one must also pass ^[3]. Grants from the matrix are permissive; rules that must always hold, such as no self-approval, are restrictive.

Who skips the rules

Some accounts are not bound by row-level security at all. Superusers and roles with the BYPASSRLS attribute skip it, and so does a table's owner unless the table is set to FORCE ROW LEVEL SECURITY ^[1]. In Supabase, the service role (the secret key) bypasses every policy; Supabase warns that it must never be used in a browser or shipped to users ^[4].

From cell to policy

Each filled cell in the matrix becomes exactly one policy; each blank cell becomes nothing, because row-level security already refuses what no policy grants. Rules such as no self-approval become restrictive policies. That is the whole mapping:

The scope helper

Every policy asks the same question: does the signed-in person hold this role, with a scope that covers this row's line? Rather than repeat the logic in every policy, the generator writes one helper function. It reads the people assignments and the lines table from Session 3's schema.

-- Generated from access/matrix.json by scripts/gen-rls.mjs. Do not edit by hand.
create schema if not exists app;

-- True when the signed-in person holds this role with a scope covering the given line.
create or replace function app.has_role(want text, row_line text)
returns boolean
language sql stable
security definer set search_path = ''
as $$
  select exists (
    select 1
    from public.people p
    join public.lines l on l.id = row_line
    where p.user_id = (select auth.uid())
      and p.role = want
      and (   (p.scope = 'line' and p.line_id = l.id)
           or (p.scope = 'area' and p.area_id = l.area_id)
           or (p.scope = 'site' and p.site_id = l.site_id))
  );
$$;

• security definer runs the function with its owner's rights, so it can read the people table even though ordinary users cannot. That is safe because the function only answers yes or no about the person asking.
• set search_path = '' stops anyone substituting a look-alike table; every name is written in full. PostgreSQL's documentation recommends this for security definer functions ^[1].
• (select auth.uid()), wrapped in a select, lets PostgreSQL work it out once per query instead of once per row. Supabase recommends this for speed on large tables ^[2].
• The app schema is not exposed through the API, so nobody can call the helper directly from a browser ^[2].

The policies for one table

Here is everything the generator writes for downtime_events, from the matrix in Session 6. Compare it line by line with the matrix: every filled cell has a policy, every blank cell has none.

alter table public.downtime_events enable row level security;
alter table public.downtime_events force row level security;

-- operator: insert read
create policy downtime_events_operator_read on public.downtime_events
  for select to authenticated using (app.has_role('operator', line_id));
create policy downtime_events_operator_insert on public.downtime_events
  for insert to authenticated
  with check (app.has_role('operator', line_id) and created_by = (select auth.uid()));

-- supervisor: read approve
create policy downtime_events_supervisor_read on public.downtime_events
  for select to authenticated using (app.has_role('supervisor', line_id));
create policy downtime_events_supervisor_approve on public.downtime_events
  for update to authenticated
  using (app.has_role('supervisor', line_id) and status = 'pending')
  with check (app.has_role('supervisor', line_id) and status = 'approved'
              and approved_by = (select auth.uid()));

-- manager: read · maintenance: read
create policy downtime_events_manager_read on public.downtime_events
  for select to authenticated using (app.has_role('manager', line_id));
create policy downtime_events_maintenance_read on public.downtime_events
  for select to authenticated using (app.has_role('maintenance', line_id));

-- rule no_self_approval
create policy downtime_events_no_self_approval on public.downtime_events
  as restrictive for update to authenticated
  using (created_by <> (select auth.uid()));

-- approve may change only the approval columns
revoke update on public.downtime_events from authenticated;
grant update (status, approved_by, approved_at) on public.downtime_events to authenticated;

Every table, no exceptions

The generator switches row-level security on for every table in the schema, including tables no role can reach (such as an audit log written only by the database). A table without row-level security, in a schema the API exposes, is open to anyone holding the publishable key. Supabase's database advisors flag such tables in the dashboard ^[4], and chapter 3 adds a test that fails CI if one exists.

What no looks like

Row-level security usually says no quietly. A query for rows outside your scope returns nothing, with no error. An update aimed at a row you cannot touch changes nothing, with no error. Only an insert (or an update whose result fails WITH CHECK) raises an error: new row violates row-level security policy, with the SQL error code 42501 ^[1].

A test for every cell

For every role and every table, the tests do what the matrix says should work and what it says should be refused, and check both. Session 4's synthetic data gives them rows on several lines and areas to work with. Supabase supports database tests written with pgTAP, run with its command-line tool ^{[2] [3]}.

-- supabase/tests/downtime_events.test.sql (run with: supabase test db)
begin;
select plan(5);

-- Sign in as the line 2 operator from the seed data.
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000002"}';

select ok((select count(*) from public.downtime_events where line_id = 'line-2') > 0,
  'operator sees line 2 downtime');
select is((select count(*) from public.downtime_events where line_id = 'line-3'), 0::bigint,
  'operator sees no line 3 downtime');
select lives_ok($$ insert into public.downtime_events (line_id, minutes, reason, created_by)
  values ('line-2', 12, 'jam', '00000000-0000-0000-0000-000000000002') $$,
  'operator records downtime on line 2');
select throws_ok($$ insert into public.downtime_events (line_id, minutes, reason, created_by)
  values ('line-3', 12, 'jam', '00000000-0000-0000-0000-000000000002') $$,
  '42501', null, 'operator cannot record downtime on line 3');
select is((select count(*) from public.work_orders), 0::bigint,
  'operator sees no work orders');

select * from finish();
rollback;

set local role authenticated and the request.jwt.claims setting make the database behave exactly as it does for that signed-in person, so auth.uid() returns their id ^[2]. Everything runs inside a transaction that is rolled back, so the test leaves no trace.

Let the generator write the tests

Writing every test by hand would drift from the matrix just as hand-written policies would. Ask your agent to generate the tests from the matrix too: for each role and table, one allowed and one refused check per action, using known users from the seed data. Then hand-write the few tests the generator cannot know about, such as self-approval.

No table left open

One more check guards the whole schema. PostgreSQL's catalogue records, for each table, whether row-level security is on. A test that lists tables in the exposed schema without it should always return nothing ^[4]:

-- Fails if any table the API can reach has row-level security off.
select is(
  (select count(*) from pg_tables where schemaname = 'public' and not rowsecurity),
  0::bigint,
  'every public table has row-level security');