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

[Jerry George]

Hi Tatsuo/team,

Thank you for your email and information.

In that case can you please confirm the considerations for having Certificate
Authentication only between client and pgpool-II? Does pgpool-II terminate
the SSL based on the CN string (on the x509 Certificate) and then use a
separate connection to authenticate with the backends? If yes, what about
the password requirements for backend connection from Pgpool-II?

Thanks,
Jerry



On Fri, 5 Nov 2021 at 01:33, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211105/02a059e3/attachment.htm>