We are developing application in Microsoft Access linked via ODBC with PostgreSQL 8.2 database. The ODBC Data Source is configured to login using single user and password for all users logining to our application in MS.
Now. We need to check out if user that trying to login has valid user and password in PostgreSQL database. We are asking tables pg_authid and/or pg_shadow for user names and passwords. Passwords in those tables are stored in md5. We have following statement to compare password hash with password typed by user:
SELECT 1
FROM pg_authid
WHERE rolename='<typed_user_name>'
AND rolpassword='md5'||md5('<typed_user_password>');
But hash generated this way is different than password hash stored in pg_authid.
What we are doing wrong ? Is there any possibility to checkout passwords in pg_authid table ?
P.S. Sorry for my awfull english.
s/rolename/rolname/
The password isn't hashed on its own; it's salted with the username, so you'd really want:
