gistfile1.txt

observatory=> SELECT certificates.id,
observatory->        issuer->'o'->>0 AS Issuer,
observatory->        subject->>'cn' AS Subject,
observatory->        san AS SubjectAltName
observatory-> FROM certificates
observatory->       INNER JOIN trust ON (trust.cert_id=certificates.id),
observatory->      jsonb_array_elements_text(x509_subjectAltName) AS san
observatory-> WHERE jsonb_typeof(x509_subjectAltName) != 'null'
observatory->       AND ( subject#>>'{cn}' ~ '\.mozilla\.com'
observatory(>             OR
observatory(>             san ~ '\.mozilla\.com'
observatory(>           )
observatory->       AND trust.trusted_mozilla='true'
observatory->       AND certificates.not_valid_after>now()
observatory->       AND cast(issuer#>>'{o}' AS text) NOT LIKE '%DigiCert Inc%'
observatory-> GROUP BY certificates.id, san
observatory-> ORDER BY certificates.id ASC;

    id     |    issuer     |            subject             |          subjectaltname           
-----------+---------------+--------------------------------+-----------------------------------
   1060309 | GeoTrust Inc. | *.mozilla.com.tw               | *.mozilla.com.tw
   1060309 | GeoTrust Inc. | *.mozilla.com.tw               | mozilla.com.tw
   5662669 | GeoTrust Inc. | *.mozilla.com.cn               | *.mozilla.com.cn
   5662669 | GeoTrust Inc. | *.mozilla.com.cn               | mozilla.com.cn
  60327159 | GeoTrust Inc. | *.mozilla.com.tw               | *.mozilla.com.tw
  60327159 | GeoTrust Inc. | *.mozilla.com.tw               | mozilla.com.tw
 186516621 | Amazon        | developer.mozilla.org          | developer.mozilla.com
 186616418 | Amazon        | mozilla.community              | analytics.mozilla.community
 186616418 | Amazon        | mozilla.community              | rust-analytics.mozilla.community
 186622175 | Amazon        | person-api.sso.mozilla.com     | person-api.sso.mozilla.com
 186650873 | Amazon        | sso.mozilla.com                | sso.mozilla.com
 187008120 | Amazon        | snippets.mozilla.com           | snippets.mozilla.com
 187008120 | Amazon        | snippets.mozilla.com           | snippets-prod.moz.works
 187133174 | Amazon        | snippets.mozilla.com           | snippets.mozilla.com
 187133174 | Amazon        | snippets.mozilla.com           | snippets-prod.moz.works
 187160213 | Amazon        | snippets.mozilla.com           | snippets.mozilla.com
 187160213 | Amazon        | snippets.mozilla.com           | snippets-prod.moz.works
 187231213 | Amazon        | basket.mozilla.org             | basket.mozilla.com
 187416004 | Amazon        | developer.mozilla.org          | developer.mozilla.com
 187572045 | Amazon        | support.mozilla.org            | support.mozilla.com
 187578792 | Amazon        | developer.mozilla.org          | developer.mozilla.com
 187593890 | Amazon        | hubs.mozilla.com               | hubs.mozilla.com
 187593890 | Amazon        | hubs.mozilla.com               | hubs.mozilla.org
 187596937 | Amazon        | supportusers.mozilla.community | supportusers.mozilla.community
 187994282 | Amazon        | support.mozilla.org            | support.mozilla.com
 188004019 | Amazon        | snippets.mozilla.com           | snippets.mozilla.com
 188004019 | Amazon        | snippets.mozilla.com           | snippets-prod.frankfurt.moz.works
 188004347 | Let's Encrypt | static.mozilla.com             | static-haul.mozilla.com
 188004347 | Let's Encrypt | static.mozilla.com             | static.mozilla.com
 188008776 | Let's Encrypt | static.mozilla.com             | static.mozilla.com
 188020638 | Amazon        | dinopark.mozilla.community     | dinopark.mozilla.community
 188025021 | Amazon        | snippets.mozilla.com           | snippets.mozilla.com
 188025021 | Amazon        | snippets.mozilla.com           | snippets-prod.moz.works
 188025021 | Amazon        | snippets.mozilla.com           | snippets-prod.oregon-b.moz.works
 188034984 | Amazon        | developer.mozilla.org          | developer.mozilla.com
(35 rows)