Registros de auditoría a prueba de manipulación en PostgreSQL con encadenamiento de hashes

Por qué los registros de auditoría normales son fáciles de disputar

Un rastro de auditoría es el registro al que recurres cuando algo parece mal: un reembolso extraño, un cambio de permisos que nadie recuerda o un registro de cliente que “desapareció”. Si el rastro de auditoría puede editarse, deja de ser evidencia y se convierte en otro dato que alguien puede reescribir.

Muchos “logs de auditoría” son solo tablas normales. Si las filas pueden actualizarse o borrarse, la historia también puede actualizarse o borrarse.

Una distinción clave: bloquear las ediciones no es lo mismo que hacer que las ediciones sean detectables. Puedes reducir cambios con permisos, pero cualquiera con suficiente acceso (o una credencial de admin robada) aún puede alterar el historial. La evidencia de manipulación acepta esa realidad. Puede que no prevengas todo cambio, pero puedes hacer que los cambios dejen una huella obvia.

Los registros normales se disputan por razones previsibles. Usuarios privilegiados pueden “arreglar” el log después del hecho. Una cuenta de aplicación comprometida puede escribir entradas creíbles que parezcan tráfico normal. Las marcas de tiempo pueden rellenarse para ocultar un cambio tardío. O alguien borra solo las líneas más dañinas.

“Tamperevident” significa diseñar el rastro de auditoría para que incluso una edición pequeña (cambiar un campo, eliminar una fila, reordenar eventos) sea detectable después. No prometes magia. Prometes que cuando alguien pregunte “¿Cómo sabemos que este registro es real?”, puedas ejecutar comprobaciones que muestren si el log fue tocado.

Decide qué necesitas probar

Un rastro de auditoría a prueba de manipulación solo es útil si responde a las preguntas que enfrentarás más tarde: quién hizo qué, cuándo lo hizo y qué cambió.

Empieza por los eventos que importan a tu negocio. Los cambios de datos (crear, actualizar, borrar) son la base, pero las investigaciones a menudo giran también en torno a seguridad y acceso: inicios de sesión, restablecimientos de contraseña, cambios de permisos y bloqueos de cuentas. Si manejas pagos, reembolsos, créditos o pagos, trata el movimiento de dinero como eventos de primera clase, no como un efecto secundario de una fila actualizada.

Luego decide qué hace creíble a un evento. Los auditores suelen esperar un actor (usuario o servicio), una marca de tiempo del servidor, la acción realizada y el objeto afectado. Para actualizaciones, almacena valores antes y después (o al menos los campos sensibles), además de un id de petición o id de correlación para poder vincular muchos cambios pequeños en la base de datos a una sola acción de usuario.

Por último, sé explícito sobre lo que “inmutable” significa en tu sistema. La regla más simple es: nunca actualizar ni borrar filas de auditoría, solo insertar. Si algo está mal, escribe un nuevo evento que corrija o sustituya al anterior y mantén el original visible.

Construye una tabla de auditoría append-only

Mantén los datos de auditoría separados de tus tablas normales. Un esquema dedicado audit reduce ediciones accidentales y facilita razonar sobre los permisos.

El objetivo es simple: las filas pueden añadirse, pero nunca cambiarse ni eliminarse. En PostgreSQL, se lo haces cumplir con privilegios (quién puede hacer qué) y un par de protecciones en el diseño de la tabla.

Aquí tienes una tabla práctica para empezar:

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

Algunos campos son especialmente útiles durante las investigaciones:

• occurred_at con DEFAULT now() para que la hora la ponga la base de datos, no el cliente.
• entity_type y entity_id para poder seguir un registro a través de cambios.
• request_id para que una acción de usuario se pueda trazar a través de varias filas.

Restringe con roles. Tu rol de aplicación debería poder INSERT y SELECT en audit.events, pero no UPDATE ni DELETE. Mantén cambios de esquema y permisos más fuertes para un rol de admin que no use la app.

Captura cambios con triggers (limpios y predecibles)

Si quieres un rastro de auditoría a prueba de manipulación, el lugar más fiable para capturar cambios es la base de datos. Los logs de la aplicación pueden omitirse, filtrarse o reescribirse. Un trigger se dispara sin importar qué app, script o herramienta admin toque la tabla.

Mantén los triggers aburridos. Su trabajo debe ser una sola cosa: anexar un evento de auditoría por cada INSERT, UPDATE y DELETE en las tablas que importan.

Un registro de auditoría práctico suele incluir el nombre de la tabla, el tipo de operación, la clave primaria, los valores antes y después, una marca de tiempo y identificadores que te permitan agrupar cambios relacionados (id de transacción e id de correlación).

Los ids de correlación son la diferencia entre “20 filas actualizadas” y “esto fue un clic de botón”. Tu app puede establecer un id de correlación por petición (por ejemplo, en una configuración de sesión de BD), y el trigger puede leerlo. Almacena también txid_current() para poder agrupar cambios cuando falte el id de correlación.

Aquí tienes un patrón simple de trigger que sigue siendo predecible porque solo inserta en la tabla de auditoría (ajusta nombres para que coincidan con tu esquema):

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;

Resiste la tentación de hacer más dentro de los triggers. Evita consultas extra, llamadas a la red o ramificaciones complejas. Los triggers pequeños son más fáciles de probar, más rápidos de ejecutar y más difíciles de discutir en una revisión.

Agrega encadenamiento de hashes para que las ediciones dejen huellas

Una tabla append-only ayuda, pero alguien con suficiente acceso aún puede reescribir filas pasadas. El encadenamiento de hashes hace visible ese tipo de manipulación.

Añade dos columnas a cada fila de auditoría: prev_hash y row_hash (a veces llamado chain_hash). prev_hash almacena el hash de la fila anterior en la misma cadena. row_hash guarda el hash de la fila actual, calculado a partir de los datos de la fila más prev_hash.

Lo que hashéeas importa. Quieres una entrada estable y reproducible para que la misma fila siempre produzca el mismo hash.

Un enfoque práctico es hashear una cadena canónica construida a partir de columnas fijas (timestamp, actor, acción, id de entidad), una carga útil canónica (a menudo jsonb, porque las claves se almacenan de manera consistente) y el prev_hash.

Ten cuidado con detalles que pueden cambiar sin significado, como espacios en blanco, orden de claves en JSON en texto plano o formatos localizados. Mantén los tipos consistentes y serializa de una manera predecible.

Encadena por flujo, no por toda la base de datos

Si encadenas cada evento de auditoría en una única secuencia global, las escrituras pueden convertirse en un cuello de botella. Muchos sistemas encadenan dentro de un “flujo”, como por inquilino, por tipo de entidad o por objeto de negocio.

Cada nueva fila busca el último row_hash para su flujo, lo almacena como prev_hash y luego calcula su propio 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'
);

Haz snapshot del head de la cadena

Para revisiones más rápidas, almacena el row_hash más reciente (el “head” de la cadena) periódicamente, por ejemplo diariamente por flujo, en una pequeña tabla de snapshots. Durante una investigación, puedes verificar la cadena hasta cada snapshot en lugar de escanear todo el historial de una vez. Los snapshots también facilitan comparar exportaciones y detectar huecos sospechosos.

Concurrencia y orden sin romper la cadena

El encadenamiento de hashes se complica con tráfico real. Si dos transacciones escriben filas de auditoría al mismo tiempo y ambas usan el mismo prev_hash, puedes acabar con forks. Eso debilita tu capacidad para demostrar una única secuencia limpia.

Primero decide qué representa tu cadena. Una cadena global es más fácil de explicar pero tiene la mayor contención. Múltiples cadenas reducen la contención, pero debes ser claro sobre lo que prueba cada cadena.

Cualquiera sea el modelo, define un orden estricto con un id de evento monótono (normalmente un id respaldado por una secuencia). Las marcas de tiempo no son suficientes porque pueden colisionar y manipularse.

