Row Level Security (RLS) — Plataforma OLP

Princípio

Todas as tabelas do projeto possuem RLS habilitado. As policies seguem o princípio do menor privilégio — cada papel recebe apenas as permissões mínimas necessárias.

---

Padrão de Policy para Dados de Escola

A maioria das tabelas com escopo de escola segue este padrão:

-- SELECT: usuário vê dados da sua escola
CREATE POLICY "select_by_escola" ON tabela
  FOR SELECT USING (
    escola_id::text = auth.jwt()->>'escola_id'
    AND lower(auth.jwt()->>'principal_role') = ANY(ARRAY['escola','coordenador','diretor'])
  );

-- INSERT: apenas gestor/coordenador
CREATE POLICY "insert_by_escola" ON tabela
  FOR INSERT WITH CHECK (
    escola_id::text = auth.jwt()->>'escola_id'
    AND lower(auth.jwt()->>'principal_role') = ANY(ARRAY['escola','coordenador'])
  );

-- UPDATE: apenas gestor/coordenador
CREATE POLICY "update_by_escola" ON tabela
  FOR UPDATE USING (
    escola_id::text = auth.jwt()->>'escola_id'
    AND lower(auth.jwt()->>'principal_role') = ANY(ARRAY['escola','coordenador'])
  );

---

Modelo de Permissões por Papel

Administrador

Escopo	Acesso	Tabelas
CRUD Completo	ALL	escolas, endereco_escola, usuarios, usuario_papeis, papeis, escola_assinaturas, escola_faturas, planos
INSERT	INSERT	notificacoes (criar notificações para qualquer usuário)
CRUD Feature Flags	INSERT/UPDATE/DELETE	canary_groups, canary_group_escolas, canary_group_usuarios, feature_flag_canary; UPDATE feature_flags
Leitura (Auditoria)	SELECT	olimpiadas, fases_olimpiada, alunos, inscricoes_olimpiada, responsaveis, logs_transacoes, mural_*
Sem Acesso (menor privilégio)	—	turmas (conteúdo pedagógico — admin sem acesso por design, decisão Fase 5 RLS)
Sem Acesso	—	INSERT/UPDATE/DELETE em dados escolares ou de olimpíadas

Especialista

Escopo	Acesso	Tabelas
CRUD Completo	ALL	olimpiadas, fases_olimpiada, atividades_cronograma, niveis_competicao, banners_login, headers_novidades, cursos, cursos_videos, templates_mensagem
Leitura	SELECT	escolas, escola_olimpiadas, inscricoes_olimpiada, configuracoes_fase_nivel
Sem Acesso	—	Dados de alunos, turmas, responsáveis, usuários

Escola (Gestor)

Escopo	Acesso	Tabelas
CRUD Completo	ALL	alunos, turmas, responsaveis, aluno_responsaveis, inscricoes_olimpiada, resultados_aluno, mural_*, mensagens_enviadas
Leitura	SELECT	olimpiadas, fases_olimpiada, series_escolares, niveis_competicao
Sem Acesso	—	tarefas, eventos_manuais_escola, dados de outras escolas

Coordenador

Tabela	SELECT	INSERT	UPDATE	DELETE
tarefas	✅ própria escola	✅	✅ (criador/responsável/coordenador)	✅ (criador ou órfã)
eventos_manuais_escola	✅ própria escola	✅	✅ (criador ou órfã)	✅ (criador ou órfã)
Demais tabelas operacionais	✅ própria escola	✅	✅	Depende da tabela

Tarefas e eventos órfãos (onde criado_por IS NULL por exclusão de usuário) podem ser gerenciados por qualquer coordenador da mesma escola.

Diretor

Escopo	Acesso	Observação
Leitura	SELECT própria escola	Dashboards e relatórios apenas
Sem Acesso	—	tarefas, eventos_manuais_escola

Portal (Aluno / Responsável)

Papel	Acesso	Filtro
Aluno	SELECT	id = auth.jwt()->>'sub' (apenas seus dados)
Responsável	SELECT	aluno_id IN (SELECT get_alunos_responsavel()) (apenas filhos vinculados)

