Sv Community El Salvador

Soporte y Tecnología => Programación => Base de Datos => Mensaje iniciado por: tekun en junio 25, 2012, 12:39:21 pm

Título: [HOW2] pistas de auditoria con postgres y hstore
Publicado por: tekun en junio 25, 2012, 12:39:21 pm

ok, ahora les voy a explicar como implementar pistas de auditoría informática en una db Postgres, creo que desde 8.4 se puede instalar hstore  en postgres, si no es así, no me importa!

bien, primero empezamos por lo primero  :yao_ming:

creamos la extensión

Código: [Seleccionar]

CREATE EXTENSION hstore
supongamos una tabla como esta

Código: [Seleccionar]

CREATE TABLE generales.orders
(
  id integer,
  firstname character varying(200),
  lastname character varying(200),
  email character varying(200),
  CONSTRAINT orders_pkey PRIMARY KEY (id )
);
ahora la tabla historica, que servira para vaciar todas las filas eliminadas de cualquier tablita  :)

Código: [Seleccionar]

CREATE TABLE generales.filas_eliminadas
(
   id serial,
   fila hstore,
   fecha timestamp without time zone,
   usuario character varying(20),
   table_name text,
   PRIMARY KEY (del_id)
)

ahora la función.... que vuelvo a repetir, aplica para distribuir en tantos triggers queramos para todas las tablas..

Código: [Seleccionar]

CREATE OR REPLACE FUNCTION generales.trg_registros_eliminados_auditoria() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
AS $BODY$
DECLARE
     tbl_name text := 'filas_eliminadas' ;
     archive_row hstore := hstore(OLD.*);
     usr_name text := session_user;
BEGIN
     EXECUTE 'insert into ' ||quote_ident(tbl_name)
         ||'(fila,fecha,usuario,table_name)' ||
         ' VALUES('||quote_literal(archive_row)||',now(),'
         ||quote_literal(usr_name)||','|| quote_literal(TG_TABLE_NAME) ||')';
     RETURN OLD;
END;
$BODY$
creamos nuestro trigger

Código: [Seleccionar]

CREATE TRIGGER tri_bitacora_eliminados  AFTER DELETE  ON generales.orders  FOR EACH ROW
  EXECUTE PROCEDURE generales.trg_registros_eliminados_auditoria();

listo !

cual es el resultado

Citar

DELETE from orders where id=5;
DELETE 1

como se mira:?

Citar

test=> SELECT * from filas_eliminadas;
-[ RECORD 1 ]
1;""id"=>"5", "email"=>"selaginella@jajaja.com", "lastname"=>"apellido", "firstname"=>"nombre";"2012-06-25 13:17:33";"postgres";"orders"

ups, alguien elimino algo, los ultimos tres datos son la fecha+hora, usuario y la tabla.... ahammnn

ahhh, démole otra vista... hstore es una gran cosa

Citar

SELECT (each(fila)).* from filas_eliminadas where fila ->'id'='1';

key | value
——————-+———————
id|1
email | selaginella@jajaja.com
lastname | apellido
firstname| nombre

para replicar a las demás tablitas de tu db hay que crear los triggers para las demas tablas, utilizando la misma función


si no estas con postgres, que esperas pásate al lado de los que saben, y empieza a hacer más robusto tu db/sistema  :)