The login_hook database extension

The login_hook extension provides a way to execute some code at client login.

The login_hook extension consists of a shared library login_hook.so and schema login_hook in which a function login() can be created to perform some after-login code.

Postgres version

The login_hook database extension is intended for postgresql versions 10 and higher.

Installation

First you'll need to compile the database extension. Please check the Postgres manual for that.

After compilation, the login_hook.so library must be set to load at session start. So please alter the postgresql.conf file and add the login_hook.so library to the session_preload_libraries setting. For example:

      .
      .
      .

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
#
session_preload_libraries = 'login_hook'

Restart the database to activate the setting

Then execute

create extension login_hook;

in the database in which you want the login function to be executed when a client logs in.

Then create a login_hook.login() function. It must have no arguments. Returning anything other than void is not very useful as no one will ever see the result. Example:

create or replace function login_hook.login()
                           returns void
                           security definer
                           language plpgsql
                           as
$$
declare
    < ... whatever you need ... >
begin
    < ... do something useful ... >
exception when others then
    raise notice 'login_hook.login() failed';
end
$$;

Make sure that exceptions are properly dealt with because otherwise logging on to the database might be challenging.