Manage expired certificates

[wp-perc]

Is your feature request related to a problem? Please describe.

Scenario:

1. Create a job to discover a SSL-based service with the x509 module and run it
2. Update/Renew/Replace the SSL Certificate used by the service
3. Run the job to update data inside x509 module

After, x509 will show 2 certificates: the old one and the new one. This is annoying, especially when the old one is expired. If you use services like Let's Encrypt that sign certificates with a relatively short expiration date, x509 will display dozens of expired certificates after few months.

Describe the solution you'd like

Best option may be:

1. Introduce an entity named SSL Service (or something you like) that groups together all SSL Certificate with the same subject and has as valid_from, valid_to etc. values coming from the most recent certificate in the group
2. Detailed view of a SSL Service may show the list of SSL Certificates it groups, ordered by expiration
3. SSL Service list should become the main view int he module

Describe alternatives you've considered

Removing expired certificates replaced by new ones is a viable solution: in my view, x509 is a way to asset and monitor the array of SSL Certificates currently in use, so expired but replaced certificates should disappear. Another alternative might be change Web Interface behavior to hide certificates expired after a while (7 days or so, should be a configurable value), or put them at the end of the list.

The last (and easiest) idea I come up with is removing all the expired certificates in a scheduled manner (via cronjob, timer etc.).

Additional context

[wp-perc]

Actually, I implemented some SQL Queries to remove certificates expired in the last X days. Hope this can be a starting point.

`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;`

[flybyray]

thx. target hosts which had at least one certificate rotation might stay in the target table. older entries have NULL in column latest_certificate_chain_id .

This might help.

            DELETE
            FROM x509_target
            WHERE id IN ( SELECT id
            FROM (
            SELECT id
            FROM x509_target AS table_alias1
            WHERE latest_certificate_chain_id IS NULL AND (
            SELECT COUNT(*) FROM x509_target
            WHERE hostname = table_alias1.hostname
            ) > 1
            ORDER BY id ASC) AS table_alias2
            );

[ShowMeYourSkil]

Hello, thank you for your contribution. Unfortunately, the SQL command does not delete my expired certificate.

Unfortunately, I cannot find the expiry date of the certificate in any table in the database. I have uploaded an expired .crt certificate as a test.

Can you help me here? Has the DB schema changed with the latest versions?