User Registration, Access Date and Tables Consulted in the Postgresql database

Asked

Viewed 51 times

1

Good afternoon guys. I know very little about Postgresql and the following demand came for me:

I basically need to register in a table (within the Postgresql database itself) the user who access, Date/time of access, which table he will be consulting the data and date/time he left the database.

I already searched the methods of pg_stat and found nothing.

Thank you so much for your help!

postgresql

  • There are some tools that help you in this task. For example pgAudit. I will not put the link because the moderator deletes my comment. Search on the Internet.

  • Depending on what you want to take a look at the manual, chapter Server Configuration / Error Reporting and Logging, which shows how to log the information you need in csv format and then load such data into a BD table. It may meet your needs.

1 answer

0

you can create a log_acao table with the following fields: schema TEXT text table usuario TEXT timestamp acao TEXT dado_original TEXT dado_new TEXT text query and then you can create a function for each action:

CREATE OR REPLACE FUNCTION fnc_log_acao_trg (
)
RETURNS trigger AS
$body$
DECLARE
  --
  v_old_data TEXT;
  v_new_data TEXT;
  --
BEGIN
  --
  -- Grava todo e qualquer tipo de ação executada nas tabelas vinculadas a esta função
  IF (TG_OP = 'UPDATE') THEN
    --
    v_old_data := ROW(OLD.*);
    v_new_data := ROW(NEW.*);
    --
    INSERT INTO tbl_log_acao
           (schema
           ,tabela
           ,nome_usuario
           ,datahora_acao
           ,acao
           ,dado_original
           ,dado_novo
           ,consulta
           )
    VALUES (TG_TABLE_SCHEMA::TEXT
           ,TG_TABLE_NAME::TEXT
           ,session_user::TEXT
           ,CURRENT_TIMESTAMP
           ,substring(TG_OP,1,1)
           ,v_old_data
           ,v_new_data
           ,current_query()
           );
    --
    RETURN NEW;
    --
  ELSIF (TG_OP = 'DELETE') THEN
    --
    v_old_data := ROW(OLD.*);
    --
    INSERT INTO tbl_log_acao
           (schema
           ,tabela
           ,nome_usuario
           ,datahora_acao
           ,acao
           ,dado_original
           ,dado_novo
           ,consulta
           )
    VALUES (TG_TABLE_SCHEMA::TEXT
           ,TG_TABLE_NAME::TEXT
           ,session_user::TEXT
           ,CURRENT_TIMESTAMP
           ,substring(TG_OP,1,1)
           ,v_old_data
           , current_query()
           );
    --
    RETURN OLD;
    --
  ELSIF (TG_OP = 'INSERT') THEN
    --
    v_new_data := ROW(NEW.*);
    --
    INSERT INTO tbl_log_acao
           (schema
           ,tabela
           ,nome_usuario
           ,datahora_acao
           ,acao
           ,dado_original
           ,dado_novo
           ,consulta
           )
    VALUES (TG_TABLE_SCHEMA::TEXT
           ,TG_TABLE_NAME::TEXT
           ,session_user::TEXT
           ,CURRENT_TIMESTAMP
           ,substring(TG_OP,1,1)
           ,v_new_data
           ,current_query()
           );
    --
    RETURN NEW;
    --
  ELSE
    --
    RAISE WARNING '[fnc_log_acao_trg] - Outra ação ocorreu: %, no %',TG_OP,now();
    RETURN NULL;
    --
  END IF;
  --
  -- Mantem apenas os últimos 60 dias gravados em banco --
  delete from tbl_log_acao
  where  datahora_acao < (CURRENT_DATE - 60);
  --
EXCEPTION
  WHEN data_exception THEN
    --
    RAISE WARNING '[fnc_log_acao_trg] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
    RETURN NULL;
    --
  WHEN unique_violation THEN
    --
    RAISE WARNING '[fnc_log_acao_trg] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
    RETURN NULL;
    --
  WHEN OTHERS THEN
    --
    RAISE WARNING '[fnc_log_acao_trg] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
    RETURN NULL;
    --
  --
END;
$body$
LANGUAGE 'plpgsql;

and to finish, you can run the action Trigger in the tables

CREATE TRIGGER tga_monitor
       AFTER INSERT
          OR UPDATE
          OR DELETE
          ON tbl_monitor
         FOR EACH ROW EXECUTE PROCEDURE fnc_log_acao_trg();

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.