Inicio
Centro de Conocimiento
Historial de Actividad de Datos en PostgreSQL: Mejores Prácticas para el Monitoreo y la Seguridad

Historial de Actividad de Datos en PostgreSQL: Mejores Prácticas para el Monitoreo y la Seguridad

PostgreSQL, una base de datos relacional de código abierto ampliamente utilizada, ofrece varias herramientas y enfoques para monitorear y rastrear el historial de actividad de datos. En este artículo se explorarán los conceptos básicos del historial de actividad de datos en PostgreSQL, cubriendo métodos clave como pgaudit, estadísticas acumulativas y pg_stat_statements.

También le guiaremos en la configuración de auditoría de consultas SQL para una tabla de muestra e introduciremos DataSunrise, una robusta suite de seguridad que centraliza y simplifica el monitoreo de actividad de datos.

Por Qué es Importante Monitorear el Historial de Actividad de Datos

Monitorear el historial de actividad de datos es esencial por varias razones. Garantiza que se pueda detectar actividad sospechosa, cumplir con los requisitos de cumplimiento y optimizar el rendimiento de la base de datos. Al mantener un ojo atento sobre quién accedió a sus datos, cuándo y qué cambios se realizaron, puede proteger su base de datos contra accesos no autorizados y posibles violaciones de datos. Además, el seguimiento de la actividad de datos ayuda a identificar cuellos de botella en el rendimiento y optimizar la ejecución de consultas, lo que lleva a un entorno de base de datos más eficiente y seguro.

Enfoques para Rastrear el Historial de Actividad de Datos en PostgreSQL

PostgreSQL proporciona varias herramientas integradas y extensiones para rastrear y monitorear la actividad de datos. Tres de los métodos más comúnmente utilizados son pgaudit, estadísticas acumulativas y pg_stat_statements. Cada una de estas herramientas ofrece funcionalidades únicas, y comprender sus diferencias le ayudará a elegir el enfoque adecuado para sus necesidades.

pgaudit: Auditoría Completa de SQL

pgaudit es una extensión de PostgreSQL diseñada para proporcionar una auditoría detallada de las instrucciones SQL. Registra todas las actividades SQL, incluyendo las sentencias DML (Lenguaje de Manipulación de Datos) y DDL (Lenguaje de Definición de Datos). Esta herramienta es particularmente útil para fines de cumplimiento, ya que permite a los administradores mantener un registro detallado de las actividades de la base de datos.

Configuración de pgaudit para Auditar Consultas SQL

La siguiente configuración es para Ubuntu Server 24.04 y PostgreSQL versión 16 instalado desde los repositorios de Ubuntu como se muestra a continuación.

Para configurar pgaudit para auditar consultas SQL en la tabla mock_data, siga estos pasos:

Instale la extensión pgaudit:

# sudo apt-get update
# sudo apt-get install postgresql-16-pgaudit

Configure la extensión en postgresql.conf:

nano /etc/postgresql/16/main/postgresql.conf

editando la línea: shared_preload_libraries = ‘pgaudit’.

Y reinicie el demonio de la base de datos PostgreSQL.

sudo systemctl restart postgresql

Habilite la extensión en la base de datos usando la interfaz de línea de comandos de psql:

CREATE EXTENSION pgaudit;

Configure los ajustes de pgaudit (también en psql):

ALTER SYSTEM SET pgaudit.log = 'write';
Reload la configuración de PostgreSQL:
SELECT pg_reload_conf();

Ejecute la consulta SQL en la tabla mock_data:

SELECT * FROM mock_data WHERE id = 1;
INSERT INTO mock_data (first_name, last_name, email, ip_address) VALUES ('John', 'Doe', '[email protected]', '192.168.1.1');

pgaudit registrará estas consultas con sus respectivas marcas de tiempo, lo que le permitirá rastrear quién realizó las acciones y cuándo. Use el siguiente comando para ver el final del archivo de registro:

cat /var/log/postgresql/postgresql-16-main.log | tail -n 20

Todas las líneas etiquetadas como ‘AUDIT’ provienen de la extensión pgaudit.

Estadísticas Acumulativas: Métricas de Toda la Base de Datos

Las estadísticas acumulativas en PostgreSQL proporcionan datos agregados acerca del rendimiento y uso de la base de datos. Estas estadísticas incluyen información sobre el número de consultas ejecutadas, tuplas leídas y bloques obtenidos, entre otras métricas. Mientras que las estadísticas acumulativas ofrecen una visión general amplia de la actividad de la base de datos, carecen de la granularidad de pgaudit, ya que no rastrean las sentencias SQL individuales ni sus marcas de tiempo.

Para acceder a las estadísticas acumulativas, puede consultar la vista pg_stat_database:

SELECT datname, numbackends, xact_commit, xact_rollback
FROM pg_stat_database;

Para acceder a las consultas activas en ejecución:

SELECT pid, usename, datname, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

Esta vista también proporciona información valiosa sobre el rendimiento de la base de datos, pero no ofrece las capacidades detalladas de auditoría necesarias para rastrear actividades de datos específicas.

pg_stat_statements: Monitoreo del Rendimiento de Consultas

pg_stat_statements es otra extensión de PostgreSQL que rastrea las estadísticas de ejecución de todas las sentencias SQL. A diferencia de las estadísticas acumulativas, pg_stat_statements se centra en el rendimiento de las consultas, proporcionando detalles como el tiempo de ejecución, el número de llamadas y el promedio de tiempo por llamada. Esta herramienta es particularmente útil para identificar consultas lentas y optimizar el rendimiento de la base de datos.