Para evitar condiciones de carrera al calcular prev_hash, serializa “obtener último hash + insertar la siguiente fila” para cada flujo. Enfoques comunes son bloquear una sola fila que representa el head del flujo, o usar un advisory lock con clave del id del flujo. La meta es que dos escritores al mismo flujo no puedan leer el mismo último hash.

El particionado y el sharding afectan dónde “vive la última fila”. Si esperas particionar los datos de auditoría, mantén cada cadena completamente contenida dentro de una partición usando la misma clave de partición que la clave del flujo (por ejemplo, tenant id). Así, las cadenas por tenant siguen siendo verificables incluso si luego se mueven entre servidores.

Cómo verificar la cadena durante una investigación

El encadenamiento de hashes solo ayuda si puedes demostrar que la cadena sigue intacta cuando alguien lo pregunta. El enfoque más seguro es una consulta de verificación en solo lectura (o un job) que recalcule el hash de cada fila a partir de los datos almacenados y lo compare con lo registrado.

Un verificador simple que puedes ejecutar bajo demanda

Un verificador debe: reconstruir el hash esperado para cada fila, confirmar que cada fila enlace con la anterior y marcar cualquier cosa que parezca incorrecta.

Aquí hay un patrón común usando funciones ventana. Ajusta los nombres de columna para que coincidan con tu tabla.

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;

Más allá de “roto o no”, vale la pena buscar huecos (ids faltantes en un rango), enlaces fuera de orden y duplicados sospechosos que no coincidan con flujos de trabajo reales.

Registra los resultados de verificación como eventos inmutables

No ejecutes una consulta y entierres la salida en un ticket. Almacena los resultados de verificación en una tabla append-only separada (por ejemplo, audit_verification_runs) con hora de ejecución, versión del verificador, quién lo disparó, el rango comprobado y contadores de enlaces rotos y discrepancias de hash.

Eso te da un segundo rastro: no solo el log de auditoría está intacto, sino que puedes mostrar que lo estás comprobando.

Una cadencia práctica es: ejecutar después de cualquier deploy que toque la lógica de auditoría, nightly para sistemas activos y siempre antes de una auditoría planificada.

Errores comunes que rompen la evidencia de manipulación

La mayoría de fallos no tienen que ver con el algoritmo de hash. Tienen que ver con excepciones y huecos que le dan a la gente espacio para discutir.

La forma más rápida de perder confianza es permitir actualizaciones a filas de auditoría. Incluso si es “solo esta vez”, has creado un precedente y una vía funcional para reescribir la historia. Si necesitas corregir algo, añade un nuevo evento de auditoría que explique la corrección y conserva el original.

El encadenamiento de hashes también falla cuando hasheas datos inestables. JSON es una trampa común. Si hasheas una cadena JSON, diferencias inocuas (orden de claves, espacios, formato numérico) pueden cambiar el hash y hacer ruidosa la verificación. Prefiere una forma canónica: campos normalizados, jsonb u otra serialización consistente.

Otros patrones que socavan un rastro defendible:

• Hashear solo el payload y saltarse el contexto (timestamp, actor, id del objeto, acción).
• Capturar cambios solo en la aplicación y asumir que la base de datos coincide para siempre.
• Usar un rol de base de datos que pueda escribir datos de negocio y también alterar el historial de auditoría.
• Permitir NULLs en prev_hash dentro de una cadena sin una regla clara y documentada.

La separación de funciones importa. Si el mismo rol puede insertar eventos de auditoría y también modificarlos, la evidencia de manipulación se convierte en una promesa en lugar de un control.

Lista de comprobación rápida para un rastro de auditoría defendible

Un rastro de auditoría defendible debe ser difícil de cambiar y fácil de verificar.

Empieza por control de acceso: la tabla de auditoría debe ser append-only en la práctica. El rol de la aplicación debería insertar (y generalmente leer), pero no actualizar ni borrar. Los cambios de esquema deben restringirse fuertemente.

Asegúrate de que cada fila responda a las preguntas que hará un investigador: quién lo hizo, cuándo ocurrió (lado servidor), qué pasó (nombre de evento claro más operación), qué tocó (nombre de entidad e id) y cómo se conecta (request/correlation id e id de transacción).

