Cómo crear auditoría por tabla en PostgreSQL con pgAudit

Hola a todos! Siguiendo con el hilo de la entrada anterior, hoy vamos a ir a algo más específico y os vamos a mostrar cómo crear la auditoría por tabla en PostgreSQL.

En entornos de bases de datos de alto rendimiento, auditar cada sesión de usuario puede saturar rápidamente el almacenamiento. Esta guía explica cómo implementar una Auditoría de Objetos, permitiéndote vigilar únicamente las tablas críticas (como las de datos sensibles) de forma quirúrgica.

Limpieza de la Auditoría Global (Requisito Previo)

Para evitar la duplicidad de registros y el consumo excesivo de disco, primero debemos resetear los parámetros que capturaban toda la actividad de la base de datos.

Comando para desactivar el registro global:

ALTER SYSTEM SET pgaudit.log = 'DDL';
ALTER SYSTEM SET log_hostname = on;
ALTER SYSTEM SET log_line_prefix = '%m [%u@%d] [%h] [%a] [%p]: ';
SELECT pg_reload_conf();

Nota: Con esto, el motor dejará de registrar cada SELECT o INSERT de la base de datos y solo reportará cambios estructurales.

Configuración del Entorno de Objetos

Pgaudit utiliza el sistema de roles de PostgreSQL para determinar qué objetos auditar. Crearemos un rol que servirá como «marcador» o filtro, también vincularemos el rol con la extensión:

Crear el rol (no necesita permisos de login).
Definir este rol como el filtro de pgaudit.

CREATE ROLE auditor_tabla;
ALTER SYSTEM SET pgaudit.role = 'auditor_tabla';
SELECT pg_reload_conf();

Activación de Auditoría por Tabla en PostgreSQL

La auditoría se activa otorgando permisos al rol auditor_tabla sobre los objetos de interés. Cualquier usuario que interactúe con estas tablas generará un registro.

Ejemplo para la tabla evidencia_final:

GRANT SELECT, INSERT, UPDATE, DELETE ON schema_auditoria.evidencia_final TO auditor_tabla;
SELECT
    grantee AS rol_auditor,
    table_schema AS esquema,
     table_name AS tabla,
    privilege_type AS tipo_auditoria
FROM
    information_schema.role_table_grants
 WHERE
     grantee = 'auditor_tabla';

Nota: Si no aplicas este GRANT a una tabla, pgaudit la ignorará por completo en los logs de READ/WRITE.

Prueba de Concepto y Análisis de Evidencia

Para validar que la transición fue exitosa, realizaremos un ciclo completo con el usuario auditor_test.

A. Ejecución de comandos

INSERT INTO schema_auditoria.evidencia_final (NOMBRE ,APELLIDO ,NIE , CORREO) VALUES
('PEDRO', 'PEREZ', '5545454', 'Pedro');
drop table schema_auditoria.evidencia_final;

B. Validación en el Log de Postgres

Usa el siguiente filtro en la terminal de Linux para ver ambos tipos de eventos:

grep "auditor_test" /POSTGRES/log/postgresql-Wed.log | grep -E "CREATE TABLE|INSERT|SELECT|DELETE|UPDATE|DROP TABLE|CREATE TABLE"

Consultas de Verificación

Para saber en todo momento qué tablas estás vigilando, ejecuta:

SELECT
     grantee AS rol_auditor,
     table_schema AS esquema,
     table_name AS tabla,
     privilege_type AS tipo_auditoria
 FROM
     information_schema.role_table_grants
 WHERE
     grantee = 'auditor_tabla';

Conclusiones y Recomendaciones

Optimización de Disco: Solo se genera log de datos en tablas sensibles.

Seguridad DDL: Mantienes la visibilidad de quién borra objetos (DROP) gracias al parámetro global
Orden: Diferencias claramente en los logs qué fue una acción de sesión y qué fue una interacción con un objeto protegido.

Si quieres que de esta tarea se encarguen profesiones. Contáctanos sin problema y lo valoramos.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle y/o SQL Server. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn

Cómo Crear la Auditoría por Tabla en PostgreSQL