pgpool-II authentication failure
I am using pgpool-II 3.2.1 in from of two PostgreSQL 9.1 configured as master-hot standby.
When trying to authenticate from PGAdmin I get:
Error connecting to the server: FATAL: password authentication failed for user "postgres"
In pgpool logs I see:
2012-11-01 14:40:13 LOG: pid 3023: connection received: host=89.xxx.xxx.xxx port=57150
2012-11-01 14:40:13 DEBUG: pid 3023: Protocol Major: 1234 Minor: 5679 database: user:
2012-11-01 14:40:13 DEBUG: pid 3023: SSLRequest from client
2012-11-01 14:40:13 DEBUG: pid 3023: pool_ssl: SSL requested but SSL support is not available
2012-11-01 14:40:13 DEBUG: pid 3023: read_startup_packet: application_name: pgAdmin III - Browser
2012-11-01 14:40:13 DEBUG: pid 3023: Protocol Major: 3 Minor: 0 database: postgres user: postgres
2012-11-01 14:40:13 DEBUG: pid 3023: new_connection: connecting 0 backend
2012-11-01 14:40:13 DEBUG: pid 3023: new_connection: connecting 1 backend
2012-11-01 14:40:13 DEBUG: pid 3023: pool_ssl: SSL requested but SSL support is not available
2012-11-01 14:40:13 DEBUG: pid 3023: pool_ssl: SSL requested but SSL support is not available
2012-11-01 14:40:13 DEBUG: pid 3023: pool_read_message_length: slot: 0 length: 12
2012-11-01 14:40:13 DEBUG: pid 3023: pool_read_message_length: slot: 1 length: 12
2012-11-01 14:40:13 DEBUG: pid 3023: pool_do_auth: auth kind:5
2012-11-01 14:40:13 DEBUG: pid 3023: trying md5 authentication
2012-11-01 14:40:13 DEBUG: pid 3023: password does not match: frontend:md5286c9c7c4ac8a365170e0f448c99ad48 pgpool:md5cf0e9615c13af6243edfd11d2461d12e
2012-11-01 14:40:13 DEBUG: pid 3023: do_md5failed in slot 0
What is really strange is that the md5 hashes from logs change with each request so it doesn't really make sense comparing with the one I set in both /usr/local/etc/pool_passwd and /usr/local/etc/pcp.conf
Can you help me spot the configuration issue?
Answer
Found the problem!
pg_md5 which is shipped together with pgpool generated an incorrect md5 (when I used it). After I replaced it in the config with the md5 from the PostgreSQL DB it worked. You can get the DB md5 by running the following SQL query:
select passwd from pg_shadow where usename = 'username';