Audit - Mailing list pgsql-general

Hi list. I just want to send to you an sql file containing tools for audit the UPDATE and DELETE
statements in a database by saving all the modifications made by a network/system/database user.

I hope you find it useful

El Santi



=====
Santiago Cassina

Responder a: ctqeo@yahoo.com



_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com/*  Expa�ol  */
/*  El tan preciado trigger de auditor�as, habr�a que pegarle una chequeada y mejorarlo un poguito m�s  */
/*  Santiago Cassina - Salta - Argentina - 26 de Agosto de 2004  */
/*  mail: scap2000@yahoo.com - ctqeo@hotmail.com  */
/*  Este trigger se ejecuta ANTES de un UPDATE, DELETE o INSERT y modifica una funci�n previamente creada, la cual se
ejecuta */ 
/*  despu�s de la acci�n. Esta funci�n guarda la fecha y hora de la acci�n, el OID del registro, el nombre del usuario,
elnombre del esquema, */ 
/*  el nombre de la tabla, el nombre del campo afectado, los datos que ten�a antes de la modificaci�n y los datos luego
de*/ 
/*  la modificaci�n del registro, tambi�n el ID del proceso por si se quiere obtener m�s informaci�n desde el
archivo.logde postgresql */ 
/*  como ser la IP de la placa de red y otras cosillas. En caso de un DELETE, solamente modifica la columna "estado" de
latabla  */ 
/*  afectada asign�ndole un "*", o sea que el DELETE realmente no se realiza, sino que se marca el registro borrado,
parauna buena */ 
/*  implementaci�n de este m�todo se deben filtrar los registros a mostrar en nuestra aplicaci�n para ocultar aquellos
que */ 
/*  ya han sido "borrados" agregando la cl�usula "where estado<>'*'"  */
/*  NOTA 1: si el administrador de la base de datos es el que va a realizar m�ltiples updates se recomienda
deshabilitareste  */ 
/*  trigger ya que har� crecer la tabla de auditor�as de manera abrupta, pues guardar� TODOS los cambios que se
realicen */ 
/*  NOTA 2: al usar phpPgAdmin, quise borrar un registro haciendo click en el link "Eliminar" en un registro, y el
trigger */ 
/*  no se ejecut� como esperaba, ni borr� el registro ni actualiz� la columna "estado" con un "*".  */
/*  Supongo que ser� un error de "compatibilidad" del phpPgAdmin con este trigger :-)  Realmente no s� que pasa :-P */
/*  Fin Expa�ol  */

/*  English Translation  */
/*  THE TRANSLATION TO ENGLISH... My apologies... I hope you find it useful :-)  */
/*  The most wanted trigger!!! You maybe want to re-check and modify... be free!  */
/*  Santiago Cassina - Salta - Argentina - August, 26 - 2004  */
/*  mail: scap2000@yahoo.com - ctqeo@hotmail.com  */
/*  This trigger is executed before an UPDATE, DELETE or INSERT statement modifying a previously created function, wich
isexecuted  */ 
/*  after the statement. This function saves, in the audit table, the statement's date and time, */
/*  the record OID, the user name, the schema name, the affected table name, the affected field name,  */
/*  the data in the field before the statement execution and the data in the field after the statement execution  */
/*  and (maybe the most important thing) the process ID (PID) in case you want to obtain extra info about  */
/*  IP, MAC and other things (it's up to you and your brain capacity :-). Don't forget to modify the postgresql.conf
*/
/*  to obtain the desired log-level. In case the user executes a DELETE statement, it saves in the "estado" field of
*/
/*  the affected table a "*" character, indicating the field has been erased (Not phisically but logically for the
"dummyuser".  */ 
/*  Obviously you must filter the records with an "*" in the "estado" field if you don't want a user deletes the same
registeragain and again.  */ 
/*  Note 1: be careful! If the database administrator runs an large update with this trigger, may experience a slow
performance */ 
/*  because the trigger is saving ALL activitie into the audit table, so... disable it, execute the update and enable
itagain!  */ 
/*  Note 2: using phpPgAdmin I wanted to delete a record by clicking on the "Delete" link on the record and... the
triggerdoes nothing???  */ 
/*  The trigger will not delete the record, will not update the "estado" field with an "*" character */
/*  I guess it's a "compatibility" error between phpPgAdmin and this trigger :-)  I really don't know :-P  */
/*  End of English Translation  */

