Monitorar atividades dos usuários - Postgres

[Ninja2112]

Ola...

Desejo preparar um relatorio sobre as atividades individuais dos usuários(inserts,updates,...). Como posso fazer isto com o objetivo de monitorar a produção ? A versão que trabalho é Postgres 9.0 com PgAdmin III. Já tentei com pg_stat_activity e pg_stat_statement mas não fornecem os dados que preciso.                                                                        Desejo pegar o resultado dessa query e exportar para um arquivo .xls por exemplo...

Grato

[Ronivaldo Lopes]

Bom dia, uso uma única trigger genérica e uma única tabela para gravar o log de todas as tabelas a serem monitoradas. Todos os campos das tabelas são gravados no campo dados1 do tipo jsonb. No exemplo1, todas as tabelas tem como chave o campo id, e para comandos update, somente os campos que foram atualizados são gravados nos campos dados1 e dados2, depois e antes da atualização, respectivamente.

Você pode simplificar e fazer o comando do insert funcionar para o update (exemplo2), nesse caso pode excluir o campo dados2 da tabela _generic_log.

Caso deseje gravar os dados antes da alteração, e as tabela possuírem os campos chaves com nomes diferentes, já que se trata de um banco antigo, para cada tabela repetir o comando, substituindo a palavra key pelo nome da campo chave da tabela (exemplo3).

Na ultima parte, tem a rotina que associa todas as tabelas do banco para gravar na tabela _generic_log, com exceção as tabelas que estão as linha do IF, nesse caso a própria tabela _generic_log, pois entrará em loop ao incluir seu próprio log.

Feito isso, a tabela _generic_log terá as informações necessárias para você monitorar todas as ações dos usuários.

-- ********************

drop table if exists public._generic_log;

create table public._generic_log (
  id serial not null primary key,
  tabela varchar(50) not null,
  usuario varchar(50) not null default current_setting('app.user_name'),
  ip_address varchar(15) not null default current_setting('app.ip_address'),
  mac_address varchar(17) not null default current_setting('app.mac_address'),
  data_hora int8 not null default to_char(now(), 'yyyymmddHH24missms')::int8,
  operacao char not null check (operacao in ('I', 'U', 'D')),
  dados1 jsonb not null,
  dados2 jsonb null );
 
create index _generic_log_ind on public._generic_log using brin ( tabela );

-- ***************************************************

exemplo1

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if tg_op = 'INSERT' then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;        
  else -- update
    insert into _generic_log (tabela, operacao, dados1, dados2 )  
      select *
        from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                 from jsonb_each( to_jsonb( new ) ) n
                 join jsonb_each( to_jsonb( old ) ) o on n.key = o.key
                 where n.key in ('id','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
        where dados1 <> dados2;            
  end if;  

  return null;
end;
$$ language plpgsql;]

-- ****************************                    

exemplo2

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if (tg_op = 'INSERT') or (tg_op = 'UPDATE') then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;             
  end if;  

  return null;
end;
$$ language plpgsql;
                          
-- ****************************                    

exemplo3

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if (tg_op = 'INSERT') or (tg_op = 'UPDATE') then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;             
  else -- update

    if tg_table_name = 'bancos' then
      insert into _generic_log (tabela, operacao, dados1, dados2 )  
        select *
          from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                   from jsonb_each( to_jsonb( new ) ) n
                   join jsonb_each( to_jsonb( old ) ) o on n.cdbanco = o.cdbanco
                   where n.key in ('cdbanco','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
          where dados1 <> dados2;       
    elseif tg_table_name = 'agencias' then
      insert into _generic_log (tabela, operacao, dados1, dados2 )  
        select *
          from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                   from jsonb_each( to_jsonb( new ) ) n
                   join jsonb_each( to_jsonb( old ) ) o on n.cdagencia = o.cdagencia
                   where n.key in ('cdagencia','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
          where dados1 <> dados2;  
    end if;
     
  end if;  

  return null;
end;
$$ language plpgsql;

-- ***************************************************
-- ***************************************************
--
--  Executa a trigger generica para todas as tabela do banco de dados
--

do
$$
  declare _tabela varchar(100);
  declare _obj record;
begin  
  for _obj in select schemaname as schema, relname as tabela from pg_stat_user_tables where substring(relname for 1 from 1) <> '_' order by relname loop

    _tabela := _obj.schema || '.' || _obj.tabela;
    
    execute format('drop trigger if exists log_generic on %s;', _tabela );
    execute format('drop trigger if exists log_generic_update on %s;', _tabela );
    
    if not _obj.tabela = any (values ('_generic_log','_tabela1...','_tabela2...')) then

      -- insert or delete
      execute format('create trigger log_generic after insert or delete on %s for each row execute function public.check_generic_log();', _tabela );
      
      -- update --> somente se houve alteração ==> for each row WHEN (old.* is distinct from new.*)        
      execute format('create trigger log_generic_update after update on %s for each row when (old.* is distinct from new.*) execute function public.check_generic_log();', _tabela );
    end if;
  end loop;
end;
$$

Editado Abril 11 por Ronivaldo Lopes

[Ninja2112]

Ola, amigo....muito grato pela atenção e riqueza de detalhes...vou testar aqui.

[Ninja2112]

Ele não suporta o formato jsonb, então estou tentando adaptar com outro tipo e achar equivalente p jsonb_obj_agg e jsonb_each