<div dir="ltr">Hi,<div class="gmail_quote"><div dir="ltr"><div class="gmail_quote"><div dir="ltr"><div><br></div><div>I am testing pgpool2 and have a setup of 2 postgres nodes, using streaming replication, and 1 pgpool2 node.<br></div><div><br></div><div>Postgres version 15.5</div><div>pgpool2 version 4.5.1</div><div>Both running on RH EL 8</div><div><br></div><div>pgpool2 is installed on a different node and the firewall ports are all open. <br></div><div><br></div><div>However, the connection to pgpool2 via port 9999 fails from the first postgres node fails with error:</div><div><br></div><div>psql -U postgres -h <hostname> -p 9999</div><div>Password for user postgres:<br>psql: error: connection to server at "<hostname_pgpool2>" (<ip_pgpool2_node>), port 9999 failed: ERROR: unable to read message kind<br>DETAIL: kind does not match between main(52) slot[1] (45)</div><div><br></div><div>pgpool log:</div><div>2024-01-15 10:58:01.092: child pid 59665: LOG: pool_read_kind: error message from 1 th backend:no pg_hba.conf entry for host "<ip>", user "postgres", database "postgres", no encryption<br>2024-01-15 10:58:01.092: child pid 59665: ERROR: unable to read message kind<br>2024-01-15 10:58:01.092: child pid 59665: DETAIL: kind does not match between main(52) slot[1] (45)<br>2024-01-15 10:58:01.977: main pid 59631: LOG: find_primary_node: make_persistent_db_connection_noerror failed on node 0</div><div><br></div><div>postgres log:</div><div>time=2024-01-15 10:58:15 CET, pid=2952802 db=postgres, usr=nobody, client=<hostname_pgpool2> , app=[unknown], line=1 :: FATAL: password authentication failed for user "nobody"<br>time=2024-01-15 10:58:15 CET, pid=2952802 db=postgres, usr=nobody, client=<
hostname_pgpool2
> , app=[unknown], line=2 :: DETAIL: Role "nobody" does not exist.<br> Connection matched pg_hba.conf line 112: "host all all <ip_pgpool2_node>/32 scram-sha-256"</div><div><br></div><div>I configured pgpool.conf as follows:</div><div><br></div><div>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_clustering_mode = 'streaming_replication'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">listen_addresses = '*'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">port = 9999<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">unix_socket_directories =
'/var/run/postgresql'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">pcp_listen_addresses = '*'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">pcp_port = 9898<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">pcp_socket_dir = '/var/run/postgresql'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_hostname0 = <hostname_postgres_node1><span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_port0 = 5432<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_weight0 = 1<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_data_directory0 =
'<path1>'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_application_name0 =
'<app_name1>'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_hostname1 = </span>
<span style="font-size:11pt;font-family:"Courier New"" lang="EN-US"><hostname_postgres_node2></span>
</p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_port1 = 5432<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_weight1 = 1<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_data_directory1 =
'<path2>'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">backend_application_name1 = '<app_name2>'<span></span></span></p>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US">pid_file_name =
'/var/run/pgpool/pgpool.pid'</span></p><p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:"Courier New"" lang="EN-US"><br></span></p><p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">
</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">enable_pool_hba = on<span></span></span></p>
<p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">pool_passwd = 'pool_passwd'<span></span></span></p>
</div><div><br></div><div>And generated a .<span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">pgpoolkey<span></span></span>
file:<br></div><div></div><div>
<p style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">su – postgres<span></span></span></p>
<p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">echo '<<i>postgres_password</i>>' > /var/lib/pgsql/.pgpoolkey<span></span></span></p>
<p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">chmod 600 ~/.pgpoolkey <br></span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US"><br></span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">
Then I generated an encrypted entry for
scram-sha-256 in pool_passwd:<br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US"></span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US"></span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">
<span style="font-size:10pt;font-family:"Courier New"" lang="EN-US">pg_enc -m -k /var/lib/pgsql/.pgpoolkey
-u postgres -p</span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US"><br></span></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">On the postgres database nodes the pg_hba.conf has appropiate entries for the pgpool2 node:</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">host all postgres <ip_address_pgpool2_node>/32 scram-sha-256<br>host all postgres <ip_address_pgpool2_node>/32 scram-sha-256</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">Do you have any idea, why the connection between pgpool2 and postgres database fails?</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">Why is it trying to authenticate with user nobody?</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">I also created a user nobody in the database, but the errors are still there.</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">Your help would be appreciated.<br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">Regards</p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif">Oliver<br></p><p class="MsoNormal" style="margin:0cm;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:10pt;font-family:"Courier New"" lang="EN-US"><span></span></span></p>
</div></div>
</div></div>
</div></div>