On 16th of October 2023, Alexander Korotkov committed patch:
Add support event triggers on authenticated login This commit introduces trigger on login event, allowing to fire some actions right on the user connection. This can be useful for logging or connection check purposes as well as for some personalization of environment. Usage details are described in the documentation included, but shortly usage is the same as for other triggers: create function returning event_trigger and then create event trigger on login event. In order to prevent the connection time overhead when there are no triggers the commit introduces pg_database.dathasloginevt flag, which indicates database has active login triggers. This flag is set by CREATE/ALTER EVENT TRIGGER command, and unset at connection time when no active triggers found. Author: Konstantin Knizhnik, Mikhail Gribkov Discussion: https://postgr.es/m/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru Reviewed-by: Pavel Stehule, Takayuki Tsunakawa, Greg Nancarrow, Ivan Panchenko Reviewed-by: Daniel Gustafsson, Teodor Sigaev, Robert Haas, Andres Freund Reviewed-by: Tom Lane, Andrey Sokolov, Zhihong Yu, Sergey Shinderuk Reviewed-by: Gregory Stark, Nikita Malakhov, Ted Yu
Long time ago we got EVENT triggers. While I don't really use them (at least directly, as I use pgl_ddl_deploy which uses them a lot) they definitely have their usecases.
Now, we can get some code executed on every session creation. It would look like:
=$ CREATE OR REPLACE FUNCTION login_validate() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE BEGIN IF session_user <> 'depesz' THEN raise exception 'Go AWAY'; END IF; raise log 'user login: %', session_user; END; $$; =$ CREATE EVENT TRIGGER login_validate ON login EXECUTE FUNCTION login_validate();
With this, if I'll try to login to my db, and issue some query using depesz account, I get:
=$ psql -U depesz -d depesz -X -c 'select now()' now ------------------------------- 2023-10-24 14:49:58.931324+02 (1 ROW)
and in pg logs I see:
2023-10-24 14:49:58.929 CEST depesz@depesz 403707 [local] LOG: connection authenticated: user="depesz" method=trust (/home/pgdba/data/pg_hba.conf:119) 2023-10-24 14:49:58.929 CEST depesz@depesz 403707 [local] LOG: connection authorized: user=depesz database=depesz application_name=psql 2023-10-24 14:49:58.931 CEST depesz@depesz 403707 [local] LOG: user login: depesz 2023-10-24 14:49:58.931 CEST depesz@depesz 403707 [local] CONTEXT: PL/pgSQL function login_validate() line 7 at RAISE 2023-10-24 14:49:58.931 CEST depesz@depesz 403707 [local] LOG: duration: 0.140 ms statement: select now() 2023-10-24 14:49:58.931 CEST depesz@depesz 403707 [local] LOG: disconnection: session time: 0:00:00.002 user=depesz database=depesz host=[local]
But if another user would try to do the same:
=$ psql -U test -d depesz -X -c 'select now()' psql: error: connection TO server ON socket "/tmp/.s.PGSQL.5430" failed: FATAL: GO away CONTEXT: PL/pgSQL FUNCTION login_validate() line 5 at RAISE
and logs have:
2023-10-24 14:50:46.689 CEST test@depesz 404179 [local] LOG: connection authenticated: user="test" method=trust (/home/pgdba/data/pg_hba.conf:119) 2023-10-24 14:50:46.689 CEST test@depesz 404179 [local] LOG: connection authorized: user=test database=depesz application_name=psql 2023-10-24 14:50:46.690 CEST test@depesz 404179 [local] FATAL: Go away 2023-10-24 14:50:46.690 CEST test@depesz 404179 [local] CONTEXT: PL/pgSQL function login_validate() line 5 at RAISE 2023-10-24 14:50:46.690 CEST test@depesz 404179 [local] LOG: disconnection: session time: 0:00:00.002 user=test database=depesz host=[local]
Of course, there are easier ways to disallow user to connect. But it can be helpful to enforce certain policies, like, for example: every connection has to have “application_name" set:
CREATE OR REPLACE FUNCTION login_validate() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE BEGIN IF '' = current_setting('application_name') THEN raise exception 'You don''t have application_name set!'; END IF; raise log 'user logged with application name: %', current_setting('application_name'); END; $$; CREATE EVENT TRIGGER login_validate ON login EXECUTE FUNCTION login_validate();
I can still use psql:
=$ psql -X -c 'select now()' now ------------------------------- 2023-10-24 14:54:11.250606+02 (1 ROW)
But if I'd try to run simplest app (yes, I know, “shees, it's perl) without setting application_name:
=$ perl -le 'use DBI; print DBI->connect("dbi:Pg:dbname=depesz;host=127.0.0.1;port=5430")->selectall_arrayref("select now()")->[0]->[0]' DBI CONNECT('dbname=depesz;host=127.0.0.1;port=5430','',...) failed: connection TO server at "127.0.0.1", port 5430 failed: FATAL: You don't have application_name set! CONTEXT: PL/pgSQL function login_validate() line 5 at RAISE at -e line 1. Can't CALL method "selectall_arrayref" ON an undefined VALUE at -e line 1.
and it will immediately pass if I'd supply application_name name, by env variable:
=$ PGAPPNAME=test perl -le 'use DBI; print DBI->connect("dbi:Pg:dbname=depesz;host=127.0.0.1;port=5430")->selectall_arrayref("select now()")->[0]->[0]' 2023-10-24 14:57:10.602237+02
or by connection option:
=$ perl -le 'use DBI; print DBI->connect("dbi:Pg:dbname=depesz;host=127.0.0.1;port=5430;application_name=whatever")->selectall_arrayref("select now()")->[0]->[0]' 2023-10-24 15:15:03.451867+02
Using the same approach we could make trigger that disallows login of certain users at certain times, or just pre-fills some global things for user on login.
Great stuff. Thanks a lot to everyone involved.
Is it also possible to create an EVENT TRIGGER “ON logout”?
Oracle “login trigger”(s) come to mind here.
Probably because I lived so many years without, I cannot figure out right now which purpose they can have in PostgreSQL except for a little auditing or per-user settings, when you are not using a pooler.
And I am curious about performances.
IBM Db2 has CONNECT_PROC parameter at database level. Admin can create stored procedure and then update above parameter with stored procedure name.
I have used it to collect users client version and to get SSL/TLS version. This indicated who is using old clients and needs to upgrade client, before I have performed restrictions on database.