Rob Stradling robstradling

## go.mod
module gist.github.com/robstradling/3bf7c340bc4479f3b3a71c58a9842657

go 1.19

require github.com/sijms/go-ora/v2 v2.7.21

## find_embedded_dodo_scts.sql
-- CREATE TABLE temp_dodo_embedded_scts ( certificate_id bigint, issuer_ca_id integer );
-- GRANT SELECT ON temp_dodo_embedded_scts TO GUEST;

\timing on

\set ON_ERROR_STOP on

DO
$$DECLARE
	t_minCertificateID	certificate.ID%TYPE;

## 20221011_AllCAsAndCACertificatesForSectigoSelfAssessment.csv
CA Certificate Type,Issuer Common Name,Subject DN,SHA-256(Certificate),Not Before,Not After,CA Owner,Main CPS?,eIDAS CPS?,Document Signing CPS?,External CPS?,Serial Number,Subject Key Identifier
Root,AAA Certificate Services,"CN=AAA Certificate Services
O=Comodo CA Limited
L=Salford
ST=Greater Manchester
C=GB",D7A7A0FB5D7E2731D771E9484EBCDEF71D5F0C3E0A2948782BC83EE0EA699EF4,2004-01-01 00:00:00,2028-12-31 23:59:59,Sectigo,Main,n/a,n/a,n/a,01,A0110A233E96F107ECE2AF29EF82A57FD030A4B4
Intermediate,AAA Certificate Services,"CN=TrustAsia ECC DV TLS CA G2
O=TrustAsia Technologies, Inc.
C=CN",5251E2A5B3A42021667994B04E195B693B9B71B752267C44DC310A77979BDC00,2022-01-10 00:00:00,2028-12-31 23:59:59,Sectigo,Main,n/a,n/a,n/a,00D21ADFBDC3C45772BDF698B2BAD8FB0D,01A6B0FD96C74AA86922B2119E60DB6A34C61452
Intermediate,AAA Certificate Services,"CN=TrustAsia ECC EV TLS CA G2

## Active_Russian_SANdNSNames.csv

          
            CA OWNER ↓    DATE OF VALIDITY →
            2022-02-21
            2022-02-22
            2022-02-23
            2022-02-24
            2022-02-25
            2022-02-26
            2022-02-27
            2022-02-28
            2022-03-01
            2022-03-02
            2022-03-03
            2022-03-04
            2022-03-05
            2022-03-06
            2022-03-07
            2022-03-08
            2022-03-09
            2022-03-10
            2022-03-11
            2022-03-12
            2022-03-13
            2022-03-14
            2022-03-15
            2022-03-16
            2022-03-17
            2022-03-18
            2022-03-19
            2022-03-20
            2022-03-21
            2022-03-22
            2022-03-23
            2022-03-24
            2022-03-25
            2022-03-26
            2022-03-27
            2022-03-28
            2022-03-29
            2022-03-30
            2022-03-31
            2022-04-01
            2022-04-02
            2022-04-03
            2022-04-04
            2022-04-05
            2022-04-06
            2022-04-07
            2022-04-08
            2022-04-09
            2022-04-10
            2022-04-11
            2022-04-12
            2022-04-13
            2022-04-14
            2022-04-15
            2022-04-16
            2022-04-17
            2022-04-18
            2022-04-19
            2022-04-20
            2022-04-21
            2022-04-22
            2022-04-23
            2022-04-24
            2022-04-25
            2022-04-26
            2022-04-27
            2022-04-28
            2022-04-29
            2022-04-30
            2022-05-01
            2022-05-02
            2022-05-03
            2022-05-04
            2022-05-05
            2022-05-06
            2022-05-07
            2022-05-08
            2022-05-09
            2022-05-10
            2022-05-11
            2022-05-12
            2022-05-13
            2022-05-14
            2022-05-15
            2022-05-16
            2022-05-17
            2022-05-18
            2022-05-19
            2022-05-20
            2022-05-21

