-- ============================================================================
-- NoData — Postgres RLS lockdown template
--
-- A working starting point for a Postgres database that has tables with PII.
-- Apply per-table; review and adapt the policies to your tenant model.
--
-- This template comes from the NoData Guard scanner's auto-generated
-- migration_sql output (see /kit sample-guard-scan.json for a full example).
--
-- Free for any use. No attribution required.
-- ============================================================================

-- ── Pattern 1: tenant-isolation table (rows belong to one tenant) ──
ALTER TABLE my_tenant_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON my_tenant_table
  FOR SELECT
  TO authenticated
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE POLICY tenant_isolation_modify ON my_tenant_table
  FOR ALL
  TO authenticated
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
  WITH CHECK (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

-- ── Pattern 2: per-user owned table (rows belong to one user) ──
ALTER TABLE user_owned_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_owns_row_read ON user_owned_table
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

CREATE POLICY user_owns_row_write ON user_owned_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

CREATE POLICY user_owns_row_update ON user_owned_table
  FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY user_owns_row_delete ON user_owned_table
  FOR DELETE
  TO authenticated
  USING (user_id = auth.uid());

-- ── Pattern 3: service-role-only lockdown (no client access at all) ──
ALTER TABLE secrets_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY service_role_only ON secrets_table
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- Note: omitting policies for authenticated/anon roles means they get NO access.

-- ── Pattern 4: public-read (e.g., reference data like terms_of_service) ──
ALTER TABLE public_reference_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY public_read ON public_reference_table
  FOR SELECT
  TO anon, authenticated
  USING (true);
-- (no INSERT/UPDATE/DELETE policies → only service_role can write)

-- ── Anti-pattern check: USING (true) on authenticated → equivalent to no RLS ──
-- The Guard scanner flags this. If you find it in your DB, replace with one
-- of patterns 1-4 above.
--
-- Example of what to remove:
--   CREATE POLICY orders_authenticated_full_access ON orders
--     FOR ALL TO authenticated
--     USING (true);  -- ← BAD: every authenticated user sees every row

-- ── Function search_path hardening (Guard low-severity finding) ──
-- Apply per SECURITY DEFINER function:
ALTER FUNCTION public.my_security_definer_fn(int)
  SET search_path = pg_catalog, public;
-- This prevents schema-shadowing attacks against SECURITY DEFINER functions.
