turistei/docs/database/auditorias.sql at main · 44CodeSimone/turistei · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- =====================================================
-- TURISTEI - SECURITY & INTEGRITY AUDITS (DOCUMENTATION)
-- This file mirrors the production audit routines that validate
-- database hardening and financial integrity.
-- =====================================================

-- =====================================================
-- Security audit
-- =====================================================
-- Function: admin_auditoria_seguranca_json()
-- Output:
-- {
--   "PASS": true,
--   "details": {
--     "rls_forced": true,
--     "no_public_grants": true,
--     "no_public_execute": true,
--     "secure_search_path": true,
--     "indexes_ok": true,
--     "fk_integrity_ok": true
--   }
-- }
--
-- Purpose:
-- - Ensure all tables have RLS + FORCE enabled
-- - Verify no GRANT exists for anon/authenticated
-- - Verify no EXECUTE exists for PUBLIC
-- - Confirm hardened default privileges
-- - Validate FK indexes and financial protections

-- =====================================================
-- Financial integrity audit
-- =====================================================
-- Checks:
-- - No deletions allowed in financial history tables
-- - ON DELETE RESTRICT enforced on:
--     pedidos
--     pedidos_itens
--     pagamentos_pedido
--     comissoes_itens
--     repasses_prestador
-- - Historical payout consistency

-- =====================================================
-- Performance audit
-- =====================================================
-- Validates:
-- - FK support indexes exist
-- - Critical query paths are optimized