Lutz Broedel wrote:
> 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
A quick look at the source shows that the hashed value stored in
pg_authid uses the role name as a salt for the hashing of the password.
Moreover, the value in pg_authid has the string "md5" prepended to the
hash value (I imagine to allow different hash algorithms to be used, but
I haven't personally seen anything but "md5").
Given your example above, the following statement should do what you are
looking for:
SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5'
|| md5('my_password' || 'my_user');
Hope this helps.
Andrew