WebPKI Issuance and CRL Revocation of Russian/Belarusian subjectAltName:dNSNames (.ru, .su, .by)

Active_Russian_SANdNSNames.csv

New_Russian_SANdNSNames.csv

report.sql

-- New Cert dNSNames report.
\COPY (SELECT sub2.* FROM ( SELECT array_to_string(array_prepend('"CA OWNER ↓    DATE OF VALIDITY →"', array_agg((gs::date)::text)), ',') line FROM generate_series('2022-02-21', now() AT TIME ZONE 'UTC' - interval '1 day', '1 day'::interval) gs UNION SELECT array_to_string(array_prepend('"' || sub.CA_OWNER::text || '"', array_agg(sub.COUNT_NEW::text ORDER BY sub.DATE_OF_VALIDITY)), ',') line FROM ( WITH ca_owner AS ( SELECT cac.CA_ID, array_to_string(array_agg(DISTINCT coalesce(nullif(cc.SUBORDINATE_CA_OWNER, ''), cc.INCLUDED_CERTIFICATE_OWNER)), ',') CA_OWNER FROM ccadb_certificate cc, ca_certificate cac WHERE cc.CERTIFICATE_ID = cac.CERTIFICATE_ID AND EXISTS ( SELECT 1 FROM ca_trust_purpose ctp WHERE ctp.CA_ID = cac.CA_ID AND ctp.TRUST_CONTEXT_ID IN (1, 5, 12) AND ctp.TRUST_PURPOSE_ID = 1 ) GROUP BY cac.CA_ID ) SELECT coalesce(co.CA_OWNER, 'OTHER') CA_OWNER, trcs.DATE_OF_VALIDITY, sum(trcs.COUNT_NEW) COUNT_NEW FROM temp_russian_certs_summary trcs LEFT JOIN ca_owner co ON (trcs.ISSUER_CA_ID = co.CA_ID) WHERE trcs.DATE_OF_VALIDITY < date_trunc('day', now() AT TIME ZONE 'UTC') GROUP BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ORDER BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ) sub GROUP BY sub.CA_OWNER ) sub2 ORDER BY CASE substr(line, 1, 6) WHEN '"CA OW' THEN 1 WHEN '"OTHER' THEN 3 ELSE 2 END, line) TO 'New_Russian_SANdNSNames.csv'

-- Active Cert dNSNames report.
\COPY (SELECT sub2.* FROM ( SELECT array_to_string(array_prepend('"CA OWNER ↓    DATE OF VALIDITY →"', array_agg((gs::date)::text)), ',') line FROM generate_series('2022-02-21', now() AT TIME ZONE 'UTC' - interval '1 day', '1 day'::interval) gs UNION SELECT array_to_string(array_prepend('"' || sub.CA_OWNER::text || '"', array_agg(sub.COUNT_ACTIVE::text ORDER BY sub.DATE_OF_VALIDITY)), ',') line FROM ( WITH ca_owner AS ( SELECT cac.CA_ID, array_to_string(array_agg(DISTINCT coalesce(nullif(cc.SUBORDINATE_CA_OWNER, ''), cc.INCLUDED_CERTIFICATE_OWNER)), ',') CA_OWNER FROM ccadb_certificate cc, ca_certificate cac WHERE cc.CERTIFICATE_ID = cac.CERTIFICATE_ID AND EXISTS ( SELECT 1 FROM ca_trust_purpose ctp WHERE ctp.CA_ID = cac.CA_ID AND ctp.TRUST_CONTEXT_ID IN (1, 5, 12) AND ctp.TRUST_PURPOSE_ID = 1 ) GROUP BY cac.CA_ID ) SELECT coalesce(co.CA_OWNER, 'OTHER') CA_OWNER, trcs.DATE_OF_VALIDITY, sum(trcs.COUNT_UNEXPIRED - trcs.COUNT_REVOKED) COUNT_ACTIVE FROM temp_russian_certs_summary trcs LEFT JOIN ca_owner co ON (trcs.ISSUER_CA_ID = co.CA_ID) WHERE trcs.DATE_OF_VALIDITY < date_trunc('day', now() AT TIME ZONE 'UTC') GROUP BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ORDER BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ) sub GROUP BY sub.CA_OWNER ) sub2 ORDER BY CASE substr(line, 1, 6) WHEN '"CA OW' THEN 1 WHEN '"OTHER' THEN 3 ELSE 2 END, line) TO 'Active_Russian_SANdNSNames.csv'

