<div dir="ltr">Hi Tatsuo<div><br></div><div>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.</div><div><br></div><div>The configurations files look like this:</div><div><font face="monospace"><b>[postgres@buda pgpool-II]$ vi pgpool.conf</b><br>#------------------------------------------------------------------------------<br># STREAMING REPLICATION MODE<br>#------------------------------------------------------------------------------<br># - Streaming -<br>sr_check_period = 10<br>sr_check_user = 'postgres'<br>sr_check_password = ''<br>sr_check_database = 'postgres'<br><br># - Authentication -<br>enable_pool_hba = on<br>pool_passwd = 'pool_passwd'<br><br>#------------------------------------------------------------------------------<br># HEALTH CHECK GLOBAL PARAMETERS<br>#------------------------------------------------------------------------------<br>health_check_period = 10<br>health_check_timeout = 20<br>health_check_user = 'postgres'<br>health_check_password = ''<br>health_check_database = 'postgres'<br><br></font><div><font face="monospace"><b>[postgres@buda pgpool-II]$ cat pool_hba.conf<br></b># "local" is for Unix domain socket connections only<br>local all all scram-sha-256<br># IPv4 local connections:<br>host all all <a href="http://127.0.0.1/32">127.0.0.1/32</a> trust<br>host all all ::1/128 trust<br>host all all <a href="http://192.168.1.0/24">192.168.1.0/24</a> scram-sha-256</font><br></div><div><br></div><font face="monospace"><b>[postgres@buda pgpool-II]$ cat pool_passwd </b><br>replicador:AESKXF6Ksr76jXd82/nyf7HPw==<br>postgres:AESSW1V5WTuP1xf1xFenoLDhA==<br><br></font></div><div><font face="arial, sans-serif">The pool_passwd content was generated executing: </font>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</div><div><br></div><div>Postgres primary and secondary servers are working with scram-sha-256 auth method.</div><div><br></div><div>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)..</div><div><br></div><div><font face="monospace"><b>[postgres@buda ~]$ psql -p 9999</b><br>Contraseña para usuario <b><font color="#ff0000">postgres</font></b>: <br>psql: error: falló la conexión al servidor en el socket «/run/postgresql/.s.PGSQL.9999»: ERROR: invalid authentication packet from backend<br>DETALLE: failed to get the authentication packet length<br>SUGERENCIA: This is likely caused by the inconsistency of auth method among DB nodes. </font></div><div><font face="monospace">Please check the previous error messages (hint: length field) from pool_read_message_length and recheck the pg_hba.conf settings.<br></font></div><div><br></div><div><font face="arial, sans-serif">I presume <b style="color:rgb(255,0,0)">postgres </b><font color="#000000">is the user declared in pcp.conf wich password is md5</font></font></div><div><font face="arial, sans-serif"><font color="#000000"><br></font></font></div><div><font face="monospace"><b>[postgres@buda pgpool-II]$ cat pcp.conf</b><br># USERID:MD5PASSWD<br>postgres:ad9dfc895ce42200ba6e1127aacc7873</font><br></div><div><br></div><div>I don't know who is rejecting the authentication, pgpool or postgresql. Both primary and secondary have the same pg_hba.conf</div><div><font face="monospace">local all all scram-sha-256<br>host all all <a href="http://192.168.1.0/24">192.168.1.0/24</a> scram-sha-256<br># replication privilege.<br>local replication all trust<br>host replication all <a href="http://127.0.0.1/32">127.0.0.1/32</a> trust<br>host replication all ::1/128 trust<br>host replication replicador <a href="http://192.168.1.0/24">192.168.1.0/24</a> scram-sha-256</font></div><div><br></div><div>The log look like this</div><div><font face="monospace">2025-02-13 10:38:28.028: health_check pid 6518: LOG: process started<br>2025-02-13 10:38:28.039: main pid 6480: LOG: pgpool-II successfully started. version 4.5.5 (hotooriboshi)<br>2025-02-13 10:38:28.039: main pid 6480: LOG: node status[0]: 1<br>2025-02-13 10:38:28.039: main pid 6480: LOG: node status[1]: 2<br>2025-02-13 10:38:47.350: child pid 6497: LOG: unable to read message length<br>2025-02-13 10:38:47.350: child pid 6497: ERROR: invalid authentication packet from backend<br>2025-02-13 10:39:08.124: child pid 6512: LOG: unable to read message length<br>2025-02-13 10:39:08.124: child pid 6512: ERROR: invalid authentication packet from backend<br></font></div><div><br></div><div><span style="font-family:arial,sans-serif"> Thank you very much for the support and the patiente</span></div><div><span style="font-family:arial,sans-serif"><br></span></div><div><span style="font-family:arial,sans-serif">kind regards</span></div><div><span style="font-family:arial,sans-serif"><br></span></div><div><span style="font-family:arial,sans-serif">Mauricio Fernández</span></div></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">El jue, 13 feb 2025 a las 9:24, Tatsuo Ishii (<<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>>) escribió:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Mauricio,<br>
<br>
> Hi Tatsuo..<br>
> <br>
> Waiting for your answer, I was investigating a little and found what you<br>
> are saying.<br>
> <br>
> I've configured /etc/pgpool-II/pcp.conf in the following way:<br>
> <br>
> [postgres@buda pgpool-II]$ pg_md5 -p<br>
> password: posgres_password<br>
> ad9dfc895ce42200ba6e1127aacc7873<br>
> <br>
> [postgres@buda pgpool-II]$cat pcp.conf<br>
> postgres:ad9dfc895ce42200ba6e1127aacc7873<br>
> <br>
> Configured pcp, pcp_attach_node could do their work<br>
> <br>
> [postgres@buda pgpool-II] pcp_attach_node -p 9898 -h localhost -n 1 -U<br>
> postgres -W<br>
> And now, pgpool every time start can see the two nodes<br>
<br>
Great!<br>
<br>
> But the problem now is I can't connect to to pgppool<br>
> <br>
> [postgres@buda ~]$ psql -p 9999 -U postgres<br>
> psql: error: failed the conection to the server in the socket<br>
> «/run/postgresql/.s.PGSQL.9999»: ERROR: invalid authentication packet from<br>
> backend<br>
> DETALLE: failed to get the authentication packet length<br>
> SUGERENCIA: This is likely caused by the inconsistency of auth method<br>
> among DB nodes.Please check the previous error messages (hint: length<br>
> field) from<br>
> pool_read_message_length and recheck the pg_hba.conf settings.<br>
> <br>
> I presume there ir a conflict between md5 and scram-sha-256<br>
<br>
In this case you need to configure pool_hba.conf and pool_passwd.<br>
<br>
pool_hba.conf should have lines something like:<br>
<br>
local all foo md5<br>
<br>
<a href="https://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html</a><br>
<br>
If you want to avoid using pool_passwd and pool_hba.conf, you can use<br>
allow_clear_text_frontend_auth:<br>
<br>
<a href="https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH</a><br>
<br>
But as the name suggests, a clear text password will fly over between<br>
client and pgpool. If you go with this, it is strongly recommended to<br>
use SSL between the client and pgpool.<br>
<br>
> The file pg_hba.conf look like this:<br>
> <br>
> # TYPE DATABASE USER ADDRESS METHOD<br>
> <br>
> # "local" is for Unix domain socket connections only<br>
> local all all<br>
> scram-sha-256<br>
> <br>
> # IPv4 local connections:<br>
> host all all <a href="http://192.168.1.0/24" rel="noreferrer" target="_blank">192.168.1.0/24</a><br>
> scram-sha-256<br>
> <br>
> # IPv6 local connections:<br>
> host all all ::1/128 trust<br>
> host all all <a href="http://192.168.1.0/24" rel="noreferrer" target="_blank">192.168.1.0/24</a> trust<br>
> <br>
> # Allow replication connections from localhost, by a user with the<br>
> # replication privilege.<br>
> local replication all trust<br>
> host replication all <a href="http://127.0.0.1/32" rel="noreferrer" target="_blank">127.0.0.1/32</a> trust<br>
> host replication all ::1/128 trust<br>
> host replication replicador <a href="http://192.168.1.0/24" rel="noreferrer" target="_blank">192.168.1.0/24</a> scram-sha-256<br>
> <br>
> I'm not using the file pool_passwd because XXX_check_password are set<br>
> in /etc/pgpool-II/pgpool.conf<br>
> <br>
> The encrypted password in postgresql is<br>
> <br>
> [postgres@buda ~]$ psql -p 5434 -U postgres<br>
> Contraseña para usuario postgres:<br>
> psql (17.2)<br>
> Digite «help» para obtener ayuda.<br>
> <br>
> postgres=# select passwd from pg_shadow where usename = 'postgres';<br>
> <br>
> passwd<br>
> <br>
> ------------------------------------------------------------------------------------------------------------------------------------<br>
> ---<br>
> SCRAM-SHA-256$4096:w+Mtz/vy4Ily+fhUXjMLKw==$JtG156jWegalB3lYU2USg4S/IIw9xEOk7S7HLqbE4ec=:thnRnO4N0sb5Boh5Mxz4wRAMzfjvCJ+cRV8clii+oP<br>
> g=<br>
> (1 fila)<br>
> <br>
> postgres=#<br>
> <br>
> When I manually generate a password encrypted AES256, the result doesn't<br>
> resemble de password in the database<br>
> <br>
> [postgres@buda ~]$ pg_enc -p -k /var/lib/pgsql/.pgpoolkey<br>
> db password:<br>
> trying to read key from file /var/lib/pgsql/.pgpoolkey<br>
> <br>
> SW1V5WTuP1xf1xFenoLDhA==<br>
> pool_passwd string: AESSW1V5WTuP1xf1xFenoLDhA==<br>
<br>
SCRAM-SHA-256 and AES256 are totally different encrytions and the<br>
ecrypted text are necessarily same.<br>
<br>
> Anyway, the summary is :<br>
> <br>
> <br>
> - pgpool is watching two nodes<br>
> - I can't communicate with pgpool.<br>
> <br>
> <br>
> Thank you very much for the support<br>
> <br>
> Kind Regards<br>
> <br>
> Mauricio Fernández<br>
> <br>
> <br>
> El mié, 12 feb 2025 a las 22:42, Tatsuo Ishii (<<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>)<br>
> escribió:<br>
> <br>
>> Hello Mauricio,<br>
>><br>
>> > Hola Tatsuo<br>
>> ><br>
>> > I've tried the command<br>
>> ><br>
>> > [postgres@buda pgpool-II]$ pcp_attach_node -p 9898 -h localhost 1<br>
>> > FATAL: authentication failed for user "postgres"<br>
>> > DETAIL: username and/or password does not match<br>
>><br>
>> Have you created pcp_conf? pcp commands require pcp_conf for their<br>
>> authentication (separate from pgpool.conf or pool_passwd).<br>
>><br>
>> <a href="https://www.pgpool.net/docs/latest/en/html/configuring-pcp-conf.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/latest/en/html/configuring-pcp-conf.html</a><br>
>><br>
>> > May be this authentication error is the source problem at all..<br>
>> ><br>
>> > Right now, the pgpool.conf setting have the following:<br>
>> ><br>
>> > sr_check_period = 10<br>
>> > sr_check_user = 'postgres'<br>
>> > sr_check_password = 'the_postgres_passw'<br>
>> ><br>
>> > health_check_user = 'postgres'<br>
>> > health_check_password = ' the_postgres_passw'<br>
>> > health_check_database = 'postgres'<br>
>> ><br>
>> > Initially I had let the passwords parameters empty '' and set the<br>
>> passwords<br>
>> > through<br>
>> ><br>
>> > pg_enc -m -f /etc/pgpool-II/pgpool.conf -u postgres -k<br>
>> > /var/lib/pgsql/.pgpoolkey<br>
>> ><br>
>> > [postgres@buda pgpool-II]$ cat pool_passwd<br>
>> > postgres:AESSW1V5WTuP1xf1xFenoLDhA==<br>
>> ><br>
>> > Obviously didn't work...<br>
>> ><br>
>> > I'm assuming one of the check users (sr or health ) is used by pgpool.<br>
>><br>
>> This should work. What kind of errors do you see in pgpool.log?<br>
>><br>
>> Best reagards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS K.K.<br>
>> English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
</blockquote></div>