[pgpool-general: 8995] Errors when starting and connecting pgpool2

[Dieter Pillefatz]

Hi,

I am testing pgpool2 and have a setup of 2 postgres nodes, using streaming
replication, and 1 pgpool2 node.

Postgres version 15.5
pgpool2 version 4.5.1
Both running on RH EL 8

pgpool2 is installed on a different node and the firewall ports are all
open.

However, the connection to pgpool2 via port 9999 fails from the first
postgres node fails with error:

psql -U postgres -h <hostname> -p 9999
Password for user postgres:
psql: error: connection to server at "<hostname_pgpool2>"
(<ip_pgpool2_node>), port 9999 failed: ERROR:  unable to read message kind
DETAIL:  kind does not match between main(52) slot[1] (45)

pgpool log:
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
2024-01-15 10:58:01.092: child pid 59665: ERROR:  unable to read message
kind
2024-01-15 10:58:01.092: child pid 59665: DETAIL:  kind does not match
between main(52) slot[1] (45)
2024-01-15 10:58:01.977: main pid 59631: LOG:  find_primary_node:
make_persistent_db_connection_noerror failed on node 0

postgres log:
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"
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.
        Connection matched pg_hba.conf line 112: "host  all     all
    <ip_pgpool2_node>/32       scram-sha-256"

I configured pgpool.conf as follows:

backend_clustering_mode = 'streaming_replication'

listen_addresses = '*'

port = 9999

unix_socket_directories = '/var/run/postgresql'

pcp_listen_addresses = '*'

pcp_port = 9898

pcp_socket_dir = '/var/run/postgresql'

backend_hostname0 = <hostname_postgres_node1>

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '<path1>'

backend_application_name0 = '<app_name1>'

backend_hostname1 =  <hostname_postgres_node2>

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '<path2>'

backend_application_name1 = '<app_name2>'

pid_file_name = '/var/run/pgpool/pgpool.pid'


enable_pool_hba = on

pool_passwd = 'pool_passwd'

And generated a .pgpoolkey file:

su – postgres

echo '<*postgres_password*>' > /var/lib/pgsql/.pgpoolkey

chmod 600 ~/.pgpoolkey


Then I generated an encrypted entry for scram-sha-256  in pool_passwd:

pg_enc -m -k /var/lib/pgsql/.pgpoolkey -u postgres -p


On the postgres database nodes the pg_hba.conf has appropiate entries for
the pgpool2 node:

host    all    postgres          <ip_address_pgpool2_node>/32
scram-sha-256
host    all    postgres          <ip_address_pgpool2_node>/32
scram-sha-256


Do you have any idea, why the connection between pgpool2 and postgres
database fails?

Why is it trying to authenticate with user nobody?

I also created a user nobody in the database, but the errors are still
there.


Your help would be appreciated.


Regards

Oliver
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240115/8f8f04f5/attachment.htm>