Critical
E-001 · Row-Level Security disabled on the rmas table (CVE-2025-48757 class)
Exploit E-001 — before
EXPLOITED anon key (no login) read 2 RMA rows across 2 tenants, HTTP 200
after
blocked anon key denied: HTTP 200 []
Before / after — Database: schemas, RLS policies, RPC
before/ — vulnerable
-- Tallgrass Precision Components — customer portal
-- Two schemas in the SAME local Supabase/Postgres instance so both the
-- vulnerable and hardened versions can be queried live, side by side,
-- through the same anon key, without restarting the stack:
-- app_before — the flawed build (every flaw documented in audit/FINDINGS.md)
-- app_after — the hardened build
-- Both schemas are exposed via PostgREST (see supabase/config.toml api.schemas).
-- auth.users is shared between them (same demo accounts, same passwords) so the
-- functional tests are apples to apples.
create extension if not exists pgcrypto;
create schema if not exists app_before;
create schema if not exists app_after;
grant usage on schema app_before to anon, authenticated;
grant usage on schema app_after to anon, authenticated;
-- ===========================================================================
-- Shared demo accounts (auth.users) — used by both schemas.
-- Local-only passwords, obviously fake, documented in README.
-- ===========================================================================
insert into auth.users
(instance_id, id, aud, role, email, encrypted_password, email_confirmed_at,
raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token,
recovery_token, email_change_token_new, email_change)
values
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a001', 'authenticated', 'authenticated',
'jmiller@skylineaero.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a002', 'authenticated', 'authenticated',
'dchen@skylineaero.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a003', 'authenticated', 'authenticated',
'rpatel@meridianmed.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a004', 'authenticated', 'authenticated',
'staff@tallgrasspc.example', crypt('Demo-Staff-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', '')
on conflict (id) do nothing;
insert into auth.identities
(id, provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
select gen_random_uuid(), u.id::text, u.id, jsonb_build_object('sub', u.id::text, 'email', u.email), 'email', now(), now(), now()
from auth.users u
where u.id in ('a0000000-0000-0000-0000-00000000a001','a0000000-0000-0000-0000-00000000a002',
'a0000000-0000-0000-0000-00000000a003','a0000000-0000-0000-0000-00000000a004')
on conflict do nothing;
-- ===========================================================================
-- app_before — tables (flaws injected on purpose; see audit/FINDINGS.md)
-- ===========================================================================
create table app_before.tenants (
id uuid primary key default gen_random_uuid(),
name text not null,
invite_code text not null unique,
created_at timestamptz not null default now()
);
-- F-003: tenant_id and role live directly on the user-updatable profile row,
-- and (see policies below) the update policy lets a user change BOTH.
create table app_before.profiles (
id uuid primary key references auth.users(id) on delete cascade,
email text,
tenant_id uuid references app_before.tenants(id),
role text not null default 'customer' check (role in ('customer','staff')),
created_at timestamptz not null default now()
);
create table app_before.orders (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text not null,
part_number text not null,
qty integer not null check (qty > 0),
due_date date not null,
status text not null default 'Open'
check (status in ('Open','In Production','Inspection','Shipped','Delivered','Cancelled')),
created_at timestamptz not null default now()
);
create table app_before.certs (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text,
title text not null,
file_path text not null,
created_at timestamptz not null default now()
);
-- F-001: RLS never enabled on this table at all. Readable/writable by anyone
-- holding the anon key, straight from the browser via PostgREST.
create table app_before.rmas (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text,
reason text not null,
status text not null default 'Requested'
check (status in ('Requested','Approved','Received','Resolved','Rejected')),
created_by uuid references auth.users(id),
created_at timestamptz not null default now()
);
create index on app_before.orders (tenant_id);
create index on app_before.certs (tenant_id);
create index on app_before.rmas (tenant_id);
-- F-006: SECURITY DEFINER function with NO auth/role check inside. Any
-- authenticated caller (the grant below even allows it) can move any order
-- to any status for any tenant.
create or replace function app_before.admin_set_order_status(p_order_id uuid, p_status text)
returns void
language plpgsql
security definer
set search_path = app_before, public
as $$
begin
update app_before.orders set status = p_status where id = p_order_id;
end;
$$;
grant execute on function app_before.admin_set_order_status(uuid, text) to authenticated;
-- new-user trigger: role is fixed to 'customer' at signup (that part is fine);
-- the real hole is that the profiles UPDATE policy below lets the user change
-- tenant_id and role afterwards.
create or replace function app_before.handle_new_user()
returns trigger language plpgsql security definer set search_path = app_before, public as $$
declare
t uuid;
begin
select id into t from app_before.tenants
where invite_code = new.raw_user_meta_data->>'invite_code';
insert into app_before.profiles (id, email, tenant_id, role)
values (new.id, new.email, t, 'customer')
on conflict (id) do nothing;
return new;
end;
$$;
drop trigger if exists on_auth_user_created_before on auth.users;
create trigger on_auth_user_created_before
after insert on auth.users
for each row execute function app_before.handle_new_user();
-- ------------------------------------------------------------------- RLS
alter table app_before.tenants enable row level security;
alter table app_before.profiles enable row level security;
alter table app_before.orders enable row level security;
alter table app_before.certs enable row level security;
-- app_before.rmas: RLS left OFF on purpose (F-001).
-- F-009 (part of the anon-reconnaissance finding): RLS is "on" for tenants but
-- the policy is `using (true)` AND granted to anon, so anyone with just the
-- published anon key — no login required — can list every company name.
create policy before_tenants_select_anyone on app_before.tenants
for select to anon, authenticated using (true);
-- F-003: a user may select their own profile AND update it — including the
-- tenant_id and role columns, which PostgREST lets through because the policy
-- has no column restriction and no WITH CHECK pinning them to their old values.
create policy before_profiles_select on app_before.profiles
for select to authenticated using (true);
create policy before_profiles_update_self on app_before.profiles
for update to authenticated using (id = auth.uid());
-- F-002: RLS is "on" (looks safe in a quick audit) but the SELECT policy is
-- `using (true)` — every authenticated customer can read every tenant's
-- orders, not just their own.
create policy before_orders_select_all on app_before.orders
for select to authenticated using (true);
create policy before_orders_write_all on app_before.orders
for insert to authenticated with check (true);
create policy before_orders_update_all on app_before.orders
for update to authenticated using (true);
create policy before_certs_select_all on app_before.certs
for select to authenticated using (true);
create policy before_certs_write_all on app_before.certs
for insert to authenticated with check (true);
-- app_before.rmas has no policies at all — moot, because RLS is disabled on
-- the table, so PostgREST does not even consult policies.
-- ===========================================================================
-- app_after — hardened tables
-- ===========================================================================
create table app_after.tenants (
id uuid primary key default gen_random_uuid(),
name text not null,
invite_code text not null unique,
created_at timestamptz not null default now()
);
create table app_after.profiles (
id uuid primary key references auth.users(id) on delete cascade,
email text,
tenant_id uuid references app_after.tenants(id),
role text not null default 'customer' check (role in ('customer','staff')),
created_at timestamptz not null default now()
);
create table app_after.orders (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text not null,
part_number text not null,
qty integer not null check (qty > 0),
due_date date not null,
status text not null default 'Open'
check (status in ('Open','In Production','Inspection','Shipped','Delivered','Cancelled')),
created_at timestamptz not null default now()
);
create table app_after.certs (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text,
title text not null,
file_path text not null,
created_at timestamptz not null default now()
);
create table app_after.rmas (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text,
reason text not null,
status text not null default 'Requested'
check (status in ('Requested','Approved','Received','Resolved','Rejected')),
created_by uuid references auth.users(id),
created_at timestamptz not null default now()
);
create index on app_after.orders (tenant_id);
create index on app_after.certs (tenant_id);
create index on app_after.rmas (tenant_id);
-- helper functions read the caller's OWN row only, security definer so the
-- policies that call them don't recurse into profiles' own RLS.
create or replace function app_after.current_tenant_id()
returns uuid language sql stable security definer set search_path = app_after, public as $$
select tenant_id from app_after.profiles where id = auth.uid()
$$;
create or replace function app_after.is_staff()
returns boolean language sql stable security definer set search_path = app_after, public as $$
select coalesce((select role = 'staff' from app_after.profiles where id = auth.uid()), false)
$$;
-- F-006 fix: SECURITY INVOKER (runs as the caller, so RLS on orders still
-- applies) PLUS an explicit staff check — belt and suspenders. A customer
-- calling this gets a permission error, not a silent no-op.
create or replace function app_after.admin_set_order_status(p_order_id uuid, p_status text)
returns void
language plpgsql
security invoker
set search_path = app_after, public
as $$
begin
if not app_after.is_staff() then
raise exception 'not authorized' using errcode = '42501';
end if;
update app_after.orders set status = p_status where id = p_order_id;
end;
$$;
grant execute on function app_after.admin_set_order_status(uuid, text) to authenticated;
-- new-user trigger: tenant comes from the invite code at signup, same as
-- before — the fix is that nothing downstream lets the user change it later.
create or replace function app_after.handle_new_user()
returns trigger language plpgsql security definer set search_path = app_after, public as $$
declare
t uuid;
begin
select id into t from app_after.tenants
where invite_code = new.raw_user_meta_data->>'invite_code';
insert into app_after.profiles (id, email, tenant_id, role)
values (new.id, new.email, t, 'customer')
on conflict (id) do nothing;
return new;
end;
$$;
drop trigger if exists on_auth_user_created_after on auth.users;
create trigger on_auth_user_created_after
after insert on auth.users
for each row execute function app_after.handle_new_user();
-- ------------------------------------------------------------------- RLS
alter table app_after.tenants enable row level security;
alter table app_after.profiles enable row level security;
alter table app_after.orders enable row level security;
alter table app_after.certs enable row level security;
alter table app_after.rmas enable row level security;
-- F-009 fix: tenants are no longer readable by anon, and an authenticated
-- user only sees their own company (staff see all).
create policy after_tenants_select on app_after.tenants
for select to authenticated
using (id = app_after.current_tenant_id() or app_after.is_staff());
-- F-003 fix: a user may read their own profile. There is NO update policy at
-- all for authenticated users — tenant_id and role can only change through
-- the SECURITY DEFINER trigger (signup) or a staff-only RPC, never a direct
-- PostgREST PATCH from the browser.
create policy after_profiles_select on app_after.profiles
for select to authenticated
using (id = auth.uid() or app_after.is_staff());
create policy after_profiles_staff_update on app_after.profiles
for update to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- F-002 fix: real tenant scoping, not `using (true)`.
create policy after_orders_select on app_after.orders
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_orders_staff_write on app_after.orders
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
create policy after_certs_select on app_after.certs
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_certs_staff_write on app_after.certs
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- F-007 fix: rmas is readable per-tenant, and a customer may INSERT only a
-- row for their OWN tenant, as themselves, starting in 'Requested' — the
-- WITH CHECK that app_before never had.
create policy after_rmas_select on app_after.rmas
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_rmas_customer_insert on app_after.rmas
for insert to authenticated
with check (
tenant_id = app_after.current_tenant_id()
and status = 'Requested'
and created_by = auth.uid()
);
create policy after_rmas_staff_write on app_after.rmas
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- ===========================================================================
-- Storage — certs buckets
-- ===========================================================================
-- F-005: public bucket, no storage policies needed to read — any PDF path is
-- fetchable by anyone who can guess or list it, across every tenant.
insert into storage.buckets (id, name, public)
values ('certs-before', 'certs-before', true)
on conflict (id) do nothing;
-- F-005 fix: private bucket, explicit per-tenant policies (same shape as the
-- DB RLS: first path segment is the tenant id).
insert into storage.buckets (id, name, public)
values ('certs-after', 'certs-after', false)
on conflict (id) do nothing;
create policy after_certs_files_read on storage.objects for select to authenticated
using (
bucket_id = 'certs-after'
and (
app_after.is_staff()
or (storage.foldername(name))[1] = app_after.current_tenant_id()::text
)
);
create policy after_certs_files_staff_insert on storage.objects for insert to authenticated
with check (bucket_id = 'certs-after' and app_after.is_staff());
create policy after_certs_files_staff_update on storage.objects for update to authenticated
using (bucket_id = 'certs-after' and app_after.is_staff());
create policy after_certs_files_staff_delete on storage.objects for delete to authenticated
using (bucket_id = 'certs-after' and app_after.is_staff());
-- ===========================================================================
-- Seed data — fictional. Same tenants/orders/etc. in both schemas so
-- functional tests are directly comparable.
-- ===========================================================================
insert into app_before.tenants (id, name, invite_code) values
('11111111-1111-1111-1111-111111111111', 'Skyline Aerospace (demo)', 'AERO-DEMO-7K2Q'),
('22222222-2222-2222-2222-222222222222', 'Meridian Medical Devices (demo)', 'MED-DEMO-4M9X');
insert into app_after.tenants (id, name, invite_code) values
('11111111-1111-1111-1111-111111111111', 'Skyline Aerospace (demo)', 'AERO-DEMO-7K2Q'),
('22222222-2222-2222-2222-222222222222', 'Meridian Medical Devices (demo)', 'MED-DEMO-4M9X');
-- profiles: 2 customers at Skyline, 1 at Meridian, 1 staff — same ids in both schemas
insert into app_before.profiles (id, email, tenant_id, role) values
('a0000000-0000-0000-0000-00000000a001', 'jmiller@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a002', 'dchen@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a003', 'rpatel@meridianmed.example', '22222222-2222-2222-2222-222222222222', 'customer'),
('a0000000-0000-0000-0000-00000000a004', 'staff@tallgrasspc.example', null, 'staff')
on conflict (id) do nothing;
insert into app_after.profiles (id, email, tenant_id, role) values
('a0000000-0000-0000-0000-00000000a001', 'jmiller@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a002', 'dchen@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a003', 'rpatel@meridianmed.example', '22222222-2222-2222-2222-222222222222', 'customer'),
('a0000000-0000-0000-0000-00000000a004', 'staff@tallgrasspc.example', null, 'staff')
on conflict (id) do nothing;
insert into app_before.orders (tenant_id, order_number, part_number, qty, due_date, status) values
('11111111-1111-1111-1111-111111111111', 'PO-10041', 'TPC-AL-7075-BRKT-220', 150, '2026-10-30', 'In Production'),
('11111111-1111-1111-1111-111111111111', 'PO-10042', 'TPC-TI-6AL4V-FTG-118', 40, '2026-11-14', 'Open'),
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'TPC-AL-6061-HSG-305', 300, '2026-09-28', 'Shipped'),
('22222222-2222-2222-2222-222222222222', 'MM-5520', 'TPC-SS-316L-CANN-045', 500, '2026-10-22', 'Inspection'),
('22222222-2222-2222-2222-222222222222', 'MM-5527', 'TPC-SS-316L-HDL-012', 250, '2026-11-05', 'In Production'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'TPC-PEEK-SPC-077', 1000, '2026-09-15', 'Delivered');
insert into app_after.orders (tenant_id, order_number, part_number, qty, due_date, status) values
('11111111-1111-1111-1111-111111111111', 'PO-10041', 'TPC-AL-7075-BRKT-220', 150, '2026-10-30', 'In Production'),
('11111111-1111-1111-1111-111111111111', 'PO-10042', 'TPC-TI-6AL4V-FTG-118', 40, '2026-11-14', 'Open'),
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'TPC-AL-6061-HSG-305', 300, '2026-09-28', 'Shipped'),
('22222222-2222-2222-2222-222222222222', 'MM-5520', 'TPC-SS-316L-CANN-045', 500, '2026-10-22', 'Inspection'),
('22222222-2222-2222-2222-222222222222', 'MM-5527', 'TPC-SS-316L-HDL-012', 250, '2026-11-05', 'In Production'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'TPC-PEEK-SPC-077', 1000, '2026-09-15', 'Delivered');
insert into app_before.certs (tenant_id, order_number, title, file_path) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Certificate of Conformance – PO-10037', '11111111-1111-1111-1111-111111111111/coc-po-10037.pdf'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Material Cert & CoC – MM-5491', '22222222-2222-2222-2222-222222222222/coc-mm-5491.pdf');
insert into app_after.certs (tenant_id, order_number, title, file_path) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Certificate of Conformance – PO-10037', '11111111-1111-1111-1111-111111111111/coc-po-10037.pdf'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Material Cert & CoC – MM-5491', '22222222-2222-2222-2222-222222222222/coc-mm-5491.pdf');
insert into app_before.rmas (tenant_id, order_number, reason, status, created_by) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Three housings have a burr on the bore edge.', 'Approved', 'a0000000-0000-0000-0000-00000000a001'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Spacer dimension out of tolerance on 12 pcs.', 'Requested', 'a0000000-0000-0000-0000-00000000a003');
insert into app_after.rmas (tenant_id, order_number, reason, status, created_by) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Three housings have a burr on the bore edge.', 'Approved', 'a0000000-0000-0000-0000-00000000a001'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Spacer dimension out of tolerance on 12 pcs.', 'Requested', 'a0000000-0000-0000-0000-00000000a003');
-- grants: anon/authenticated need table-level privileges; row visibility is
-- then enforced (or, in app_before's case, NOT enforced) by RLS above.
grant select, insert, update, delete on all tables in schema app_before to anon, authenticated;
grant select, insert, update, delete on all tables in schema app_after to anon, authenticated;
grant usage on all sequences in schema app_before to anon, authenticated;
grant usage on all sequences in schema app_after to anon, authenticated;
after/ — hardened
-- Tallgrass Precision Components — customer portal
-- Two schemas in the SAME local Supabase/Postgres instance so both the
-- vulnerable and hardened versions can be queried live, side by side,
-- through the same anon key, without restarting the stack:
-- app_before — the flawed build (every flaw documented in audit/FINDINGS.md)
-- app_after — the hardened build
-- Both schemas are exposed via PostgREST (see supabase/config.toml api.schemas).
-- auth.users is shared between them (same demo accounts, same passwords) so the
-- functional tests are apples to apples.
create extension if not exists pgcrypto;
create schema if not exists app_before;
create schema if not exists app_after;
grant usage on schema app_before to anon, authenticated;
grant usage on schema app_after to anon, authenticated;
-- ===========================================================================
-- Shared demo accounts (auth.users) — used by both schemas.
-- Local-only passwords, obviously fake, documented in README.
-- ===========================================================================
insert into auth.users
(instance_id, id, aud, role, email, encrypted_password, email_confirmed_at,
raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token,
recovery_token, email_change_token_new, email_change)
values
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a001', 'authenticated', 'authenticated',
'jmiller@skylineaero.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a002', 'authenticated', 'authenticated',
'dchen@skylineaero.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a003', 'authenticated', 'authenticated',
'rpatel@meridianmed.example', crypt('Demo-Customer-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', ''),
('00000000-0000-0000-0000-000000000000', 'a0000000-0000-0000-0000-00000000a004', 'authenticated', 'authenticated',
'staff@tallgrasspc.example', crypt('Demo-Staff-2026!', gen_salt('bf')), now(),
'{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', '')
on conflict (id) do nothing;
insert into auth.identities
(id, provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
select gen_random_uuid(), u.id::text, u.id, jsonb_build_object('sub', u.id::text, 'email', u.email), 'email', now(), now(), now()
from auth.users u
where u.id in ('a0000000-0000-0000-0000-00000000a001','a0000000-0000-0000-0000-00000000a002',
'a0000000-0000-0000-0000-00000000a003','a0000000-0000-0000-0000-00000000a004')
on conflict do nothing;
-- ===========================================================================
-- app_before — tables (flaws injected on purpose; see audit/FINDINGS.md)
-- ===========================================================================
create table app_before.tenants (
id uuid primary key default gen_random_uuid(),
name text not null,
invite_code text not null unique,
created_at timestamptz not null default now()
);
-- F-003: tenant_id and role live directly on the user-updatable profile row,
-- and (see policies below) the update policy lets a user change BOTH.
create table app_before.profiles (
id uuid primary key references auth.users(id) on delete cascade,
email text,
tenant_id uuid references app_before.tenants(id),
role text not null default 'customer' check (role in ('customer','staff')),
created_at timestamptz not null default now()
);
create table app_before.orders (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text not null,
part_number text not null,
qty integer not null check (qty > 0),
due_date date not null,
status text not null default 'Open'
check (status in ('Open','In Production','Inspection','Shipped','Delivered','Cancelled')),
created_at timestamptz not null default now()
);
create table app_before.certs (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text,
title text not null,
file_path text not null,
created_at timestamptz not null default now()
);
-- F-001: RLS never enabled on this table at all. Readable/writable by anyone
-- holding the anon key, straight from the browser via PostgREST.
create table app_before.rmas (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_before.tenants(id) on delete cascade,
order_number text,
reason text not null,
status text not null default 'Requested'
check (status in ('Requested','Approved','Received','Resolved','Rejected')),
created_by uuid references auth.users(id),
created_at timestamptz not null default now()
);
create index on app_before.orders (tenant_id);
create index on app_before.certs (tenant_id);
create index on app_before.rmas (tenant_id);
-- F-006: SECURITY DEFINER function with NO auth/role check inside. Any
-- authenticated caller (the grant below even allows it) can move any order
-- to any status for any tenant.
create or replace function app_before.admin_set_order_status(p_order_id uuid, p_status text)
returns void
language plpgsql
security definer
set search_path = app_before, public
as $$
begin
update app_before.orders set status = p_status where id = p_order_id;
end;
$$;
grant execute on function app_before.admin_set_order_status(uuid, text) to authenticated;
-- new-user trigger: role is fixed to 'customer' at signup (that part is fine);
-- the real hole is that the profiles UPDATE policy below lets the user change
-- tenant_id and role afterwards.
create or replace function app_before.handle_new_user()
returns trigger language plpgsql security definer set search_path = app_before, public as $$
declare
t uuid;
begin
select id into t from app_before.tenants
where invite_code = new.raw_user_meta_data->>'invite_code';
insert into app_before.profiles (id, email, tenant_id, role)
values (new.id, new.email, t, 'customer')
on conflict (id) do nothing;
return new;
end;
$$;
drop trigger if exists on_auth_user_created_before on auth.users;
create trigger on_auth_user_created_before
after insert on auth.users
for each row execute function app_before.handle_new_user();
-- ------------------------------------------------------------------- RLS
alter table app_before.tenants enable row level security;
alter table app_before.profiles enable row level security;
alter table app_before.orders enable row level security;
alter table app_before.certs enable row level security;
-- app_before.rmas: RLS left OFF on purpose (F-001).
-- F-009 (part of the anon-reconnaissance finding): RLS is "on" for tenants but
-- the policy is `using (true)` AND granted to anon, so anyone with just the
-- published anon key — no login required — can list every company name.
create policy before_tenants_select_anyone on app_before.tenants
for select to anon, authenticated using (true);
-- F-003: a user may select their own profile AND update it — including the
-- tenant_id and role columns, which PostgREST lets through because the policy
-- has no column restriction and no WITH CHECK pinning them to their old values.
create policy before_profiles_select on app_before.profiles
for select to authenticated using (true);
create policy before_profiles_update_self on app_before.profiles
for update to authenticated using (id = auth.uid());
-- F-002: RLS is "on" (looks safe in a quick audit) but the SELECT policy is
-- `using (true)` — every authenticated customer can read every tenant's
-- orders, not just their own.
create policy before_orders_select_all on app_before.orders
for select to authenticated using (true);
create policy before_orders_write_all on app_before.orders
for insert to authenticated with check (true);
create policy before_orders_update_all on app_before.orders
for update to authenticated using (true);
create policy before_certs_select_all on app_before.certs
for select to authenticated using (true);
create policy before_certs_write_all on app_before.certs
for insert to authenticated with check (true);
-- app_before.rmas has no policies at all — moot, because RLS is disabled on
-- the table, so PostgREST does not even consult policies.
-- ===========================================================================
-- app_after — hardened tables
-- ===========================================================================
create table app_after.tenants (
id uuid primary key default gen_random_uuid(),
name text not null,
invite_code text not null unique,
created_at timestamptz not null default now()
);
create table app_after.profiles (
id uuid primary key references auth.users(id) on delete cascade,
email text,
tenant_id uuid references app_after.tenants(id),
role text not null default 'customer' check (role in ('customer','staff')),
created_at timestamptz not null default now()
);
create table app_after.orders (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text not null,
part_number text not null,
qty integer not null check (qty > 0),
due_date date not null,
status text not null default 'Open'
check (status in ('Open','In Production','Inspection','Shipped','Delivered','Cancelled')),
created_at timestamptz not null default now()
);
create table app_after.certs (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text,
title text not null,
file_path text not null,
created_at timestamptz not null default now()
);
create table app_after.rmas (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references app_after.tenants(id) on delete cascade,
order_number text,
reason text not null,
status text not null default 'Requested'
check (status in ('Requested','Approved','Received','Resolved','Rejected')),
created_by uuid references auth.users(id),
created_at timestamptz not null default now()
);
create index on app_after.orders (tenant_id);
create index on app_after.certs (tenant_id);
create index on app_after.rmas (tenant_id);
-- helper functions read the caller's OWN row only, security definer so the
-- policies that call them don't recurse into profiles' own RLS.
create or replace function app_after.current_tenant_id()
returns uuid language sql stable security definer set search_path = app_after, public as $$
select tenant_id from app_after.profiles where id = auth.uid()
$$;
create or replace function app_after.is_staff()
returns boolean language sql stable security definer set search_path = app_after, public as $$
select coalesce((select role = 'staff' from app_after.profiles where id = auth.uid()), false)
$$;
-- F-006 fix: SECURITY INVOKER (runs as the caller, so RLS on orders still
-- applies) PLUS an explicit staff check — belt and suspenders. A customer
-- calling this gets a permission error, not a silent no-op.
create or replace function app_after.admin_set_order_status(p_order_id uuid, p_status text)
returns void
language plpgsql
security invoker
set search_path = app_after, public
as $$
begin
if not app_after.is_staff() then
raise exception 'not authorized' using errcode = '42501';
end if;
update app_after.orders set status = p_status where id = p_order_id;
end;
$$;
grant execute on function app_after.admin_set_order_status(uuid, text) to authenticated;
-- new-user trigger: tenant comes from the invite code at signup, same as
-- before — the fix is that nothing downstream lets the user change it later.
create or replace function app_after.handle_new_user()
returns trigger language plpgsql security definer set search_path = app_after, public as $$
declare
t uuid;
begin
select id into t from app_after.tenants
where invite_code = new.raw_user_meta_data->>'invite_code';
insert into app_after.profiles (id, email, tenant_id, role)
values (new.id, new.email, t, 'customer')
on conflict (id) do nothing;
return new;
end;
$$;
drop trigger if exists on_auth_user_created_after on auth.users;
create trigger on_auth_user_created_after
after insert on auth.users
for each row execute function app_after.handle_new_user();
-- ------------------------------------------------------------------- RLS
alter table app_after.tenants enable row level security;
alter table app_after.profiles enable row level security;
alter table app_after.orders enable row level security;
alter table app_after.certs enable row level security;
alter table app_after.rmas enable row level security;
-- F-009 fix: tenants are no longer readable by anon, and an authenticated
-- user only sees their own company (staff see all).
create policy after_tenants_select on app_after.tenants
for select to authenticated
using (id = app_after.current_tenant_id() or app_after.is_staff());
-- F-003 fix: a user may read their own profile. There is NO update policy at
-- all for authenticated users — tenant_id and role can only change through
-- the SECURITY DEFINER trigger (signup) or a staff-only RPC, never a direct
-- PostgREST PATCH from the browser.
create policy after_profiles_select on app_after.profiles
for select to authenticated
using (id = auth.uid() or app_after.is_staff());
create policy after_profiles_staff_update on app_after.profiles
for update to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- F-002 fix: real tenant scoping, not `using (true)`.
create policy after_orders_select on app_after.orders
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_orders_staff_write on app_after.orders
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
create policy after_certs_select on app_after.certs
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_certs_staff_write on app_after.certs
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- F-007 fix: rmas is readable per-tenant, and a customer may INSERT only a
-- row for their OWN tenant, as themselves, starting in 'Requested' — the
-- WITH CHECK that app_before never had.
create policy after_rmas_select on app_after.rmas
for select to authenticated
using (tenant_id = app_after.current_tenant_id() or app_after.is_staff());
create policy after_rmas_customer_insert on app_after.rmas
for insert to authenticated
with check (
tenant_id = app_after.current_tenant_id()
and status = 'Requested'
and created_by = auth.uid()
);
create policy after_rmas_staff_write on app_after.rmas
for all to authenticated
using (app_after.is_staff()) with check (app_after.is_staff());
-- ===========================================================================
-- Storage — certs buckets
-- ===========================================================================
-- F-005: public bucket, no storage policies needed to read — any PDF path is
-- fetchable by anyone who can guess or list it, across every tenant.
insert into storage.buckets (id, name, public)
values ('certs-before', 'certs-before', true)
on conflict (id) do nothing;
-- F-005 fix: private bucket, explicit per-tenant policies (same shape as the
-- DB RLS: first path segment is the tenant id).
insert into storage.buckets (id, name, public)
values ('certs-after', 'certs-after', false)
on conflict (id) do nothing;
create policy after_certs_files_read on storage.objects for select to authenticated
using (
bucket_id = 'certs-after'
and (
app_after.is_staff()
or (storage.foldername(name))[1] = app_after.current_tenant_id()::text
)
);
create policy after_certs_files_staff_insert on storage.objects for insert to authenticated
with check (bucket_id = 'certs-after' and app_after.is_staff());
create policy after_certs_files_staff_update on storage.objects for update to authenticated
using (bucket_id = 'certs-after' and app_after.is_staff());
create policy after_certs_files_staff_delete on storage.objects for delete to authenticated
using (bucket_id = 'certs-after' and app_after.is_staff());
-- ===========================================================================
-- Seed data — fictional. Same tenants/orders/etc. in both schemas so
-- functional tests are directly comparable.
-- ===========================================================================
insert into app_before.tenants (id, name, invite_code) values
('11111111-1111-1111-1111-111111111111', 'Skyline Aerospace (demo)', 'AERO-DEMO-7K2Q'),
('22222222-2222-2222-2222-222222222222', 'Meridian Medical Devices (demo)', 'MED-DEMO-4M9X');
insert into app_after.tenants (id, name, invite_code) values
('11111111-1111-1111-1111-111111111111', 'Skyline Aerospace (demo)', 'AERO-DEMO-7K2Q'),
('22222222-2222-2222-2222-222222222222', 'Meridian Medical Devices (demo)', 'MED-DEMO-4M9X');
-- profiles: 2 customers at Skyline, 1 at Meridian, 1 staff — same ids in both schemas
insert into app_before.profiles (id, email, tenant_id, role) values
('a0000000-0000-0000-0000-00000000a001', 'jmiller@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a002', 'dchen@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a003', 'rpatel@meridianmed.example', '22222222-2222-2222-2222-222222222222', 'customer'),
('a0000000-0000-0000-0000-00000000a004', 'staff@tallgrasspc.example', null, 'staff')
on conflict (id) do nothing;
insert into app_after.profiles (id, email, tenant_id, role) values
('a0000000-0000-0000-0000-00000000a001', 'jmiller@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a002', 'dchen@skylineaero.example', '11111111-1111-1111-1111-111111111111', 'customer'),
('a0000000-0000-0000-0000-00000000a003', 'rpatel@meridianmed.example', '22222222-2222-2222-2222-222222222222', 'customer'),
('a0000000-0000-0000-0000-00000000a004', 'staff@tallgrasspc.example', null, 'staff')
on conflict (id) do nothing;
insert into app_before.orders (tenant_id, order_number, part_number, qty, due_date, status) values
('11111111-1111-1111-1111-111111111111', 'PO-10041', 'TPC-AL-7075-BRKT-220', 150, '2026-10-30', 'In Production'),
('11111111-1111-1111-1111-111111111111', 'PO-10042', 'TPC-TI-6AL4V-FTG-118', 40, '2026-11-14', 'Open'),
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'TPC-AL-6061-HSG-305', 300, '2026-09-28', 'Shipped'),
('22222222-2222-2222-2222-222222222222', 'MM-5520', 'TPC-SS-316L-CANN-045', 500, '2026-10-22', 'Inspection'),
('22222222-2222-2222-2222-222222222222', 'MM-5527', 'TPC-SS-316L-HDL-012', 250, '2026-11-05', 'In Production'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'TPC-PEEK-SPC-077', 1000, '2026-09-15', 'Delivered');
insert into app_after.orders (tenant_id, order_number, part_number, qty, due_date, status) values
('11111111-1111-1111-1111-111111111111', 'PO-10041', 'TPC-AL-7075-BRKT-220', 150, '2026-10-30', 'In Production'),
('11111111-1111-1111-1111-111111111111', 'PO-10042', 'TPC-TI-6AL4V-FTG-118', 40, '2026-11-14', 'Open'),
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'TPC-AL-6061-HSG-305', 300, '2026-09-28', 'Shipped'),
('22222222-2222-2222-2222-222222222222', 'MM-5520', 'TPC-SS-316L-CANN-045', 500, '2026-10-22', 'Inspection'),
('22222222-2222-2222-2222-222222222222', 'MM-5527', 'TPC-SS-316L-HDL-012', 250, '2026-11-05', 'In Production'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'TPC-PEEK-SPC-077', 1000, '2026-09-15', 'Delivered');
insert into app_before.certs (tenant_id, order_number, title, file_path) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Certificate of Conformance – PO-10037', '11111111-1111-1111-1111-111111111111/coc-po-10037.pdf'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Material Cert & CoC – MM-5491', '22222222-2222-2222-2222-222222222222/coc-mm-5491.pdf');
insert into app_after.certs (tenant_id, order_number, title, file_path) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Certificate of Conformance – PO-10037', '11111111-1111-1111-1111-111111111111/coc-po-10037.pdf'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Material Cert & CoC – MM-5491', '22222222-2222-2222-2222-222222222222/coc-mm-5491.pdf');
insert into app_before.rmas (tenant_id, order_number, reason, status, created_by) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Three housings have a burr on the bore edge.', 'Approved', 'a0000000-0000-0000-0000-00000000a001'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Spacer dimension out of tolerance on 12 pcs.', 'Requested', 'a0000000-0000-0000-0000-00000000a003');
insert into app_after.rmas (tenant_id, order_number, reason, status, created_by) values
('11111111-1111-1111-1111-111111111111', 'PO-10037', 'Three housings have a burr on the bore edge.', 'Approved', 'a0000000-0000-0000-0000-00000000a001'),
('22222222-2222-2222-2222-222222222222', 'MM-5491', 'Spacer dimension out of tolerance on 12 pcs.', 'Requested', 'a0000000-0000-0000-0000-00000000a003');
-- grants: anon/authenticated need table-level privileges; row visibility is
-- then enforced (or, in app_before's case, NOT enforced) by RLS above.
grant select, insert, update, delete on all tables in schema app_before to anon, authenticated;
grant select, insert, update, delete on all tables in schema app_after to anon, authenticated;
grant usage on all sequences in schema app_before to anon, authenticated;
grant usage on all sequences in schema app_after to anon, authenticated;
