Question on MD5 authentication

On Thu, 2006-10-12 at 15:38 -0400, Wei Weng wrote:

I am trying to connect to machine A (192.168.1.155) from a different
machine B (192.168.1.180), with password transmitted as a MD5 string.

I have the following lines in my pg_hba.conf file.

host all all 192.168.1.180 255.255.255.1 md5

I created a database user "test_user":

create user test_user with password 'test_passwd';

In A's database, I have

select usename, passwd from pg_shadow;

usename | passwd
-----------+-------------------------------------
postgres |
pgbench |
test_user | md5c573460a3b356e4610bfae406e1d8a9f

Then I try to connect to A from B with the following:

psql -h 192.168.1.155 -U test_user template1
Password for user test_user: (I typed test_passwd)
psql: FATAL: password authentication failed for user "test_user"

D'oh, accidentally sent this email out without properly finishing it.

So my question is, how can I connect to A from B with a proper MD5-ed
authentication?

Thanks!

Wei

Wei Weng <wweng@kencast.com> writes:

I have the following lines in my pg_hba.conf file.
host all all 192.168.1.180 255.255.255.1 md5

Not relevant to your immediate problem, but: you almost certainly
want 255.255.255.255 as the netmask here.

psql -h 192.168.1.155 -U test_user template1
Password for user test_user: (I typed test_passwd)
psql: FATAL: password authentication failed for user "test_user"

Did you look in the postmaster log to see if there were any more
details?

regards, tom lane

On Thu, 2006-10-12 at 15:50 -0400, Tom Lane wrote:

Wei Weng <wweng@kencast.com> writes:

I have the following lines in my pg_hba.conf file.
host all all 192.168.1.180 255.255.255.1 md5

Not relevant to your immediate problem, but: you almost certainly
want 255.255.255.255 as the netmask here.

psql -h 192.168.1.155 -U test_user template1
Password for user test_user: (I typed test_passwd)
psql: FATAL: password authentication failed for user "test_user"

Did you look in the postmaster log to see if there were any more
details?

Here is the relevant information in my postmaster-Thu.log.

FATAL: password authentication failed for user "test_user"

Doesn't say much.

Thanks.

Wei

I think I have found out something suspicious.

I used tcpdump to monitor the traffic to and from port 5432, and it
seems that the password the client on A sends out to the postmaster on B
is

"md54570471eccef21ae3c6e43033d8d2f66"

While the MD5-ed password stored in system catalog (pg_shadow) is

"md5c573460a3b356e4610bfae406e1d8a9f"

And a MD5 string generated by md5 function in postgresql is:

template1=# select md5('test_passwd');
md5
----------------------------------
daac2bc8c6fe94375b59efb7b3effd33
(1 row)

(As you can see, all 3 strings are different)

Why the difference? Is there something missing ??

Thanks

Wei

Wei Weng <wweng@kencast.com> writes:

(As you can see, all 3 strings are different)
Why the difference? Is there something missing ??

Well, the password is actually supposed to be 'md5'||md5(passwd||user),
thus:

regression=# select md5('test_passwd' || 'test_user');
md5
----------------------------------
c573460a3b356e4610bfae406e1d8a9f
(1 row)

So either you are mistyping the password at the client end, or there's
something broken about the client-side code.

regards, tom lane