Pgpool-II authentication error

Question

I am using pgpool-II 3.2.1 from two PostgreSQL 9.1 configured as standby standby.

When I try to authenticate with 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 the logs change with each request, so it really does not make sense to compare with the one that I installed in / usr / local / etc / pool _passwd and in / usr / local / etc / pcp.conf

Can you help me identify a configuration problem?

Answer 1

It depends on how you use "pg_md5" ... if you do not include the username, then it will generate the wrong MD5, no doubt used in salt:

 root:/usr/local/etc# pg_md5 password1 7c6a180b36896a0a8c02787eeafb0e4c 

But if you use the correct syntax, then you will automatically enter the correct entry in the pool_password file:

 root:/usr/local/etc# pg_md5 -m -u greg password1 root@1 :/usr/local/etc# grep greg: pool_passwd greg:md53c5b45b54c89f8af9074a2ad371e2c12 

Answer 2

Problem detected!

pg_md5 , which comes with pgpool, generates the wrong md5 (when I used it). After I replaced it in the md5 config from PostgreSQL DB, it worked. You can get the md5 DB by running the following SQL query:

 select passwd from pg_shadow where usename = 'username';