## 20210625_intermediates_with_questionable_eku.csv

          
            crt.sh ID
            notBefore
            Subject CA
            Issuer CA

            
              2657659203
              2019-11-28 08:48:09
              AC Sector Público
              AC RAIZ FNMT-RCM

            
              2657658668
              2019-11-28 08:50:02
              AC Unidades de Sellado de Tiempo
              AC RAIZ FNMT-RCM

            
              1849145005
              2018-12-20 10:15:49
              AC SERVIDORES SEGUROS TIPO1
              AC RAIZ FNMT-RCM SERVIDORES SEGUROS

            
              1849145003
              2018-12-20 10:20:38
              AC SERVIDORES SEGUROS TIPO2
              AC RAIZ FNMT-RCM SERVIDORES SEGUROS

            
              1287935739
              2019-03-12 09:29:48
              Actalis Domain Validation Server CA G1
              Actalis Authentication Root CA

            
              1283820374
              2019-03-13 08:27:08
              Actalis Extended Validation Server CA G2
              Actalis Authentication Root CA

            
              1435438944
              2019-04-15 12:36:04
              AgID CA1
              Actalis Authentication Root CA

            
              3517096458
              2020-10-13 06:10:10
              AgID CA1
              Actalis Authentication Root CA

            
              2620763357
              2020-02-19 09:58:32
              AgID CA1
              Actalis Authentication Root CA

## apple_insufficient_scts.txt
-- Step 1: Find the lowest/earliest certificate ID affected by the updated Apple CT Policy.
-- (Assumptions: (i) Certificate notBefore dates are never forward-dated and (ii) Log entry timestamps are always accurate).

