Some of my customers make use of short-expiration SSL Certificates signed by Let’s Encrypt or similar services. Our company also makes extensive use of this kind of certificate, and in the near future most (if not all) of your SSL Certificates will in any event become like them as their expiration date approaches. Maybe some of them will be renewed, while others won’t. This scenario poses two questions:
Before answering these questions, let’s dig a little below the surface of the x509 module. Two are the main objects: SSL Certificates and SSL Targets, where an SSL Target is pretty much like a service (not in Monitoring terms, but from a generic IT perspective) identified by a tuple of host name, IP address and TCP port that is exposed using a specific SSL Certificate. Each of them is stored as a single line in a dedicated SQL Table on the underlying MariaDB Database. As you can guess, there is also additional data used to better describe SSL Certificates and link them to the corresponding SSL Targets, but for the sake of this explanation this is more than enough.
Now, the answer to question #1 is: literally nothing. Each SSL certificate is a row by itself in an SQL Table, and after expiration it continues to live there. Currently the web interface doesn’t hide expired SSL certificates for you, so you will still see them there alongside the valid ones. This can be quite annoying.
The answer to question #2 is a little more complex. Even if you basically just want to renew an SSL certificate, a renewed SSL certificate has no relationship whatsoever to the original SSL certificate it renews: they are in fact two completely separate entities, and the renewed one can live and operate quite well without its original counterpart. The same goes for replacing an SSL certificate.
Therefore, a renewed or replaced SSL certificate will result in a new row in the SQL table, while the expiring SSL certificate is handled exactly as described for question #1. This means you will wind up with two SSL certificates instead of just one: one is the expiring certificate, the other is the new one.
And of course, the web interface doesn’t help either since it still leaves the old certificates visible, but the good news is that SSL Target data are consistently updated: each SSL target will still only be linked to the renewed or replacement SSL certificate. However, this itself can still be extremely annoying.
To improve this situation, you’ll need to perform some cleanup operations directly on the underlying MariaDB Database. This maintenance consists of some SQL queries that:
In the near future this maintenance will be included with the x509 module, but for now I’m afraid you have to run it manually.
To perform this maintenance, you have to:
@expired_sinceto the desired value)
SET @expired_since=1; START TRANSACTION; use x509; -- -- REMOVE EXPIRED OBJECTS -- Remove expired Certificate Chains and links -- Links are removed because of relationships DELETE FROM x509_certificate_chain WHERE id = ANY ( SELECT certificate_chain_id FROM x509_certificate_chain_link WHERE certificate_id = ANY ( SELECT DISTINCT id FROM x509_certificate WHERE FROM_UNIXTIME(valid_to) < ADDDATE(NOW(), INTERVAL -@expired_since DAY) ORDER BY id ) ); -- Remove expired Certificates DELETE FROM x509_certificate WHERE FROM_UNIXTIME(valid_to) < ADDDATE(NOW(), INTERVAL -@expired_since DAY) ORDER BY id; -- -- PERFORM DANGLING OBJECTS CLEANUP -- Remove unused Subject's DNs DELETE FROM x509_dn WHERE type='subject' AND HASH <> ALL ( SELECT DISTINCT subject_hash FROM x509_certificate ); -- Remove unused Issuer's DNs DELETE FROM x509_dn WHERE type='issuer' AND HASH <> ALL ( SELECT DISTINCT issuer_hash FROM x509_certificate ); -- Remove hosts with no certificate chain DELETE FROM x509_target WHERE id <> ALL ( SELECT DISTINCT target_id FROM x509_certificate_chain ); COMMIT;
This script contains a series of SQL Delete statements, all wrapped in a single transaction. This will ensure that the cleanup will not damage your database in case of problems. You may notice at the very beginning a variable with a self-explanatory name:
@expired_since. This represents the number of days a given SSL certificate should have expired before being included in maintenance. You can also provide a negative value, but this will also remove SSL Certificates that are about to expire, so be very careful with it. As always, you should make a backup of the x509 module’s database, in case something goes wrong. To do so, you can simply run this command directly in the SSH shell on your NetEye 4:
mysqldump --add-drop-table --single-transaction x509 > x509-pre-cleanup.sql
If for any reason you want to restore your backup, just run this command under the same conditions:
mysql < x509-pre-cleanup.sql
As stated above, this script will be provided as a scheduled job with a new release of x509 module shortly, with the ability (again from command line) to target and remove a single SSL certificate, but if you want to perform some cleanup in your x509 module now, feel free to use it.