[pgpool-general: 9362] Re: pgpool 4.5.5 can't reach stand by server

[Tatsuo Ishii]

Hi Mauricio,

> Hi Tatsuo
> 
> I'm thinking I'm really near to success. I've decided to use pool_password
> and it works, because pgpool watches the two nodes and I guess
> health checking is working because there is nothing in the log about it.
> 
> The configurations files look like this:
> *[postgres at buda pgpool-II]$ vi pgpool.conf*
> #------------------------------------------------------------------------------
> # STREAMING REPLICATION MODE
> #------------------------------------------------------------------------------
> # - Streaming -
> sr_check_period = 10
> sr_check_user = 'postgres'
> sr_check_password = ''
> sr_check_database = 'postgres'
> 
> # - Authentication -
> enable_pool_hba = on
> pool_passwd = 'pool_passwd'
> 
> #------------------------------------------------------------------------------
> # HEALTH CHECK GLOBAL PARAMETERS
> #------------------------------------------------------------------------------
> health_check_period = 10
> health_check_timeout = 20
> health_check_user = 'postgres'
> health_check_password = ''
> health_check_database = 'postgres'
> 
> 
> *[postgres at buda pgpool-II]$ cat pool_hba.conf*# "local" is for Unix domain
> socket connections only
> local   all         all                               scram-sha-256
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          trust
> host    all         all         ::1/128               trust
> host    all         all         192.168.1.0/24        scram-sha-256
> 
> *[postgres at buda pgpool-II]$ cat pool_passwd *
> replicador:AESKXF6Ksr76jXd82/nyf7HPw==
> postgres:AESSW1V5WTuP1xf1xFenoLDhA==
> 
> The pool_passwd content was generated executing: pg_enc -m -f
> /etc/pgpool-II/pgpool.conf -k /var/lib/pgsql/.pgpoolkey -i
> /etc/pgpool-II/usr.txt and I have defined environment
> variable PGPOOLKEYFILE=/var/lib/pgsql/.pgpoolkey
> 
> Postgres primary and secondary servers are working with scram-sha-256 auth
> method.
> 
> Know, when I'm trying to connect to pgpool at least ask for password (pcp
> user postgres and postgres database user have the same password)..
> 
> *[postgres at buda ~]$ psql -p 9999*
> Contraseña para usuario *postgres*:
> psql: error: falló la conexión al servidor en el socket
> «/run/postgresql/.s.PGSQL.9999»: ERROR:  invalid authentication packet from
> backend
> DETALLE:  failed to get the authentication packet length
> SUGERENCIA:  This is likely caused by the inconsistency of auth method
> among DB nodes.
> Please check the previous error messages (hint: length field) from
> pool_read_message_length and recheck the pg_hba.conf settings.
> 
> I presume *postgres *is the user declared in pcp.conf wich password is md5

The password in pcp.conf is only used for using pcp commands. They are
irrelevant to PostgreSQL authentication.

> *[postgres at buda pgpool-II]$ cat pcp.conf*
> # USERID:MD5PASSWD
> postgres:ad9dfc895ce42200ba6e1127aacc7873
> 
> I don't know who is rejecting the authentication, pgpool or postgresql.

From the log it must be postgresql.

> Both primary and secondary have the same pg_hba.conf
> local   all             all
> scram-sha-256
> host    all             all             192.168.1.0/24
>  scram-sha-256
> # replication privilege.
> local   replication     all                                     trust
> host    replication     all             127.0.0.1/32            trust
> host    replication     all             ::1/128                 trust
> host    replication     replicador      192.168.1.0/24        scram-sha-256

> BTW you seem to set backend_hostname0 = 'localhost', which is not
> recommended. You'd better to set backend_hostname0 = '192.169.0.160'
> instead.

Do you still set backend_hostname0 = 'localhost'? Then it could be the
cause of the problem. When backend0 receives the connection request
from pgpool, the client IP is 'localhost' from PostgreSQL's point of
view. So PostgreSQL looks into pg_hba.conf and fails to find matching
entry for IP = 127.0.0.1, database = postgres, user = postgres.

For standby, there's a matching entry in pg_hba.conf:

> host    all             all             192.168.1.0/24

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp