Implementación de auditoría persistente en postgreSQL

La auditoría persistente de bases de datos es el proceso continuo de rastrear, registrar y almacenar de forma inmutable quién accede a los datos, qué modificaciones realiza (INSERT, UPDATE, DELETE) y cuándo ocurren. Su objetivo es garantizar la seguridad, la integridad de la información y el cumplimiento normativo. Aunque tambien es util a la hora de realizar ingenieria inversa y entender el funcionamiento de un sistema del cual no tenemos información tecnica relevante.

En este post crearemos un sencillo sistema de auditoria en una base de datos postgresql el cual tiene como objetivo monitorear todas las tablas de una base de datos y registrar en una tabla de auditoria su movimiento, para lo cual nos ayudaremos de triggers.

Manos a la obra:

Paso 1: Tabla de auditoria:

En esta tabla registraremos todos los movimientos que se realicen sobre la base de datos abarcando en lo posible toda la información de nuestro interes, en que tabla se registro movimiento, que tipo de movimiento se realizo (INSERT, UPDATE, DELETE), registraremos la información pre y post acción, quien realizo la acción y cuando.

CREATE TABLE IF NOT EXISTS audit_log (
    id BIGSERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name  TEXT NOT NULL,
    action      TEXT NOT NULL,
    old_data    JSONB,
    new_data    JSONB,
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMPTZ DEFAULT now(),
    CONSTRAINT chk_action CHECK (action IN ('INSERT','UPDATE','DELETE'))
);

CREATE INDEX IF NOT EXISTS idx_audit_log_table_time 
ON audit_log(table_name, changed_at DESC);

Paso 2: Función trigger genérica (INSERT + UPDATE + DELETE)

Para automatizar el registro de datos nos ayudamos de triggers.

CREATE OR REPLACE FUNCTION fn_audit_multi()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(schema_name, table_name, action, new_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(schema_name, table_name, action, old_data, new_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(schema_name, table_name, action, old_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Este trigger guarda la información de la siguiente forma:

• Si se produce un INSERT: INSERT → old_data = NULL, new_data = fila insertada
• Si se produce un UPDATE: UPDATE → old_data = valores antes, new_data = valores después
• Si se produce un DELETE: DELETE → old_data = fila eliminada, new_data = NULL

Paso 3: Asignar trigger a todas las tablas

Como nuestro objetivo es monitoreal todas las tablas del sistema, asigamos el trigger a todas ellas, si solo quieres monitorear una tabla o 2, solo debes asignar el trigger a las tablas de tu interes.

DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'  -- Se ajusta si usas otro schema
          AND table_type = 'BASE TABLE'
          AND table_name NOT IN ('audit_log')
    LOOP
        EXECUTE format(
            'DROP TRIGGER IF EXISTS trg_audit_%I ON %I.%I;',
            tbl.table_name, tbl.table_schema, tbl.table_name
        );
        EXECUTE format(
            'CREATE TRIGGER trg_audit_%I 
             AFTER INSERT OR UPDATE OR DELETE ON %I.%I 
             FOR EACH ROW EXECUTE FUNCTION fn_audit_multi();',
            tbl.table_name, tbl.table_schema, tbl.table_name
        );
    END LOOP;
END $$;

Importante: En Postgres los disparadores se ejecutan de acuerdo al orden alfabético de su nombre, si tienes triggers en tu base de datos que entran en conflicto con el trigger de auditoria, solo debes ajustar el nombre.

Con estos tres pasos configuramos nuestro pequeño sistema de auditoria, ahora debemos probar si funciona.

Resumen de acciones por tabla en los últimos 5 minutos:

SELECT 
    table_name, 
    action, 
    COUNT(*) as cantidad
FROM audit_log
WHERE changed_at >= NOW() - INTERVAL '5 minutes'
GROUP BY table_name, action
ORDER BY cantidad DESC;

En nuestro sistema realizamos alguna acción, por ejemplo un login y luego ejecutamos la instrucción SQL anterior obteniendo como resultado:

Vemos que al realizar el login, este realizo una acción sobre la tabla «usuarios», la acción fue un UPDATE.

Para poder observar a detalle la acción realizada ejecutamos la instrucción:

SELECT 
    table_name,
    action,
    changed_at,
    jsonb_pretty(old_data)  AS antes,
    jsonb_pretty(new_data)  AS despues
FROM audit_log
WHERE changed_at >= NOW() - INTERVAL '5 minutes'
ORDER BY changed_at DESC;

Y obtenemos:

Este pequeño sistema nos permite monitorear la base de datos pero se debe tener en cuenta que el mismo crecera mucho en aplicaciones de alto trafico. No esta demás implementar un cron job para limpiar la tabla cada N tiempo (1 mes por ejemplo). Tambien es importante mencionar que el sistema guarda todo, incluso información sensible, como contraseñas o tokens, es mejor enmascararlos. Finalmente si el sistema usa ROLLBACK, el registro en la tabla «audit_log» tambien se deshace, si requieres de un control estricto, lo mejor es usar «pgaudit» (Extensión de auditoría de PostgreSQL que proporciona un registro detallado de auditoría de sesiones y/o objetos mediante el sistema de registro estándar de PostgreSQL).

Enjoy!!!