[pgpool-general: 7850] Re: Support for Certificate Authentication PgPool and Postgres

[Tatsuo Ishii]

> Hi,
> 
> I am looking to deploy pgpool and postgres cluster with SSL onto a
> Kubernetes Cluster.
> 
> *Reference for SSL Setup: *
> https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/
> 
> I was able to set up the Certificates for both pgpool and postgres.
> 
> But after setup, I am not able to connect through pgpool. However, I am
> able to connect to postgres directly using the hostnames attached to the
> postgres database or a headless service or just localhost for the *postgres*
> user.
> 
> Following is the error from pgpool logs,
> 
> *2021-11-04 21:57:26: pid 131: LOG:  SSL certificate authentication
> for user "postgres" with Pgpool-II is successful
> 2021-11-04 21:57:26: pid 131: ERROR:  backend authentication failed
> 2021-11-04 21:57:26: pid 131: DETAIL:  backend response with kind 'E'
> when expecting 'R'
> 2021-11-04 21:57:26: pid 131: HINT:  This issue can be caused by
> version mismatch (current version 3)
> 2021-11-04 21:57:26: pid 130: LOG:  SSL certificate authentication for
> user "postgres" with Pgpool-II is successful
> 2021-11-04 21:57:26: pid 130: ERROR:  backend authentication failed
> 2021-11-04 21:57:26: pid 130: DETAIL:  backend response with kind 'E'
> when expecting 'R'
> 2021-11-04 21:57:26: pid 130: HINT:  This issue can be caused by
> version mismatch (current version 2)*
> 
> Test: psql "sslmode=require port=5432 host=localhost dbname=postgres
> sslcert=./client.crt sslkey=./client.key sslrootcert=./ca.pem"
> --username postgres
> 
> Original Source Code for Kubernetes Manifests:
> https://github.com/bitnami/charts/tree/master/bitnami/postgresql-ha
> 
> Please see additional PRs talking about enabling both TLS at the same time,
> https://github.com/bitnami/bitnami-docker-pgpool/issues/18
> 
> Additionally, in the pgpool documentation I noticed some conflicting
> notes <https://www.pgpool.net/docs/42/en/html/auth-methods.html> like,
> 
> *Note: The certificate authentication works between only client and
> Pgpool-II. The certificate authentication does not work between
> Pgpool-II and PostgreSQL. For backend authentication you can use any
> other authentication method.*
> 
> If you could please help me understand the whether this is a
> configuration or design flaw?

No. It's a limitation of Pgpool-II. Pgpool-II allows to use the
certificate authentication between client and Pgpool-II. Since
Pgpool-II is a proxy, it needs to be authenticated by PostgreSQL as
well. Unfortunately currently Pgpool-II does not implement certificate
authentication against PostgreSQL.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp