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

[Anssi Kanninen]

Hi Luis,

How do you know pgpool ignores the node 1?
Can you log in to the database thru pgpool?

Now that you have two nodes, remember to set 
"enable_consensus_with_half_votes = on" in pgpool config.

Cheers,
Anssi Kanninen

On Thu, 17 Dec 2020, Luís Alves wrote:

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

-- 
anssi at iki.fi