MariaDB递归查询（带层级条件）：验证S/MIME证书有效性

数据库表结构 #

表：cm_certificates

证书有效性判定标准 #

• 证书未过期（cm_not_after > NOW()）
• 证书链完整且链中所有证书均未过期
• 证书链最顶层证书的cm_store_name为「roots」

现有问题 #

尝试用递归CTE编写的SQL无法满足需求：

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;

该查询会返回无效的ID4条目，且无法验证证书链的最顶端是否为根证书。

背景：使用Ciphermail作为S/MIME邮件加解密设备，自动导入邮件签名附带的证书（可能不完整），需要从数据库中筛选出符合条件的证书生成安全收件人列表，无直接API可用。

修正后的SQL查询 #

WITH RECURSIVE certificate_chain AS (
    -- 起始点：所有未过期的根证书
    SELECT 
        cm_id, 
        cm_not_after, 
        cm_issuer, 
        cm_subject, 
        cm_store_name,
        cm_subject AS root_subject,
        1 AS chain_length
    FROM cm_certificates
    WHERE cm_store_name = 'roots' AND cm_not_after > NOW()
    
    UNION ALL
    
    -- 递归遍历子证书：子证书的issuer等于父证书的subject，且子证书未过期
    SELECT 
        c.cm_id, 
        c.cm_not_after, 
        c.cm_issuer, 
        c.cm_subject, 
        c.cm_store_name,
        cc.root_subject,
        cc.chain_length + 1
    FROM cm_certificates c
    JOIN certificate_chain cc ON c.cm_issuer = cc.cm_subject
    WHERE c.cm_not_after > NOW()
)
-- 提取所有链中的证书，包括根证书本身
SELECT DISTINCT cm_id, cm_not_after, cm_issuer, cm_subject, cm_store_name
FROM certificate_chain;

逻辑说明 #

1. 起始节点：先筛选出所有未过期的根证书（cm_store_name = 'roots'且cm_not_after > NOW()），作为证书链的顶端。
2. 递归遍历：从根证书向下关联所有子证书（子证书的cm_issuer等于父证书的cm_subject），同时确保子证书未过期。
3. 去重返回：用DISTINCT避免重复条目，最终返回所有属于有效完整链的证书。

这样就能排除像ID4这种没有对应根证书的条目，同时保证整条链的所有证书都未过期，且链顶端是合法的根证书。

内容的提问来源于stack exchange，提问作者KZVMV EDV