Criando Auditoria de Tabelas no PostgreSQL

Hoje irei dar um exemplo de criação auditoria de tabelas no PostgreSQL.

A ideia deste exemplo de auditoria é utilizar uma única função de trigger para auditar N tabelas, ou seja, toda trigger de auditoria em qualquer tabela irá chamar sempre a mesma função de trigger para auditá-la, o que facilita manutenção da rotina de auditoria e possibilita manter um padrão de auditoria.


Neste exemplo considero que a tabela a ser auditada está no schema public e a auditoria da mesma será feita no schema audit, para isso iremos criar o schema utilizando o script:

create schema audit

Tendo definido quais serão as informações padrões a serem auditadas e criaremos uma tabela de auditoria padrão (audit.audit_padrao) com os campos referentes as esses informações, para isso iremos criar a tabela utilizando o script:

create table
  audit.audit_padrao
(
  id_audit int not null,
  data_audit timestamp not null,
  op_audit char(1) not null,
  procpid int not null,
  application_name text not null,
  client_hostname text not null
)

Após a criação da tabela de auditoria padrão, iremos criar a função de trigger de auditoria (audit.fnc_trg_a_iud_audit) que será utilizada para auditar qualquer tabela, para isso iremos criar a função de trigger utilizando o script:

create or replace function audit.fnc_trg_a_iud_audit() returns trigger as

$body$

declare

  nNomeTabela name;

  nNomeTabelaAudit name;

  tIdAudit audit.audit_padrao.id_audit%type;

  tDataAudit audit.audit_padrao.data_audit%type;

  tOpAudit audit.audit_padrao.op_audit%type;

  tProcPid audit.audit_padrao.procpid%type;

  tApplicationName audit.audit_padrao.application_name%type;

  tClientHostName audit.audit_padrao.client_hostname%type;

  rAudit record;

begin

  nNomeTabela := TG_TABLE_NAME;

  nNomeTabelaAudit := 'audit.' || nNomeTabela || '_audit';

  tIdAudit := nextval(nNomeTabelaAudit || '_id_audit_seq');

  tDataAudit := current_timestamp; --clock_timestamp()

  tOpAudit := substring(TG_OP, 1, 1);

  tProcPid := pg_backend_pid();

  tApplicationName := fnc_application_name(pg_backend_pid());

  tClientHostName := fnc_client_hostname(pg_backend_pid());

  case tOpAudit when 'I', 'U' then

    rAudit := new;

  when 'D' then 

    rAudit := old;

  end case; 

  execute 'insert into ' || nNomeTabelaAudit || ' ' ||

               'select $1, $2, $3, $4, $5, $6, $7.*' 

               using tIdAudit, tDataAudit, tOpAudit, tProcPid, tApplicationName, tClientHostName, rAudit;

  return null;

end;

$body$

language 'plpgsql';

Após a criação da função de trigger de auditoria o próximo passo seria a criação da trigger de auditoria na tabela a ser auditada, porém isso pode ser  um trabalho repetitivo dependendo do número de tabelas a serem auditadas, então facilitar e manter um padrão iremos criar uma função (audit.fnc_audit_tabela) que faça a criação da trigger de auditoria em uma tabela especifica, para isso iremos criar a função utilizando o script:

create or replace function audit.fnc_audit_tabela(nNomeTabela name, bRecriar boolean) returns boolean as

$body$
declare
  nNomeTabelaAudit name;
begin
  nNomeTabelaAudit := 'audit.' || nNomeTabela || '_audit';

  if bRecriar then
    execute 'drop table if exists ' || nNomeTabelaAudit;
  end if;

  begin
    execute 'create table ' || nNomeTabelaAudit || ' as (select * from audit.audit_padrao cross join ' || nNomeTabela || ' limit 0)';
  exception
    when duplicate_table then
      --Não faz nada
  end;

  begin
    execute 'create sequence ' || nNomeTabelaAudit || '_id_audit_seq';
  exception
    when duplicate_table then
      --Não faz nada;
  end;

  execute 'drop trigger if exists trg_a_iud_audit on ' || nNomeTabela || ' cascade';

  execute 'create trigger trg_a_iud_audit ' ||
               'after insert or update or delete on ' || nNomeTabela || ' for each row ' ||
               'execute procedure audit.fnc_trg_a_iud_audit()';

  return true;

end;
$body$
language 'plpgsql';

Agora fica fácil criar a auditoria de qualquer tabela, basta chamar a função audit.fnc_audit_tabela passando o nome da tabela a ser auditada como parâmetro. Por exemplo iremos auditar todas as tabelas do schema public, para isso iremos criar a auditoria de todas tabelas do schema public utilizando o script:

select 

  tablename,

  audit.fnc_audit_tabela(tablename, false) 

from

  pg_tables

where

  schemaname = 'public'

order by

  tablename

Considerações:

• Como pode-se observar a auditoria de toda tabela é feita no schema audit, onde a tabela de auditoria tem o mesmo da tabela auditada acrescido de _audit (sendo que esse acréscimo foi feito para evitar problemas, como por exemplo, o que encontrei utilizando componente Zeos no Delphi, onde quando o mesmo efetuava internamente uma consulta de metadados de uma tabela e encontrada 2 tabelas com o mesmo nome em schema diferentes, talvez o problema seja devido a minha não especificação do schema no select da query, mas fica a dica).
• Muito cuidado ao chamar a função audit.fnc_audit_tabela passando o segundo parâmetro com o valor True, isto fará com que a tabela de auditoria seja apagada e criada novamente.

Observações da utilização deste método de auditoria:

1. Sempre que um novo campo for adicionado em uma tabela que é auditada o mesmo também deverá ser adicionado na sua tabela de auditoria;
2. Na tabela de auditoria em primeiro lugar devem estar os campos padrões de auditoria e sem seguida os campos da tabela auditada seguindo sua mesma ordem, ou seja, a mesma ordem dos campos na tabela auditada deve ser seguida na tabela de auditoria após os campos padrões de auditoria;

Espero que tenham gostado. Até o próximo post.

Extras:

Segue as funções utilizadas na função de trigger de auditoria:

---

create or replace function fnc_application_name(iProcPid int) returns text as
$body$
declare
  tApplicationName text;
begin

  select
    application_name into tApplicationName
  from
    pg_stat_activity
  where
    (procpid = iProcPid);

  return tApplicationName;

end;
$body$
language 'plpgsql'

---

create or replace function fnc_application_name() returns text as
$body$
declare
  tApplicationName text;
begin

  tApplicationName := fnc_application_name(pg_backend_pid());

  return tApplicationName;

end;
$body$
language 'plpgsql'

---

create or replace function fnc_client_hostname(iProcPid int) returns text as
$body$
declare
  tClientHostName text;
begin

  select
    client_hostname into tClientHostName
  from
    pg_stat_activity
  where
    (procpid = iProcPid);

  return tClientHostName;

end;
$body$
language 'plpgsql'

---

create or replace function fnc_client_hostname() returns text as
$body$
declare
  tClientHostName text;
begin

  tClientHostName := fnc_client_hostname(pg_backend_pid());

  return tClientHostName;

end;
$body$
language 'plpgsql'