I did it as:
CREATE FUNCTION login(auid TEXT, password TEXT, dbhost TEXT DEFAULT NULL) RETURNS ...
LANGUAGE PLPGSQL AS
$$
DECLARE
rec RECORD;
conn_str TEXT;
dbname TEXT;
conn_name TEXT;
failed BOOLEAN := FALSE;
BEGIN
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';
END IF;
IF dbhost IS NULL THEN
dbhost := FORMAT('%s', INET_SERVER_ADDR());
END IF;
dbname := CURRENT_DATABASE();
conn_str = 'host=' || dbhost || ' dbname=' || dbname || ' user=' || auid || ' password=' || password;
conn_name := FORMAT('conn_%s', RANDOM());
BEGIN
PERFORM dblink_connect(conn_name, conn_str);
EXCEPTION WHEN OTHERS THEN failed := TRUE;
END;
BEGIN
PERFORM dblink_disconnect(conn_name);
EXCEPTION WHEN OTHERS THEN NULL;
END;
IF failed THEN
RAISE EXCEPTION 'User failed to login to the DB'
USING HINT='It seems the password is wrong';
ELSE
...
RETURN ...
END IF;
END;
$$;
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 dblink
extension.
This login()
function verifies the password of the user by connecting to itself (to the current host (if dbhost
is 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 INET_SERVER_ADDR()
.