Postgresql – Any way to verify user password from PL/PGSQL

plpgsqlpostgresql

I need to verify user password, but this user may be "local" (with md5 method in pg_hba.conf) or LDAP user. This works fine and "local" and LDAP users can login, but I need to verify their password in PL/PGSQL in some function. Is it possible? To call some Postgres system function like verify_login(userName TEXT, userPassword TEXT) which returns BOOLEAN and it knows how to check the password of the user when it is local/LDAP/etc. user.

PS. Little bit strange idea, but it's the requirement of my task.

Best Answer

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().

Related Question