Create and manage logon and logoff event triggers - PolarDB

Logon and logoff triggers are event triggers that fire a specified function or stored procedure whenever a client connects to or disconnects from the database.

Logon trigger — fires after the Postgres backend process starts, when a client connects to the database.
Logoff trigger — fires before the Postgres backend process exits, when a client initiates a logoff.

Syntax

CREATE EVENT TRIGGER trigger_name event_login_or_logoff
            EXECUTE FUNCTION_or_PROCEDURE func_name()

event_login_or_logoff:
         AFTER LOGON ON DATABASE
        | BEFORE LOGOFF ON DATABASE

Oracle PL variables

PolarDB supports the following Oracle PL variables in logon and logoff trigger functions.

Variable	Type	Description
`polar_login_user`	TEXT	Username used to log on
`polar_database_name`	TEXT	Name of the database logged on to
`polar_instance_num`	INT	Number of clusters logged on to (always `1`)
`polar_client_ip`	TEXT	Client IP address

Usage notes

Connection pools and proxies: If your application uses a connection pool or connection proxy, logon and logoff triggers may not fire on every client connect or disconnect. The triggers fire only when the Postgres backend process starts or exits — not when the pool reuses an existing connection.
Multiple triggers on the same event: If multiple triggers are registered for the same event and one fails, all trigger actions stop and the transaction is rolled back.
Error handling for logon triggers: When a logon trigger fails, PolarDB logs the error and attempts to send the following warning to the client: event trigger occur error after user login. For more information, see log. The client is not ready to receive messages at this point, so always check the logs.
Error handling for logoff triggers: When a logoff trigger fails, PolarDB logs the error. The client will not receive the error because it has already disconnected.

Example: Log user connections to an audit table

The following example logs every user connection and disconnection to an audit table named users_log.

Set up the audit table and trigger function

-- Create the audit table to store connection events
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 the trigger function that inserts a row on each logon or logoff event
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 hr.logon_trigger AFTER LOGON
  ON DATABASE execute function public.sample_event_trigger();

Create a logoff trigger

CREATE EVENT TRIGGER hr.logoff_trigger BEFORE LOGOFF
  ON DATABASE execute function public.sample_event_trigger();

Drop the triggers

DROP EVENT TRIGGER logon_trigger;
DROP EVENT TRIGGER logoff_trigger;