PolarDB:Logon and logoff triggers

How it works

Logon and logoff triggers respond to client-level connection events:

• Logon — fires AFTER a client connects to the database.

• Logoff — fires BEFORE a client disconnects from the database.

Each trigger calls a function or stored procedure that you define. The trigger fires when the underlying Postgres backend process starts (logon) or is about to exit (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

Session variables

PolarDB exposes the following Oracle PL variables inside logon and logoff trigger functions:

Usage notes

Connection pools and proxies

Logon and logoff triggers fire only when the Postgres backend process starts or exits. If you use a connection pool or connection proxy, the triggers may not fire on every client connect or disconnect — only when the pool opens or closes a backend connection.

Multiple triggers on the same event

If an event (such as logon) has multiple triggers and one of them fails, all triggers for that event are terminated and the transaction is rolled back.

Error handling

• Logoff trigger errors: Error details are written to the log. The client may not receive the error because it has already disconnected.

• Logon trigger errors: Error details are written to the log. PolarDB attempts to send the following warning to the client: event trigger occur error after user login. For more information, see log.

Examples

The following examples show a common audit logging pattern: recording each connection and disconnection in a users_log table.

-- Audit log table: stores one row per connection or disconnection
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
);

-- Trigger function: called by both the logon and logoff triggers
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,    -- username of the connecting client
     polar_database_name, -- database being connected to
     tg_event,            -- 'login' or 'logoff'
     polar_client_ip,     -- client IP address
     tg_tag,              -- trigger tag
     polar_instance_num); -- cluster number (always 1)
END;

-- Fires after each successful client connection
CREATE EVENT TRIGGER hr.logon_trigger AFTER LOGON
  ON DATABASE EXECUTE FUNCTION public.sample_event_trigger();

-- Fires before each client disconnection
CREATE EVENT TRIGGER hr.logoff_trigger BEFORE LOGOFF
  ON DATABASE EXECUTE FUNCTION public.sample_event_trigger();

DROP EVENT TRIGGER logon_trigger;
DROP EVENT TRIGGER logoff_trigger;