Thread: Question on MD5 authentication

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"

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