Run PLpgSQL function before login

I wrote simple extension session_exec. This extension enforce running some specified function before user is connected to PostgreSQL. This function can be used for some complex user management.

Configuration:

session_preload_libraries to session_exec
session_exec.login_name to login

Usage:

# first login
[pavel@dhcppc4 plpgsql_check]$ psql
WARNING:  function "login()" does not exist

postgres=# CREATE OR REPLACE FUNCTION login()
postgres-# RETURNS void AS $$
postgres$# BEGIN
postgres$#   IF current_database() <> 'admindb' AND session_user = 'postgres' THEN 
postgres$#     RAISE EXCEPTION 'user postgres is blocked';
postgres$#   ELSE
postgres$#     RAISE NOTICE 'Hello, %', session_user;
postgres$#   END IF;
postgres$#   RETURN;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;

# new login
 [pavel@dhcppc4 plpgsql_check]$ psql
NOTICE:  Hello, pavel

-- try to login as Postgres
postgres=# \c postgres postgres
FATAL:  unhandled exception in login function "login"
DETAIL:  user postgres is blocked
CONTEXT:  PL/pgSQL function login() line 4 at RAISE
session_exec: perform login function "login"
Previous connection kept

This is example only. For this specific use case the modification of pg_hba.conf is better. But you can implement more complex logic in plpgsql. The login function can be specific for database or for user via ALTER DATABASE xx SET or ALTER ROLE xx SET.

Attention:

When you use login function, the connect to database will be little bit slower. Use it only when it is necessary.