28. 12. 2020 Rocco Pezzani Asset Management, NetEye, Unified Monitoring

Cleaning up Your Certificate Inventory

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:

What happens within the x509 module when an SSL Certificate expires?
What happens within the x509 module when an SSL Certificate is renewed or replaced?

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:

Address and remove expired SSL certificates
Remove any SSL target that is no longer linked to any SSL certificate
Identify and remove accessory data no longer related to SSL certificates

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:

Open an SSH shell on your NetEye 4 server with root privileges
Open MariaDB by typing mysql
Paste the following script into the shell (after adjusting the variable @expired_since to the desired value)
As a precaution, run all your Scans to update and fill in any missing information (even if that’s not needed)

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.