-- Newly Revoked Cert dNSNames report.
\COPY (SELECT sub2.* FROM ( SELECT array_to_string(array_prepend('"CA OWNER ↓    DATE OF VALIDITY →"', array_agg((gs::date)::text)), ',') line FROM generate_series('2022-02-21', now() AT TIME ZONE 'UTC' - interval '1 day', '1 day'::interval) gs UNION SELECT array_to_string(array_prepend('"' || sub.CA_OWNER::text || '"', array_agg(sub.COUNT_NEWLY_REVOKED::text ORDER BY sub.DATE_OF_VALIDITY)), ',') line FROM ( WITH ca_owner AS ( SELECT cac.CA_ID, array_to_string(array_agg(DISTINCT coalesce(nullif(cc.SUBORDINATE_CA_OWNER, ''), cc.INCLUDED_CERTIFICATE_OWNER)), ',') CA_OWNER FROM ccadb_certificate cc, ca_certificate cac WHERE cc.CERTIFICATE_ID = cac.CERTIFICATE_ID AND EXISTS ( SELECT 1 FROM ca_trust_purpose ctp WHERE ctp.CA_ID = cac.CA_ID AND ctp.TRUST_CONTEXT_ID IN (1, 5, 12) AND ctp.TRUST_PURPOSE_ID = 1 ) GROUP BY cac.CA_ID ) SELECT coalesce(co.CA_OWNER, 'OTHER') CA_OWNER, trcs.DATE_OF_VALIDITY, sum(trcs.COUNT_NEWLY_REVOKED) COUNT_NEWLY_REVOKED FROM temp_russian_certs_summary trcs LEFT JOIN ca_owner co ON (trcs.ISSUER_CA_ID = co.CA_ID) WHERE trcs.DATE_OF_VALIDITY < date_trunc('day', now() AT TIME ZONE 'UTC') GROUP BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ORDER BY co.CA_OWNER, trcs.DATE_OF_VALIDITY ) sub GROUP BY sub.CA_OWNER ) sub2 ORDER BY CASE substr(line, 1, 6) WHEN '"CA OW' THEN 1 WHEN '"OTHER' THEN 3 ELSE 2 END, line) TO 'Revoked_Russian_SANdNSNames.csv'

Revoked_Russian_SANdNSNames.csv

setup.sql

CREATE TABLE temp_russian_certs (
  CERTIFICATE_ID  bigint,
  NAME_VALUE text,
  ISSUER_CA_ID integer,
  SERIAL_NUMBER bytea,
  REVOKED_AT timestamp,
  REASON_CODE smallint,
  NOT_BEFORE timestamp,
  NOT_AFTER timestamp
);

CREATE INDEX trc_certid
  ON temp_russian_certs ( CERTIFICATE_ID );

CREATE INDEX trc_serial
  ON temp_russian_certs ( SERIAL_NUMBER );

CREATE INDEX trc_ica_serial
  ON temp_russian_certs ( ISSUER_CA_ID, SERIAL_NUMBER );

CREATE INDEX trc_notbefore
  ON temp_russian_certs ( NOT_BEFORE );


CREATE TABLE temp_russian_certs_summary (
  ISSUER_CA_ID         integer,
  DATE_OF_VALIDITY     date,
  COUNT_NEW            bigint,
  COUNT_UNEXPIRED      bigint,
  COUNT_REVOKED        bigint,
  COUNT_NEWLY_REVOKED  bigint
);


GRANT SELECT ON temp_russian_certs TO guest;

GRANT SELECT ON temp_russian_certs_summary TO guest;

update.sql

-- Discover certs.  This can be re-run to find new certs since the last run.
DO
$$DECLARE
  t_maxCertificateID  temp_russian_certs.CERTIFICATE_ID%TYPE;
  t_count             bigint;
  l_loop              record;
