script generador de trigger para auditoría en Postgres

Al terminar de seguir estos pasos, tendremos una tabla dedicada exclusivamente para el registro de las operaciones en nuestra base de datos. Los datos a ser guardados son:

• Nombre de la tabla afectada.
• id registro afectado
• Nombre del campo afectado.
• Valor anterior del cambio.
• Nuevo valor del campo.
• Dirección ip del cliente que realizó el cambio.
• Fecha y hora de cambio (con huso horario, timestamptz)
• Tipo de operación

Requisito para el uso del script es tener instalado TemplateToolkit, en Debian el paquete es libtemplate-perl

Entonces procedemos a crear la tabla de auditoría.


CREATE TABLE auditoria(
id serial NOT NULL,
nombre_tabla varchar,
id_campo int8,
nombre_campo varchar,
valor_anterior varchar,
valor_nuevo varchar,
fecha_operacion timestamptz,
tipo_operacion char(1) DEFAULT 1, -- i = insert ; u = update
ip inet,
CONSTRAINT auditoria_pkey PRIMARY KEY (id),
CONSTRAINT auditoria_tipo_operacion_check CHECK (tipo_operacion = 'i'::bpchar OR tipo_operacion = 'u'::bpchar)
)
WITHOUT OIDS;
ALTER TABLE auditoria OWNER TO postgres;
COMMENT ON COLUMN auditoria.tipo_operacion IS 'i = insert ; u = update ';

El lenguaje usado para este trigger es pl/pgsql , así que para trabajar con él debemos primero agregarlo a la base de datos. Con el front-end pgadmin3 es mas que suficiente colocando ‘plpgsql’ en el campo de nombre de nuevo lenguaje en la base de datos a aplicar el trigger.

Ahora usamos el siguiente script para generar el trigger correspondiente para cada tabla a ser auditada.

#ARCHIVO: genera_trigger_auditoria.pl #!/usr/bin/perl
use Template ;
$trigger = "genera_trigger_auditoria/trigger.tt" ;
$archivo_salida = $ARGV[0] . "-audit.trigger";
for ( $i = 1 ; $i <= $#ARGV ; $i++ ){
push(@campos , $ARGV[$i]);
}
$variables = {
tabla => $ARGV[0],
campos => \@campos
};
$template = Template->new();
$template->process($trigger, $variables , "triggers/".$archivo_salida)
|| die "Hubo un error al generar la plantilla " . $template->error() . "\n";

#genera_trigger_auditoria/trigger.tt

BEGIN
IF (TG_OP = 'DELETE') THEN
RAISE EXCEPTION 'NO SE PERMITEN BORRADOS FISICOS';
END IF ;
IF (TG_OP = 'UPDATE') THEN
[% FOREACH cp = campos %]
IF (OLD.[% cp %] <> NEW.[% cp %]) THEN
IF ('[% cp %]' = 'eliminado') THEN
insert into auditoria(nombre_tabla,id_campo,nombre_campo,valor_anterior,valor_nuevo,ip,fecha_operacion,tipo_operacion)
values( '[% tabla %]', OLD.id, '[% cp %]', OLD.[% cp %]::int, NEW.[% cp %]::int, inet_client_addr(), current_timestamp, 'u');
ELSE
insert into auditoria(nombre_tabla,id_campo,nombre_campo,valor_anterior,valor_nuevo,ip,fecha_operacion,tipo_operacion)
values( '[% tabla %]', OLD.id, '[% cp %]', OLD.[% cp %], NEW.[% cp %], inet_client_addr(), current_timestamp, 'u');
END IF;
END IF;
[% END %]
return new ;
END IF ;
IF (TG_OP = 'INSERT') THEN
[% FOREACH cp = campos %]
IF (NEW.[% cp %] IS NOT null) THEN
IF ('[% cp %]' = 'eliminado') THEN
insert into auditoria(nombre_tabla,id_campo,nombre_campo,valor_anterior,valor_nuevo,ip,fecha_operacion,tipo_operacion)
values( '[% tabla %]', NEW.id, '[% cp %]', null, NEW.[% cp %]::int, inet_client_addr(), current_timestamp, 'i');
ELSE
insert into auditoria(nombre_tabla,id_campo,nombre_campo,valor_anterior,valor_nuevo,ip,fecha_operacion,tipo_operacion)
values( '[% tabla %]', NEW.id, '[% cp %]', null, NEW.[% cp %], inet_client_addr(), current_timestamp, 'i');
END IF;
END IF;
[% END %]
return new ;
end IF ;
END;

La sintaxis de uso es la siguiente:

$ perl genera_trigger_auditoria.pl NOMBRE_TABLA campo1 campo2 campoN

Con esto se crea un archivo con el formato <triggers/NOMBRE_TABLA-audit.trigger>

El contenido de este archivo debemos agregarlo a la lista de triggers disponibles en la base de datos, para posteriormente ser asociados a tabla/eventos. Esto lo hacemos usando pgadmin3 de la siguiente manera:

NOMBRE_BASE_DATOS > schemas > public > Trigger functions

menú: Edit > create

En language colocamos plpgsql y en la pestaña de parametros colocamos el texto generado por el script usado anteriormente.
Ahora asociamos la tabla/evento al trigger, hay que hacer esto por cada tabla.
NOMBRE_BASE_DATOS > schemas > public > tables > NOMBRE_TABLA > triggers

menú: Edit > create

• Verificamos el la opción “row” esté marcada.
• En el campo “trigger” asociamos el trigger recien creado.
• Marcamos la opción para que se dispare antes (BEFORE) de realizar la sentencia.
• Marcamos todos los eventos: INSERT, UPDATE, DELETE.

Ya con esto debería estar funcionado.

Si tienen alguna sugerencia de como se puede guardar un usuario enviado a nivel de aplicación, los comentarios son bien recibidos.

PD: El trigger evita la operación de “DELETE” pues se recomienda trabajar con borrado lógico, es decir, un campo extra boleano para cada tabla para identificar si está eliminado o no.

~ por dvst en Abril 15, 2008.