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

[Jerry George]

Hi Tatsuo,

Thank you for your reply. I am good. I understand how to set this up using
the following documentation.

https://www.pgpool.net/docs/42/en/html/restrictions.html

Regards,
Jerry

On Fri, Nov 5, 2021 at 5:22 AM Jerry George <jerrygb at gmail.com> wrote:

> 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/234d2566/attachment-0001.htm>