---

Papel escola_trial

Papel atribuído a escolas em período de teste (auto-cadastro /cadastro/trial). Isolamento em três camadas:

Camada 1 — Permissões hardcoded

supabase/functions/_shared/permissions.ts retorna { menuItems: [], permissoes: [], canaryFlags: [] } para esse papel, independentemente do que estiver em usuarios_escola_permissoes. Zero CRUD por design.

Camada 2 — Bloqueio de gestão de permissões

admin-usuarios-escola recusa qualquer leitura/escrita de permissões para usuários com papel escola_trial. Tentativas retornam 403.

Camada 3 — RLS específicas (todas SELECT-only)

Tabela	Policy	Escopo
escolas	escolas_select_trial	Só a própria escola
escola_assinaturas	assinaturas_select_trial	Só a própria assinatura
olimpiadas	olimpiadas_escola_read_active	Olimpíadas com status='ativa'

Tabelas inacessíveis ao papel escola_trial

alunos, usuarios, usuarios_escola_permissoes, usuarios_escola_sub_permissoes, turmas, inscricoes_olimpiada, controle_*, mural_*, responsaveis, aluno_responsaveis, escola_faturas, eventos_manuais_escola (INSERT/UPDATE/DELETE removidos em migration 20260131191956, apenas SELECT permanece).

Edge functions acessíveis

Apenas eventos-calendario-trial (read-only) e /me. Nenhuma rota de mutação está exposta no frontend (<DashboardTrial /> não renderiza sidebar nem rotas administrativas).

Conversão para papel escola

Action admin-assinaturas.convert_trial_to_paid troca o papel automaticamente. A partir desse momento, o usuário passa pelo fluxo normal de permissões da escola pagante.

SSOT do ciclo completo: docs/business/SUBSCRIPTION_BILLING.md §10

---

Tabelas Críticas

Tabela	INSERT	SELECT	UPDATE	DELETE
logs_transacoes	Sistema only (service_role)	Admin + Gestor da escola	Bloqueado	Bloqueado
login_otps / portal_otps	Sistema only	Bloqueado	Sistema only	Bloqueado
cron_status	Sistema only	Admin only	Sistema only	Bloqueado
configuracoes_plataforma	Especialista	Público (leitura)	Especialista	Bloqueado
feature_flags	Autenticado (leitura)	—	Admin (UPDATE)	—
canary_groups	Autenticado (leitura)	Admin	Admin	Admin
canary_group_escolas	Admin + Escola (scoped)	Admin	—	Admin
canary_group_usuarios	Admin + Self	Admin	—	Admin
feature_flag_canary	Autenticado (leitura)	Admin	—	Admin
usuarios_escola_sub_permissoes	Admin (global) / Escola (scoped) / Self	Admin / Escola (scoped)	Admin / Escola (scoped)	Admin / Escola (scoped)

---

SECURITY DEFINER — Prevenção de Recursão Infinita

Problema

Quando policies de tabelas diferentes referenciam umas às outras em subqueries, ocorre recursão infinita (Postgres error 42P17).

Exemplo do ciclo:

alunos (policy: portal_responsavel_filhos_alunos)
    → Subquery em aluno_responsaveis
        → aluno_responsaveis (policy: aluno_resp_escola_select)
            → EXISTS (SELECT 1 FROM alunos WHERE ...)
                → RECURSÃO INFINITA

Solução

Funções SECURITY DEFINER executam com privilégios do owner (superuser), bypassando RLS durante sua execução. Isso quebra o ciclo de recursão.

Funções Implementadas

1. aluno_pertence_escola(p_aluno_id uuid) → boolean

Verifica se um aluno pertence à escola do usuário logado (sistema).

CREATE OR REPLACE FUNCTION public.aluno_pertence_escola(p_aluno_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM alunos
    WHERE id = p_aluno_id AND escola_id::text = auth.jwt()->>'escola_id'
  );
$$;

Uso: Policies de sistema em aluno_responsaveis para gestores/coordenadores.

2. get_alunos_responsavel() → SETOF uuid