Instalación y Uso de pg_stat_statements

Instale la extensión pg_stat_statements:

sudo apt-get update
sudo apt-get install postgresql-contrib

Configure la extensión:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 1000
track_activity_query_size = 2048

Reinicie PostgreSQL:

sudo systemctl restart postgresql

Inicie sesión en psql y cree la extensión:

CREATE EXTENSION pg_stat_statements;

Consulte la vista de pg_stat_statements:

SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / 
    nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query ILIKE '%mock_data%'
ORDER BY total_exec_time DESC
LIMIT 5;

Esto proporcionará una lista de las consultas más costosas en términos de tiempo, ayudándole a identificar cuellos de botella en el rendimiento.

Puede restablecer las estadísticas:

SELECT pg_stat_statements_reset();

Diferencias Entre pgaudit, Estadísticas Acumulativas y pg_stat_statements

Cada una de estas herramientas sirve para un propósito diferente al monitorear el historial de actividad de datos en PostgreSQL:

pgaudit: Se centra en la auditoría y el registro de sentencias SQL individuales para fines de seguridad y cumplimiento.

Estadísticas Acumulativas: Proporciona métricas de rendimiento agregadas para toda la base de datos, útil para una visión general de alto nivel.

pg_stat_statements: Rastrea el rendimiento de la ejecución de consultas, ayudando a identificar y optimizar consultas lentas.

Combinando estas herramientas, puede lograr un monitoreo y auditoría exhaustivos de su base de datos PostgreSQL, asegurando tanto la seguridad como el rendimiento.

Para verificar si las extensiones están presentes en la base de datos, use los siguientes comandos:

SELECT * FROM pg_extension;

SHOW shared_preload_libraries;

El resultado puede ser algo así:

Aprovechamiento de DataSunrise para el Monitoreo Centralizado de Actividad de Datos

Aunque las herramientas integradas de PostgreSQL proporcionan capacidades robustas de monitoreo, gestionar el historial de actividad de datos en varias bases de datos puede ser un desafío. Aquí es donde entra DataSunrise. DataSunrise es una poderosa suite de seguridad que ofrece control centralizado sobre todas las tareas de monitoreo y auditoría de actividad de datos.

Creación de una Instancia de DataSunrise para PostgreSQL

Suponiendo que DataSunrise ya está instalado, siga estos pasos para crear una instancia y monitorear el historial de actividad de datos:

Inicie sesión en la consola de DataSunrise.
Navegue a la sección ‘Configuración – Bases de Datos’ y cree una nueva instancia de PostgreSQL.
Configure los ajustes de conexión, incluyendo el host, el puerto y las credenciales de la base de datos.
Navegue a la sección de Auditoría – Reglas. Aquí cree y especifique las tablas, columnas y acciones que desea auditar, como SELECT, INSERT, UPDATE y DELETE.

Guarde la regla, y comenzará a monitorear automáticamente. Cuando accede a la base de datos a través del proxy de la instancia, DataSunrise intercepta las consultas y registra todos los datos especificados en los Registros de Transacciones y Registro de Sesiones. Puede ver estos registros en las secciones ‘Auditoría – Registros de Transacciones’ y ‘Auditoría – Registros de Sesiones’ de la interfaz de Auditoría.

DataSunrise ahora capturará y almacenará todo el historial de actividad de datos para la instancia de PostgreSQL especificada, proporcionando una vista centralizada y uniforme de todas las acciones realizadas en la base de datos.

Beneficios de Usar DataSunrise

DataSunrise ofrece varias ventajas para gestionar el historial de actividad de datos en PostgreSQL:

Control Centralizado: Gestione y monitoree todas sus bases de datos desde una única interfaz, reduciendo la sobrecarga administrativa.
Auditoría Integral: Rastree todas las actividades SQL en varias bases de datos, asegurando el cumplimiento de los requisitos regulatorios.
Configuración Flexible: Personalice las reglas de auditoría según sus necesidades específicas, incluyendo el filtrado por usuario, acción o tabla.
Alertas en Tiempo Real: Reciba notificaciones de actividades sospechosas, lo que le permite tomar medidas inmediatas.

Con DataSunrise, usted obtiene una herramienta poderosa para asegurar sus bases de datos PostgreSQL y asegurarse de que toda actividad de datos sea rastreada y registrada eficientemente.

Conclusión

Monitorear el historial de actividad de datos en PostgreSQL es crucial para mantener la seguridad, el cumplimiento y el rendimiento. Herramientas como pgaudit, estadísticas acumulativas y pg_stat_statements proporcionan valiosas perspectivas sobre las actividades de su base de datos. Para un monitoreo más avanzado y centralizado, DataSunrise ofrece una solución integral que simplifica el seguimiento de la actividad de datos en múltiples instancias de PostgreSQL. Combinando estas herramientas, puede lograr un entorno de base de datos seguro y bien optimizado.

DataSunrise proporciona herramientas flexibles y fáciles de usar para la seguridad de bases de datos, incluyendo auditoría, enmascaramiento y otras características avanzadas. Visite nuestro sitio web de DataSunrise para programar una demostración en línea y para explorar cómo nuestras soluciones pueden ayudarle a gestionar más efectivamente el historial de actividad de datos en PostgreSQL.