Auditoría de acceso y registro de acciones de usuarios

Imagina que tu base de datos es como un club con reglas de membresía súper estrictas. Solo pueden entrar los de confianza, pero queremos saber quién vino, cuándo, cuánto tiempo estuvo y qué hizo. Justo para eso sirve la auditoría en PostgreSQL. Con ella puedes:

1. Rastrear las acciones de los usuarios. Por ejemplo, quién y a qué hora ejecutó una consulta importante.

2. Detectar actividad sospechosa. Puede ser alguien que intenta leer datos a los que no debería tener acceso.

3. Cumplir con requisitos legales y estándares. En muchas industrias (como la financiera o la médica) es obligatorio llevar un registro detallado de las acciones de los usuarios.

4. Entender el sistema desde dentro. El registro de logs ayuda a ver qué consultas se ejecutan más, dónde hay cuellos de botella y cómo optimizar el rendimiento.

Configuración de los parámetros de logging

PostgreSQL te deja configurar la auditoría de acciones usando parámetros de configuración como log_statement y log_connections. Vamos a verlos más de cerca.

1. log_statement: ¿qué registramos?

El parámetro log_statement define qué consultas SQL se van a guardar en los logs. Esto es muy útil para entender qué está pasando en el sistema.

Valores posibles de log_statement:

• none — no registrar nada (buena opción si te gusta vivir al límite).
• ddl — registrar solo los comandos que cambian la estructura de la base de datos (por ejemplo, CREATE TABLE).
• mod — registrar los comandos que modifican datos (por ejemplo, INSERT, UPDATE, DELETE).
• all — registrar absolutamente todo.

Ejemplo de configuración: para cambiar este parámetro tienes que editar el archivo postgresql.conf:

# Configuramos el registro de todas las consultas SQL
log_statement = 'all'

Después de esto, guarda los cambios y reinicia el servidor de PostgreSQL:

pg_ctl reload

Ahora tu club va a registrar en el diario cada acción, desde pedir una bebida hasta los pasos de baile.

2. log_connections: ¿quién entra al club?

El parámetro log_connections se encarga de registrar en los logs la información sobre cada nueva conexión a la base de datos. Además, hay un parámetro relacionado, log_disconnections, que marca el momento en que se cierra la conexión.

Ejemplo de configuración: De nuevo, editamos el archivo postgresql.conf:

# Registramos conexiones
log_connections = on

# Registramos desconexiones
log_disconnections = on

¿Para qué sirve esto? Por ejemplo, podrás ver en los logs que tu manager estuvo dos horas intentando conectarse a la base con la contraseña equivocada. Sí, a las 3 de la mañana.

Análisis de logs: ¿qué se puede encontrar?

Después de configurar los parámetros log_statement y log_connections, PostgreSQL empezará a escribir logs. Así puede verse un archivo de log cuando log_statement = 'mod' está activado:

2023-11-01 12:45:01 UTC [12345] LOG:  conexión autorizada: usuario=admin base_de_datos=universidad
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO estudiantes (nombre, edad) VALUES ('Alicia', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE estudiantes SET edad = 23 WHERE nombre = 'Alicia';
2023-11-01 12:48:45 UTC [12345] LOG:  desconexión: tiempo de sesión: 2:45 conexión: 1/5

Cosas interesantes:

1. Quién se conectó: usuario=admin base_de_datos=universidad — ese es nuestro jefe.
2. Qué hizo: insertó una fila con el nombre Alicia y actualizó su edad.
3. Cuándo se fue: 12:48:45, después de dos minutos y medio de actividad.

Ejemplos prácticos: cómo usar la auditoría en la vida real

Vamos a ver algunos escenarios donde la auditoría y el logging pueden ser útiles.

Escenario 1: rastrear cambios en los datos

Sospechas que alguien está cambiando registros en la tabla estudiantes. Así puedes configurar la auditoría:

1. Pon log_statement = 'mod' para registrar todas las consultas que modifican datos.
2. Analiza los logs:

cat /var/log/postgresql/postgresql.log | grep "UPDATE estudiantes"

Ahora podrás ver quién, cuándo y qué cambios hizo.

Escenario 2: detectar conexiones sospechosas

Si en los logs aparecen conexiones frecuentes desde diferentes direcciones IP, puede que algo raro esté pasando. Para analizarlo usa:

1. Los logs de conexiones (log_connections).
2. Filtrado por direcciones IP:

cat /var/log/postgresql/postgresql.log | grep "conexión autorizada"

Escenario 3: analizar el rendimiento de las consultas

¿Quieres saber por qué tu servidor va lento? Una forma fácil es activar log_statement = 'all' por un rato (por ejemplo, una hora), recoger los logs y ver dónde el servidor pasa más tiempo.

Buenas prácticas

No registres absolutamente todo. Configura log_statement para guardar solo las acciones importantes — DDL y cambios de datos.

Automatiza el análisis de logs. Usa scripts o herramientas para leer y analizar los logs regularmente, como grep, awk o incluso ELK Stack (Elasticsearch, Logstash, Kibana).

Gestiona el tamaño de los logs. Configura la rotación de logs usando los parámetros de PostgreSQL o herramientas del sistema operativo (por ejemplo, logrotate en Linux).

¡Espero que ahora te sientas como el sheriff de tu base de datos! El logging y la auditoría no solo son protección, sino también una forma genial de entender mejor qué pasa en tu sistema. ¡Veremos cómo aplicar estos conocimientos en proyectos reales!