Re: Comparing md5 hash with md5 password hash

2011/3/2 Michał Koba <toyman(at)poczta(dot)fm>

> Hi
>
> 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:

rolpassword = 'md5' || md5('password' || rolname);

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935