Lista IDs de alunos vinculados ao responsável logado.

CREATE OR REPLACE FUNCTION public.get_alunos_responsavel()
RETURNS SETOF uuid LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT aluno_id FROM aluno_responsaveis
  WHERE responsavel_id::text = auth.jwt()->>'sub';
$$;

Uso: Policy portal_responsavel_filhos_alunos em alunos, inscricoes_olimpiada, resultados_aluno.

3. responsavel_vinculado_aluno(p_aluno_id uuid) → boolean

Verifica se o responsável logado tem vínculo com um aluno específico.

CREATE OR REPLACE FUNCTION public.responsavel_vinculado_aluno(p_aluno_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM aluno_responsaveis
    WHERE aluno_id = p_aluno_id AND responsavel_id::text = auth.jwt()->>'sub'
  );
$$;

4. responsavel_tem_aluno_na_escola(p_responsavel_id uuid) → boolean

Verifica se o responsável tem algum filho na escola do usuário logado.

Por que é seguro?

Risco	Mitigação
SQL Injection	SQL é hardcoded, não aceita parâmetros de texto
Privilege Escalation	Função valida claims JWT antes de retornar
Data Leakage	Retorna apenas UUIDs ou booleans, não dados completos
Search Path Attack	SET search_path = public força schema explícito

---

Histórico de Hardening

Data	Descrição
2026-04-06	usuarios_escola_sub_permissoes: Removida policy PUBLIC permissiva, criadas 10 policies restritivas (admin/escola/self). Fix de privilege escalation.
2026-04-06	Feature flags + canary: Criadas SELECT policies para authenticated em 5 tabelas. Permite migração futura de service_role → supabaseUser. Ref: ADR-011.
2026-04-06	permissions.ts: Migradas queries de sub_permissoes de supabaseSystem → supabaseUser (2 locais)
2026-03-05	gestao-resultados: Implementado queryInChunks para evitar limite de URL do PostgREST com arrays grandes de UUIDs (>100). Corrige bug onde escolas com 600+ inscrições viam resultados zerados.
2026-03-04	FK SET NULL: 11 FKs de usuarios alteradas de NO ACTION para SET NULL para permitir exclusão de usuários preservando histórico
2026-03-04	Tarefas: SELECT restrito a coordenador (removido diretor, pedagogico, professor, marketing). DELETE restrito a coordenador (criador ou órfã)
2026-03-04	Eventos manuais: removida policy SELECT para escola_trial, removidas policies genéricas sem filtro de papel, UPDATE/DELETE incluem suporte a registros órfãos
2026-03-04	resultados_aluno: Policy SELECT expandida para incluir coordenador e diretor (antes apenas escola)
2026-03-04	importacao_resultados_sessoes: Policy SELECT adicionada para owner da sessão (usuario_id = sub)
2026-01-22	Implementação de funções SECURITY DEFINER para portal
2026-01-25	Hardening admin: removido CRUD em 19 tabelas fora do escopo, mantido SELECT para auditoria
2026-01-25	Especialista: mantém CRUD em olimpíadas, Admin recebe SELECT-only
2026-04-06	usuario_papeis: Corrigida policy morta (auth.uid() → auth.jwt()->>'sub'). Policy usuario_papeis_self_select para self-read
2026-04-06	coordenador_cores: Criadas 3 policies (cores_select_escola, cores_insert_self, cores_update_self). Migrado de service_role para RLS
2026-04-06	coordenador_cores: Corrigida policy cores_update_self — adicionado WITH CHECK para prevenir alteração de usuario_id/escola_id
2026-04-06	Migração de código: ~15 queries em permissions.ts, feature-gate.ts, user-profile, tarefas-escola/helpers de supabaseSystem → supabaseUser
2026-04-07	admin-feature-flags Fase 1: 12 policies admin-only (UPDATE feature_flags; INSERT/UPDATE/DELETE canary_groups; SELECT/INSERT/DELETE canary_group_escolas e canary_group_usuarios; INSERT/DELETE feature_flag_canary). Código migrado de createSupabaseSystem() → createSupabaseClient(req).
2026-04-07	Fase 3: escola_assinaturas_escola_update_own e escola_faturas_escola_update_own — escola pode UPDATE seus próprios registros. coordenador-videos removido supabaseSystem (RPC SECURITY DEFINER). escola-pagamentos/recalculate_due_dates migrado para RLS.
2026-04-07	Hardening legado: 19 policies migradas de TO public → TO authenticated em 6 tabelas (cursos_videos, escola_assinaturas, escola_faturas, usuario_progresso_video, video_comentarios, video_materiais). cursos_videos_especialista_full (FOR ALL) decomposta em 4 policies por operação. coordenador-videos restrito a papel coordenador apenas.

