TLS with pgpool not working

[MatteoGioioso]

Hello

I am trying to setup TLS certificates with pgpool and postgres to have a secure connection between client <-> pgpool <-> postgres.

As my understanding when using verify-ca the client will provide the root certificate of the CA for verification with the backend; same should happen with pgpool in the middle, my client will provide the root certificate to verify the connection with pgpool, then pgpool will become the client and provide the root certificate via ssl_ca_cert option, to verify with the postgres backend.

However pgpool seems to reject my ca, this is the link to the repro repository: https://github.com/MatteoGioioso/tls_issue_pgpool

Following those instructions, everything works by connecting directly to postgres:

  db_master:
    build:
      dockerfile: pg.Dockerfile
      context: .
    command: -c ssl=on -c ssl_cert_file=/tls/cert.crt -c ssl_key_file=/tls/cert.key
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres

Dockerfile:

FROM postgres:12

COPY cert.crt /tls/cert.crt
COPY cert.key /tls/cert.key
COPY root.crt /tls/root.crt

WORKDIR /tls
RUN chmod 600 cert.key
RUN chown postgres:postgres cert.key

This config will work with verify-ca:

psql "sslmode=verify-ca sslrootcert=root.crt host=localhost user=postgres port=5432"

psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1), server 12.9 (Debian 12.9-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

However same setup, but with pgpool (you can check it from the repo I have linked) it does not work:

psql "sslmode=verify-ca sslrootcert=root.crt host=localhost user=postgres port=5432"

psql: error: connection to server at "localhost" (::1), port 5432 failed: SSL error: certificate verify failed

In the logs:

pg_pool_1     | 2022-04-08 08:37:46: pid 38: LOG:  using clear text authentication with frontend
pg_pool_1     | 2022-04-08 08:37:46: pid 38: DETAIL:  backend will still use md5 auth
pg_pool_1     | 2022-04-08 08:37:46: pid 38: HINT:  you can disable this behavior by setting allow_clear_text_frontend_auth to off
pg_pool_1     | 2022-04-08 08:37:47: pid 38: LOG:  pool_reuse_block: blockid: 0
pg_pool_1     | 2022-04-08 08:37:47: pid 38: CONTEXT:  while searching system catalog, When relcache is missed
pg_pool_1     | 2022-04-08 08:37:50: pid 40: LOG:  pool_ssl: "SSL_accept": "tlsv1 alert unknown ca"

[pengbo0328]

Thank you for reporting this issue.

The Docker image doesn't support root.crt configuration. I think it cause this issue. I am going to add ssl_ca_cert support.

[MatteoGioioso]

@pengbo0328 Thanks for your reply.

The Docker image doesn't support root.crt configuration

I though that was under pool.conf, is there a build or startup option to allow it?

I am going to add ssl_ca_cert support.

That would be great, If you need help let me know. With some hints I could try to make a PR.

[pengbo0328]

I though that was under pool.conf, is there a build or startup option to allow it?

I have updated the Docker image to allow the custom TLS certificates and private key: https://github.com/pgpool/pgpool2_on_k8s/blob/master/docs/index.md#tls-settings

However, sslrootcert is not supported yet.

That would be great, If you need help let me know. With some hints I could try to make a PR.

It would be appreciated if you could make a PR.

[MatteoGioioso]

@pengbo0328 As my understanding you are not adding the root.crt into the pgpool.conf from the entrypoint.sh.

echo "ssl_ca_cert = '${PGPOOL_INSTALL_DIR}/tls/root.crt'" >> ${PGPOOL_INSTALL_DIR}/etc/pgpool.conf

Or is there something more that I am missing?

Thanks

[MatteoGioioso]

@pengbo0328 I have added that line to the entrypoint.sh and tested again and it worked.

I have made a PR

[MatteoGioioso]

@pengbo0328 Hey, did you have time to take a look at the PR? Thanks