TLSBRv2 §7.1.2.7.6 Subscriber Certificate Extensions: Survey of critical flag non-compliance

check_extension_criticality.go

package main

import (
	"context"
	"flag"
	"fmt"
	"math"
	"os/signal"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5"
)

func main() {
	// Configure graceful shutdown capabilities.
	ctx, stop := signal.NotifyContext(context.Background(), syscall.SIGHUP, syscall.SIGINT, syscall.SIGTERM)
	defer stop()

	// Define and parse flags.
	var startID, endID, batchSize int64
	var logLevel string
	flag.Int64Var(&startID, "startID", -1, "crt.sh ID to start from [-1 = stream new records, starting at max(ID)+1]")
	flag.Int64Var(&endID, "endID", math.MaxInt64, "crt.sh ID to stop at")
	flag.Int64Var(&batchSize, "batchSize", 100000, "Number of certificate records to process per batch")
	flag.StringVar(&logLevel, "logLevel", "debug", "Logging verbosity [debug, info, error, fatal]")
	flag.Parse()

	if startID > endID {
		panic("startID must be <= endID")
	} else if batchSize > 100000 {
		panic("batchSize must be <=100000")
	}

	// Construct the query.
	query := `INSERT INTO temp_extension_criticality_20240322
SELECT c.ID, c.ISSUER_CA_ID,
		x509_hasExtension(c.CERTIFICATE, '1.3.6.1.5.5.7.1.1', TRUE) as AIA,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.35', TRUE) as AKI,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.32', TRUE) as CP,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.37', TRUE) as EKU,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.15', FALSE) as KU,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.19', FALSE) as BC,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.31', TRUE) as CDP,
		x509_hasExtension(c.CERTIFICATE, '1.3.6.1.4.1.11129.2.4.2', TRUE) as SCTL,
		x509_hasExtension(c.CERTIFICATE, '2.5.29.14', TRUE) as SKI
	FROM certificate c
	WHERE c.ID BETWEEN $1 AND $2
		/* Exclude certificate partitions prior to 2023 */
		AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) >= '2023-01-01'::date
		/* Issued on or after the effective date of TLS BR v2.0.0 */
		AND coalesce(x509_notBefore(c.CERTIFICATE), '-infinity'::timestamp) >= '2023-09-15'::date
		/* Contains the id-kp-serverAuthentication EKU */
		AND x509_isEKUPermitted(c.CERTIFICATE, '1.3.6.1.5.5.7.3.1')
		AND (
			/* Check for critical authorityInformationAccess extension */
			x509_hasExtension(c.CERTIFICATE, '1.3.6.1.5.5.7.1.1', TRUE)
			/* Check for critical authorityKeyIdentifier extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.35', TRUE)
			/* Check for critical certificatePolicies extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.32', TRUE)
			/* Check for critical extKeyUsage extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.37', TRUE)
			/* Check for non-critical keyUsage extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.15', FALSE)
			/* Check for non-critical basicConstraints extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.19', FALSE)
			/* Check for critical crlDistributionPoints extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.31', TRUE)
			/* Check for critical SignedCertificateTimestampList extension */
			OR x509_hasExtension(c.CERTIFICATE, '1.3.6.1.4.1.11129.2.4.2', TRUE)
			/* Check for critical subjectKeyIdentifier extension */
			OR x509_hasExtension(c.CERTIFICATE, '2.5.29.14', TRUE)
		)`

	// Parse the connect string URI.
	var pgxConfig *pgx.ConnConfig
	var err error
	if pgxConfig, err = pgx.ParseConfig("postgresql:///certwatch?host=localhost&user=certwatch"); err != nil {
		panic(err)
	}

	// Connect to crt.sh:5432.
	var crtsh *pgx.Conn
	if crtsh, err = pgx.ConnectConfig(context.Background(), pgxConfig); err != nil {
		panic(err)
	}
	defer crtsh.Close(context.Background())

	// Main loop: repeatedly run the query to search batches of certificate records.
	maxCertificateID := int64(-1)
	var thisBatchSize int64
	var sleepFor time.Duration
for_loop:
	for i := startID; i < endID; i += thisBatchSize {
		if sleepFor > 0 {
			fmt.Printf("Sleeping for %v\n", sleepFor)
		}

		select {
		case <-time.After(sleepFor):
		case <-ctx.Done():
			fmt.Printf("Interrupted (last = %d)\n", (i - 1))
			break for_loop
		}

		sleepFor = time.Second * 15

		if maxCertificateID <= i {
			// Get the latest certificate ID.
			if err = crtsh.QueryRow(context.Background(), "SELECT max(ID) FROM certificate").Scan(&maxCertificateID); err != nil {
				fmt.Printf("Could not obtain latest ID: %v\n", err)
				continue
			}

			if i == -1 {
				startID = maxCertificateID + 1
				i = startID
			}
			if maxCertificateID > endID {
				maxCertificateID = endID
			}
		}

		if thisBatchSize = maxCertificateID - i + 1; thisBatchSize >= batchSize {
			thisBatchSize = batchSize // Enforce the maximum batch size.
			sleepFor = 0              // No need to sleep after this batch.
		} else if thisBatchSize <= 0 {
			fmt.Printf("No more certificates available yet\n")
			continue
		}

		var tx pgx.Tx
		tx, err = crtsh.BeginTx(context.Background(), pgx.TxOptions{})
		if err != nil {
			panic(err)
		}

		fmt.Printf("Processing certificates %d to %d\n", i, i+thisBatchSize-1)

		// Get batch of results.
		if _, err = tx.Exec(context.Background(), query, i, i+thisBatchSize-1); err != nil {
			panic(err)
		}

		tx.Commit(context.Background())
	}
}

