Thread: does multi host connection string supports login event trigger exception
Hello to all the readers,
I'm trying to build a login event trigger in conjonction with a multi host connectio string
to manager instances roles in a logical replication cluster.
I'm surprised by the result found with (pg client 16+ on windows and linux),
here is my test case:
--on XX server
--create a LOGIN EVENT TRIGGER
--psql
CREATE OR REPLACE FUNCTION init_session()
RETURNS event_trigger SECURITY DEFINER
LANGUAGE plpgsql AS
$$
BEGIN
IF session_user = 'aaaaa' THEN
RAISE EXCEPTION 'Login forbidden';
END IF;
END;
$$;
-- trigger definition
CREATE EVENT TRIGGER init_session
ON login
EXECUTE FUNCTION init_session();
ALTER EVENT TRIGGER init_session ENABLE ALWAYS;
No trigger on YY server
--on ZZ server
set PGPASSWORD=pass
psql -d "host=XX,YY dbname=postgres port=5432 user=aaaaa"
psql: error: connection to server at "XX" (xx.xx.xx.xx), port 5432 failed: FATAL: Login forbidden
CONTEXT: PL/pgSQL function init_session() line 4 at RAISE
** It exits without trying connection on YY host !?**
Similar connection string on JDBC work as I wish:
- try XX, with error
- then try YY server and succeed.
Did I miss something ?
Regards
PAscal
Re: does multi host connection string supports login event trigger exception
From
Pavel Luzanov
Date:
Hi, legrand!
According to docs:
If a connection is established successfully, but authentication fails, the remaining hosts in the list are not tried.
So, I think that this is impossible to use multiple hosts feature of libpq with on login trigger to skip some host.
On 19.06.2025 22:31, legrand legrand wrote:
P {margin-top:0;margin-bottom:0;} I'm trying to build a login event trigger in conjonction with a multi host connectio stringto manager instances roles in a logical replication cluster.
According to docs:
If a connection is established successfully, but authentication fails, the remaining hosts in the list are not tried.
So, I think that this is impossible to use multiple hosts feature of libpq with on login trigger to skip some host.
-- Pavel Luzanov Postgres Professional: https://postgrespro.com
Re : does multi host connection string supports login event trigger exception
From
legrand legrand
Date:
Hi, Pavel !
Thank you for this fast and detailled answer !!
As LOGIN trigger is not working with multi host connections, I’ll have to find an other solution (maybe overloading pg_is_in_recovery() function)
Have a good day :0)
Regards
PAscal