Welcome to Part 6 of our series exploring the exciting new features anticipated in the official PostgreSQL 17 release. In this series, we delve into newly added features and discuss how they can benefit database developers and migration engineers transitioning to PostgreSQL 17 in future.

In case you missed the earlier parts:

• PL/pgSQL – the procedural language in PostgreSQL 17.
• Null constraints and performance improvements in PostgreSQL 17.
• COPY command has become more user-friendly in PostgreSQL 17.
• Enhanced Merge Command in PostgreSQL 17.
• Correlated-in-clause-join-transformation.

Ready to enhance your PostgreSQL development skills? My course on PostgreSQL and PL/pgSQL will help you master database development. Click here to start your journey, and use code DBGRP30 to avail 30% off!

Event Trigger during Database Connections in PostgreSQL 17

In databases, the login event is the first interaction a user has when connecting, making it an ideal candidate to trigger functionality based on different use cases. Logon triggers are used for various purposes, such as logging, enforcing business constraints, or populating global variables within database sessions.

If you are wondering till now how connections were logged so we have log_connections and log_disconnections database configuration to enable it at instance level.

Let’s explore Login Event Triggers with an example that mandates checks on application_name for every connection to a database.

Example: Enforcing application_name for Every Connection

In the example below, we create a Login Trigger that ensures all connections specify an application_name. Setting application_name can be useful for code instrumentation, root cause analysis, and is part of the pg_stat_activity view, which can be used for snapshots of database or connection processes at the application level.

CREATE OR REPLACE FUNCTION mandate_app_info() RETURNS event_trigger AS $$
BEGIN
  IF nullif(current_setting('application_name'),'') is null then 
    RAISE EXCEPTION 'Login not allowed for missing application_name' USING HINT = 'Please set application_name context';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER on_login_trigger ON login EXECUTE PROCEDURE mandate_app_info();
ALTER EVENT TRIGGER on_login_trigger ENABLE ALWAYS;

PostgreSQL 17 Event Trigger Login Workflow and considerations.

The Login Event Trigger will be invoked for each new connection request to the database. It’s critical to keep the procedural code simple; complex logic can lead to runtime exceptions that will prevent users from connecting to the database.

With an event trigger on login, database new metadata column dathasloginevt is updated, and procedural code logic is executed only for every successful authentication.

pg17=# select dathasloginevt from pg_database where datname = 'pg17';
 dathasloginevt
----------------
 t

Key considerations when designing use cases around Login Event Triggers:

1. DML withtin Event Trigger Procedural Code.

If planning to perform DML within the trigger, ensure that you account for standby servers and take appropriate actions.

pg17=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

As Login trigger will be fired for each and every user connecting to the databases, its importance to design the procedural code with appropiate security considerations. Either defined it as SECURITY DEFINER or make sure all user has necessary privilege to perform DML on the table.

2.. If use cases is aroungd logging, check if log_connection or log_disconnection can be used.

Connection logging can still be done using log_connections and log_disconnections, which log connection information into PostgreSQL logs.
If additional information is required, consider using a Login Event Trigger.

pg17=# \dconfig log_connections|log_disconnections
     Parameter      | Value
--------------------+-------
 log_connections    | off
 log_disconnections | off
(2 rows)

3. Multiple Login Event Trigger.

You can define multiple triggers on the same event, which will run in alphabetical order based on the trigger name.

CREATE OR REPLACE FUNCTION welcome() RETURNS event_trigger AS $$
BEGIN
     raise notice 'Welcome to PostgreSQL 17';
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER on_login_trigger_welcome ON login EXECUTE PROCEDURE welcome();
ALTER EVENT TRIGGER on_login_trigger_welcome ENABLE ALWAYS;

4. Use event_triggers for mitigate login Trigger Failure.

Any failure in the Login Event Trigger will prevent connections to the database. Proper testing and sanity checks on the code logic are essential.If a failure occurs, use the event_triggers configuration to disable the trigger at the system level and re-allow user connections with running login trigger procedural code logic.

pg17=# \dconfig event_triggers
   Parameter    | Value
----------------+-------
 event_triggers | on

pg17=# alter system set event_triggers to off;
pg17=#  select pg_reload_conf();

pg17=# \dconfig event_triggers
   Parameter    | Value
----------------+-------
 event_triggers | off

PostgreSQL 17 Login Trigger in action.

Let’s see our application_name enforcement example in action. If the application_name is not included in the connection settings, the connection will fail with a user-defined exception.

Note: psql and pgAdmin set the application_name by default when initiating a connection.

Conclusion

PostgreSQL 17’s Login Event Triggers provide powerful new ways to control and manage database connections. By incorporating these triggers, developers can enforce policies, enhance security, and streamline connection workflows effectively.

Check out the official examples that highlight multiple use cases for Logon Event Triggers.