Use logon and logoff triggers to audit user activity - PolarDB

Logon and logoff triggers are event triggers that automatically run a function or stored procedure when a user connects to or disconnects from a PolarDB for PostgreSQL database. Use them to implement session auditing, dynamic role assignment, or session variable initialization.

How it works

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

Triggers fire at the backend process level, not at the client connection level. If you use connection pools or connection proxies, the triggers may not fire on every client connect or disconnect, because the backend process may remain running across multiple client sessions.

Syntax

The two event names are after_user_logon and before_user_logoff.

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

Usage notes

Multiple triggers on the same event: if any one trigger fails, all triggers for that event are terminated and the transaction is rolled back.
Logon trigger errors: PolarDB logs the error because the client is not ready. PolarDB tries to send the following warning to the client: "event trigger occur error after user login. For more information, see log".
Logoff trigger errors: PolarDB logs the error. The client may not receive the error because the client has already exited.

Create logon and logoff triggers

The following example creates a users_log table and a trigger function that records each logon and logoff event, then attaches the function to both events.

The example uses the public schema. Replace it with your own schema if needed. To check your current schema, run SHOW search_path;.

Step 1: Create the log table.

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
);

Step 2: Create the trigger function.

The function uses PolarDB built-in session variables to capture connection context:

Variable	Description
`polar_login_user`	Name of the user who logged on
`polar_database_name`	Name of the database
`polar_client_ip`	IP address of the client
`polar_instance_num`	Instance number
`tg_event`	Event that fired the trigger (`after_user_logon` or `before_user_logoff`)
`tg_tag`	Tag associated with the trigger

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;

Step 3: Attach the function to the logon and logoff events.

-- Fires after each user connects
CREATE EVENT TRIGGER login_event_trigger on after_user_logon
    EXECUTE PROCEDURE public.sample_event_trigger();

-- Fires before each user disconnects
CREATE EVENT TRIGGER logout_event_trigger on before_user_logoff
    EXECUTE PROCEDURE public.sample_event_trigger();

Each time a user logs on or logs off, a row is inserted into users_log with the session details.

Drop triggers

DROP EVENT TRIGGER login_event_trigger;
DROP EVENT TRIGGER logout_event_trigger;