Auditoría de tablas

Tema: 

¿Con qué frecuencia se le ha pedido a alguno de ustedes que se registren todos los cambios de las tablas para auditoria? AFAIR, todos y cada uno de los clientes que he tenido me ha pedido esto

Hasta ahora, he usado table_log (http://pgfoundry.org/projects/tablelog) para eso, pero eso esta lejos de ser una solución ideal por varias razones.

table_log lo que hace es crear una nueva tabla por cada tabla que se va a auditar con las mismas columnas que tiene la tabla original más algunas columnas adicionales al final de la tabla, esta implementación tiene algunos problemas:

1) si se agregan nuevas columnas a la tabla original tendremos que complicarnos para agregar las nuevas columnas en el lugar correcto en la tabla de auditoria al menos si no quiero perder la historia.

2) si decide borrar una columna, tocará borrar también en la tabla de auditoria de lo contrario no se podran insertar nuevos registros ni en la tabla real. En este caso la solución es perder un poco de historia.

3) Se crea un registro por cada INSERT, un registro por cada DELETE y 2 registros por cada UPDATE (la versión antigua y nueva del registro se almacenan);
por lo que en tablas que se actualizan frecuentemente el tamaño de la auditoría puede ser el doble de la real.

4) no hay soporte para el registro de las acciones TRUNCATE

5) no hay forma sencilla de determinar cuál es la versión antigua y la nueva de una misma tupla, es necesario comparar la fecha/hora del cambio, el usuario y el modo del cambio (UPDATE) entre las versiones marcadas *old* y las marcadas *new*

6) falta información, por lo menos IP del cliente

Por estas razones, cree un trigger para hacer esto un poco mejor.

lo que he hecho aquí es lo siguiente:

- Uso hstore, y me aseguro que sólo registren los valores que han cambiado. De este modo si se agregan o quitan columnas no hay ninguna acción correctiva que tomar.

- Se inserta sólo un registro por acción a fin de utilizar menos espacio que table_log

- Registra las acciones TRUNCATE (sin embargo, se necesita crear un trigger adicional para esto)

- Los valores viejos y nuevos estan en el mismo registro por lo que es muy obvio que ha cambiado en la tupla

- Almacena la ip desde la que se hizo el cambio o NULL si se hizo localmente

- Se usa una sola tabla para todas las tablas que se esten auditando, de esa manera si se quiere tener tablas separadas para alguna de ellas podemos particionar por relid

Por ahora, he escrito esto en plpgsql pero podría pensar en trasladarlo a c si parece útil...
https://github.com/jcasanov/pg_audit

Comentarios

Requisitos

Cabe agregar que esto lo probe en 9.0, no se si funcione en 8.4 porque no veo la función hstore(text[], text[]). Obviamente, además necesita tener instalado hstore que esta entre los contrib

Jaime Casanova
www.2ndQuadrant.com

Muy buen aporte ... Yo había

Imagen de deathUser

Muy buen aporte ...

Yo había agregado (interceptando la llamada de la librería de abstracción de acceso a la BDD) la funcionalidad a nivel de aplicación, pero claro, si alguien le mete mano directo a la vena, pues no se registra...

bye
;)

Asegurar la tabla de auditoria

El secreto es asegurar apropiadamente la tabla de auditoria, yo le quitaria todos los permisos (al crear la funcion como security definer solo necesitas que el dueño de la funcion tenga acceso a la tabla no el resto de usuarios). Quiza podria pensar en usar otro esquema para esa tabla y todas las particiones que decida crearle.

Jaime Casanova
www.2ndQuadrant.com