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

> I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
> I see references to "pool_passwd" in the pgpool documentation, but I see
> nothing indicating *where* this file should exist and how pgpool finds it.
> I've set my accounts up in pcp.conf, however, I do not believe this is what
> is expected.

Sorry for not enough description about pool_passwd. It's located under
the same directory as pgpool.conf. So the default is
/usr/local/etc/pool_passwd.

You need to create /usr/local/etc/pool_passwd if the uid to run pgpool
server does not have the right to create a new file under
/usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd
owned by the same uid as pgpool. After that you should be able to
register your password as decribed in the doc.

Authentication / Access Controls
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> When I try to access the database with md5 turned on, I see the following
> error in my pgpool debug log: read_password_packet_password: failed to read
> password packet "p"
>
> I can get everything working with trust.
>
> postgres log:
> *LOG: unexpected EOF on client connection*
> LOG: disconnection: session time: 0:00:00.007 user=test database=test
> host=10.201.121.204 port=42812
>
> psql output:
> psql -h 127.0.0.1 -p 9999 test test
> Password for user test:
> *psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> *
> pgpool debug log:
> 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:51 LOG: pid 6633: connection received: host=127.0.0.1
> port=48336
> 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
> *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
> to read password packet "p"
> 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
> *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
> 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:53 LOG: pid 6633: connection received: host=127.0.0.1
> port=48338
> 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
> *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
> segmentation fault
> 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
> signal 11
> *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited
>
> pg_hba.conf:
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust
> host all all 0.0.0.0/0 md5
>
> pool_hba.conf:
> local all all trust
> host all all 0.0.0.0/0
> md5
>
> pgpool.conf:
> listen_addresses = '*'
> port = 9999
> pcp_port = 9898
> socket_dir = '/srv/pgpool/run'
> pcp_socket_dir = '/srv/pgpool/run'
> backend_socket_dir = '/srv/pgpool/run'
> pcp_timeout = 10
> num_init_children = 64
> max_pool = 4
> child_life_time = 300
> connection_life_time = 0
> child_max_connections = 0
> client_idle_limit = 0
> authentication_timeout = 60
> logdir = '/srv/pgpool/log'
> pid_file_name = '/srv/pgpool/run/pgpool.pid'
> replication_mode = false
> load_balance_mode = false
> replication_stop_on_mismatch = false
> failover_if_affected_tuples_mismatch = false
> replicate_select = false
> reset_query_list = 'ABORT; DISCARD ALL'
> white_function_list = ''
> black_function_list = 'nextval,setval'
> print_timestamp = true
> master_slave_mode = false
> master_slave_sub_mode = 'slony'
> delay_threshold = 0
> log_standby_delay = 'none'
> connection_cache = true
> health_check_timeout = 20
> health_check_period = 0
> health_check_user = 'nobody'
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = true
> insert_lock = true
> ignore_leading_white_space = true
> log_statement = false
> log_per_node_statement = false
> log_connections = true
> log_hostname = false
> parallel_mode = false
> enable_query_cache = false
> pgpool2_hostname = ''
> system_db_hostname = 'localhost'
> system_db_port = 5432
> system_db_dbname = 'pgpool'
> system_db_schema = 'pgpool_catalog'
> system_db_user = 'pgpool'
> system_db_password = ''
> backend_hostname0 = '10.215.74.98'
> backend_port0 = 5432
> backend_weight0 = 1
> *enable_pool_hba = true
> *recovery_user = 'nobody'
> recovery_password = ''
> recovery_1st_stage_command = ''
> recovery_2nd_stage_command = ''
> recovery_timeout = 90
> client_idle_limit_in_recovery = 0
> lobj_lock_table = ''
> ssl = false
> debug_level = 100