3. Auditoría del dato

Temas del programa: 3.1.6 y 3.2

Tres niveles de auditoría

Nivel	Registra	Ejemplo	Mecanismo
Sentencia	El texto SQL	`UPDATE clientes SET rfc = ...`	`log_statement`
Dato	Valor antes y después, por registro	`rfc: GAMA780912XYZ → GAMA780912ZZZ`	Disparador + tabla
Objeto	Qué tabla o columna se leyó o escribió	Lectura de `clientes.rfc`	`pgaudit` (opcional)

Un UPDATE sin WHERE genera una línea en la bitácora y una fila por registro afectado en la tabla de auditoría.

Diseñar el registro de auditoría

Campo	Responde
`momento TIMESTAMPTZ`	¿Cuándo? Con zona horaria
`usuario_sesion TEXT`	¿Quién provocó el cambio?
`aplicacion TEXT`	¿Con qué aplicación? (la declara el cliente)
`ip_cliente INET`	¿Desde dónde?
`operacion TEXT`	`INSERT`, `UPDATE` o `DELETE`
`valores_antes JSONB`	¿Qué se perdió?
`valores_despues JSONB`	¿Qué se escribió?

La tabla de auditoría también contiene datos personales. Necesita control de acceso y política de conservación propios.

Ejemplo guiado: esquema y tabla de auditoría

CREATE SCHEMA auditoria;
REVOKE ALL ON SCHEMA auditoria FROM PUBLIC;

CREATE TABLE auditoria.cambios_clientes (
    id_evento        BIGSERIAL PRIMARY KEY,
    momento          TIMESTAMPTZ NOT NULL DEFAULT now(),
    usuario_sesion   TEXT        NOT NULL DEFAULT session_user,
    usuario_efectivo TEXT        NOT NULL DEFAULT current_user,
    aplicacion       TEXT        DEFAULT current_setting('application_name'),
    ip_cliente       INET        DEFAULT inet_client_addr(),
    operacion        TEXT        NOT NULL,
    id_cliente       BIGINT,
    valores_antes    JSONB,
    valores_despues  JSONB
);

Ejemplo guiado: la función de disparador

