Auditoria en PostgreSQL

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 🙂

Anuncios

Replicacion en PostgreSQL con Slony-I

Me toco configurar la replicacion de la postgresql, el escenario principal era entre Windows (XP y 2003) y la remota posibilidad de contar con algun Solaris como servidor final; pero ahora aparecio en escena Linux :).
Motores disponibles:

  • PostgreSQL 8.2.6 con Slony 1.2.13R-pg82 (Windows)
  • PostgreSQL 8.3 con Slony 1.2.14 (Windows)
  • PostgreSQL 8.3 (Linux) con Slony-I 1.2.13
  • PostgreSQL Plus AS 8.3 con Slony 1.2.11 (Windows)

Como primera regla a tener en cuenta:
Las versiones de slony deben ser iguales en los nodos
Pruebas realizadas:
Prueba 1
Escenario:

sargon ——————————> saladino


Sargon: 10.10.8.137 Windows 2003 Server PostgreSQL 8.2.6 con Slony-I 1.2.13
Saladino: 10.10.8.214 Ubuntu Server 8.04.1 kernel 2.6.24-19-server PostgreSQL 8.3 con slony-I 1.2.13

Configuracion de Sargon

1)Instalacion del PostgreSQL:

aca no hay nada raro, el mayor problema que se puede tener es que la cuenta ya exista cosa que se soluciona facilemente haciendo boton derecho sobre Mi PC -> administrar -> usuarios locales y grupos -> usuarios y ahi boton derecho sobre la cuenta, eliminar y listo!
Otra cosa: la instalacion via terminal server siempre da error. hay que hacerlo sentado fisicamente en la pc :). Por VNC no lo probe.

2)Instalacion del slony
En este caso es el tipico caso de aplis windows 😛 es un siguiente -> siguiente




3) Configuracion de la replicacion:

  • En el nodo maestro (sargon)

################################
cluster name = mgm_replic;
node 1 admin conninfo = ‘dbname=replica host=10.10.8.137 user=replipostgres password=replipostgres’;
node 2 admin conninfo = ‘dbname=replica host=10.10.8.214 user=replipostgres password=replipostgres’;
init cluster ( id=1, comment = ‘Nodo Maestro en Windows’);
table add key (node id=1,fully qualified name = ‘auditoria.movimiento_audit’);
table add key (node id=1,fully qualified name = ‘auditoria.seguimiento_audit’);
table add key (node id=1,fully qualified name = ‘auditoria.stock_audit’);
table add key (node id=1,fully qualified name = ‘auditoria.vale_audit’);
create set (id=1, origin=1, comment=’aqui van todas mis tablas a replicar’);
set add table (set id=1, origin=1, id=1, fully qualified name = ‘public.personal’,comment=’mi tabla de personal’);
set add table (set id=1, origin=1, id=2, fully qualified name = ‘auditoria.movimiento_audit’,key= serial,comment=’mi tabla de auditoria de movimientos’);
set add table (set id=1, origin=1, id=3, fully qualified name = ‘auditoria.seguimiento_audit’,key= serial,comment=’mi tabla de auditoria de seguimiento’);
set add table (set id=1, origin=1, id=4, fully qualified name = ‘auditoria.stock_audit’,key= serial,comment=’mi tabla de auditoria de stock’);
set add table (set id=1, origin=1, id=5, fully qualified name = ‘auditoria.vale_audit’,key= serial,comment=’mi tabla de auditoria de stock’);
store node (id=2, comment = ‘Nodo Esclavo en la linux’);
store path (server = 1, client = 2, conninfo=’dbname=replica host=10.10.8.137 user=replipostgres password=replipostgres’);
store path (server = 2, client = 1, conninfo=’dbname=replica host=10.10.8.214 user=replipostgres password=replipostgres’);
store listen (origin=1, provider = 1, receiver =2);
store listen (origin=2, provider = 2, receiver =1);

##########################################
lo guardo en un archivo llamado “maestro.txt”
y ejecuto en el cmd:
#######################
C:\Archivos de programa\PostgreSQL\8.2\bin>slonik.exe “c:\Archivos de programa\PostgreSQL\replicacion\maestro.txt”

C:\Archivos de programa\PostgreSQL\8.2\bin>
########################

Si no dio error, todo esta bien. En caso de haber alguno se debe ver a que hace referencia: entrada en el pg_hba.conf, no existe la cuenta, etc.

