[pgpool-general: 9361] Re: pgpool 4.5.5 can't reach stand by server
Mauricio Fernandez
mmauricio.fernandez at gmail.com
Thu Feb 13 23:23:08 JST 2025
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
*[postgres at buda pgpool-II]$ cat pcp.conf*
# USERID:MD5PASSWD
postgres:ad9dfc895ce42200ba6e1127aacc7873
I don't know who is rejecting the authentication, pgpool or 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
The log look like this
2025-02-13 10:38:28.028: health_check pid 6518: LOG: process started
2025-02-13 10:38:28.039: main pid 6480: LOG: pgpool-II successfully
started. version 4.5.5 (hotooriboshi)
2025-02-13 10:38:28.039: main pid 6480: LOG: node status[0]: 1
2025-02-13 10:38:28.039: main pid 6480: LOG: node status[1]: 2
2025-02-13 10:38:47.350: child pid 6497: LOG: unable to read message length
2025-02-13 10:38:47.350: child pid 6497: ERROR: invalid authentication
packet from backend
2025-02-13 10:39:08.124: child pid 6512: LOG: unable to read message length
2025-02-13 10:39:08.124: child pid 6512: ERROR: invalid authentication
packet from backend
Thank you very much for the support and the patiente
kind regards
Mauricio Fernández
El jue, 13 feb 2025 a las 9:24, Tatsuo Ishii (<ishii at postgresql.org>)
escribió:
> Hi Mauricio,
>
> > Hi Tatsuo..
> >
> > Waiting for your answer, I was investigating a little and found what you
> > are saying.
> >
> > I've configured /etc/pgpool-II/pcp.conf in the following way:
> >
> > [postgres at buda pgpool-II]$ pg_md5 -p
> > password: posgres_password
> > ad9dfc895ce42200ba6e1127aacc7873
> >
> > [postgres at buda pgpool-II]$cat pcp.conf
> > postgres:ad9dfc895ce42200ba6e1127aacc7873
> >
> > Configured pcp, pcp_attach_node could do their work
> >
> > [postgres at buda pgpool-II] pcp_attach_node -p 9898 -h localhost -n 1 -U
> > postgres -W
> > And now, pgpool every time start can see the two nodes
>
> Great!
>
> > But the problem now is I can't connect to to pgppool
> >
> > [postgres at buda ~]$ psql -p 9999 -U postgres
> > psql: error: failed the conection to the server in the 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 there ir a conflict between md5 and scram-sha-256
>
> In this case you need to configure pool_hba.conf and pool_passwd.
>
> pool_hba.conf should have lines something like:
>
> local all foo md5
>
> https://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html
>
> If you want to avoid using pool_passwd and pool_hba.conf, you can use
> allow_clear_text_frontend_auth:
>
>
> https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH
>
> But as the name suggests, a clear text password will fly over between
> client and pgpool. If you go with this, it is strongly recommended to
> use SSL between the client and pgpool.
>
> > The file pg_hba.conf look like this:
> >
> > # TYPE DATABASE USER ADDRESS METHOD
> >
> > # "local" is for Unix domain socket connections only
> > local all all
> > scram-sha-256
> >
> > # IPv4 local connections:
> > host all all 192.168.1.0/24
> > scram-sha-256
> >
> > # IPv6 local connections:
> > host all all ::1/128 trust
> > host all all 192.168.1.0/24 trust
> >
> > # Allow replication connections from localhost, by a user with the
> > # 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
> >
> > I'm not using the file pool_passwd because XXX_check_password are set
> > in /etc/pgpool-II/pgpool.conf
> >
> > The encrypted password in postgresql is
> >
> > [postgres at buda ~]$ psql -p 5434 -U postgres
> > Contraseña para usuario postgres:
> > psql (17.2)
> > Digite «help» para obtener ayuda.
> >
> > postgres=# select passwd from pg_shadow where usename = 'postgres';
> >
> > passwd
> >
> >
> ------------------------------------------------------------------------------------------------------------------------------------
> > ---
> >
> SCRAM-SHA-256$4096:w+Mtz/vy4Ily+fhUXjMLKw==$JtG156jWegalB3lYU2USg4S/IIw9xEOk7S7HLqbE4ec=:thnRnO4N0sb5Boh5Mxz4wRAMzfjvCJ+cRV8clii+oP
> > g=
> > (1 fila)
> >
> > postgres=#
> >
> > When I manually generate a password encrypted AES256, the result doesn't
> > resemble de password in the database
> >
> > [postgres at buda ~]$ pg_enc -p -k /var/lib/pgsql/.pgpoolkey
> > db password:
> > trying to read key from file /var/lib/pgsql/.pgpoolkey
> >
> > SW1V5WTuP1xf1xFenoLDhA==
> > pool_passwd string: AESSW1V5WTuP1xf1xFenoLDhA==
>
> SCRAM-SHA-256 and AES256 are totally different encrytions and the
> ecrypted text are necessarily same.
>
> > Anyway, the summary is :
> >
> >
> > - pgpool is watching two nodes
> > - I can't communicate with pgpool.
> >
> >
> > Thank you very much for the support
> >
> > Kind Regards
> >
> > Mauricio Fernández
> >
> >
> > El mié, 12 feb 2025 a las 22:42, Tatsuo Ishii (<ishii at postgresql.org>)
> > escribió:
> >
> >> Hello Mauricio,
> >>
> >> > Hola Tatsuo
> >> >
> >> > I've tried the command
> >> >
> >> > [postgres at buda pgpool-II]$ pcp_attach_node -p 9898 -h localhost 1
> >> > FATAL: authentication failed for user "postgres"
> >> > DETAIL: username and/or password does not match
> >>
> >> Have you created pcp_conf? pcp commands require pcp_conf for their
> >> authentication (separate from pgpool.conf or pool_passwd).
> >>
> >> https://www.pgpool.net/docs/latest/en/html/configuring-pcp-conf.html
> >>
> >> > May be this authentication error is the source problem at all..
> >> >
> >> > Right now, the pgpool.conf setting have the following:
> >> >
> >> > sr_check_period = 10
> >> > sr_check_user = 'postgres'
> >> > sr_check_password = 'the_postgres_passw'
> >> >
> >> > health_check_user = 'postgres'
> >> > health_check_password = ' the_postgres_passw'
> >> > health_check_database = 'postgres'
> >> >
> >> > Initially I had let the passwords parameters empty '' and set the
> >> passwords
> >> > through
> >> >
> >> > pg_enc -m -f /etc/pgpool-II/pgpool.conf -u postgres -k
> >> > /var/lib/pgsql/.pgpoolkey
> >> >
> >> > [postgres at buda pgpool-II]$ cat pool_passwd
> >> > postgres:AESSW1V5WTuP1xf1xFenoLDhA==
> >> >
> >> > Obviously didn't work...
> >> >
> >> > I'm assuming one of the check users (sr or health ) is used by
> pgpool.
> >>
> >> This should work. What kind of errors do you see in pgpool.log?
> >>
> >> Best reagards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS K.K.
> >> English: http://www.sraoss.co.jp/index_en/
> >> Japanese:http://www.sraoss.co.jp
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250213/da01a697/attachment-0001.htm>
More information about the pgpool-general
mailing list