EL blog de Pelin

Cuando uno tiene que auditar las transacciones realizadas en la base de datos tiene varias aproximaciones a tomar, buscando en internet encontre muchas opciones y la mas adecuada estaba en la documentacion de postgresql.
Estrictamente hablando son una serie de triggers, que al ejecutarse un cambio sobre un registro almacena el valor anterior del registro, el nuevo valor, la hora a la que se realizo el cambio y el usuario de la base de datos. en mi caso tenemos un sistema web y la captura del usuario de la aplicacion es bastante engorroso.
La solucion encontrada fue generar una tabla de transacciones en la cual se almacena un numero de transaccion, el nombre de usuario y la tabla sobre la cual se realizo la transaccion. En cada tabla a ser auditada se la agrega un campo al final llamado nro_transaccion. Cuando se va a realizar un cambio en algun registro primero se escribe en la tabla transaccion, luego en la tabla propiamente dicha y finalmente el trigger escribe en la tabla de auditoria.
Los siguentes scripts son una mezcla partiendo del script que figura en la documentacion de PostgreSQL, agregados mios y finalmente mi amigo Luis Escobar creo las funciones para automatizar el proceso de agregar tablas a auditar.

1) Crear la base de datos de prueba:

postgres=# CREATE DATABASE “pruebaAudit” postgres-# WITH ENCODING=’SQL_ASCII’; CREATE DATABASE postgres=# COMMENT ON DATABASE “pruebaAudit” IS ‘Base de datos de prueba para auditoria’; COMMENT

2) Crear el esquema:

postgres=# \c pruebaAudit
You are now connected to database “pruebaAudit”.
pruebaAudit=# create schema auditoria;
CREATE SCHEMA

3) Crear las tablas

Tabla personal:

