PolarDB:Logon and logoff triggers

Overview

Logon and logoff triggers are event triggers and can trigger specified functions or stored procedures when you log on to and log off from the database. Logon means that the Postgres backend process starts when you connect to the database. Logoff means that the Postgres backend process is about to exit when you initiate the logoff command.

Syntax

CREATE EVENT TRIGGER trigger_name event_login_or_logoff
            EXECUTE FUNCTION_or_PROCEDURE func_name '(' ')'

event_login_or_logoff:
          on after_user_logon
        | on before_user_logoff

Precautions

• If you use third-party tools such as connection pools and connection proxies, the logon and logoff triggers may not be triggered when the client is disconnected or connected because they are triggered only when the backend process starts or exits.
• Like other event triggers, if an event (such as logon) has multiple triggers and one of the triggers fails, the actions of all triggers are terminated and the transaction is rolled back.
• When the logoff trigger is triggered and an error occurs, the error details are generated in the log. The error details may only exist in the log but not received by the client because the client has exited. When the logon trigger is triggered and an error occurs, the error details are generated in the log because the client is not ready. PolarDB tries to send the following warning message to the client: "event trigger occur error after user login. For more information, see log".

Examples

• Prepare data for the test.

  CREATE TABLE users_log (
    id       serial,
    user_name VARCHAR2(64),
    database_name VARCHAR2(64),
    event     VARCHAR2(64),
    client_ip VARCHAR2(64),
    tag       VARCHAR2(64),
    instance_num int
  );
  
  CREATE function sample_event_trigger return event_trigger is begin
  INSERT into polar_loginout.users_log (user_name,database_name,event,client_ip, tag, instance_num)
  values (polar_login_user,polar_database_name,tg_event,polar_client_ip,tg_tag,polar_instance_num);
  end;
                      

• Create a logon trigger.

  CREATE event trigger login_event_trigger on after_user_logon
      execute procedure public.sample_event_trigger();

• Create a logoff trigger.

  CREATE event trigger logout_event_trigger on before_user_logoff
      execute procedure public.sample_event_trigger();

• Delete the logon trigger.

  DROP event trigger login_event_trigger;

• Delete the logoff trigger.

  DROP event trigger logout_event_trigger;