ddl.sql

DROP TABLE IF EXISTS temp_extension_criticality_20240322;

CREATE TABLE temp_extension_criticality_20240322 (
	CERTIFICATE_ID	bigint,
	ISSUER_CA_ID	bigint,
	AIA				boolean,
	AKI				boolean,
	CP				boolean,
	EKU				boolean,
	KU				boolean,
	BC				boolean,
	CDP				boolean,
	SCTL			boolean,
	SKI				boolean
);

generate_report.sh

#!/bin/bash

# psql doesn't support multi-line \COPY statements, so we use the HEREDOC workaround described by https://minhajuddin.com/2017/05/18/how-to-pass-a-multi-line-copy-sql-to-psql/

ERRORFILE=`mktemp`

cat <<SQL | tr -d '\n' | psql -h crt.sh -p 5432 -U guest -d certwatch -v ON_ERROR_STOP=1 -X 2>$ERRORFILE
\COPY (
WITH ca_owners AS (
  SELECT min(cc.INCLUDED_CERTIFICATE_OWNER) INCLUDED_CERTIFICATE_OWNER, cac.CA_ID
    FROM ccadb_certificate cc, ca_certificate cac
    WHERE cc.CERTIFICATE_ID = cac.CERTIFICATE_ID
    GROUP BY cac.CA_ID
),
ca_trusted AS (
  SELECT ctp.CA_ID, array_to_string(array_agg(tc.CTX ORDER BY tc.CTX), ',') TRUSTED_BY
    FROM ca_trust_purpose ctp, trust_context tc
    WHERE ctp.TRUST_PURPOSE_ID = 1 /* Server Authentication */
      AND ctp.TRUST_CONTEXT_ID = tc.ID
      AND tc.CTX IN ('Apple', 'Chrome', 'Microsoft', 'Mozilla')
    GROUP BY ctp.CA_ID
)
SELECT INCLUDED_CERTIFICATE_OWNER as "Included Cert. Owner",
       count(*) as "# Certs+Precerts",
       rtrim(CASE WHEN AIA THEN 'Authority Info Access, ' ELSE '' END
            || CASE WHEN AKI THEN 'Authority Key Identifier, ' ELSE '' END
            || CASE WHEN CP THEN 'Certificate Policies, ' ELSE '' END
            || CASE WHEN EKU THEN 'Extended Key Usage, ' ELSE '' END
            || CASE WHEN KU THEN 'Key Usage, ' ELSE '' END
            || CASE WHEN BC THEN 'Basic Constraints, ' ELSE '' END
            || CASE WHEN CDP THEN 'CRL Distribution Points, ' ELSE '' END
            || CASE WHEN SCTL THEN 'Signed Certificate Timestamp List, ' ELSE '' END
            || CASE WHEN SKI THEN 'Subject Key Identifier, ' ELSE '' END,
            ', ') as "Non-compliant Critical Flags",
       ct.TRUSTED_BY as "Trusted by",
       'https://crt.sh/?id=' || min(t.CERTIFICATE_ID)::text as "Lowest ID",
       'https://crt.sh/?id=' || max(t.CERTIFICATE_ID)::text as "Highest ID"
  FROM temp_extension_criticality_20240322 t, ca_owners co
         LEFT OUTER JOIN ca_trusted ct ON (co.CA_ID = ct.CA_ID)
  WHERE t.ISSUER_CA_ID = co.CA_ID
    AND ct.TRUSTED_BY IS NOT NULL
  GROUP BY INCLUDED_CERTIFICATE_OWNER, AIA, AKI, CP, EKU, KU, BC, CDP, SCTL, SKI, ct.TRUSTED_BY
 ORDER BY INCLUDED_CERTIFICATE_OWNER
) TO 'report.csv' CSV HEADER
SQL

cat $ERRORFILE
rm -f $ERRORFILE

go.mod

module gist.github.com/robstradling/6a5ecca872cf28232d90638fc2c44ed5

go 1.21.6

require github.com/jackc/pgx/v5 v5.5.5

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20231201235250-de7065d80cb9 // indirect
	golang.org/x/crypto v0.21.0 // indirect
	golang.org/x/text v0.14.0 // indirect
)

go.sum

github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
github.com/jackc/pgservicefile v0.0.0-20231201235250-de7065d80cb9 h1:L0QtFUgDarD7Fpv9jeVMgy/+Ec0mtnmYuImjTz6dtDA=
github.com/jackc/pgservicefile v0.0.0-20231201235250-de7065d80cb9/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
github.com/jackc/pgx/v5 v5.5.5 h1:amBjrZVmksIdNjxGW/IiIMzxMKZFelXbUoPNb+8sjQw=
github.com/jackc/pgx/v5 v5.5.5/go.mod h1:ez9gk+OAat140fv9ErkZDYFWmXLfV+++K0uAOiwgm1A=
github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk=
github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
golang.org/x/crypto v0.21.0 h1:X31++rzVUdKhX5sWmSOFZxx8UW/ldWx55cbf08iNAMA=
golang.org/x/crypto v0.21.0/go.mod h1:0BP7YvVV9gBbVKyeTG0Gyn+gZm94bibOW5BjDEYAOMs=
golang.org/x/sync v0.1.0 h1:wsuoTGHzEhffawBOhz5CYhcrV4IdKZbEyZjBMuTp12o=
golang.org/x/sync v0.1.0/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/text v0.14.0 h1:ScX5w1eTa3QqT8oi6+ziP7dTV1S2+ALU0bI+0zXKWiQ=
golang.org/x/text v0.14.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU=
gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=

report.csv