Hans-Juergen Schoenig: Logon trigger in PostgreSQL

Starting with version 17, PostgreSQL provides a feature many Oracle users have been waiting for: the LOGON trigger. The idea is to make the database launch a function as soon as the user tries to log in.

This post explains how this works and how such a LOGIN trigger (as it is called in the PostgreSQL world) can be implemented and used in real life.

Writing a simple LOGIN trigger in PostgreSQL

A LOGIN trigger is defined for a database (not for an instance). To demonstrate how this works, we first create a simple database:

local_machine:~ hs$ psql postgres
psql (17.2)
Type "help" for help.

postgres=# CREATE DATABASE login_example;
CREATE DATABASE
postgres=# \c login_example

You are now connected to database "login_example" as user "hs".

The idea of this example is to write a simple log entry for each successful login attempt. Here is the definition of the table:

login_example=# CREATE TABLE t_user_login (
id serial,
tstamp timestamptz DEFAULT now(),
who text
);
CREATE TABLE

Like any trigger in PostgreSQL, we first need to create a function before identifying the trigger itself. The special aspect here is that the trigger has to be enabled to make it fire during the login process.

The following code demonstrates how this works:

BEGIN;
CREATE OR REPLACE FUNCTION on_login_proc()
RETURNS event_trigger AS
$$
BEGIN
INSERT INTO t_user_login (who)
VALUES (SESSION_USER);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER on_login_event
ON login
EXECUTE FUNCTION on_login_proc();

ALTER EVENT TRIGGER on_login_event ENABLE ALWAYS;
COMMIT;

The important observation is the function returning an event_trigger variable, a special data type specifically for this purpose. The function itself is simple PL/pgSQL code.

Next, we define the event trigger. The event we want to listen for is "login"—a new feature available starting from PostgreSQL 17.

Finally, the event trigger is enabled and the transaction can commit.

LOGIN triggers in real life

Once the event trigger is deployed and enabled, we can test the code. In this example, I am simply using psql to connect to the desired database:

local_machine:~ hs$ psql login_example
psql (17.2)
Type "help" for help.

login_example=# SELECT * FROM t_user_login;
id | tstamp | who
----+-------------------------------+-----
1 | 2025-01-13 13:17:40.916489+01 | hs
(1 row)

The table already contains some data. Every time we log into the system, one log entry will be added.

However, we have to be careful. What happens when the code is causing issues?
Here is an example:

CREATE OR REPLACE FUNCTION on_login_proc()
RETURNS event_trigger AS
$$
BEGIN
INSERT INTO t_user_login (who)
VALUES (SESSION_USER);
SELECT 1/0;
END;
$$ LANGUAGE plpgsql;

This code will fail because at the end of the procedure, PostgreSQL will face a division by zero. The error ensures that the function will terminate, and this causes the event trigger to fail, as the next listing shows:

local_machine:~ hs$ psql login_example
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: division by zero
CONTEXT: SQL statement "SELECT 1/0"
PL/pgSQL function on_login_proc() line 5 at SQL statement

The key question is: How can we log in again? Well, it's not that easy. If an event trigger fails, the only way to fix the problem is to stop the database instance and fire it up again in single-user mode.

LOGON triggers: A word of caution

In general, LOGIN triggers (or LOGON triggers as they are called in Oracle) can be useful. However, we strongly advise extreme caution when using this feature. A tiny bug in the server-side function can lock you out of the system entirely and cause serious issues. Therefore, carefully consider whether you really need this feature.
If you do, make sure that the code has been tested properly to avoid any risk of failure. Being able to log into a server is quite "essential", and careful precautions are necessary to avoid potential issues.

Read more about "triggers" in other blog postings.

The post Logon trigger in PostgreSQL appeared first on CYBERTEC PostgreSQL | Services & Support.

Source: View source