I did it as:
CREATE FUNCTION login(auid TEXT, password TEXT, dbhost TEXT DEFAULT NULL) RETURNS ...
LANGUAGE PLPGSQL AS
failed BOOLEAN := FALSE;
SELECT u.uid AS role,u.my_role,u.display_name INTO rec FROM ldap_users u
WHERE u.uid=auid AND u.my_role IS NOT NULL;
IF NOT FOUND THEN
RAISE EXCEPTION 'User not permitted to access TV'
USING HINT='No such user or it is not allowed to access TV';
IF dbhost IS NULL THEN
dbhost := FORMAT('%s', INET_SERVER_ADDR());
dbname := CURRENT_DATABASE();
conn_str = 'host=' || dbhost || ' dbname=' || dbname || ' user=' || auid || ' password=' || password;
conn_name := FORMAT('conn_%s', RANDOM());
PERFORM dblink_connect(conn_name, conn_str);
EXCEPTION WHEN OTHERS THEN failed := TRUE;
EXCEPTION WHEN OTHERS THEN NULL;
IF failed THEN
RAISE EXCEPTION 'User failed to login to the DB'
USING HINT='It seems the password is wrong';
Specific things are replaced with
... - they are not related to the question.
To use it you need
CREATE EXTENSION IF NOT EXISTS dblink; to enable the
login() function verifies the password of the user by connecting to itself (to the current host (if
NULL) and to the current database.
It was tested and I use it but it works fine only if the host has just one IP. Otherwise, it's not clean which IP will be returned from the