/*  Creaci�n del lenguaje  */
/*  We create the language  */
CREATE OR REPLACE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
    '$libdir/plpgsql' LANGUAGE C;
DROP LANGUAGE plpgsql CASCADE;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler;

/*  Creamos el esquema  */
/*  We create the scheme  */
CREATE SCHEMA auditorias AUTHORIZATION admin;

/*  Creaci�n de la funci�n pdauditar inicial, �sta ser� modificada por la funci�n pdcrear  */
/*  We create the initial pdauditar function, this function is going to be modified by the pdcrear function  */
CREATE OR REPLACE FUNCTION auditorias.pdauditar () RETURNS TRIGGER AS '
    BEGIN
      RAISE EXCEPTION ''?A?N NO SE HA INICIALIZADO LA TABLA %!'', TG_RELNAME;
    END;
' LANGUAGE 'plpgsql';

/*  Creaci�n de la funci�n pdcrear, �sta es la que modifica pdauditar  */
/*  We create the pdcrear function, this function is going to modify the pdauditar function on the statement execution
*/
CREATE OR REPLACE FUNCTION auditorias.pdcrear () RETURNS TRIGGER
    AS '
DECLARE
  idaudit integer;
  audschema varchar := ''auditorias.'';
  consulta varchar;
  k integer;
  nuevo record;
  cols record;
  gatillo varchar := '''';
  funcion varchar := '''';
  esquema varchar := '''';
  antes text;
  id integer;
  columna varchar;
  viejo varchar;
BEGIN
  SELECT INTO idaudit COUNT(*)+1 from auditorias.audit;
  IF idaudit > 1 THEN
    SELECT INTO idaudit max(idaccion)+1 from auditorias.audit;
  END IF;

  --AQUI COMIENZA LA FUNCION pdauditar
  --HERE STARTS THE pdauditar FUNCTION
  FUNCION := ''CREATE OR REPLACE FUNCTION auditorias.pdauditar() RETURNS TRIGGER AS ''''
    DECLARE
      CONSULTA varchar;
      fecha timestamp;
      tipo varchar;
      usuario varchar;
      esquema varchar;
      id integer;
      pid integer;
      query varchar;
      columna varchar;
    BEGIN
    select into fecha current_timestamp(0);
    select into usuario session_user;
    select into pid pg_backend_pid();
    select into esquema nspname FROM pg_class bc,
      pg_namespace ns WHERE
      bc.relnamespace = ns.oid AND bc.oid = TG_RELID;
    IF TG_OP = ''''''''INSERT'''''''' THEN
      tipo:=''''''''A'''''''';
      select into id new.oid;
    END IF;
    IF TG_OP = ''''''''UPDATE'''''''' THEN
      tipo:=''''''''M'''''''';
      select into id old.oid;
    END IF;'';
    --AQUI LA DEJAMOS POR UN TIEMPO
    --HERE WE STOP THE CREATION FOR A WHILE

  IF TG_OP = ''DELETE'' THEN
    select into esquema nspname FROM pg_class bc, pg_namespace ns WHERE
      bc.relnamespace = ns.oid AND bc.oid = TG_RELID;
    select into id old.oid;

    --AQUI, EN CASO DE UN DELETE, SE ACTUALIZA LA COLUMNA "ESTADO" DE LA TABLA Y SE FINALIZA EL TRIGGER
    --IN CASE OF A DELETE STATEMENT, THE FUNCTION UPDATES THE "ESTADO" FIELD ON THE TABLE AND ENDS THE TRIGGER