pruebaAudit=# CREATE TABLE personal
pruebaAudit-# (
pruebaAudit(# idper bigserial NOT NULL,
pruebaAudit(# perdom character varying(100) NOT NULL,
pruebaAudit(# percuil character varying(11) NOT NULL,
pruebaAudit(# persex character varying(1) NOT NULL,
perdocnro character varying(10) NOT NULL,
pertelcel character varying(50) NOT NULL,
pruebaAudit(# perfchnac date,
pruebaAudit(# perdoctip character varying(4) NOT NULL,
pruebaAudit(# perdocnro character varying(10) NOT NULL,
pruebaAudit(# pertelcel character varying(50) NOT NULL,
pruebaAudit(# peremail character varying(50) NOT NULL,
pruebaAudit(# perape character varying(40) NOT NULL,
pruebaAudit(# pernom character varying(40) NOT NULL,
pruebaAudit(# eliminado smallint,
pruebaAudit(# CONSTRAINT personal_pkey PRIMARY KEY (idper)
pruebaAudit(# )
pruebaAudit-# WITH (OIDS=FALSE);
NOTICE: CREATE TABLE creará una secuencia implícita «personal_idper_seq» para la columna serial «personal.idper»
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «personal_pkey» para la tabla «personal»
CREATE TABLE

Tabla usuario

pruebaAudit=# CREATE TABLE usuario
pruebaAudit-# (
pruebaAudit(# idusu smallint NOT NULL,
pruebaAudit(# nomusu character varying(20) NOT NULL,
pruebaAudit(# passusu character varying(50) NOT NULL,
pruebaAudit(# idper bigint NOT NULL,
pruebaAudit(# eliminado smallint,
pruebaAudit(# codusu character varying(20) NOT NULL,
pruebaAudit(# CONSTRAINT codusu_pkey PRIMARY KEY (codusu),
pruebaAudit(# CONSTRAINT usuario_idper_fkey FOREIGN KEY (idper)
pruebaAudit(# REFERENCES personal (idper) MATCH SIMPLE
pruebaAudit(# ON UPDATE NO ACTION ON DELETE NO ACTION
pruebaAudit(# )
pruebaAudit-# WITH (OIDS=FALSE);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «codusu_pkey» para la tabla «usuario»
CREATE TABLE

4) Creacion de las tablas necesarias para la auditoria

pruebaAudit=# CREATE TABLE “auditoria”.infseg (
pruebaAudit(# nombtabla varchar(150) NOT NULL,
pruebaAudit(# esquema varchar(60) NOT NULL,
pruebaAudit(# activar boolean DEFAULT false,
pruebaAudit(# version smallint DEFAULT 1,
pruebaAudit(# fechageneracion timestamp WITHOUT TIME ZONE DEFAULT now(),
pruebaAudit(# fechaactivacion timestamp WITHOUT TIME ZONE,
pruebaAudit(# hastrigg boolean,
pruebaAudit(# /* Keys */
pruebaAudit(# — CONSTRAINT infseg_pkey
pruebaAudit(# PRIMARY KEY (nombtabla)
pruebaAudit(# ) WITH (
pruebaAudit(# OIDS = FALSE
pruebaAudit(# );
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «infseg_pkey» para la tabla «infseg»
CREATE TABLE

Insertamos datos:
busca las tablas que estan en el esquema public y las inserta en la tabla infoseg.

pruebaAudit=# INSERT INTO “auditoria”.infseg
pruebaAudit-# (nombtabla
pruebaAudit(# , esquema
pruebaAudit(# , version
pruebaAudit(# , hastrigg)
pruebaAudit-# select pgtb.tablename
pruebaAudit-# , pgtb.schemaname
pruebaAudit-# , 1
pruebaAudit-# , pgtb.hastriggers
pruebaAudit-# from pg_tables pgtb
pruebaAudit-# where pgtb.schemaname like ‘public’
pruebaAudit-# ;
INSERT 0 2

5) Crear las funciones:
esta funcion la creo desde la herramienta de SQL del PgAdmin

CREATE OR REPLACE FUNCTION public.CrearTrigger
(
nombretabla text,
esquema text,
db text
)
RETURNS text AS
$$
DECLARE QQ text;
BEGIN
QQ := ‘CREATE OR REPLACE FUNCTION ‘ || nombretabla || ‘_trigAUTO()
RETURNS trigger AS
$BODY$
DECLARE rows_affected INTEGER;
BEGIN IF TG_OP =”INSERT” THEN
INSERT INTO “auditoria”.’ || nombretabla || ‘ (accion, newmovimiento,consulta)
SELECT TG_OP , NEW , current_query
FROM pg_stat_activity
WHERE datname=”’ || db ||”’ AND current_query <> ””;
ELSIF TG_OP =”UPDATE” THEN
INSERT INTO “auditoria”.’ || nombretabla || ‘ (accion, oldmovimiento, newmovimiento,consulta)
SELECT TG_OP ,OLD ,NEW , current_query
FROM pg_stat_activity
WHERE datname=”’|| db ||”’ AND current_query <> ””;
ELSIF TG_OP =”DELETE” THEN
INSERT INTO “auditoria”.’ || nombretabla || ‘ (accion,oldmovimiento ,consulta)
SELECT TG_OP ,OLD , current_query
FROM pg_stat_activity
WHERE datname=”’ || db ||”’ AND current_query <> ””;
ELSE
RAISE EXCEPTION ”TG_OP % es uno de INSERT, UPDATE or DELETE.”, TG_OP;
END IF;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
IF rows_affected = 1 THEN
IF TG_OP IN (”INSERT”,”UPDATE”) THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
ELSE RAISE EXCEPTION ”Fallo el insert en auditoria.|| nombretabla ||”;
END IF;
END;
$BODY$
LANGUAGE ”plpgsql” VOLATILE STRICT;

CREATE TRIGGER tg_’ || esquema || ‘_’ || nombretabla || ‘
AFTER INSERT OR UPDATE OR DELETE ON ‘|| nombretabla || ‘
FOR EACH ROW EXECUTE PROCEDURE public.’ || nombretabla || ‘_trigauto();’
;
RETURN QQ;
End;
$$
LANGUAGE ‘plpgsql’
VOLATILE
RETURNS NULL ON NULL INPUT
;

CREATE OR REPLACE FUNCTION createTablesSeguimiento
( nombtabla text,
esquema text,
db text
)
RETURNS VOID AS
$body$
DECLARE QQ text;
BEGIN
QQ:= ‘DROP TABLE IF EXISTS “auditoria”. ‘ || nombtabla || ‘;
CREATE TABLE “auditoria”.’ || nombtabla || ‘ (
idmovimiento serial NOT NULL,
usuariodb text NOT NULL DEFAULT “current_user”(),
accion text NOT NULL,
acciontimestamp timestamp WITH TIME ZONE NOT NULL DEFAULT now(),
oldmovimiento ‘ || esquema ||’.’ || nombtabla || ‘,
newmovimiento ‘ || esquema ||’.’ || nombtabla || ‘,
consulta varchar
/* Keys */
CONSTRAINT ‘ || nombtabla || ‘_pkey
PRIMARY KEY (idmovimiento),
/* Checks */
CONSTRAINT ‘ || nombtabla || ‘_check CHECK (accion = ANY (ARRAY[''INSERT''::text, ''UPDATE''::text, ''DELETE''::text]))
) ;
ALTER TABLE “auditoria”.’ || nombtabla || ‘
OWNER TO postgres;’;
EXECUTE QQ;
execute public.CrearTrigger(nombtabla,esquema,db);
END;
$body$
LANGUAGE ‘plpgsql’
VOLATILE
;

6) Ahora vemos como quedo todo:

Esto funciona de la siguiente manera:
a) Desde el PgAdmin se hace click con el boton derecho sobre la tabla infoseg -> Ver datos -> Ver todas las filas lo cual me muestra todas las tablas del esquema public.
b) El campo Activar esta seteado a FALSE, se hace doble click sobre el de manera que se abre un checkbox; se lo tilda de manera que quede en TRUE y se guardan los cambios.

c) Posteriomente se ejecuta el siguiente script
SELECT sinf.nombtabla ,sinf.esquema ,createTablesSeguimiento(sinf.nombtabla,sinf.esquema,’pruebaAudit’)
FROM “auditoria”.infseg sinf WHERE sinf.activar

El mismo crea las tablas en el esquema auditoria y los triggers en el esquema public.

Vemos como ha quedado:

Ahora solo quedan insertar algunos valores en las tablas usuario y personal y ver como se almacenan en las tablas correspondientes en el esquema auditoria.

Bueno, a cargar datos!
Cargo dos registros en la tabla personal

pruebaAudit=# INSERT INTO personal (idper, perdom, percuil, persex, perfchnac, perdoctip, perdocnro, pertelcel, peremail, perape, pernom, eliminado) VALUES (18, ‘mi panza’, ‘5634′, ‘M’, ‘1976-07-20′, ‘DNI’, ‘34563′, ‘453′, ’sdfgq@adfv.vom’, ‘Espinosa ‘, ‘Agustin’, 0); INSERT 0 1

pruebaAudit=# INSERT INTO personal (idper, perdom, percuil, persex, perfchnac, perdoctip, perdocnro, pertelcel, peremail, perape, pernom, eliminado) VALUES (31, ‘adgasdgf’, ‘252525253′, ‘M’, ‘1972-02-07′, ‘DNI’, ‘1315351321′, ‘3456456′, ‘3456356356@cksdf.com’, ‘yo ‘, ‘dfgsdgf’, 0);
INSERT 0 1

pruebaAudit=# select * from personal; idper | perdom | percuil | persex | perfchnac | perdoctip | perdocnro | pertelcel | peremail | perape | pernom | eliminado
——-+———-+———–+——–+————+———–+————+———–+———————-+——————————-+———+———–
18 | mi panza | 5634 | M | 20/07/1976 | DNI | 34563 | 453 | sdfgq@adfv.vom | Espinosa | Agustin | 0
31 | adgasdgf | 252525253 | M | 07/02/1972 | DNI | 1315351321 | 3456456 | 3456356356@cksdf.com | yo | dfgsdgf | 0
(2 rows)

Ahora verifico en la tabla personal del esquema auditoria.

pruebaAudit=# select * from auditoria.personal; idmovimiento | usuariodb | accion | acciontimestamp | oldmovimiento | newmovimiento | consulta ————–+———–+——–+—————————–+—————+———————————————————————————————————-+———————————————————————————————————————————————————————————————————————————————————————————— 1 | postgres | INSERT | 10/11/2008 17:09:47 GMT | | (18,”mi panza”,5634,M,20/07/1976,DNI,34563,453,sdfgq@adfv.vom,”Espinosa “,Agustin,0) | INSERT INTO personal (idper, perdom, percuil, persex, perfchnac, perdoctip, perdocnro, pertelcel, peremail, perape, pernom, eliminado) VALUES (18, ‘mi panza’, ‘5634′, ‘M’, ‘1976-07-20′, ‘DNI’, ‘34563′, ‘453′, ’sdfgq@adfv.vom’, ‘Espinosa ‘, ‘Agustin’, 0); 2 | postgres | INSERT | 10/11/2008 17:10:38.171 GMT | | (31,adgasdgf,252525253,M,07/02/1972,DNI,1315351321,3456456,3456356356@cksdf.com,”yo “,dfgsdgf,0) | INSERT INTO personal (idper, perdom, percuil, persex, perfchnac, perdoctip, perdocnro, pertelcel, peremail, perape, pernom, eliminado) VALUES (31, ‘adgasdgf’, ‘252525253′, ‘M’, ‘1972-02-07′, ‘DNI’, ‘1315351321′, ‘3456456′, ‘3456356356@cksdf.com’, ‘yo ‘, ‘dfgsdgf’, 0); (2 rows)

o mejor visto desde el PgAdmin:

con esto esta demostrado que funciona.

Esto tiene algunos inconvenientes…nada es perfecto!!:

1) Si se quieren agregar mas tablas a auditar se debe ejecutar por cada tabla:

select createtablesseguimiento(’nombre-tabla’,'public’,'pruebaAudit’);

2) Si no se controlan bien las tablas a auditar se agrega mucha carga al servidor

3) Para poder sacar informacion util se debe trabajar con expresiones regulares y demas cosas bellas