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:

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:

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:

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:

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:

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:

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.