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)

---

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_SERVICE_ROLE_USAGE.md
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