Hola a todos, hoy os mostraremos cómo crear la auditoria en Postgres.
La auditoría de base de datos es el proceso de monitorear y registrar de forma persistente las acciones realizadas por los usuarios dentro de un motor de base de datos. No se trata solo de registrar errores, sino de crear una «caja negra» o historial forense que permita responder a cuatro preguntas críticas ante cualquier incidente:
- ¿Quién? La identidad del usuario o aplicación.
- ¿Qué? La sentencia exacta: un SELECT, un DELETE o un DROP.
- ¿Cuándo? El timestamp preciso con milisegundos.
- ¿Desde dónde? La IP o proceso que originó la conexión.
Entonces hoy mostraremos como crear la auditoria desde el principio y de forma muy fácil para que los puedan aplicar en sus servidores de PostgreSQL.
Instalación del Paquete (S.O.)
El primer paso es instalar el binario compatible con nuestra versión de base de datos. En este caso, utilizamos el repositorio oficial de PostgreSQL para obtener la versión 1.4 de la extensión.
Ejecutar como root:
Configuración en el Motor de Base de Datos
Para que PostgreSQL cargue la librería de auditoría, debemos modificar los parámetros de arranque. Esto lo haremos modificando el fichero postgresql.conf. Debes añadir pgaudit a las librerías precargadas:
Nota: si tiene otras extensiones no las borres solo agrega la nueva extensión de último.
Reiniciar el servicio. Para que los cambios en las librerías tengan efecto, es necesario un reinicio del servicio:
Activación de la Extensión y Parámetros
Una vez reiniciado el motor, entramos en la base de datos específica (en este ejemplo terra_pre) y activamos la funcionalidad que nos permitirá usar la auditoria en la base de datos postgres.
Optimización de los Logs (Legibilidad)
Para que el log de auditoría sea útil, necesitamos que incluya el nombre del usuario y la base de datos en cada línea. Ajustamos el prefijo del log:
Prueba de Concepto y Validación
Para verificar que todo quede correctamente y que la configuración de los parámetros aplicados anteriormente funcione:
- Ingresaremos a la base de datos con un usuario que nos va a permitir hacer Lectura/Escritura.
- Aplicaremos un DROP a la tabla que crearemos de forma que podamos verlo en el log de auditoría.
Busca la evidencia en el Log:
Ahora vamos a filtrar el log buscando específicamente a ese usuario. Fíjate cómo ahora aparece el nombre entre corchetes:
Conclusiones y Recomendaciones
Antes de comenzar con la auditoría, debemos tener en cuenta lo siguiente:
- Espacio en Disco: Auditar la clase READ (SELECTs) genera un volumen de logs masivo, especialmente si hay herramientas como repmgr monitoreando. Se recomienda usar solo WRITE, DDL en producción si el espacio es limitado.
- Rotación: Asegúrate de tener configurado log_rotation_size y log_truncate_on_rotation para evitar que el disco se llene.
- Rollback: de la Auditoría (Quitarla por completo). Si lo que deseas es dar marcha atrás a toda la configuración de pgaudit que realizamos, los pasos son los siguientes:
- Paso A: Desactivar el registro (Sin reiniciar). Si solo quieres que deje de escribir en el log pero mantener la extensión instalada:
- Paso B: Eliminar la extensión de la base de datos. Si quieres quitar la funcionalidad de la base de datos terra_pre:
- Paso C: Quitar la librería del motor (Requiere Reinicio)
- Para un «Rollback» total donde el motor ya no cargue pgaudit al arrancar:
- Edita el archivo postgresql.conf.
- Busca shared_preload_libraries y elimina pgaudit de la lista.
- Reinicia el servicio:
- Paso A: Desactivar el registro (Sin reiniciar). Si solo quieres que deje de escribir en el log pero mantener la extensión instalada:
Esperamos que después de estos pasos sepas cómo crear la auditoria. En caso contrario, puedes contactarnos sin compromiso.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn