[Pgpool-general] parallel mode ERROR: password is required, DETAIL: Non-superuser cannot connect if the server does not request a password

Vlad Kosilov vkosilov at contigo.com
Thu Jun 26 00:27:28 UTC 2008 

Hello pgpool experts,
can you help with the following error:

I'm testing pgpool parallel mode, failing query:

$ psql -U postgres -h ds1pgp -p 9999 MONSOONAUDIT_SCALABILITY -c "select 
count(1) from tcp_audit;"
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a 
password.
HINT:  Target server's authentication method must be changed.

however this one works:

$ psql -U postgres -h ds1pgp -p 9999 MONSOONAUDIT_SCALABILITY -c "select 
* from tcp_audit;"
   id   | beacon_id |                       
message                        | flow | timestamp  | unique_id | byte_count
--------+-----------+------------------------------------------------------+------+------------+-----------+------------
 366915 |     25327 | 
,49.28139,-123.129592,22,NW,343.233,1214358904,10000 | in   | 1214358906 
|           |         54

how do I change target server's authentication method ?

here is my pgpool.conf:

listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 90
max_pool = 1
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/tmp'
replication_mode = false
load_balance_mode = false
replication_stop_on_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
print_timestamp = true
master_slave_mode = false
connection_cache = false
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
insert_lock = false
ignore_leading_white_space = true
log_statement = true
log_connections = true
log_hostname = false
parallel_mode = true
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 = '192.168.0.111'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_hostname1 = '192.168.0.112'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90



and debug for the failed query, /usr/local/pgpool/bin/pgpool -d -n  2>&1
....
2008-06-25 17:02:37 DEBUG: pid 23205: I am 23205 accept fd 6
2008-06-25 17:02:37 LOG:   pid 23205: connection received: 
host=192.168.2.11 port=41677
2008-06-25 17:02:37 DEBUG: pid 23205: Protocol Major: 1234 Minor: 5679 
database:  user:
2008-06-25 17:02:37 DEBUG: pid 23205: SSLRequest: sent N; retry startup
2008-06-25 17:02:37 DEBUG: pid 23205: Protocol Major: 3 Minor: 0 
database: MONSOONAUDIT_SCALABILITY user: postgres
2008-06-25 17:02:37 DEBUG: pid 23205: new_connection: connecting 0 backend
2008-06-25 17:02:37 DEBUG: pid 23205: new_connection: connecting 1 backend
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length: slot: 0 
length: 8
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length: slot: 1 
length: 8
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 25pgpool.conf

2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: 
client_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: 
client_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 23
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 23
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: DateStyle 
value: ISO, MDY
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: DateStyle 
value: ISO, MDY
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 26
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 26
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: 
integer_datetimes value: off
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: 
integer_datetimes value: off
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 20
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_lengpgpool is 
run asth2: master slot: 1 length: 20
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: is_superuser 
value: on
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: is_superuser 
value: on
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 25
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: 
server_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: 
server_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 25
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: server_version 
value: 8.3.3
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: server_version 
value: 8.3.3
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 35
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 35
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: 
session_authorization value: postgres
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: 
session_authorization value: postgres
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 36
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 36
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: 
standard_conforming_strings value: off
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: 
standard_conforming_strings value: off
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 0 length: 28
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length2: master 
slot: 1 length: 28
2008-06-25 17:02:37 DEBUG: pid 23205: 0 th backend: name: TimeZone 
value: Canada/Pacific
2008-06-25 17:02:37 DEBUG: pid 23205: 1 th backend: name: TimeZone 
value: Canada/Pacific
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length: slot: 0 
length: 12
2008-06-25 17:02:37 DEBUG: pi
d 23205: pool_read_message_length: slot: 1 length: 12
2008-06-25 17:02:37 DEBUG: pid 23205: pool_send_auth_ok: send pid 9383 
to frontend
2008-06-25 17:02:37 DEBUG: pid 23205: read_kind_from_backend: read kind 
from 0 th backend Z NUM_BACKENDS: 2
2008-06-25 17:02:37 DEBUG: pid 23205: read_kind_from_backend: read kind 
from 1 th backend Z NUM_BACKENDS: 2
2008-06-25 17:02:37 DEBUG: pid 23205: pool_process_query: kind from 
backend: Z
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length: slot: 0 
length: 5
2008-06-25 17:02:37 DEBUG: pid 23205: pool_read_message_length: slot: 1 
length: 5
2008-06-25 17:02:37 DEBUG: pid 23205: ReadyForQuery: message length: 5
2008-06-25 17:02:37 DEBUG: pid 23205: ReadyForQuery: transaction state: I
2008-06-25 17:02:37 DEBUG: pid 23205: read kind from frontend Q(51)
2008-06-25 17:02:37 LOG:   pid 23205: statement: select count(1) from 
tcp_audit;
2008-06-25 17:02:37 DEBUG: pid 23205: initSelectStmt: ANALYZE now(0)
2008-06-25 17:02:37 DEBUG: pid 23205: inside build_range_info num= 1 
current_select=0
2008-06-25 17:02:37 DEBUG: pid 23205: inside build_range_info dist 0
2008-06-25 17:02:37 DEBUG: pid 23205: inside build_virtual_info dist 
state=P  tcp_audit
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=0,col=id,type=bigint,table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=1,col=beacon_id,type=bigint,table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=2,col=message,type=text,table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=3,col=flow,type=character varying(20),table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=4,col=timestamp,type=bigint,table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=5,col=unique_id,type=character 
varying(100),table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: append_virtual_table select=0, 
no=6,col=byte_count,type=bigint,table=tcp_audit,state=P,valid=-1
2008-06-25 17:02:37 DEBUG: pid 23205: AnalyzeReturnRecord: current_select=0
2008-06-25 17:02:37 DEBUG: pid 23205: append_select_def_info: base=0
2008-06-25 17:02:37 DEBUG: pid 23205: append_select_def_info: 
col=count,type=text base=1
2008-06-25 17:02:37 DEBUG: pid 23205: _rewriteSelectStmt select_no=0 
state=PSPPPPPP
2008-06-25 17:02:37 DEBUG: pid 23205: analyze_debug :select no(0), last 
select(-1), last_part(-1), state(S)
2008-06-25 17:02:37 DEBUG: pid 23205: _rewriteSelectStmt:START QueryRewrite
2008-06-25 17:02:37 DEBUG: pid 23205: _FuncCall: aggregate no = 0
2008-06-25 17:02:37 DEBUG: pid 23205: _rewriteResTarget: check(0) ret_num=0
2008-06-25 17:02:37 DEBUG: pid 23205: _rewriteResTarget: col ret_num=0
2008-06-25 17:02:37 DEBUG: pid 23205: _rewriteList select=0,count=0
2008-06-25 17:02:37 DEBUG: pid 23205: writeSelectHeader select_no=0 
state=0 output
2008-06-25 17:02:37 DEBUG: pid 23205: OneNode_do_command: Query:  SELECT 
sum(pool_g$0) AS count FROM dblink('host=ds1pgp 
dbname=MONSOONAUDIT_SCALABILITY port=9999 user=postgres','SELECT 
pool_parallel("SELECT count(1) FROM tcp_audit")') AS pool_t$0g (pool_g$0 
bigint )
2008-06-25 17:02:37 DEBUG: pid 23216: I am 23216 accept fd 6
2008-06-25 17:02:37 LOG:   pid 23216: connection received: 
host=192.168.0.171 port=36217
2008-06-25 17:02:37 DEBUG: pid 23216: Protocol Major: 3 Minor: 0 
database: MONSOONAUDIT_SCALABILITY user: postgres
2008-06-25 17:02:37 DEBUG: pid 23216: new_connection: connecting 0 backend
2008-06-25 17:02:37 DEBUG: pid 23216: new_connection: connecting 1 backend
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 0 
length: 8
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 1 
length: 8
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: 
client_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: 
client_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 23
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 23
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: DateStyle 
value: ISO, MDY
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: DateStyle 
value: ISO, MDY
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 26
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 26
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: 
integer_datetimes value: off
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: 
integer_datetimes value: off
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 20
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 20
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: is_superuser 
value: on
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: is_superuser 
value: on
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: 
server_encoding value: UTF8
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: 
server_encoding value: UTF8 output
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 25
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: server_version 
value: 8.3.3
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: server_version 
value: 8.3.3
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 35
2008-06-25 17:02:37 that failed DEBUG: pid 23216: 
pool_read_message_length2: master slot: 1 length: 35
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: 
session_authorization value: postgres
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: 
session_authorization value: postgres
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 36
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 36
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: 
standard_conforming_strings value: off
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: 
standard_conforming_strings value: off
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 0 length: 28
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length2: master 
slot: 1 length: 28
2008-06-25 17:02:37 DEBUG: pid 23216: 0 th backend: name: TimeZone 
value: Canada/Pacific
2008-06-25 17:02:37 DEBUG: pid 23216: 1 th backend: name: TimeZone 
value: Canada/Pacific
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 0 
length: 12
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 1 
length: 12
2008-06-25 17:02:37 DEBUG: pid 23216: pool_send_auth_ok: send pid 9384 
to frontend
2008-06-25 17:02:37 DEBUG: pid 23216: read_kind_from_backend: read kind 
from 0 th backend Z NUM_BACKENDS: 2
2008-06-25 17:02:37 DEBUG: pid 23216: read_kind_from_backend: read kind 
from 1 th backend Z NUM_BACKENDS: 2
2008-06-25 17:02:37 DEBUG: pid 23216: pool_process_query: kind from 
backend: Z
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 0 
length: 5
2008-06-25 17:02:37 DEBUG: pid 23216: pool_read_message_length: slot: 1 
length: 5
2008-06-25 17:02:37 DEBUG: pid 23216: ReadyForQuery: message length: 5
2008-06-25 17:02:37 DEBUG: pid 23216: ReadyForQuery: transaction state: I
2008-06-25 17:02:37 DEBUG: pid 23216: read kind from frontend X(58)
2008-06-25 17:02:37 DEBUG: pid 23205: pool_rewrite_stmt: XXX message_code 7
2008-06-25 17:02:37 DEBUG: pid 23205: pool_rewrite_stmt: XXX rule 705


Thanks,
Vlad

More information about the Pgpool-general mailing list