Supabase Row-Level-Security policy bundle — every-table baseline

-- =============================================================
-- Supabase Row-Level-Security baseline policy bundle
-- =============================================================
-- This is the every-table baseline. Adapt per table.
-- The Lovable BOLA April 2026 incident affected 10.3% of scanned
-- Lovable apps because their generator did NOT apply the equivalent.

-- Step 1: Enable RLS on every table
-- (Without this, the anon key — which ships in every client — reads everything)
alter table public.profiles enable row level security;
alter table public.orders enable row level security;
alter table public.invoices enable row level security;
alter table public.audit_log enable row level security;

-- Step 2: Default-deny baseline
-- Every table starts with no access; explicit allow policies layer on top.
create policy "default_deny_select" on public.profiles for select using (false);
create policy "default_deny_select" on public.orders for select using (false);
create policy "default_deny_select" on public.invoices for select using (false);
create policy "default_deny_insert" on public.profiles for insert with check (false);
create policy "default_deny_update" on public.profiles for update using (false);
create policy "default_deny_delete" on public.profiles for delete using (false);

-- Step 3: Per-user-owned-row pattern (single-tenant apps)
-- WRONG (commented out — this is what AI tools generate by default):
--   create policy "users_read_own" on public.profiles for select
--     using (auth.uid() = user_id);
-- It works for single-tenant but in MULTI-TENANT apps it leaks
-- across tenants if user_id is not tenant-unique.

-- RIGHT — per-tenant-scoped pattern with JWT claim:
create policy "users_read_own_in_tenant" on public.orders for select
  using (
    auth.uid() = user_id
    and tenant_id = (auth.jwt() ->> 'tenant')::uuid
  );

create policy "users_insert_own_in_tenant" on public.orders for insert
  with check (
    auth.uid() = user_id
    and tenant_id = (auth.jwt() ->> 'tenant')::uuid
  );

create policy "users_update_own_in_tenant" on public.orders for update
  using (
    auth.uid() = user_id
    and tenant_id = (auth.jwt() ->> 'tenant')::uuid
  )
  with check (
    auth.uid() = user_id
    and tenant_id = (auth.jwt() ->> 'tenant')::uuid
  );

create policy "users_delete_own_in_tenant" on public.orders for delete
  using (
    auth.uid() = user_id
    and tenant_id = (auth.jwt() ->> 'tenant')::uuid
  );

-- Step 4: Write-only audit-log pattern
-- Audit log: anyone authenticated can INSERT, no one can SELECT
create policy "anyone_writes_audit" on public.audit_log for insert
  with check (auth.role() = 'authenticated');
create policy "no_select_audit" on public.audit_log for select using (false);
create policy "no_update_audit" on public.audit_log for update using (false);
create policy "no_delete_audit" on public.audit_log for delete using (false);

-- Step 5: Admin-only table pattern
create policy "admins_only_select" on public.invoices for select
  using ((auth.jwt() ->> 'role')::text = 'admin');
create policy "admins_only_write" on public.invoices for all
  using ((auth.jwt() ->> 'role')::text = 'admin')
  with check ((auth.jwt() ->> 'role')::text = 'admin');

-- Step 6: Storage bucket policies (Supabase Storage)
-- Buckets need RLS too; many tutorials skip this.
create policy "users_read_own_files" on storage.objects for select
  using (
    bucket_id = 'user-uploads'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
create policy "users_insert_own_files" on storage.objects for insert
  with check (
    bucket_id = 'user-uploads'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Step 7: Verification — run this after deployment
--   1. Sign in as user A; query \`select * from orders\`. Expect: only A's orders.
--   2. Sign in as user B; query the same. Expect: only B's orders, not A's.
--   3. Tenant test: user A in tenant 1 + user B in tenant 2 with same email.
--      Query orders. Expect: each sees only their own tenant's rows.
--   4. anon-key test: with no auth, query orders. Expect: zero rows (default-deny).

-- Step 8: Securie's Supabase RLS specialist runs the above test on every PR.
-- See /guides/supabase-rls-misconfiguration for the full playbook.