CREATE OR REPLACE FUNCTION auditoria.registrar_cambio_cliente()
RETURNS trigger LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO auditoria.cambios_clientes (operacion, id_cliente, valores_despues)
        VALUES (TG_OP, NEW.id, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO auditoria.cambios_clientes (operacion, id_cliente, valores_antes, valores_despues)
        VALUES (TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO auditoria.cambios_clientes (operacion, id_cliente, valores_antes)
        VALUES (TG_OP, OLD.id, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$;

Fuente: documentación oficial de PostgreSQL, “Trigger Functions”, ejemplo 43.4 (función de disparador para auditoría)

Ejemplo guiado: asociar el disparador

REVOKE ALL ON FUNCTION auditoria.registrar_cambio_cliente() FROM PUBLIC;

CREATE TRIGGER trg_auditoria_clientes
AFTER INSERT OR UPDATE OR DELETE ON clientes
FOR EACH ROW EXECUTE FUNCTION auditoria.registrar_cambio_cliente();

AFTER

Registra lo que efectivamente ocurrió

FOR EACH ROW

Una fila de auditoría por registro afectado

TG_OP, OLD, NEW

Operación, registro anterior, registro nuevo

`SECURITY DEFINER` y `search_path`

¿Por qué SECURITY DEFINER?

app_ventas debe provocar la escritura en la auditoría sin tener privilegio de escribir en ella. La función se ejecuta con los privilegios de su propietario.

¿Por qué fijar search_path?

Sin él, un usuario podría crear objetos con el mismo nombre en otro esquema y lograr que la función los use con privilegios de propietario.

Ejemplo guiado: el incidente auditado

psql "postgresql://app_ventas:Ventas_Practica_2026@127.0.0.1/comercial?application_name=app-ventas-web" -c "UPDATE clientes SET rfc = 'GAMA780912ZZZ' WHERE id = 3"

id_evento        | 1
momento          | 2026-10-25 22:50:52.903439+00
usuario_sesion   | app_ventas
usuario_efectivo | postgres
aplicacion       | app-ventas-web
ip_cliente       | 127.0.0.1
operacion        | UPDATE

Dentro de una función SECURITY DEFINER, current_user es el propietario de la función. Si solo se registrara current_user, todos los cambios parecerían hechos por postgres.

Ejemplo guiado: ¿qué campo cambió?

SELECT a.id_evento, a.usuario_sesion, a.ip_cliente, d.key AS campo,
       a.valores_antes ->> d.key AS antes, d.value #>> '{}' AS despues
FROM auditoria.cambios_clientes a
CROSS JOIN LATERAL jsonb_each(a.valores_despues) d
WHERE a.operacion = 'UPDATE'
  AND a.valores_antes -> d.key IS DISTINCT FROM d.value
ORDER BY a.id_evento;

 id_evento | usuario_sesion | ip_cliente | campo |     antes     |    despues
-----------+----------------+------------+-------+---------------+---------------
         1 | app_ventas     | 127.0.0.1  | rfc   | GAMA780912XYZ | GAMA780912ZZZ

Ahora facturación puede restaurar el valor anterior.

Ejemplo guiado: altas y bajas

 id_evento | usuario_sesion |   aplicacion   | operacion | id_cliente |   rfc_antes   |  rfc_despues
-----------+----------------+----------------+-----------+------------+---------------+---------------
         1 | app_ventas     | app-ventas-web | UPDATE    |          3 | GAMA780912XYZ | GAMA780912ZZZ
         2 | postgres       | psql           | INSERT    |          6 |               | RUPE950101AAA
         3 | postgres       | psql           | DELETE    |          6 | RUPE950101AAA |

El cliente 6 ya no existe en clientes, pero sus datos siguen en la auditoría. Conflicto con la política de retención de la Unidad 2: ¿quién decide cuánto tiempo se conserva?

Ejemplo guiado: intentar borrar la evidencia

Desde la aplicación:

DELETE FROM auditoria.cambios_clientes                       → ERROR: permission denied for schema auditoria
ALTER TABLE clientes DISABLE TRIGGER trg_auditoria_clientes  → ERROR: must be owner of table clientes

Desde el administrador (desactiva, cambia y reactiva): la tabla de auditoría no registra el cambio, pero la bitácora sí, incluido el intento fallido de la aplicación:

user=app_ventas ... LOG:  statement: ALTER TABLE clientes DISABLE TRIGGER trg_auditoria_clientes
user=postgres ... LOG:  statement: ALTER TABLE clientes DISABLE TRIGGER trg_auditoria_clientes
user=postgres ... LOG:  statement: UPDATE clientes SET rfc = 'GAMA780912GHI' WHERE id = 3
user=postgres ... LOG:  statement: ALTER TABLE clientes ENABLE TRIGGER trg_auditoria_clientes

Un mecanismo audita al otro

Tabla de auditoría      →  detalle del dato (antes y después)
                           la desactiva el propietario de la tabla

Bitácora del servidor   →  registra que la auditoría se desactivó
                           la altera quien controla el servidor

Ningún mecanismo dentro del servidor protege contra un administrador con control total. Por eso las organizaciones envían las bitácoras a otro sistema y separan a quien opera la base de quien custodia la evidencia (separación de funciones, Unidad 2).

El límite del disparador: las lecturas

No existe disparador para SELECT. Con log_statement = 'mod', la bitácora tampoco registra consultas.

Alternativa	Ventaja	Desventaja
`log_statement = 'all'`	No requiere instalar nada	Registra todas las consultas de todas las tablas
Entregar el dato mediante una función que registre su uso	Control preciso	Requiere cambiar la aplicación
`pgaudit`, auditoría de objeto	Lecturas por tabla y por columna	Requiere instalar una extensión

`pgaudit` (práctica avanzada opcional)

pgaudit no forma parte de la distribución básica de PostgreSQL. Se instala su paquete en el servidor y la biblioteca se carga al arrancar.

SELECT name, default_version FROM pg_available_extensions WHERE name = 'pgaudit';
CREATE EXTENSION pgaudit;
-- ERROR:  pgaudit must be loaded via shared_preload_libraries

ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
SELECT pg_reload_conf();
SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'shared_preload_libraries';
--  shared_preload_libraries |         | t        ← espera un reinicio

Después de reiniciar el servicio: CREATE EXTENSION pgaudit; → versión 16.1.

Fuente: repositorio oficial del proyecto pgaudit

Recargar o reiniciar: cómo saberlo

Parámetro	Cambia con	Por qué
`log_statement`, `log_connections`, `log_line_prefix`	Recarga (`pg_reload_conf()`)	Cada proceso puede leer el nuevo valor sin detenerse
`logging_collector`	Reinicio	El proceso colector se crea al arrancar el servidor
`shared_preload_libraries`	Reinicio	Las bibliotecas se cargan en memoria al arrancar

La columna pending_restart de pg_settings responde la pregunta sin memorizar la tabla. El comando de reinicio depende del despliegue (servicio del sistema, Homebrew, servicios de Windows, contenedor). El diagnóstico es el mismo en todos.

Fuente: documentación oficial de PostgreSQL 16, “Setting Parameters” y “ALTER SYSTEM”

Ejemplo guiado: auditoría de objeto con `pgaudit`

El rol auditor no inicia sesión. Sus privilegios son un filtro de qué columnas auditar:

CREATE EXTENSION pgaudit;
CREATE ROLE auditor_datos NOLOGIN;
GRANT SELECT (rfc, telefono), UPDATE (rfc, telefono) ON clientes TO auditor_datos;
ALTER SYSTEM SET pgaudit.log = 'role, ddl';
ALTER SYSTEM SET pgaudit.role = 'auditor_datos';
ALTER SYSTEM SET log_line_prefix = '%m [%p] user=%u db=%d app=%a client=%h ';
SELECT pg_reload_conf();

user=app_ventas ... AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.clientes,"SELECT nombre, rfc FROM clientes WHERE id = 3",<not logged>
user=app_ventas ... AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.clientes,SELECT * FROM clientes WHERE id = 4,<not logged>
user=app_ventas ... AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.clientes,UPDATE clientes SET rfc = 'LOAA850315XXX' WHERE id = 1,<not logged>
user=postgres   ... AUDIT: SESSION,1,1,ROLE,GRANT,TABLE,,GRANT SELECT ON clientes TO soporte_ana,<not logged>

SELECT nombre, ciudad ... y UPDATE ... SET ciudad ... no aparecen.

Advertencias del propio `pgaudit`

Volumen

Puede generar un volumen muy grande de registros, agotar disco y afectar la latencia. Se recomienda limitar el alcance con auditoría de objeto.

Superusuarios

El README oficial indica que los superusuarios no pueden auditarse de forma confiable.

Usuario

La línea AUDIT no incluye el usuario: se obtiene de log_line_prefix.

Carga

Sin shared_preload_libraries, la extensión no registra nada.

Fuente: README oficial de pgaudit

Relación con el Laboratorio 01

La bitácora del servidor, la tabla de auditoría con disparador y pgaudit se practican en el Laboratorio 1 — Bitácoras y auditoría del dato en PostgreSQL, sobre el servidor que tú despliegues.

Qué sigue

Continúa con 4. Trazabilidad en MongoDB Community.

3. Auditoría del dato

Tres niveles de auditoría

Diseñar el registro de auditoría

Ejemplo guiado: esquema y tabla de auditoría

Ejemplo guiado: la función de disparador

Ejemplo guiado: asociar el disparador

SECURITY DEFINER y search_path

Ejemplo guiado: el incidente auditado

Ejemplo guiado: ¿qué campo cambió?

Ejemplo guiado: altas y bajas

Ejemplo guiado: intentar borrar la evidencia

Un mecanismo audita al otro

El límite del disparador: las lecturas

pgaudit (práctica avanzada opcional)

Recargar o reiniciar: cómo saberlo

Ejemplo guiado: auditoría de objeto con pgaudit

Advertencias del propio pgaudit

Relación con el Laboratorio 01

Qué sigue

`SECURITY DEFINER` y `search_path`

`pgaudit` (práctica avanzada opcional)

Ejemplo guiado: auditoría de objeto con `pgaudit`

Advertencias del propio `pgaudit`