Ejemplo de error
##############################
C:\Archivos de programa\PostgreSQL\8.2\bin>slonik.exe “c:\Archivos de programa\PostgreSQL\replicacion\maestro.txt”
c:\Archivos de programa\PostgreSQL\replicacion\maestro.txt:8: FATAL: no hay una
línea en pg_hba.conf para «10.10.8.137», usuario «replipostgres», base de
datos «replica», SSL inactivo

###############################

  • En el nodo esclavo (saladino)

###############################
#!/bin/sh
slonik <pelin@saladino:/etc/postgresql/8.3/main/replicacion$ sudo sh init_suscription-5to.sh
pelin@saladino:/etc/postgresql/8.3/main/replicacion$

###############################

Si no dio error todo esta bien.

  • Registro el servicio slon en sargon (Windows)

El windows 2003 da error al ejecutar el comando slon -regservice

#####################
C:\Archivos de programa\PostgreSQL\8.2\bin>slon -regservice
Failed to create service: 1057

######################
La solucion es: (Adjunto la respuesta de Roberto Crucianelli)
######################
slon -regservice

crea un servicio que en definitiva no es más que una serie de llaves de Registry.

Falla en Windows 2003 Server porque Microsoft cambió algunas llaves.

Lo podés levantar creando el servicio a mano.

Si no tenés el Windows Resource Kit para 2003 Server lo bajás de Microsoft. Son utilidades de línea de comandos. Uno de los programas es instsrv.exe. Hacé:

instsrv Slony-I “C:\Archivos de programa\Postgresql\8.3\bin\slon.exe”

(Usá el path que tengas a slon.) Te va a decir que lo creó.

Desde Panel de Control – Herramientas Administrativas – Servicios verificá que esté creado y deshabilitalo.

Ahora vas a regedit

Buscás:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Slony-I

(Tiene que estar.)

Abrí la llave ImagePath.

Vale:

\slon.exe”

Agregá esto ” -service” a continuación de slon.exe.

Creá una nueva entrada Multilínea (REG_MULTI_SZ) al mismo nivel que está ImagePath.

Nombre: DependOnService”

Valor: RPCSS

Salí de Regedit y vas a de nuevo a Panel de Control – Herramientas Administrativas – Servicios. Buscá Slony-I.

Asegurate que el comando que muestra (slon.exe) tenga la opción -service y que figure la dependencia de Servicio de Llamada Remota.

Ahí pasalo a Automático y le dás Iniciar. Si todo fue bien tiene que arrancar sin problemas.

*****Te va a faltar hacer:

slon -addengine Slony-I

Y reiniciar el servicio (o Windows).
######################

****Continuamos:

En sargon creo un archivo que contiene:
#######################
cluster_name = ‘mgm_replic’
conn_info=’dbname=replica user=replipostgres password=replipostgres’
########################
y lo llamo slon.conf

ejecuto:
######################
C:\ARCHIV~1\POSTGR~1\REPLIC~1>slon -addengine Slony-I slon.conf
Engine added.
NOTE! You need to restart the Slony service before this takes effect.

C:\ARCHIV~1\POSTGR~1\REPLIC~1>
######################
Reinicio el servicio Slony-I

En saladino creo un archivo similar a slon.conf y lo llamo init_slony.sh
##########################
cluster_name=mgm_replic;
conn_info=’dbname=replica host=10.10.8.214 user=replipostgres password=replipostgres’;

##########################

y
lo ejecuto en el shell

#######################
pelin@saladino:/etc/postgresql/8.3/main/replicacion$ sudo sh init_slony.sh
pelin@saladino:/etc/postgresql/8.3/main/replicacion$

########################

Ahora queda la cuestion del slony en el lado de la linux
Creo un archivo llamado “init_slony-1.sh” que contiene
#########################
#!/bin/bash
slon mgm_replic “dbname=replica host=10.10.8.214 user=replipostgres password=replipostgres” > slon_mgm_replica.out 2>&1 &
#########################

lo ejecuto en la consola:
########################
pelin@saladino:/etc/postgresql/8.3/main/replicacion$ sudo sh init_slony-1.sh
#########################

Una vez hecho esto, ya deberia estar replicando la Base de datos replica.
En el archivo slon_mgm_replica.out se puede ver el log del proceso que esta en ejecucion.

Esperemos que les sirva!.

En breve estare escribiendo mas articulos de replicacion.