Implementación de una Auditoría simple
en PostgreSQL
Constantemente, en muchas organizaciones y por diversos motivos, se
requiere llevar un control de la Base de Datos con respecto a las transacciones
que se realizan diariamente, en referencia a las operaciones INSERT,
UPDATE y DELETE de una tabla en específico.
Obtener este tipo de registro nos ayuda analizar la información con
fines estadísticos y ante todo cualquier aspecto de la seguridad, este proceso
se basa en simples mecanismos que se encargan de registrar cada una de las
operaciones que son ejecutadas.
Las funciones que se les pueden dar a este peculiar mecanismo de seguridad
son muchas, dependen de las necesidades que se puedan presentar en un
determinado momento, en aspectos básicos se puede monitorear cualquier
actividad sobre un registro determinado, es un mecanismo adicional de
respaldo, detectar fraudes, entre otros.
Existen dos formas de cómo llevar a cabo dicho control de registro, pero para
este momento vamos a explicar el más simple, utilizaremos el lenguaje
PL/PgSQL para entender el funcionamiento básico y entender sus
limitaciones.
Repasamos la lógica de este mecanismo en una serie de
pasos, así será más fácil asimilar la idea:
Crear el lenguaje PL/PgSQL dentro de la Base de Datos pedidos.
Crear la tabla llamada “tbl_audit” con su respectiva estructura que se
menciona más adelante, la cual se encarga de guardar cada transacción que
es realizada.
Crear una función que se encarga de obtener los datos de la transacción,
procesarlos para que puedan ser analizados e insertarlos en la tabla
“tbl_audit”.
Crear el Trigger / Disparador en cada tabla que se desee llevar un control
de las transacciones, obviamente no se debe crear en la tabla “tbl_audit”.
Por último, realizar una serie de consultas para comprobar y entender su
funcionamiento.
Pasos para implementar esta solución:
A. Crear un lenguaje de consulta PL/PgSQL a dicha Base de Datos, para
ello escribimos el siguiente comando desde la consola del sistema:
createlang -h localhost -U postgres plpgsql pedidos
NOTA: EN caso de que se requiera eliminar el lenguaje de la base de datos
utilizamos el siguiente comando:
droplang -h localhost -U postgres plpgsql Peliculas
B. Crear una tabla llamada “tbl_audit”, donde se guardan cada una de las
transacciones realizadas:
CREATE TABLE tbl_audit (
Id_audit serial,
NombreTabla character(45) NOT NULL,
Operacion char(1) NOT NULL,
ValorViejo text,
ValorNuevo text,
FechaActualizacion timestamp without time zone NOT NULL,
NombreUsuario character(45) NOT NULL,
CONSTRAINT pk_audit PRIMARY KEY (Id_audit));
C. Creamos una función en PL/PgSQL que nos permite insertar los datos
de aquellos registros que son afectados cada vez que se realiza una
acción de tipo INSERT, UPDATE y DELETE en una tabla
determinada, la cual es definida en la creación del Trigger:
CREATE OR REPLACE FUNCTION fn_log_audit() RETURNS trigger AS
$$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO tbl_audit (NombreTabla, Operacion, ValorViejo,
ValorNuevo, FechaActualizacion, NombreUsuario)
VALUES (TG_TABLE_NAME, 'D', OLD, NULL, now(), USER);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO tbl_audit (NombreTabla, Operacion, ValorViejo,
ValorNuevo, FechaActualizacion, NombreUsuario)
VALUES (TG_TABLE_NAME, 'U', OLD, NEW, now(), USER);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO tbl_audit (NombreTabla, Operacion, ValorViejo,
ValorNuevo, FechaActualizacion, NombreUsuario)
VALUES (TG_TABLE_NAME, 'I', NULL, NEW, now(), USER);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
NOTA: Utilizar la función cast para cambiar el tipo de un valor: CAST (
expression AS type )
D. Crear el Trigger en todas las tablas menos en “tbl_audit”, para este caso
de ejemplo usamos la tabla detalles_pedido, indicando que será
ejecutado el trigger antes de la ejecución de una instrucción INSERT,
UPDATE y DELETE para cada registro y le asignamos la función
anterior.
CREATE TRIGGER detalles_pedido_tg_audit AFTER INSERT OR UPDATE OR
DELETE
ON detalles_pedido FOR EACH ROW EXECUTE PROCEDURE fn_log_audit();
E. Por último realizaremos una serie de consultas para poner en práctica el
registro de transacciones:
INSERT INTO detalles_pedido (id_pedido, id_producto, cantidad)
VALUES (1,6, 35);
UPDATE detalles_pedido SET cantidad=45 WHERE id_pedido=1 AND
id_producto=6;
DELETE FROM detalles_pedido WHERE id_pedido=1 AND id_producto=6;
Al hacer una selección de los datos de la tabla tbl_audit podemos observar en
cada uno de los registros la acción que se le realizó a una determinada tabla,
cuáles son los datos antiguos y nuevos como la fecha de cuando fueron
registrados. Por otro lado se registra el usuario que realizo la acción sobre los
datos.