---

Checklist para Novas Tabelas

□ RLS habilitado: ALTER TABLE tabela ENABLE ROW LEVEL SECURITY
□ Policy SELECT com filtro por escola_id e papel
□ Policy INSERT com WITH CHECK por escola_id e papel
□ Policy UPDATE com USING + WITH CHECK
□ Policy DELETE apenas se necessário (preferir soft delete)
□ Admin: SELECT-only (auditoria) — nunca CRUD em dados operacionais
□ Especialista: acesso apenas se tabela é de conteúdo (olimpíadas, cursos)
□ Portal: SELECT-only com filtro por sub ou get_alunos_responsavel()
□ Testar com admin_rls_smoke via auth-diagnostics

---

RPCs SECURITY DEFINER (Operações de Senha)

Criadas na Fase 4 da migração RLS (2026-04-07)

Estas RPCs encapsulam operações sensíveis de senha, evitando exposição de senha_hash via PostgREST. Todas usam guard de ownership: (auth.jwt()->>'sub')::uuid != p_usuario_id → RAISE EXCEPTION.

RPC	Operação	Tabela	Consumidores
get_own_password_hash(p_usuario_id)	SELECT senha_hash	usuarios	set-password, change-password, reset-password
update_own_password_hash(p_usuario_id, p_senha_hash)	UPDATE senha_hash	usuarios	set-password, change-password, reset-password
get_own_password_history(p_usuario_id, p_limit)	SELECT senha_hash[]	senha_historico	password-history-helper.ts
save_to_password_history(p_usuario_id, p_senha_hash)	INSERT + cleanup	senha_historico	password-history-helper.ts

Onda 2 (2026-05-03) — Lockdown explícito: as 4 RPCs acima tinham EXECUTE para anon por default. Após Onda 2, REVOKE EXECUTE FROM anon + GRANT EXECUTE TO authenticated. Defesa runtime via auth.jwt()->>'sub' mantida (defense-in-depth).

---

Categoria 5 — RLS habilitado SEM policies (fail-close)

Padrão para tabelas cujo acesso deve ser EXCLUSIVAMENTE via RPCs SECURITY DEFINER. RLS ativa + zero policies = qualquer SELECT/INSERT/UPDATE/DELETE direto via PostgREST devolve 0 linhas, sem 401/403.

Tabela	Acesso permitido	Validado por
senha_historico	RPCs get_own_password_history, save_to_password_history	Guard (auth.jwt()->>'sub')::uuid = p_usuario_id em cada RPC + GRANT EXECUTE TO authenticated

Como adicionar uma tabela aqui: habilitar RLS, NÃO criar policies, criar RPCs SECURITY DEFINER com SET search_path = public + guard de ownership + REVOKE ALL FROM PUBLIC, anon + GRANT EXECUTE TO authenticated. Documentar via COMMENT ON TABLE.

---

Mural de Publicações (mural_publicacoes)

Onda 2 (2026-05-03) — F4 fix: removida a policy mural_pub_public_read_active que permitia leitura por anon. Substituída por policies portal-scoped + RPC para responsável global.

Policy	Operação	Quem	Condição
mural_pub_coordenador_full	ALL	coordenador autenticado	escola_id do JWT bate com a linha
mural_pub_portal_aluno_select	SELECT	aluno do portal	portal_type='aluno' + escola_id IS NOT NULL no JWT + bate com a linha + ativa=true
mural_pub_portal_responsavel_select	SELECT	responsável escola-fixa	portal_type='responsavel' + escola_id IS NOT NULL no JWT + bate com a linha + ativa=true