EXECUTION
    FUNCION := ''update '' || esquema || ''.'' || TG_RELNAME || '' set estado=''''*''''
      where oid = '' || old.oid;
    EXECUTE FUNCION;
    RETURN OLD;
  ELSE
    IF TG_OP = ''UPDATE'' THEN
      viejo:=''OLD.'';
    END IF;
    IF TG_OP = ''INSERT'' THEN
      viejo:=''NEW.'';
    END IF;
    FOR cols IN select attname as nombre, attnum as numero from pg_attribute
      where attrelid = TG_RELID
      and NOT attisdropped
      and attnum > 0
    LOOP
    --LA PARTE COMPLEJA DE LA FUNCION
    --THE COMPLEX... THE HARD... THE UGLY...
    FUNCION := FUNCION || ''
      select into columna attname as nombre from pg_attribute
        where attrelid = TG_RELID
        and NOT attisdropped
        and attnum = '' || cols.numero || '';
      IF NEW.'' || cols.nombre || ''<>'' || viejo || cols.nombre || '' THEN
        CONSULTA := ''''''''INSERT INTO '' || audschema || ''audit
          values ('' || idaudit || '','''''''' || quote_literal(tipo)
          || '''''''','''''''' || quote_literal(fecha)
          || '''''''','''''''' || quote_literal(usuario)
          || '''''''','''''''' || quote_literal(esquema
          || ''''''''.'''''''' || TG_RELNAME) || '''''''','''''''' || id
          || '''''''','''''''' || quote_literal(columna)
          || '''''''','''''''' || quote_literal('' || viejo || cols.nombre || '')
          || '''''''','''''''' || quote_literal(NEW.'' || cols.nombre || '')
          || '''''''','''''''' || pid
          || '''''''')'''''''';
        select into query current_query from pg_stat_activity where procpid = pid;
        --SI QUEREMOS VER LA FUNCION SIN EJECUTARLA NO DEBEREMOS COMENTAR LA SIGUIENTE LINEA
        --IF WE WANT TO SEE THE FUNCTION STRING, JUST UN-COMMENT THE NEXT LINE
        --RAISE EXCEPTION ''''''''PID: %, Query: %'''''''',pid,FUNCION;
        EXECUTE CONSULTA;
      END IF;'';
    END LOOP;
    FUNCION := FUNCION || '' RETURN NEW;
      END;'''' LANGUAGE plpgsql SECURITY INVOKER;'';
    --SI QUEREMOS VER LA FUNCION SIN EJECUTARLA NO DEBEREMOS COMENTAR LA SIGUIENTE LINEA
    --IF WE WANT TO SEE THE FUNCTION STRING, JUST UN-COMMENT THE NEXT LINE
    --RAISE EXCEPTION ''FUNCION: %'',funcion;
    EXECUTE FUNCION;
    RETURN NEW;
  END IF;
  --EXECUTE CONSULTA;
  --RAISE EXCEPTION ''FUNCION: %'',funcion;
END;
'
    LANGUAGE plpgsql;

/*  Espa�ol  */
/*  Estructura de la tabla audit, la tabla que guarda todas las modificaciones hechas, deberemos crear el esquema
"auditorias" */ 
/*  idaccion: el n�mero de acci�n ejecutada... autonum�rico :-P  */
/*  tipo: el tipo de accion: M: Modificacion, A: Inserci�n  */
/*  fecha: la fecha y hora de la acci�n  */
/*  usuario: el nombre del usuario que realiz� la modificaci�n  */
/*  tabla: el esquema y la tabla modificada  */
/*  campo: el nombre del campo modificado  */
/*  antes: la informaci�n que conten�a el campo antes de la modificaci�n  */
/*  despues: la informaci�n del campo despu�s de la modificaci�n  */
/*  pid: el id del proceso que ejecut� la acci�n  */
/*  Fin Espa�ol  */
/*  English Translation  */
/*  The audit table structure, this table is going to save all the modifications done to our "secure" tables  */
/*  We must have the "auditorias" scheme created  */
/*  idaccion: autonumeric :-P  */
/*  tipo: action type: M: Update, A: Insert */
/*  fecha: date and time  */
/*  usuario: user name  */
/*  tabla: scheme and table names  */
/*  campo: field name  */
/*  antes: the data in the field before the statement execution  */
/*  despues: the data in the field after the statement execution  */
/*  pid: the statement execution process ID  */
/*  End of English Translation  */
CREATE TABLE auditorias.audit (
    idaccion integer DEFAULT '0' NOT NULL,
    tipo character(1) DEFAULT '' NOT NULL,
    fecha timestamp without time zone NOT NULL,
    usuario character varying DEFAULT '' NOT NULL,
    tabla character varying(20) DEFAULT '' NOT NULL,
    id character varying(20) DEFAULT '' NOT NULL,
    campo character varying,
    antes text,
    despues text,
    pid integer
);


/*  Agregamos la columna "estado", obviamente la tabla no debe tener �sta columna  */
/*  We add the "estado" field, obviously it hasn't to be on the table already...  */
ALTER TABLE ESQUEMA.TABLA ADD COLUMN estado character;
/*  Eliminamos el NULL de la columna "estado"  */
/*  Change the value NULL of the "estado" field  */
update ESQUEMA.TABLA set estado = '';
/*  Restringimos los valores NULL de la columna "estado"  */
/*  We set to "NOT NULL" the "estado" field*/
ALTER TABLE ESQUEMA.TABLA ALTER COLUMN estado SET NOT NULL;

/*  Estas dos lineas deberan correrse para cada tabla que se desee auditar  */
/*  This two lines set the table "secure", just replace "ESQUEMA.TABLA" string with the desired "SCHEME.TABLE"  */
create trigger tgauditar after insert or update on ESQUEMA.TABLA for each row execute procedure auditorias.pdauditar();
create trigger tgcrear before insert or delete or update on ESQUEMA.TABLA for each row execute procedure
auditorias.pdcrear();

/*  Informe de Tablas, Columnas y Tipos de datos de cada columna  */
SELECT nspname, relname as tabla,
attname as columna,
attnum as numero, typname as tipodatos
FROM
  pg_class bc,
  pg_attribute ta,
  pg_namespace ns,
  pg_type ty
WHERE
  ta.attrelid = bc.oid
  and ta.attnum > 0
  and not ta.attisdropped
  and relam = 0
  and bc.relnamespace = ns.oid
  and bc.relname not like 'pg_%'
  and ta.atttypid = ty.oid
  order by nspname, relname, attnum;

/*  Informe de Esquemas y Tablas  */
SELECT nspname as esquema, nspacl as aclesquema, relname as tabla, reltuples as filas, relacl as acltabla,
usename as propietario
FROM
  pg_class bc,
  pg_namespace ns,
  pg_shadow us
WHERE
  bc.relnamespace = ns.oid
--  and ns.nspname = 'rurbano' /*SCHEME*/
  and relam = 0
--  AND bc.relname = 'cuadras' /*RELATION*/
  and nspname not like 'pg_%'
  and us.usesysid = bc.relowner
  order by nspname, relname
;
--select * from pg_constraint where
/*bc.reltuples = cantidad de filas*/

/*  Los dos informes anteriores pero ahora cruzados en uno solo  */
SELECT nspname as esquema, nspacl as aclesquema, relname as tabla, reltuples as filas, relacl as acltabla,
usename as propietario, attname as columna,
attnum as numero, typname as tipodatos
FROM
  pg_class bc,
  pg_attribute ta,
  pg_namespace ns,
  pg_shadow us,
  pg_type ty
WHERE
  ta.attrelid = bc.oid
  and ta.attnum > 0
  and not ta.attisdropped
  and bc.relnamespace = ns.oid
--  and ns.nspname = 'rurbano' /*SCHEME*/
  and relam = 0
--  AND bc.relname = 'cuadras' /*RELATION*/
  and nspname not like 'pg_%'
  and us.usesysid = bc.relowner
  and ta.atttypid = ty.oid
  order by nspname, relname, attnum
;