The introduction of PgBouncer, a lightweight connection pooler for PostgreSQL, on Azure PostgreSQL Flexible Server has been bumpy to say the least 😉
Here is an excerpt of the issue that triggers the opening of a seven-month–long-to-be-solved support ticket 🎫
PgBouncer with sslmode=verify-full triggers SSL error sslv3 alert handshake failure
Once you have set up in your PostgreSQL flexible server parameters
verify-full, saved your changes and restarted your server, you end up with
SSL error: tlsv13 alert certificate required when you attempt to run a
pqsl connection command on your server.
This happens also when setting
According to documentation
This new certificate has been added to App Service at platform level. If you are using the SSL certificates included on App Service platform in your application, then no action is needed.
DigiCertGlobalRootG2.crt.pem certificate only set for
postgresql server but not for
As a workaround, the only way to successfully connect is by setting
In the present issue the problem is being able to set the TLS mode to use for connections from clients to the highest level available which is
sslmode=verify-full activation implies providing to the connection string a path to an authentication certificate. For PostgreSQL that can be handle by PGSSLROOTCERT environment variable.
There is a lack of information in PgBouncer - Azure Database for PostgreSQL - Flexible Server on how
verify-full is handle by
So far activating
pgbouncer.client_tls_sslmode on Azure Database for PostgreSQL flexible server parameters triggers an
SSL error sslv3 alert handshake failure.
Here is an example of a
psql connection attempt failing with
SSL error: tlsv13 alert certificate required
psql "host=myPgServer.postgres.database.azure.com dbname=pgbouncer user=pgbouncer password=myPassword port=6432 sslmode=verify-full sslrootcert=~/.postgresql/root.crt" psql: error: connection to server at "myPgServer.postgres.database.azure.com" (ip), port 6432 failed: SSL error: tlsv13 alert certificate required
According to PostgreSQL documentation here are the parameters to provide when establishing a
verify-fullonly try an SSL connection, verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate.
sslrootcert: This parameter specifies the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server’s certificate will be verified to be signed by one of these authorities. The default is ~/.postgresql/root.crt.
According to pgBouncer documentation here are the parameters to provide when establishing a
verify-fullClient must use TLS with valid client certificate.
client_tls_ca_file: Root certificate file to validate client certificates.
On Azure PostgreSQL flexible server parameters, there is a panel to set
verify-full, but there is not a
pgbouncer.client_tls_ca_file allowing to specify the path to the file containing SSL certificate authority (CA).
The question is « When you activate
verify-full how do you provide the accompanying file containing SSL certificate authority (CA) » ❓
A subsidiary question could be « Does Azure assures the transmission of the SSL certificate authority (CA) from
PostgreSQL server when hitting first
pgBouncer on port
6432 by collecting the value of
sslrootcertin the connection string and converting it into
client_tls_ca_file » ❓