PgBouncer and PostgreSQL Azure Flexible Server a bumpy road 🛣
Azure PostgreSQL Flexible Server and PgBouncer with sslmode=verify-full triggers SSL error sslv3 alert handshake failure
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 pgbouncer.client_tls_sslmode to 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 pgbouncer.client_tls_sslmode to verify-ca.
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.
Is DigiCertGlobalRootG2.crt.pem certificate only set for postgresql server but not for pgbouncer?
As a workaround, the only way to successfully connect is by setting pgbouncer.client_tls_sslmode to require.
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 verify-full.
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 pgbouncer.
So far activating verify-full on 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 sslmode=verify-full connection:
sslmode: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 sslmode=verify-full connection:
client_tls_sslmode: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 pgbouncer.client_tls_sslmode to 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 pgbouncer.client_tls_sslmode to 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 pgBouncer to 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 » ❓
