How do you monitor MariaDB SSL expiration for both replication and client certificates?

user_12728862 0
Viewed 779

There are a number of tools for monitoring SSL expiration for services available via HTTPS, e.g. we use https://github.com/prometheus/blackbox_exporter to receive alerts when SSL certificates expire in <14 days on both internal and external services.

We use Percona's XtraDB cluster (i.e. MariaDB) with SSL for both front-end and replication traffic. The relevant configuration is shown below:

$ less /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
pxc_encrypt_cluster_traffic=ON
ssl-ca=/etc/ssl/xtradb_server_ca.pem
ssl-cert=/etc/ssl/xtradb_server_cert.pem
ssl-key=/etc/ssl/xtradb_server_key.pem

[client]
ssl-ca=/etc/ssl/xtradb_server_ca.pem
ssl-cert=/etc/ssl/xtradb_server_cert.pem
ssl-key=/etc/ssl/xtradb_server_key.pem

What we have yet to figure out: How do you monitor the SSL expiration for certificates loaded by mysqld.service?

We use Ansible to deploy new certificates and perform rolling-restarts of mysqld.service on each host, however it would be great to monitor and confirm that these certificates are being properly updated.

Is there a common solution for this?

percona prometheus mariadb ssl mysql
2 Answers

Currently there is no server variable or API function available to determine the validation period of a server certificate.

A simple workaround would be (assuming secure_file_priv is not set) :

$ mysql -e "SHOW VARIABLES LIKE 'ssl_cert'"
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| ssl_cert      | /etc/mysql/server-cert.pem |
+---------------+----------------------------+
$ mysql  -e "SELECT LOAD_FILE('/etc/mysql/server-cert.pem')\G" > server-cert.pem
$ openssl x509 -enddate -noout -in ./server-cert.pem
notAfter=Jan 22 10:11:10 2021 GMT
user_69305010

A solution was shared with me offline, see https://mariadb.com/kb/en/ssltls-status-variables/!

MariaDB has variables which display SSL expiration information:

mysql> show status like 'ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | May 24 11:46:23 2020 GMT |
| Ssl_server_not_before | Feb 24 11:46:23 2020 GMT |
+-----------------------+--------------------------+
2 rows in set (0.00 sec)

I'll update this solution once I've figured out how to either

  1. Expose these as metrics via https://github.com/prometheus/mysqld_exporter (opened mysqld_exporter/issues/457)
  2. Query for the variables via https://github.com/free/sql_exporter
user_127288620
Related