Responsável global (escola_id NULL no JWT): atendido pela RPC get_mural_publicacoes_responsavel_global(p_escola_id), que valida portal_type='responsavel' + vínculo aluno_responsaveis na escola pedida. Contrato de retorno explícito (RETURNS TABLE (...)) — colunas novas adicionadas a mural_publicacoes não vazam pelo portal automaticamente.

A edge mural-escola/get_publicacoes_ativas detecta o token portal (cookie olp_mural) e roteia para o cliente correto: aluno/responsável escola-fixa via supabasePortal (RLS portal-scoped); responsável global via RPC.

---

Pendências de Remediação Ativas (Onda 3)

Contexto: scan de segurança 2026-05-03. Findings reavaliados sob lente de produção (olp.digital com clientes reais). Nenhum item abaixo é aceito como risco — todos exigem correção real, não supressão.

ID	Tabela / Recurso	Problema	Plano de remediação
F1	escolas	Policy escolas_user_own_school expõe cnpj, email_contato, telefone_contato, gateway_customer_id, codigo_inep ao portal (aluno/responsável) via match de escola_id.	Separar em duas policies: portal vê apenas colunas seguras (id, nome, slug, logo_url) via view escolas_publicas ou função SECURITY DEFINER; backoffice vê tabela completa. Revogar SELECT direto na tabela base para portal_type em JWT.
F2	usuarios	Policies usuarios_gestor_select_own_school e usuarios_diretor_select_own_school permitem que gestor/diretor leiam senha_hash (e variantes legadas) de qualquer usuário da escola.	Criar view usuarios_seguro sem colunas de credencial e revogar SELECT direto na tabela usuarios para os roles de escola. Hash continua acessível apenas via RPCs SECURITY DEFINER de validação de senha.
F3	escola_faturas / escola_assinaturas	gateway_response, gateway_payment_id, gateway_subscription_id, gateway_customer_id legíveis por escola/diretor.	Avaliar restringir colunas internas do gateway ao admin. Manter para o gestor apenas o necessário para visualização da fatura (status, valor, vencimento, links de pagamento).
F4	Bucket mural-imagens	Apenas SELECT público; sem policies INSERT/UPDATE/DELETE.	Criar policies de escrita escopadas a coordenador/escola com verificação de escola_id no path do objeto.

---

Anti-Escalation em usuario_papeis (gestor escola)

Adicionado em 2026-05-08 — finding gestor_escola_insert_usuario_papeis_escalation.

Tabela papeis ganhou coluna escopo text NOT NULL CHECK (escopo IN ('global','escola','portal')) que classifica cada papel:

Escopo	Papéis
global	administrador, especialista
escola	escola, escola_trial, coordenador, diretor, pedagogico, professor, marketing
portal	aluno, responsavel

Trigger trg_block_escola_role_escalation em BEFORE INSERT OR UPDATE ON usuario_papeis aplica regras fail-close:

• service_role e jobs sem JWT (cron, migrations) bypassam.
• administrador / especialista podem atribuir qualquer papel.
• escola (gestor):
  • só pode atribuir papéis com escopo='escola' (allowlist via metadata, não denylist).
  • não pode promover outro gestor escola (evita auto-replicação).
  • escola_id do registro inserido tem que bater com o escola_id do JWT.
• Qualquer outro principal_role é rejeitado (fail-close: papel novo nasce sem permissão de atribuir nada).

Erros levantados: 42501 com mensagem específica para auditoria.

Como adicionar um papel novo no futuro:

1. INSERT em papeis com escopo correto (global, escola ou portal). É só isso.
2. Trigger lê de papeis.escopo — zero edits em código quando criar papel novo de escola.

Como NÃO fazer: denylist hardcoded de papéis globais no trigger. Viola o Core rule de fail-close — papel global novo cairia como permitido até alguém lembrar de editar o trigger.