MariaDB - Recursive Query with condition on last recursion level

given a MariaDB database with information about S/MIME certificates and the following database structure:

Table: cm_certificates

How can I query all "valid" certificates?

Criteria for validity:

1. Is not expired (cm_not_after > NOW())
2. Certificate chain is complete and none is expired
3. Last / top level certificate in chain has cm_store_name "root"

The chain is mapped via cm_issuer and cm_subject. The cm_issuer of one certificate is the cm_subject of another.

In my example the certificates with the id of 1,2,3 are valid. Number 4 is invalid. (root certificate not in database)

What I tried

I tried to use "Recursive Common Table Expressions".

WITH recursive ancestor as (
  SELECT * FROM `cm_certificates` WHERE cm_not_after > NOW()
  UNION
  SELECT c.*
  FROM `cm_certificates` AS c, ancestor AS a
  WHERE a.cm_issuer = c.cm_subject AND c.cm_not_after > NOW()
)
SELECT * FROM ancestor;

But with this I also get the entry with ID 4. Also I don't know how to check, if the last chain node is a "root" certificate.

Background:

We use ciphermail as an appliance for en-/decrypting e-mail traffic (S/MIME). We primarily use "domain encryption". However, ciphermail also automatically imports all certificates that are sent with emails as signatures. (Without importing the whole chain).

As encrypted communication is not possible with all recipients, we want to create an automatically generated list of all "secure" recipients on our intranet page. Because ciphermail only uses valid certificates (not expired + validated chain) for encryption I need to validate the chain based on database information. (No direct API available)

You are thinking about the recursion backwardly. Start with valid roots and recurse to valid certificates issued by already found subjects:

with recursive valid_certificates as (
  select cm_id valid_id, cm_subject valid_subject
  from cm_certificates
  where cm_store_name='roots' and cm_not_after > utc_timestamp()

  union

  select cm_id, cm_subject
  from valid_certificates
  join cm_certificates on cm_issuer=valid_subject
  where cm_not_after > utc_timestamp()
)
select cm.* from valid_certificates
join cm_certificates cm on cm_id=valid_id
# optionally exclude roots
where cm_store_name <> 'roots'