Luego valida la capa de integridad. Una prueba rápida es reproducir un segmento y confirmar que cada prev_hash coincide con el hash de la fila anterior, y que cada hash almacenado coincide con el recálculo.

Operativamente, trata la verificación como un job normal:

• Ejecuta comprobaciones programadas de integridad y almacena resultados de paso/fallo y rangos.
• Alerta sobre discrepancias, huecos y enlaces rotos.
• Conserva backups el tiempo suficiente para cubrir tu ventana de retención y restringe la retención para que el historial de auditoría no pueda “limpiarse” temprano.

Ejemplo: detectar una edición sospechosa en una revisión de cumplimiento

Un caso de prueba común es una disputa por un reembolso. Un cliente afirma que se le aprobaron $250 de reembolso, pero el sistema ahora muestra $25. Soporte insiste en que la aprobación fue correcta y cumplimiento quiere una respuesta.

Empieza por acotar la búsqueda usando un id de correlación (id de orden, id de ticket o refund_request_id) y una ventana temporal. Extrae las filas de auditoría para ese id de correlación y enmárcalas alrededor de la hora de la aprobación.

Buscas el conjunto completo de eventos: petición creada, reembolso aprobado, monto del reembolso establecido y cualquier actualización posterior. Con un diseño a prueba de manipulación, también verificas si la secuencia se mantuvo intacta.

Un flujo simple de investigación:

• Extrae todas las filas de auditoría para el id de correlación en orden cronológico.
• Recalcula el hash de cada fila a partir de sus campos almacenados (incluyendo prev_hash).
• Compara los hashes calculados con los almacenados.
• Identifica la primera fila que difiere y verifica si las filas posteriores también fallan.

Si alguien editó una sola fila de auditoría (por ejemplo, cambiando el monto de 250 a 25), el hash de esa fila ya no coincidirá. Porque la fila siguiente incluye el hash anterior, la discrepancia suele propagarse hacia adelante. Esa cascada es la huella: muestra que el registro de auditoría fue alterado tras el hecho.

Lo que la cadena puede decirte: ocurrió una edición, dónde la cadena se rompió por primera vez y el alcance de las filas afectadas. Lo que no puede decir por sí sola: quién hizo la edición, cuál era el valor original si fue sobrescrito, o si otras tablas también fueron cambiadas.

Próximos pasos: desplegar con cuidado y mantenerlo sostenible

Trata tu rastro de auditoría como cualquier otro control de seguridad. Implántalo en pasos pequeños, demuestra que funciona y luego expándelo.

Comienza con las acciones que más te perjudicarían si se disputaran: cambios de permisos, pagos, reembolsos, exportaciones de datos y anulaciones manuales. Una vez cubiertas, añade eventos de menor riesgo sin cambiar el diseño central.

Escribe el contrato para tus eventos de auditoría: qué campos se registran, qué significa cada tipo de evento, cómo se calcula el hash y cómo ejecutar la verificación. Mantén esa documentación junto a tus migraciones de base de datos y conserva el procedimiento de verificación repetible.

Los ejercicios de restauración importan porque las investigaciones a menudo arrancan desde backups, no desde el sistema vivo. Restaura regularmente a una BD de prueba y verifica la cadena de extremo a extremo. Si no puedes reproducir el mismo resultado de verificación tras una restauración, tu evidencia de manipulación será difícil de defender.

Si estás construyendo herramientas internas y flujos admin con AppMaster (appmaster.io), estandarizar las escrituras de eventos de auditoría mediante procesos consistentes del lado servidor ayuda a mantener uniforme el esquema de eventos y los ids de correlación entre funciones, lo que simplifica mucho la verificación y las investigaciones.

Programa tiempo de mantenimiento para este sistema. Los rastros de auditoría suelen fallar silenciosamente cuando los equipos publican nuevas funciones pero olvidan añadir eventos, actualizar las entradas del hash o mantener los jobs de verificación y los ejercicios de restauración en funcionamiento.