Re: pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

On Tue, Sep 21, 2010 at 10:26 AM, Bryan Murphy <bmurphy1976(at)gmail(dot)com>wrote:

> On Mon, Sep 20, 2010 at 6:23 PM, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
>
>> I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5
>> auth. Your log seems to indicate that the password in pool_passwd and
>> the one in pg_shadow are not identical. Can you verify that?
>> The query result:
>>
>> select passwd from pg_shadow where usename = 'test';
>>
>> must be identical the password in pool_passwd for user test.
>
>
> Ah, that was it! I'll double check how I'm generating the passwords in
> pool_passwd, but once I copied the md5 from pg_shadow to pool_passwd it
> started working.
>
> Many thanks for your help!
>
> Bryan
>

I'm sorry, when I went back over to double check my steps I realized I ran
the wrong command. I am *still* having the problem. It appears that the
MD5 hashes now match, but it's still failing. I have postgres and pgpool
installed in /opt/postgresql, but I have the same problem when I put
pool_passwd in /etc and /opt/postgresql/etc.

psql -h 127.0.0.1 -p 9999 template1 test
Password for user test:
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

psql -h 10.240.163.95 template1 test
Password for user test:
psql (9.0.0)
Type "help" for help.

template1=# select usename, passwd from pg_shadow where usename = 'test';
usename | passwd
---------+-------------------------------------
test | md505a671c66aefea124cc08b76ea6d30bb
(1 row)

cat /opt/postgresql/etc/pool_passwd
test:05a671c66aefea124cc08b76ea6d30bb

cat /opt/postgresql/etc/pgpool.conf | grep hostname0
backend_hostname0 = '10.240.163.95'

pgpool log:
2010-09-21 15:36:26 LOG: pid 4661: connection received: host=127.0.0.1
port=41418
2010-09-21 15:36:26 DEBUG: pid 4661: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-21 15:36:26 DEBUG: pid 4661: new_connection: connecting 0 backend
2010-09-21 15:36:26 DEBUG: pid 4661: pool_ssl: SSL requested but SSL support
is not available
2010-09-21 15:36:26 DEBUG: pid 4661: pool_read_message_length: slot: 0
length: 12
2010-09-21 15:36:26 DEBUG: pid 4661: pool_do_auth: auth kind:5
2010-09-21 15:36:26 DEBUG: pid 4661: trying md5 authentication
2010-09-21 15:36:26 DEBUG: pid 4661: DB node id: 0 salt: fa1f213a
2010-09-21 15:36:26 DEBUG: pid 4661: read_password_packet_password: failed
to read password packet "p"
2010-09-21 15:36:26 ERROR: pid 4661: do_md5: read_password_packet failed
2010-09-21 15:36:26 DEBUG: pid 4661: do_md5failed in slot 0
2010-09-21 15:36:27 DEBUG: pid 4717: I am 4717 accept fd 6
2010-09-21 15:36:27 LOG: pid 4717: connection received: host=127.0.0.1
port=41420
2010-09-21 15:36:27 DEBUG: pid 4717: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-21 15:36:27 DEBUG: pid 4717: new_connection: connecting 0 backend
2010-09-21 15:36:27 DEBUG: pid 4717: pool_ssl: SSL requested but SSL support
is not available
2010-09-21 15:36:27 DEBUG: pid 4717: pool_read_message_length: slot: 0
length: 12
2010-09-21 15:36:27 DEBUG: pid 4717: pool_do_auth: auth kind:5
2010-09-21 15:36:27 DEBUG: pid 4717: trying md5 authentication
2010-09-21 15:36:27 DEBUG: pid 4717: DB node id: 0 salt: 1c8e5961
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler called
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: call wait3
2010-09-21 15:36:27 ERROR: pid 4654: Child process 4717 was terminated by
segmentation fault
2010-09-21 15:36:27 DEBUG: pid 4654: child 4717 exits with status 11 by
signal 11
2010-09-21 15:36:27 DEBUG: pid 4654: fork a new child pid 4722
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: normally exited
2010-09-21 15:36:27 DEBUG: pid 4722: I am 4722