login_hook

Overview

Version

Build

You can build the RPM / DEB packages for login_hook using pig build:

pig build pkg login_hook         # build RPM / DEB packages

Install

You can install login_hook directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install login_hook;          # Install for current active PG version

pig ext install -y login_hook -v 18  # PG 18
pig ext install -y login_hook -v 17  # PG 17
pig ext install -y login_hook -v 16  # PG 16
pig ext install -y login_hook -v 15  # PG 15
pig ext install -y login_hook -v 14  # PG 14

dnf install -y login_hook_18       # PG 18
dnf install -y login_hook_17       # PG 17
dnf install -y login_hook_16       # PG 16
dnf install -y login_hook_15       # PG 15
dnf install -y login_hook_14       # PG 14

apt install -y postgresql-18-login-hook   # PG 18
apt install -y postgresql-17-login-hook   # PG 17
apt install -y postgresql-16-login-hook   # PG 16
apt install -y postgresql-15-login-hook   # PG 15
apt install -y postgresql-14-login-hook   # PG 14

Create Extension:

CREATE EXTENSION login_hook;

Usage

login_hook: Execute code on user login, comparable to Oracle’s after logon trigger

login_hook allows executing custom PL/pgSQL code whenever a user logs into the database.

CREATE EXTENSION login_hook;

Configuration

Add to postgresql.conf:

session_preload_libraries = 'login_hook'

Creating the Login Function

Define a login_hook.login() function that will execute on every login:

CREATE OR REPLACE FUNCTION login_hook.login() RETURNS VOID LANGUAGE PLPGSQL AS $$
BEGIN
    IF NOT login_hook.is_executing_login_hook() THEN
        RAISE EXCEPTION 'Should only be invoked by login_hook';
    END IF;

    -- Your login logic here:
    RAISE NOTICE 'Hello %', current_user;

EXCEPTION
    WHEN OTHERS THEN
        RAISE LOG 'Error in login_hook.login(): %', SQLERRM;
END
$$;
GRANT EXECUTE ON FUNCTION login_hook.login() TO PUBLIC;

The PUBLIC grant is required because the function runs for every connecting user.

Functions

Important Notes

• The function is NOT invoked for background processes or during recovery mode
• Handle all exceptions within the function – failures will prevent normal users from logging in
• Superusers get a warning but can still log in when the function fails
• For PostgreSQL 17+, consider using the native login event trigger instead