role passwords and md5()

Dear list,

I am trying to verify the password given by a user against the system
catalog. Since I need the password hash later on, I can not just use the
authentication mechanism for verification, but need to do this in SQL
statements.
Unfortunately, even if I set passwords to use MD5 encryption in
pg_hba.conf, the SQL function MD5() returns a different hash.

A (shortened) example:
CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password';

SELECT * FROM pg_authid
WHERE rolname='my_user' AND rolpassword=MD5('my_password');

Any ideas, what to do to make this work?
Best regards,
Lutz Broedel

--
To verify the digital signature, you need to load the following certificate:
https://pki.pca.dfn.de/uh-ca/pub/cacert/rootcert.crt