certwatch@certwatch=> SELECT min(c.ID)
  FROM certificate c
  WHERE x509_notBefore(c.CERTIFICATE) >= '2021-04-21'::timestamp
    AND c.ID IN (
      SELECT ctle.CERTIFICATE_ID
        FROM ct_log_entry_2021 ctle
        WHERE ctle.ENTRY_TIMESTAMP >= '2021-04-21'::timestamp

## unexpired_server_certs_key_sizes_20210107.csv

          
            Count
            Key Algorithm
            Key Size
            min(crt.sh ID)
            max(crt.sh ID)

            
              403267520
              RSA
              2048
              14
              3887788878

            
              67076276
              EC
              256
              2771492
              3887788865

            
              57012579
              RSA
              4096
              398
              3887788867

            
              6467447
              EC
              384
              2771491
              3887788832

            
              3098147
              RSA
              3072
              12070670
              3887788756

            
              4658
              RSA
              8192
              242899024
              3886849719

            
              695
              RSA
              1024
              42
              3702550713

            
              116
              RSA
              3456
              1237838607
              3882686786

            
              103
              RSA
              4048
              1112885148
              3815023853

## mozilla_ca_certs_with_ocspsigning_eku.sql
WITH mozilla_server_cas AS (
  SELECT DISTINCT ctp.CA_ID
    FROM ca_trust_purpose ctp
    WHERE ctp.TRUST_CONTEXT_ID = 5  /* Mozilla */
      AND ctp.TRUST_PURPOSE_ID = 1  /* Server Authentication */
)
SELECT c.ID,
       coalesce(cc.CA_OWNER, cc.INCLUDED_CERTIFICATE_OWNER) as CA_OWNER,
       CASE WHEN mo.CERTIFICATE_ID IS NOT NULL THEN 'Revoked' ELSE 'Unrevoked' END as ONECRL_STATUS,
       x509_signatureHashAlgorithm(c.CERTIFICATE) as SIG_HASH_ALG,

## how_many_cas_are_there.txt
AC Camerfirma, S.A.
Actalis
ADACOM S.A.
Agencia Notarial de Certificación (ANCERT)
Amazon Trust Services
AOL
Apple Inc.
Asseco Data Systems S.A. (previously Unizeto Certum)
Athens Exchange S.A. (Athex)
Atos

## Makefile
chrome_ev:
	gcc -o chrome_ev chrome_ev.cc
	module gist.github.com/robstradling/3bf7c340bc4479f3b3a71c58a9842657

	go 1.19

	require github.com/sijms/go-ora/v2 v2.7.21
	-- CREATE TABLE temp_dodo_embedded_scts ( certificate_id bigint, issuer_ca_id integer );
	-- GRANT SELECT ON temp_dodo_embedded_scts TO GUEST;

	\timing on

	\set ON_ERROR_STOP on

	DO
	$$DECLARE
	t_minCertificateID certificate.ID%TYPE;
	CA Certificate Type,Issuer Common Name,Subject DN,SHA-256(Certificate),Not Before,Not After,CA Owner,Main CPS?,eIDAS CPS?,Document Signing CPS?,External CPS?,Serial Number,Subject Key Identifier
	Root,AAA Certificate Services,"CN=AAA Certificate Services
	O=Comodo CA Limited
	L=Salford
	ST=Greater Manchester
	C=GB",D7A7A0FB5D7E2731D771E9484EBCDEF71D5F0C3E0A2948782BC83EE0EA699EF4,2004-01-01 00:00:00,2028-12-31 23:59:59,Sectigo,Main,n/a,n/a,n/a,01,A0110A233E96F107ECE2AF29EF82A57FD030A4B4
	Intermediate,AAA Certificate Services,"CN=TrustAsia ECC DV TLS CA G2
	O=TrustAsia Technologies, Inc.
	C=CN",5251E2A5B3A42021667994B04E195B693B9B71B752267C44DC310A77979BDC00,2022-01-10 00:00:00,2028-12-31 23:59:59,Sectigo,Main,n/a,n/a,n/a,00D21ADFBDC3C45772BDF698B2BAD8FB0D,01A6B0FD96C74AA86922B2119E60DB6A34C61452
	Intermediate,AAA Certificate Services,"CN=TrustAsia ECC EV TLS CA G2
crt.sh ID	notBefore	Subject CA	Issuer CA
2657659203	2019-11-28 08:48:09	AC Sector Público	AC RAIZ FNMT-RCM
2657658668	2019-11-28 08:50:02	AC Unidades de Sellado de Tiempo	AC RAIZ FNMT-RCM
1849145005	2018-12-20 10:15:49	AC SERVIDORES SEGUROS TIPO1	AC RAIZ FNMT-RCM SERVIDORES SEGUROS
1849145003	2018-12-20 10:20:38	AC SERVIDORES SEGUROS TIPO2	AC RAIZ FNMT-RCM SERVIDORES SEGUROS
1287935739	2019-03-12 09:29:48	Actalis Domain Validation Server CA G1	Actalis Authentication Root CA
1283820374	2019-03-13 08:27:08	Actalis Extended Validation Server CA G2	Actalis Authentication Root CA
1435438944	2019-04-15 12:36:04	AgID CA1	Actalis Authentication Root CA
3517096458	2020-10-13 06:10:10	AgID CA1	Actalis Authentication Root CA
2620763357	2020-02-19 09:58:32	AgID CA1	Actalis Authentication Root CA
	-- Step 1: Find the lowest/earliest certificate ID affected by the updated Apple CT Policy.
	-- (Assumptions: (i) Certificate notBefore dates are never forward-dated and (ii) Log entry timestamps are always accurate).

	certwatch@certwatch=> SELECT min(c.ID)
	FROM certificate c
	WHERE x509_notBefore(c.CERTIFICATE) >= '2021-04-21'::timestamp
	AND c.ID IN (
	SELECT ctle.CERTIFICATE_ID
	FROM ct_log_entry_2021 ctle
	WHERE ctle.ENTRY_TIMESTAMP >= '2021-04-21'::timestamp
Count	Key Algorithm	Key Size	min(crt.sh ID)	max(crt.sh ID)
403267520	RSA	2048	14	3887788878
67076276	EC	256	2771492	3887788865
57012579	RSA	4096	398	3887788867
6467447	EC	384	2771491	3887788832
3098147	RSA	3072	12070670	3887788756
4658	RSA	8192	242899024	3886849719
695	RSA	1024	42	3702550713
116	RSA	3456	1237838607	3882686786
103	RSA	4048	1112885148	3815023853
	WITH mozilla_server_cas AS (
	SELECT DISTINCT ctp.CA_ID
	FROM ca_trust_purpose ctp
	WHERE ctp.TRUST_CONTEXT_ID = 5 /* Mozilla */
	AND ctp.TRUST_PURPOSE_ID = 1 /* Server Authentication */
	)
	SELECT c.ID,
	coalesce(cc.CA_OWNER, cc.INCLUDED_CERTIFICATE_OWNER) as CA_OWNER,
	CASE WHEN mo.CERTIFICATE_ID IS NOT NULL THEN 'Revoked' ELSE 'Unrevoked' END as ONECRL_STATUS,
	x509_signatureHashAlgorithm(c.CERTIFICATE) as SIG_HASH_ALG,
	AC Camerfirma, S.A.
	Actalis
	ADACOM S.A.
	Agencia Notarial de Certificación (ANCERT)
	Amazon Trust Services
	AOL
	Apple Inc.
	Asseco Data Systems S.A. (previously Unizeto Certum)
	Athens Exchange S.A. (Athex)
	Atos