[pgpool-general: 7362] Re: User "pgpool" does not have a valid SCRAM

[Luís Alves]

Seems my passwords were not encrypted correctly.
After doing:

ALTER USER pgpool WITH ENCRYPTED PASSWORD '*****';
ALTER USER postgres WITH ENCRYPTED PASSWORD '**';
ALTER USER repl WITH ENCRYPTED PASSWORD '*****';

postgres=# SELECT
postgres-#     rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
postgres-# FROM pg_authid
postgres-# WHERE rolcanlogin;
 rolname  | has_upgraded
----------+--------------
 repl     | t
 pgpool   | t
 postgres | t

Now they are correct and it can connect. Nevertheless, I'm having
other issues. Here is the Backend Connection Settings


# - Backend Connection Settings -
backend_hostname0 = 'qs-auth-01.dc.internal'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/export/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'qs-auth-01.dc.internal'
backend_hostname1 = 'qs-auth-02.dc. internal '
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/export/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'qs-auth-02.dc.internal'


node qs-auth-01.dc.internal should be the primary and has postgres running
there, but pgpool seems to ignore it.
node qs-auth-02.dc.internal is shutdown as I want to apply replication from
node 1.

Here is the log:

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

I see nothing in the node qs-auth-01.dc.internal postgres logs. Is there
another place where pgpool looks to find postgres servers?

Details about pgpool version:
data]# yum list installed | grep pgpool
pgpool-II-pg11.x86_64                 4.1.5-1pgdg.rhel7          @pgpool41
pgpool-II-pg11-extensions.x86_64      4.1.5-1pgdg.rhel7          @pgpool41
pgpool-II-release.noarch              4.1-2
 @/pgpool-II-release-4.1-2.noarchtGN7Vc

Regards,
LA







On Wed, Dec 16, 2020 at 8:51 AM Luís Alves <luisalves00 at gmail.com> wrote:

> I'm getting:
>
> 2020-12-16 08:21:59.766 GMT [502] LOG:  connection received:
> host=10.230.4.147 port=34162
> 2020-12-16 08:21:59.774 GMT [502] FATAL:  password authentication failed
> for user "pgpool"
> 2020-12-16 08:21:59.774 GMT [502] DETAIL:  User "pgpool" does not have a
> valid SCRAM verifier.
>         Connection matched pg_hba.conf line 34: "host    all
> all             0.0.0.0/0               scram-sha-256"
>
> (pg_hba.conf  is quite permissive for now so I can access individual nodes
> directly)
>
> I have:
>
> postgres=# \du
>                                      List of roles
>  Role name |                         Attributes                         |
>  Member of
>
> -----------+------------------------------------------------------------+--------------
>  pgpool    |                                                            |
> {pg_monitor}
>  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
> {}
>  repl      | Replication                                                |
> {}
>
>
> and
>
>
> # - Authentication -
>
> #authentication_timeout = 1min          # 1s-600s
> #password_encryption = md5              # md5 or scram-sha-256
> password_encryption = scram-sha-256
> #db_user_namespace = off
>
>
> Where should I look to provide the proper authentication?
>
> Regards,
> Luís Alves
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20201217/b17a3391/attachment.htm>