disclosure-bureau/docs/adrs/ADR-004-auth-and-rls-evolution.md

adr	title	status	date	deciders	project
ADR-004	Auth model evolution — manter Supabase GoTrue + RLS publico-read; novas tabelas case/* write-only por investigator role	accepted	2026-05-23	sa-principal, sa-security-engineer, sa-platform-lead	disclosure-bureau

Context

Modelo de auth atual:

• GoTrue email magic-link, SMTP Spacemail, MAILER_AUTOCONFIRM=false.
• profiles.role ∈ {user, admin, suspended}, com budget_cap_usd, daily_quota.
• Sessoes is_public=true sao readable por anon (compartilhaveis via share_token UUID).
• RLS publico-read em chunks/entities/documents/entity_mentions/relations.
• service_role key em env do container web (necessario para usage_events INSERT bypass RLS + admin tasks). F5 do security audit.

Decisao de fronteira na W3: novas tabelas (hypotheses, evidence, contradictions, witnesses, gaps, residual_uncertainties, investigation_jobs) — quem escreve?

Options

1. service_role escreve tudo (mesmo padrao atual). Investigator-runtime usa service_role.
2. Role intermediario investigator com permissoes minimas. Investigator-runtime usa esse role; web nao.
3. Investigator usa Postgres direto sem RLS: bypass desde o nivel de conexao.

Decision

Opcao 2: Role investigator granular.

Criar role Postgres minimo:

CREATE ROLE investigator LOGIN NOINHERIT PASSWORD :'INVESTIGATOR_PASSWORD';
-- Reads (mesmo que anon ja tem, mas explicito)
GRANT SELECT ON public.chunks, public.entities, public.entity_mentions,
                public.relations, public.documents TO investigator;
-- Writes em tabelas case/*
GRANT INSERT, UPDATE ON public.hypotheses, public.evidence, public.contradictions,
                       public.witnesses, public.gaps, public.residual_uncertainties,
                       public.investigation_jobs TO investigator;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO investigator;
-- Negar tudo o resto
REVOKE ALL ON public.profiles, public.chat_sessions, public.messages,
              public.usage_events FROM investigator;
REVOKE ALL ON SCHEMA auth FROM investigator;

E:

• Investigator-runtime container usa postgres://investigator:${INVESTIGATOR_PASSWORD}@db:5432/postgres (NUNCA service_role).
• Web service continua com postgres://postgres:... (acesso amplo necessario para createServiceClient nos pontos especificos).
• Em W5, avaliar reducao de uso de service_role no web criando role web_service similar com permissoes ainda menores que postgres.

RLS nas novas tabelas:

ALTER TABLE public.hypotheses ENABLE ROW LEVEL SECURITY;
CREATE POLICY hypotheses_public_read ON public.hypotheses FOR SELECT USING (TRUE);
GRANT SELECT ON public.hypotheses TO anon, authenticated;
-- Investigator role usa bypass via INSERT/UPDATE direto (sem RLS aplica em writes do owner role; ele tem GRANT)

Mesmo padrao para todas as 7 novas tabelas.

Modificacoes nas existentes:

• relations ganha RLS (F4). Hoje sem.
• messages.citations JSONB continua mas adicionar coluna hypothesis_id REFERENCES public.hypotheses(hypothesis_id) se chat citar hipoteses geradas.

Sessions publicas:

Decisao do F6 (moderation_state) fica fora deste ADR porque e produto/legal, nao auth. Mas RLS aceitara extensao:

CREATE POLICY public_sessions_select ON public.chat_sessions
  FOR SELECT USING (
    auth.uid() = user_id OR
    (is_public = TRUE AND COALESCE(moderation_state, 'approved') IN ('approved'))
  );

Consequences

Positivas:

• Blast radius do investigator-runtime menor (RCE no container nao acesso ao auth.users / profiles).
• Auditabilidade granular: cada INSERT em hypotheses/evidence tem created_by = '<detective>@detective' + role Postgres investigator.
• Aderencia a least-privilege.

Negativas:

• 1 password adicional para gerir (INVESTIGATOR_PASSWORD). Mitigacao: docker secrets em W5.
• Investigator nao pode ler messages (intencional) — se algum dia detetive precisar contexto de sessao do user, precisa de hand-off explicito (ex: chat passar userTurn no payload do job).

Verification

• \du investigator confirma role + permissoes.
• Teste manual: investigator role tenta SELECT FROM auth.users -> erro permission denied.
• Teste manual: investigator role faz INSERT INTO hypotheses -> sucesso.
• Service_role uses count auditavel: grep createServiceClient no web/.

References

• security-audit-report.md F4, F5, F6
• agentic-layer-spec.md secao 3.2 (container env)
• infra/disclosure-stack/init-db.sql (roles bootstrap atual)