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
Leitura (Auditoria)	SELECT	olimpiadas, fases_olimpiada, alunos, turmas, inscricoes_olimpiada, responsaveis, logs_transacoes, mural_*
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)

---

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

---

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-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

---

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