This is a feature I am sure a lot of people have waited for and finally it was committed for PostgreSQL 17. Back in my Oracle days it was quite common to have a trigger firing once a user logged into the database. This is e.g. useful to setup some logging or deny logins based on the day of the week or the time of the day. Starting with PostgreSQL 17 you can do the same.

The new on login trigger in PostgreSQL is an event trigger. In addition to ddl_command_start, ddl_command_end, table_rewrite and sql_drop an event trigger can now fire on the “login” event.

Let’s do a simple example to see how this works. An event trigger requires a function which return “event trigger”, so we need to create this first:

create or replace function deny_session_daniel()
  returns event_trigger security definer
  as
$$
  declare
  begin
    if ( session_user = 'daniel' )
    then
      raise exception 'user % is not allowed to login', 'daniel';
    end if;
  end
$$ language plpgsql;

This is a pretty simple and useless function: It will deny all logins from user “daniel” by raising an exception. This can of course be done more elegant by not creating this user at all or deny connections for this user in pg_hba.conf. Anyway, for the purpose of this post, it demonstrates what can be done. The next step is to create the event trigger:

postgres=# create event trigger my_login_trg
  on login
  execute function deny_session_daniel();
CREATE EVENT TRIGGER
postgres=# alter event trigger my_login_trg enable always;
ALTER EVENT TRIGGER

As this event trigger was created in the “postgres” database it only should fire there, and it should only raise an exception if the user who is trying to connect is named “daniel”:

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \c template1 postgres
You are now connected to database "template1" as user "postgres".
template1=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".

Doing the same as the newly created “daniel” user should raise an exception when we try connect to the “postgres” database, but not to the “template1” database:

postgres=# create user daniel with login password 'daniel';
CREATE ROLE
postgres=# \c postgres daniel
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  user daniel is not allowed to login
CONTEXT:  PL/pgSQL function deny_session_daniel() line 6 at RAISE
Previous connection kept

This works as expected, but you need to be careful. If there is a bug on your event trigger function this will not allow any logins at all, e.g.:

postgres=# create or replace function deny_session_daniel()
  returns event_trigger security definer
  as
$$
  declare
  begin
      raise exception 'user %1 is not allowed to login', session_user;
  end;
$$ language plpgsql; 
CREATE FUNCTION

From now on, nobody will be able to connect to the “postgres” database, not even the superuser “postgres”:

postgres=# \c postgres daniel
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  user daniel1 is not allowed to login
CONTEXT:  PL/pgSQL function deny_session_daniel() line 4 at RAISE
Previous connection kept
postgres=# \c postgres postgres
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  user postgres1 is not allowed to login
CONTEXT:  PL/pgSQL function deny_session_daniel() line 4 at RAISE
Previous connection kept

To workaround this, you need to temporarily disable event triggers at all:

postgres=# \c template1 postgres
You are now connected to database "template1" as user "postgres".
template1=# alter system set event_triggers TO off;
ALTER SYSTEM
template1=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

template1=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".

After that, fix the issue and enable event triggers again:

postgres=# create or replace function deny_session_daniel()
  returns event_trigger security definer
  as
$$
  declare
  begin
    if ( session_user = 'daniel' )
    then
      raise exception 'user %1 is not allowed to login', 'daniel';
    else 
      raise notice 'current user is %1', session_user;
    end if;
  end
$$ language plpgsql;  
CREATE FUNCTION
postgres=# alter system set event_triggers TO on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \c postgres daniel
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  user daniel1 is not allowed to login
CONTEXT:  PL/pgSQL function deny_session_daniel() line 6 at RAISE
Previous connection kept

What you also need to check is, if you are on a replica because writing anything to a replica will fail as it is read only. If it fails, then the login fails as well.

A nice feature, thanks to all involved in this.

Post Views: 1,152
Thumbnail [60x60]
by
Daniel Westermann