Skip to content

Instantly share code, notes, and snippets.

@jvehent
Created December 14, 2018 19:23
Show Gist options
  • Save jvehent/0a041180f191c8ad601741c1ff1a8390 to your computer and use it in GitHub Desktop.
Save jvehent/0a041180f191c8ad601741c1ff1a8390 to your computer and use it in GitHub Desktop.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment