<div dir="ltr"><div>Seems my passwords were not encrypted correctly.</div><div>After doing:</div><div><br></div><div>ALTER USER pgpool WITH ENCRYPTED PASSWORD '*****';</div><div>ALTER USER postgres WITH ENCRYPTED PASSWORD '**';<br>ALTER USER repl WITH ENCRYPTED PASSWORD '*****';</div><div><br></div><div>
<span id="gmail-hs_cos_wrapper_post_body" class="gmail-hs_cos_wrapper gmail-hs_cos_wrapper_meta_field gmail-hs_cos_wrapper_type_rich_text"><pre class="gmail-language-sql"><code class="gmail-language-sql">postgres=# SELECT<br>postgres-# rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded<br>postgres-# FROM pg_authid<br>postgres-# WHERE rolcanlogin;<br> rolname | has_upgraded<br>----------+--------------<br> repl | t<br> pgpool | t<br> postgres | t<br><br></code></pre><pre class="gmail-language-sql"><code class="gmail-language-sql">Now they are correct and it can connect. Nevertheless, I'm having other issues. Here is the Backend Connection Settings
</code></pre></span><div style="box-sizing:border-box;font-family:"Segoe UI",system-ui,"Apple Color Emoji","Segoe UI Emoji",sans-serif;font-size:14px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><div># - Backend Connection Settings -</div>
<div>
<div> </div>
<div>
<div>backend_hostname0 = 'qs-auth-01.dc.internal'<br>
backend_port0 = 5432<br>
backend_weight0 = 1<br>
backend_data_directory0 = '/export/pgsql/data'<br>
backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
backend_application_name0 = 'qs-auth-01.dc.internal'</div>
<div>
<div> </div>
<div>
<div>backend_hostname1 = 'qs-auth-02.dc.
internal
'<br>
backend_port1 = 5432<br>
backend_weight1 = 1<br>
backend_data_directory1 = '/export/pgsql/data'<br>
backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
backend_application_name1 = 'qs-auth-02.dc.internal'</div><div><br></div><div><br></div><div>node
qs-auth-01.dc.internal should be the primary and has postgres running there, but pgpool seems to ignore it.</div><div>node
qs-auth-02.dc.internal is shutdown as I want to apply replication from node 1.
</div><div><br></div><div>Here is the log:<br></div><div><br></div><div>Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [915-1] 2020-12-17 09:18:32: pid 20471: ERROR: failed to make persistent db connection<br>Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [915-2] 2020-12-17 09:18:32: pid 20471: DETAIL: connection to host:"qs-auth-02.dc.internal:5432" failed<br>Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [916-1] 2020-12-17 09:18:32: pid 20471: LOG: health check retrying on DB node: 1 (round:2)<br>Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [917-1] 2020-12-17 09:18:33: pid 20471: LOG: failed to connect to PostgreSQL server on "qs-auth-02.dc.internal:5432", getsockopt() detected error "Connection refused"<br>Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [918-1] 2020-12-17 09:18:33: pid 20471: ERROR: failed to make persistent db connection<br>Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [918-2] 2020-12-17 09:18:33: pid 20471: DETAIL: connection to host:"qs-auth-02.dc.internal:5432" failed<br>Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [919-1] 2020-12-17 09:18:33: pid 20471: LOG: health check retrying on DB node: 1 (round:3)<br>Dec 17 09:18:34 qs-auth-02 pgpool[20471]: [920-1] 2020-12-17 09:18:34: pid 20471: LOG: failed to connect to PostgreSQL server on "qs-auth-02.dc.internal:5432", getsockopt() detected error "Connection refused"</div><div><br></div><div>I see nothing in the node
qs-auth-01.dc.internal postgres logs. Is there another place where pgpool looks to find postgres servers?</div><div><br></div><div>Details about pgpool version:<br></div><div>data]# yum list installed | grep pgpool<br>pgpool-II-pg11.x86_64 4.1.5-1pgdg.rhel7 @pgpool41<br>pgpool-II-pg11-extensions.x86_64 4.1.5-1pgdg.rhel7 @pgpool41<br>pgpool-II-release.noarch 4.1-2 @/pgpool-II-release-4.1-2.noarchtGN7Vc</div><div><br></div><div>Regards,</div><div>LA<br></div></div></div></div></div></div>
<span id="gmail-hs_cos_wrapper_post_body" class="gmail-hs_cos_wrapper gmail-hs_cos_wrapper_meta_field gmail-hs_cos_wrapper_type_rich_text"><pre class="gmail-language-sql"><code class="gmail-language-sql"><br><br><br><br><br></code></pre><pre class="gmail-language-sql"><code class="gmail-language-sql"><span class="gmail-token gmail-punctuation"></span>
</code></pre></span>
</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Dec 16, 2020 at 8:51 AM Luís Alves <<a href="mailto:luisalves00@gmail.com">luisalves00@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>I'm getting:<br></div><div><br></div><div>2020-12-16 08:21:59.766 GMT [502] LOG: connection received: host=10.230.4.147 port=34162<br>2020-12-16 08:21:59.774 GMT [502] FATAL: password authentication failed for user "pgpool"<br>2020-12-16 08:21:59.774 GMT [502] DETAIL: User "pgpool" does not have a valid SCRAM verifier.<br> Connection matched pg_hba.conf line 34: "host all all <a href="http://0.0.0.0/0" target="_blank">0.0.0.0/0</a> scram-sha-256"</div><div><br></div><div>(pg_hba.conf is quite permissive for now so I can access individual nodes directly)<br></div><div><br></div><div>I have:</div><div><br></div><div>postgres=# \du<br> List of roles<br> Role name | Attributes | Member of<br>-----------+------------------------------------------------------------+--------------<br> pgpool | | {pg_monitor}<br> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}<br> repl | Replication | {}<br></div><div><br></div><div><br></div><div>and</div><div><br></div><div><br></div><div># - Authentication -<br><br>#authentication_timeout = 1min # 1s-600s<br>#password_encryption = md5 # md5 or scram-sha-256<br>password_encryption = scram-sha-256<br>#db_user_namespace = off</div><div><br></div><div><br></div><div>Where should I look to provide the proper authentication?</div><div><br></div><div>Regards,</div><div>Luís Alves<br></div></div>
</blockquote></div>