Unable to use custom certificate chain for frontend SSL

[aguthrie]

A clear description of the issue

we are attempting to use a custom ca + cert + key for frontend SSL. instead of a single cert, we are using a cert chain with an intermediate cert, but we are unable to successfully establish app -> ProxySQL connections with SSL

ProxySQL version

ProxySQL version 2.6.3-107-gcdfcfdc, codename Truls

OS version

debian 12 (Docker)

The steps to reproduce the issue

our cert chain is setup as follows: root CA -> intermediate CA -> cert

I've added the certs into the proxysql files as follows:

• /var/lib/proxysql/proxysql-ca.pem - root CA
• /var/lib/proxysql/proxysql-cert.pem - cert + intermediate CA:

-----BEGIN CERTIFICATE-----
<cert goes here>
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
<intermediate CA goes here>
-----END CERTIFICATE-----

• /var/lib/proxysql/proxysql-key.pem - key

ProxySQL successfully starts and loads the certs:

2024-09-27 00:04:33 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
2024-09-27 00:04:33 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.

however, I'm not able to connect with SSL using mysql:

mysql -h<proxysql_host> -P7000 -u<user> -p<pass> -e '\s' --ssl-mode=VERIFY_CA --ssl-ca <root CA file> | grep -P 'SSL|Connection'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed

switching ssl-mode to REQUIRED works fine. so the issue only occurs with ssl-mode VERIFY_CA

---

I believe the issue occurs due to how proxysql loads the cert files here: https://github.com/sysown/proxysql/blob/adde80966c11b738d8445673d0c82302a10bf326/src/proxy_tls.cpp#L189-L214

the PEM_read_bio_X509 function only loads a single cert from the file. if the file contains multiple certs (like ours does), it must be called multiple times. example

The full ProxySQL error log

there are not any ProxySQL errors when this occurs, only client errors

[aguthrie]

we were able to get this working by configuring the files this way:

/var/lib/proxysql/proxysql-ca.pem - intermediate CA
/var/lib/proxysql/proxysql-cert.pem - cert
/var/lib/proxysql/proxysql-key.pem - key

i.e. removing the root CA from the proxysql files. I'm now successfully able to connect with sql-mode VERIFY_CA