encrypted_column_example.sql

-- =====================================================================
-- EXAMPLE: column-level encryption via Supabase Vault
-- ---------------------------------------------------------------------
-- Stores the SSN as a Vault secret id; the plaintext never touches a
-- table. Reads are mediated by a SECURITY DEFINER RPC that checks the
-- caller has a legitimate reason to see the value (org owner + admin).
-- =====================================================================

create table if not exists public.customers (
  id              uuid primary key default gen_random_uuid(),
  org_id          uuid not null,
  display_name    text not null,
  ssn_secret_id   uuid references vault.secrets(id) on delete set null,
  created_at      timestamptz not null default now()
);

alter table public.customers enable row level security;

drop policy if exists customers_select on public.customers;
create policy customers_select on public.customers
  for select to authenticated
  using ( org_id = security.current_org_id() );

drop policy if exists customers_insert on public.customers;
create policy customers_insert on public.customers
  for insert to authenticated
  with check ( org_id = security.current_org_id() and security.is_admin() );

-- ---------------------------------------------------------------------
-- WRITE: callable by admins; encrypts via Vault and stores the secret id
-- ---------------------------------------------------------------------
create or replace function public.set_customer_ssn(p_customer uuid, p_ssn text)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_org    uuid;
  v_old_id uuid;
  v_new_id uuid;
begin
  select org_id, ssn_secret_id into v_org, v_old_id
    from public.customers where id = p_customer;

  if v_org is null then raise exception 'customer not found'; end if;
  if v_org <> security.current_org_id() or not security.is_admin() then
    raise exception 'forbidden' using errcode = 'insufficient_privilege';
  end if;

  v_new_id := security.encrypt_text(p_ssn);
  update public.customers set ssn_secret_id = v_new_id where id = p_customer;

  if v_old_id is not null then
    delete from vault.secrets where id = v_old_id;
  end if;
end;
$$;

revoke all on function public.set_customer_ssn(uuid, text) from public, anon;
grant execute on function public.set_customer_ssn(uuid, text) to authenticated;

-- ---------------------------------------------------------------------
-- READ: callable by admins; logs the access in security.audit_log
-- ---------------------------------------------------------------------
create or replace function public.get_customer_ssn(p_customer uuid)
returns text
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_org   uuid;
  v_sid   uuid;
  v_plain text;
begin
  select org_id, ssn_secret_id into v_org, v_sid
    from public.customers where id = p_customer;

  if v_sid is null then return null; end if;
  if v_org <> security.current_org_id() or not security.is_admin() then
    raise exception 'forbidden' using errcode = 'insufficient_privilege';
  end if;

  v_plain := security.decrypt_text(v_sid);

  -- explicit audit row for sensitive read (mutations are auto-audited)
  insert into security.audit_log (
    ts, actor_id, actor_role, schema_name, table_name, op, row_pk,
    new_data, statement
  ) values (
    now(), auth.uid(), security.current_role_label(),
    'public', 'customers', 'U', p_customer::text,
    jsonb_build_object('ssn_read', true), 'public.get_customer_ssn'
  );

  return v_plain;
end;
$$;

revoke all on function public.get_customer_ssn(uuid) from public, anon;
grant execute on function public.get_customer_ssn(uuid) to authenticated;