Create a certificate lifecycle view

[RufusJWB]

To simplify the search for unrevokes, unexpired certificates of a specific CA (e.g. to find all certificates with weak Debian keys that are not revoked nor expired) I would like to propose the creation of a view, that encapsulates the search for some typical lifecycle data of a certificate:

create view CERTIFICATE_LIFECYCLE as
select 
             C.ID CERTIFICATE_ID,
             C.ISSUER_CA_ID CA_ID,
             encode(X509_SERIALNUMBER(C.CERTIFICATE), 'hex') SERIAL_NUMBER,
             X509_SUBJECTNAME(C.CERTIFICATE) SUBJECT_DISTINGUISHED_NAME,
             (CASE WHEN (x509_print(C.CERTIFICATE) LIKE '%CT Precertificate Poison%') THEN
                 'Precertificate'
             ELSE
                 'Certificate'
             END) CERTIFICATE_TYPE,
             X509_NOTBEFORE(C.CERTIFICATE) NOT_BEFORE,
             X509_NOTAFTER(C.CERTIFICATE) NOT_AFTER,
             CTLE.FIRST_SEEN FIRST_SEEN,
             COALESCE(CRL.REVOKED, 0) REVOKED,
             COALESCE(LCI.LINT_ERRORS, 0) LINT_ERRORS,
             X509_NOTAFTER(C.CERTIFICATE) < now() EXPIRED
     from CERTIFICATE C
     join lateral
         (select MIN(CTLE.ENTRY_TIMESTAMP) FIRST_SEEN,
                 CTLE.CERTIFICATE_ID
          from CT_LOG_ENTRY CTLE
          where CTLE.CERTIFICATE_ID = C.ID
          group by CTLE.CERTIFICATE_ID) CTLE on true
     left join lateral
         (select COUNT(CRL.CA_ID) REVOKED,
                 CRL.SERIAL_NUMBER
          from CRL_REVOKED CRL
          where CRL.CA_ID = C.ISSUER_CA_ID
              and CRL.SERIAL_NUMBER = X509_SERIALNUMBER(C.CERTIFICATE)
          group by CRL.SERIAL_NUMBER) CRL on true
     left join lateral
         (select COUNT(LCI.CERTIFICATE_ID) LINT_ERRORS,
                 LCI.CERTIFICATE_ID
          from LINT_CERT_ISSUE LCI
          where LCI.CERTIFICATE_ID = C.ID
          group by LCI.CERTIFICATE_ID) LCI on true

[RufusJWB]

@robstradling Did you have a chance of thinking about this proposal?

[robstradling]

@RufusJWB Thanks for proposing this, and sorry for the delayed reply. I've just created the view.

[RufusJWB]

Thank you @robstradling ! You are doing an excellent job for the community in total.