Aprenda a criar trilhas de auditoria detectáveis no PostgreSQL usando tabelas append-only e encadeamento por hash, de modo que edições sejam fáceis de detectar em revisões e investigações.
Uma trilha de auditoria é o registro ao qual você recorre quando algo parece errado: um reembolso estranho, uma mudança de permissão que ninguém lembra, ou um registro de cliente que “desapareceu”. Se a trilha de auditoria puder ser editada, ela para de ser evidência e vira mais um dado que alguém pode reescrever.
Muitos “logs de auditoria” são apenas tabelas regulares. Se linhas podem ser atualizadas ou deletadas, a história também pode ser atualizada ou deletada.
Uma distinção importante: impedir edições não é o mesmo que tornar edições detectáveis. Você pode reduzir mudanças com permissões, mas qualquer pessoa com acesso suficiente (ou uma credencial de admin roubada) ainda pode alterar o histórico. Tamper-evidence aceita essa realidade. Você pode não impedir toda alteração, mas pode fazer com que mudanças deixem uma impressão óbvia.
Logs de auditoria normais são contestados por razões previsíveis. Usuários privilegiados podem “corrigir” o log depois do fato. Uma conta de aplicação comprometida pode gravar entradas críveis que parecem tráfego normal. Timestamps podem ser preenchidos retroativamente para esconder uma alteração tardia. Ou alguém deleta apenas as linhas mais danosas.
“Tamper-evident” significa que você projeta a trilha de auditoria de forma que até uma pequena edição (mudar um campo, remover uma linha, reordenar eventos) se torne detectável depois. Você não está prometendo mágica. Está prometendo que, quando alguém perguntar “Como sabemos que este log é real?”, você pode rodar checagens que mostram se o log foi tocado.
Uma trilha de auditoria detectável só é útil se responder às perguntas que você enfrentará depois: quem fez o quê, quando fizeram e o que mudou.
Comece pelos eventos que importam para o seu negócio. Mudanças de dados (create, update, delete) são a base, mas investigações frequentemente dependem também de segurança e acesso: logins, resets de senha, mudanças de permissão e bloqueios de conta. Se você lida com pagamentos, reembolsos, créditos ou pagamentos, trate movimentação de dinheiro como eventos de primeira classe, não como um efeito colateral de uma linha atualizada.
Depois, decida o que torna um evento crível. Auditores geralmente esperam um ator (usuário ou serviço), um timestamp do lado do servidor, a ação tomada e o objeto afetado. Para updates, armazene valores antes e depois (ou pelo menos os campos sensíveis), além de um request id ou correlation id para vincular várias mudanças pequenas no banco a uma única ação do usuário.
Finalmente, seja explícito sobre o que “imutável” significa no seu sistema. A regra mais simples é: nunca atualize ou delete linhas de auditoria, apenas insira. Se algo estiver errado, escreva um novo evento que corrija ou supersede o antigo, mantendo o original visível.
Mantenha os dados de auditoria separados das suas tabelas normais. Um schema dedicado audit reduz edições acidentais e facilita o raciocínio sobre permissões.
O objetivo é simples: linhas podem ser adicionadas, mas nunca alteradas ou removidas. No PostgreSQL, você aplica isso com privilégios (quem pode fazer o quê) e alguns controles na modelagem da tabela.
Aqui está uma tabela inicial prática:
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE audit.events (
id bigserial PRIMARY KEY,
entity_type text NOT NULL,
entity_id text NOT NULL,
event_type text NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
actor_id text,
occurred_at timestamptz NOT NULL DEFAULT now(),
request_id text,
before_data jsonb,
after_data jsonb,
notes text
);
Alguns campos são especialmente úteis em investigações:
occurred_at com DEFAULT now() para que o tempo seja carimbado pelo banco, não pelo cliente.entity_type e entity_id para que você possa seguir um registro através de mudanças.request_id para que uma ação de usuário seja rastreada através de várias linhas.Bloqueie com roles. A role da sua aplicação deve poder INSERT e SELECT em audit.events, mas não UPDATE ou DELETE. Mantenha mudanças de schema e permissões mais fortes para uma role de admin que não seja usada pela app.
Se você quer uma trilha de auditoria detectável, o lugar mais confiável para capturar mudanças é o próprio banco de dados. Logs da aplicação podem ser pulados, filtrados ou reescritos. Um trigger dispara não importa qual app, script ou ferramenta de admin toque a tabela.
Mantenha triggers sem frescura. O trabalho deles deve ser uma coisa: inserir um evento de auditoria para cada INSERT, UPDATE e DELETE nas tabelas que importam.
Um registro de auditoria prático costuma incluir o nome da tabela, tipo da operação, chave primária, valores antes e depois, um timestamp e identificadores que permitem agrupar mudanças relacionadas (tx id e correlation id).
IDs de correlação são a diferença entre “20 linhas atualizadas” e “Isso foi um clique de botão”. Sua app pode definir um correlation id por requisição (por exemplo, numa configuração de sessão do BD), e o trigger pode ler isso. Armazene também txid_current() para que você ainda possa agrupar mudanças quando o correlation id estiver ausente.
Aqui está um padrão simples de trigger que se mantém previsível porque apenas insere na tabela de auditoria (ajuste nomes conforme seu schema):
CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
corr_id text;
BEGIN
corr_id := current_setting('app.correlation_id', true);
INSERT INTO audit_events(
occurred_at, table_name, op, row_pk,
old_row, new_row, db_user, txid, correlation_id
) VALUES (
now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
Resista à tentação de fazer mais dentro dos triggers. Evite consultas extras, chamadas de rede ou ramificações complexas. Triggers pequenos são mais fáceis de testar, mais rápidos de rodar e mais difíceis de contestar em uma revisão.
Uma tabela append-only ajuda, mas alguém com acesso suficiente ainda pode reescrever linhas antigas. Encadeamento por hash torna esse tipo de adulteração visível.
Adicione duas colunas a cada linha de auditoria: prev_hash e row_hash (às vezes chamado de chain_hash). prev_hash armazena o hash da linha anterior na mesma cadeia. row_hash armazena o hash da linha atual, calculado a partir dos dados da linha mais prev_hash.
O que você hashea importa. Você quer uma entrada estável e repetível para que a mesma linha sempre produza o mesmo hash.
Uma abordagem prática é hashear uma string canônica construída a partir de colunas fixas (timestamp, ator, ação, id da entidade), um payload canônico (frequentemente jsonb, porque as chaves são armazenadas consistentemente) e o prev_hash.
Tenha cuidado com detalhes que podem mudar sem significado, como espaços em branco, ordem de chaves JSON em texto plano ou formatação específica de localidade. Mantenha tipos consistentes e serialize de uma forma previsível.
Se você encadear todos os eventos de auditoria em uma única sequência global, escritas podem tornar-se um gargalo. Muitos sistemas encadeiam dentro de um “stream”, como por tenant, por tipo de entidade ou por objeto de negócio.
Cada nova linha consulta o último row_hash do seu stream, armazena isso em prev_hash e depois calcula seu próprio row_hash.
-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
concat_ws('|',
stream_key,
occurred_at::text,
actor_id::text,
action,
entity,
entity_id::text,
payload::jsonb::text,
encode(prev_hash, 'hex')
),
'sha256'
);
Para análises mais rápidas, armazene o row_hash mais recente (o “chain head”) periodicamente, por exemplo diariamente por stream, em uma pequena tabela de snapshot. Durante uma investigação, você pode verificar a cadeia até cada snapshot em vez de escanear todo o histórico de uma vez. Snapshots também tornam mais fácil comparar exports e notar lacunas suspeitas.
Encadeamento por hash fica complicado sob tráfego real. Se duas transações gravarem linhas de auditoria ao mesmo tempo e ambas usarem o mesmo prev_hash, você pode terminar com forks. Isso reduz sua habilidade de provar uma sequência única e limpa.
Primeiro, decida o que sua cadeia representa. Uma cadeia global é a mais fácil de explicar, mas tem maior contenção. Múltiplas cadeias reduzem contenção, mas você precisa ser claro sobre o que cada cadeia prova.
Qualquer que seja o modelo, defina uma ordem estrita com um id de evento monotônico (geralmente um id suportado por sequence). Timestamps não são suficientes porque podem colidir e ser manipulados.
Para evitar condições de corrida ao calcular prev_hash, serialize o “obter último hash + inserir próxima linha” para cada stream. Abordagens comuns são bloquear uma única linha que representa o head do stream, ou usar um advisory lock com chave do stream id. O objetivo é que dois escritores no mesmo stream não leiam ambos o mesmo last hash.
Partitioning e sharding afetam onde a “última linha” vive. Se você espera particionar dados de auditoria, mantenha cada cadeia totalmente contida dentro de uma partição usando a mesma chave de partição que a chave do stream (por exemplo, tenant id). Assim, cadeias de tenant permanecem verificáveis mesmo que tenants sejam movidos entre servidores.
Encadeamento por hash só ajuda se você puder provar que a cadeia ainda se mantém quando alguém perguntar. A abordagem mais segura é uma consulta de verificação somente leitura (ou job) que recompute o hash de cada linha a partir dos dados armazenados e compare com o que está registrado.
Um verificador deve: reconstruir o hash esperado para cada linha, confirmar que cada linha aponta para a anterior e sinalizar qualquer coisa fora do esperado.
Aqui está um padrão comum usando window functions. Ajuste nomes de colunas para corresponder à sua tabela.
WITH ordered AS (
SELECT
id,
created_at,
actor_id,
action,
entity,
entity_id,
payload,
prev_hash,
row_hash,
LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
/* expected row hash, computed the same way as in your insert trigger */
encode(
digest(
coalesce(prev_hash, '') || '|' ||
id::text || '|' ||
created_at::text || '|' ||
coalesce(actor_id::text, '') || '|' ||
action || '|' ||
entity || '|' ||
entity_id::text || '|' ||
payload::text,
'sha256'
),
'hex'
) AS expected_row_hash
FROM audit_log
)
SELECT
id,
created_at,
CASE
WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
ELSE 'OK'
END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;
Além de “quebrado ou não”, vale checar lacunas (ids faltando em uma faixa), links fora de ordem e duplicatas suspeitas que não batem com workflows reais.
Não rode uma consulta e enterre a saída em um ticket. Armazene resultados de verificação em uma tabela append-only separada (por exemplo, audit_verification_runs) com hora da execução, versão do verificador, quem a disparou, o intervalo checado e contagens de broken links e hash mismatches.
Isso lhe dá uma segunda trilha: não só o log de auditoria está intacto, mas você pode mostrar que vem verificando-o.
Uma cadência prática é: rodar após qualquer deploy que toque a lógica de auditoria, diariamente para sistemas ativos e sempre antes de uma auditoria planejada.
A maioria das falhas não é sobre o algoritmo de hash. São exceções e lacunas que dão espaço para argumentação.
A forma mais rápida de perder confiança é permitir updates em linhas de auditoria. Mesmo que seja “só desta vez”, você criou um precedente e um caminho funcional para reescrever história. Se precisar corrigir algo, adicione um novo evento de auditoria que explique a correção e mantenha o original.
Encadeamento por hash também falha quando você hashea dados instáveis. JSON é uma armadilha comum. Se você hashear uma string JSON, diferenças inofensivas (ordem de chaves, espaços, formatação de números) podem mudar o hash e tornar a verificação barulhenta. Prefira uma forma canônica: campos normalizados, jsonb ou outra serialização consistente.
Outros padrões que minam uma trilha defensável:
prev_hash dentro de uma cadeia sem uma regra clara e documentada.Separação de responsabilidades importa. Se a mesma role pode inserir eventos de auditoria e também modificá-los, tamper-evidence vira promessa em vez de controle.
Uma trilha defensável deve ser difícil de alterar e fácil de verificar.
Comece pelo controle de acesso: a tabela de auditoria deve ser append-only na prática. A role da aplicação deve inserir (e normalmente ler), mas não atualizar ou deletar. Mudanças de schema devem ser bem restritas.
Garanta que cada linha responda às perguntas que um investigador fará: quem fez, quando aconteceu (lado servidor), o que aconteceu (nome claro do evento mais operação), o que foi afetado (nome da entidade e id) e como se conecta (request/correlation id e transaction id).
Então valide a camada de integridade. Um teste rápido é reproduzir um segmento e confirmar que cada prev_hash bate com o hash da linha anterior, e que cada hash armazenado bate com o recomputado.
Operacionalmente, trate verificação como um job normal:
Um caso de teste comum é uma disputa de reembolso. Um cliente alega que foi aprovado um reembolso de $250, mas o sistema agora mostra $25. O suporte insiste que a aprovação estava correta e a conformidade quer uma resposta.
Comece restringindo a busca usando um correlation id (order id, ticket id ou refund_request_id) e uma janela de tempo. Puxe as linhas de auditoria para esse correlation id e coloque-as em volta do horário da aprovação.
Você busca o conjunto completo de eventos: pedido criado, reembolso aprovado, valor do reembolso definido e quaisquer updates posteriores. Com um design tamper-evident, você também verifica se a sequência permaneceu intacta.
Um fluxo simples de investigação:
prev_hash).Se alguém editou uma única linha de auditoria (por exemplo, mudando amount de 250 para 25), o hash dessa linha não corresponderá mais. Como a próxima linha inclui o hash anterior, a incompatibilidade tipicamente se propaga adiante. Essa cascata é a impressão digital: mostra que o registro de auditoria foi alterado depois do fato.
O que a cadeia pode dizer: que uma edição aconteceu, onde a cadeia primeiro quebrou e o escopo das linhas afetadas. O que ela não pode dizer sozinha: quem fez a edição, qual era o valor original se foi sobrescrito, ou se outras tabelas também foram alteradas.
Trate sua trilha de auditoria como qualquer outro controle de segurança. Implante em passos pequenos, prove que funciona e então expanda.
Comece com ações que lhe dariam maior prejuízo se contestadas: mudanças de permissão, pagamentos, reembolsos, exports de dados e sobrescritas manuais. Quando esses estiverem cobertos, adicione eventos de menor risco sem alterar o design principal.
Documente o contrato dos eventos de auditoria: quais campos são registrados, o que cada tipo de evento significa, como o hash é calculado e como rodar a verificação. Mantenha essa documentação junto às suas migrations do banco e torne o procedimento de verificação repetível.
Drills de restauração importam porque investigações frequentemente começam a partir de backups, não do sistema ao vivo. Restaure regularmente para um banco de testes e verifique a cadeia de ponta a ponta. Se você não conseguir reproduzir o mesmo resultado de verificação após uma restauração, sua tamper-evidence será difícil de defender.
Se você está construindo ferramentas internas e fluxos de trabalho administrativos com AppMaster (appmaster.io), padronizar gravações de eventos de auditoria através de processos server-side consistentes ajuda a manter o esquema de eventos e correlation ids uniformes entre features, o que torna verificação e investigações muito mais simples.
Agende tempo de manutenção para esse sistema. Trilhas de auditoria normalmente falham silenciosamente quando times lançam novas features mas esquecem de adicionar eventos, atualizar entradas do hash ou manter jobs de verificação e drills de restauração rodando.
21 de jan. de 2026
8 min
20 de jan. de 20268 min
20 de jan. de 20268 minExperimente o AppMaster com plano gratuito.
Quando estiver pronto, você poderá escolher a assinatura adequada.