BEGIN
  TRUNCATE TABLE temp_russian_certs_summary;

  SELECT coalesce(max(CERTIFICATE_ID), 0)
    INTO t_maxCertificateID
    FROM temp_russian_certs;
  RAISE NOTICE 'Discovering Russian certs newer than crt.sh ID %', t_maxCertificateID;

  INSERT INTO temp_russian_certs ( CERTIFICATE_ID, NAME_VALUE, ISSUER_CA_ID )
  SELECT cai.CERTIFICATE_ID, cai.NAME_VALUE, cai.ISSUER_CA_ID
    FROM certificate_and_identities cai
    WHERE cai.CERTIFICATE_ID > t_maxCertificateID
      AND plainto_tsquery('certwatch', '%.ru') @@ identities(cai.CERTIFICATE)
      AND cai.NAME_VALUE ILIKE '%.ru'
      AND cai.NAME_TYPE IN ('2.5.4.3', 'san:dNSName')
      AND coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
  UNION
  SELECT cai.CERTIFICATE_ID, cai.NAME_VALUE, cai.ISSUER_CA_ID
    FROM certificate_and_identities cai
    WHERE cai.CERTIFICATE_ID > t_maxCertificateID
      AND plainto_tsquery('certwatch', '%.su') @@ identities(cai.CERTIFICATE)
      AND cai.NAME_VALUE ILIKE '%.su'
      AND cai.NAME_TYPE IN ('2.5.4.3', 'san:dNSName')
      AND coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
  UNION
  SELECT cai.CERTIFICATE_ID, cai.NAME_VALUE, cai.ISSUER_CA_ID
    FROM certificate_and_identities cai
    WHERE cai.CERTIFICATE_ID > t_maxCertificateID
      AND plainto_tsquery('certwatch', '%.by') @@ identities(cai.CERTIFICATE)
      AND cai.NAME_VALUE ILIKE '%.by'
      AND cai.NAME_TYPE IN ('2.5.4.3', 'san:dNSName')
      AND coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) > now() AT TIME ZONE 'UTC';
  GET DIAGNOSTICS t_count = ROW_COUNT;
  RAISE NOTICE 'New Russian certs found: %', t_count;

  -- Get serial numbers of newly added certs.
  UPDATE temp_russian_certs
    SET SERIAL_NUMBER = x509_serialNumber(c.CERTIFICATE),
        NOT_BEFORE = x509_notBefore(c.CERTIFICATE),
        NOT_AFTER = x509_notAfter(c.CERTIFICATE)
    FROM certificate c
    WHERE CERTIFICATE_ID = c.ID
      AND SERIAL_NUMBER IS NULL;
  RAISE NOTICE 'Serial Numbers and Validity Periods added';

  -- Get latest revocation information.  Do this for all certs, in case any reason codes have been updated.
  UPDATE temp_russian_certs trc
    SET REVOKED_AT = cr.REVOCATION_DATE,
        REASON_CODE = cr.REASON_CODE
    FROM crl_revoked cr
    WHERE trc.ISSUER_CA_ID = cr.CA_ID
      AND trc.SERIAL_NUMBER = cr.SERIAL_NUMBER;
  RAISE NOTICE 'Revocation Details added';

  FOR l_loop IN (
    SELECT generate_series('2022-02-21', now() AT TIME ZONE 'UTC', '1 day'::interval)::date DATE_OF_VALIDITY
  ) LOOP
    INSERT INTO temp_russian_certs_summary ( ISSUER_CA_ID, DATE_OF_VALIDITY, COUNT_NEW, COUNT_UNEXPIRED, COUNT_REVOKED, COUNT_NEWLY_REVOKED )
      SELECT trc.ISSUER_CA_ID, l_loop.DATE_OF_VALIDITY,
             sum(CASE WHEN trc.NOT_BEFORE BETWEEN l_loop.DATE_OF_VALIDITY AND (l_loop.DATE_OF_VALIDITY + '1 day'::interval - '1 second'::interval) THEN 1 ELSE 0 END),
             sum(CASE WHEN l_loop.DATE_OF_VALIDITY BETWEEN trc.NOT_BEFORE AND (trc.NOT_AFTER - '1 day'::interval + '1 second'::interval) THEN 1 ELSE 0 END),
             sum(CASE WHEN (l_loop.DATE_OF_VALIDITY BETWEEN trc.NOT_BEFORE AND (trc.NOT_AFTER - '1 day'::interval + '1 second'::interval)) AND (coalesce(trc.REVOKED_AT, 'infinity'::timestamp) <= l_loop.DATE_OF_VALIDITY) THEN 1 ELSE 0 END),
             sum(CASE WHEN trc.REVOKED_AT BETWEEN l_loop.DATE_OF_VALIDITY AND (l_loop.DATE_OF_VALIDITY + '1 day'::interval - '1 second'::interval) THEN 1 ELSE 0 END)
        FROM temp_russian_certs trc
        GROUP BY trc.ISSUER_CA_ID;
    GET DIAGNOSTICS t_count = ROW_COUNT;
    RAISE NOTICE '%: %', l_loop.DATE_OF_VALIDITY, t_count;
  END LOOP;
END$$;

Some notes on how to interpret the data:

• These reports currently cover the .ru, .su, and .by TLDs only.
• The "Active" report shows the total number of Russian/Belarusian subject:dNSNames that were "active" (i.e., Unexpired and Unrevoked) for the entirety of each 24hr period (in the UTC timezone), grouped by CA Owner / Day.
• The "New" report shows daily Issuance of Russian/Belarusian subject:dNSNames since 21st February 2022, grouped by CA Owner / Day.
• The "Revoked" report shows daily Revocations (via CRL) of Russian/Belarusian subject:dNSNames. (It's not feasible to report on revocations via OCSP, because crt.sh doesn't have that information cached).
• Each subjectAltName:dNSName field observed by crt.sh is counted individually, which means that there's no deduplication of precert/cert pairs or of multiple occurrences of the same FQDN across multiple certs.

Whoever sees this, please don't ban issuing certificates to these zones. We rely on CA to browse Internet safely. Doing that would create government controlled CA. Implementing global MITM would be a matter of time (see Kazakhstan scenario). People of Russia and Belarus want to browse free Internet in search of truth (Many "opposition" websites reside in .ru zone, ex: tjournal.ru, which is blocked by Russian Government).