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

[Tatsuo Ishii]

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
>>