Handle CA Owners for roots no longer in the Mozilla root program. Add script that updates the Mozilla cert validation telemetry data.

3 files changed, 69 insertions, 7 deletions

diff --git a/create_schema.sql b/create_schema.sql
index 71288a2..990f3c8 100644
--- a/create_schema.sql
+++ b/create_schema.sql
@@ -602,6 +602,7 @@ CREATE TABLE mozilla_root_hashes (
 	CERTIFICATE_SHA256	bytea,
 	BIN_NUMBER			smallint,
 	DISPLAY_ORDER		smallint,
+	CA_OWNER			text,
 	CONSTRAINT mrh_pk
 		PRIMARY KEY (BIN_NUMBER, CERTIFICATE_SHA256)
 );
diff --git a/update_mozilla_cert_validation_success.sql b/update_mozilla_cert_validation_success.sql
new file mode 100644
index 0000000..8484964
--- /dev/null
+++ b/update_mozilla_cert_validation_success.sql
@@ -0,0 +1,61 @@
+\timing
+
+CREATE TABLE mozilla_root_hashes_new ( LIKE mozilla_root_hashes INCLUDING INDEXES);
+
+\i mozilla_root_hashes.sql
+
+UPDATE mozilla_root_hashes_new mrhn
+	SET CERTIFICATE_ID = c.ID
+	FROM certificate c
+	WHERE mrhn.CERTIFICATE_SHA256 = digest(c.CERTIFICATE, 'sha256');
+
+UPDATE mozilla_root_hashes_new mrhn
+	SET DISPLAY_ORDER = sub.ROW_NUMBER,
+		CA_OWNER = sub.CA_OWNER
+	FROM (
+		SELECT mrhn2.CERTIFICATE_ID, coalesce(md.CA_OWNER, mrh.CA_OWNER) CA_OWNER,
+				row_number() OVER (ORDER BY coalesce(md.CA_OWNER, mrh.CA_OWNER), get_ca_name_attribute(cac.CA_ID))
+			FROM mozilla_root_hashes_new mrhn2
+				LEFT OUTER JOIN ca_certificate cac ON (mrhn2.CERTIFICATE_ID = cac.CERTIFICATE_ID)
+				LEFT OUTER JOIN mozilla_disclosure md ON (mrhn2.CERTIFICATE_ID = md.CERTIFICATE_ID)
+				LEFT OUTER JOIN mozilla_root_hashes mrh ON (mrhn2.CERTIFICATE_ID = mrh.CERTIFICATE_ID)
+			WHERE mrhn2.CERTIFICATE_ID = cac.CERTIFICATE_ID
+			GROUP BY mrhn2.CERTIFICATE_ID, coalesce(md.CA_OWNER, mrh.CA_OWNER), cac.CA_ID
+		) sub
+	WHERE mrhn.CERTIFICATE_ID = sub.CERTIFICATE_ID;
+
+BEGIN WORK;
+
+LOCK mozilla_root_hashes;
+
+TRUNCATE mozilla_root_hashes;
+
+INSERT INTO mozilla_root_hashes
+	SELECT * FROM mozilla_root_hashes_new;
+
+COMMIT WORK;
+
+DROP TABLE mozilla_root_hashes_new;
+
+
+TRUNCATE TABLE mozilla_cert_validation_success_import;
+
+\COPY mozilla_cert_validation_success_import FROM mozilla_cert_validation_success.csv
+
+
+TRUNCATE TABLE mozilla_cert_validation_success;
+
+INSERT INTO mozilla_cert_validation_success (
+		SUBMISSION_DATE, BIN_NUMBER, COUNT, CERTIFICATE_ID
+	)
+	SELECT mcvsi.SUBMISSION_DATE, mcvsi.BIN_NUMBER, sum(mcvsi.COUNT), mrh.CERTIFICATE_ID
+		FROM mozilla_cert_validation_success_import mcvsi
+			LEFT OUTER JOIN mozilla_root_hashes mrh ON (mcvsi.BIN_NUMBER = mrh.BIN_NUMBER)
+		WHERE mcvsi.RELEASE = 'release'
+		GROUP BY mcvsi.SUBMISSION_DATE, mcvsi.BIN_NUMBER, mrh.CERTIFICATE_ID;
+
+
+-- Cache page(s).
+SELECT substr(web_apis(NULL, '{output,maxage}'::text[], '{mozilla-certvalidations-by-root,0}'::text[]), 1, 6);
+
+SELECT substr(web_apis(NULL, '{output,maxage}'::text[], '{mozilla-certvalidations-by-owner,0}'::text[]), 1, 6);
diff --git a/web_apis.fnc b/web_apis.fnc
index ef04e84..64a40f6 100644
--- a/web_apis.fnc
+++ b/web_apis.fnc
@@ -725,8 +725,8 @@ BEGIN
               <TD>Mozilla</TD>
               <TD>
                 <A href="/mozilla-disclosures">CA Certificate Disclosures</A>
-                <BR><A href="/mozilla-onecrl">OneCRL</A>
                 <BR><A href="/mozilla-certvalidations">Certificate Validations</A>
+                <BR><A href="/mozilla-onecrl">OneCRL</A>
               </TD>
             </TR>
           </TABLE>
@@ -1159,12 +1159,12 @@ Content-Type: application/json
 		t_outputType := 'csv';
 		t_output := 'Date';
 		FOR l_record IN (
-					SELECT mrh.CERTIFICATE_ID, get_ca_name_attribute(cac.CA_ID) FRIENDLY_NAME, get_ca_name_attribute(cac.CA_ID, 'organizationalUnitName') OU, replace(md.CA_OWNER, chr(10), ', ') CA_OWNER
+					SELECT mrh.CERTIFICATE_ID, get_ca_name_attribute(cac.CA_ID) FRIENDLY_NAME, get_ca_name_attribute(cac.CA_ID, 'organizationalUnitName') OU, replace(mrh.CA_OWNER, chr(10), ', ') CA_OWNER
 						FROM mozilla_root_hashes mrh
 							LEFT OUTER JOIN ca_certificate cac ON (mrh.CERTIFICATE_ID = cac.CERTIFICATE_ID)
 							LEFT OUTER JOIN mozilla_disclosure md ON (mrh.CERTIFICATE_ID = md.CERTIFICATE_ID)
 						WHERE mrh.DISPLAY_ORDER IS NOT NULL
-						GROUP BY mrh.DISPLAY_ORDER, mrh.CERTIFICATE_ID, cac.CA_ID, md.CA_OWNER
+						GROUP BY mrh.DISPLAY_ORDER, mrh.CERTIFICATE_ID, cac.CA_ID, mrh.CA_OWNER
 						ORDER BY mrh.DISPLAY_ORDER
 				) LOOP
 			IF l_record.FRIENDLY_NAME IN ('GlobalSign') THEN
@@ -1191,11 +1191,11 @@ Content-Type: application/json
 		t_outputType := 'csv';
 		t_output := 'Date';
 		FOR l_record IN (
-					SELECT coalesce(replace(md.CA_OWNER, chr(10), ', '), 'UNKNOWN') CA_OWNER
+					SELECT coalesce(replace(mrh.CA_OWNER, chr(10), ', '), 'UNKNOWN') CA_OWNER
 						FROM mozilla_root_hashes mrh
 							LEFT OUTER JOIN mozilla_disclosure md ON (mrh.CERTIFICATE_ID = md.CERTIFICATE_ID)
 						WHERE mrh.DISPLAY_ORDER IS NOT NULL
-						GROUP BY md.CA_OWNER
+						GROUP BY mrh.CA_OWNER
 						ORDER BY min(mrh.DISPLAY_ORDER)
 				) LOOP
 			t_output := t_output || '|' || coalesce(l_record.CA_OWNER, 'UNKNOWN');
@@ -1203,12 +1203,12 @@ Content-Type: application/json
 
 		t_temp := '';
 		FOR l_record IN (
-					SELECT coalesce(replace(md.CA_OWNER, chr(10), ', '), 'UNKNOWN') CA_OWNER, min(mrh.DISPLAY_ORDER) DISPLAY_ORDER, mcvs.SUBMISSION_DATE, sum(mcvs.COUNT) COUNT
+					SELECT coalesce(replace(mrh.CA_OWNER, chr(10), ', '), 'UNKNOWN') CA_OWNER, min(mrh.DISPLAY_ORDER) DISPLAY_ORDER, mcvs.SUBMISSION_DATE, sum(mcvs.COUNT) COUNT
 						FROM mozilla_cert_validation_success mcvs, mozilla_root_hashes mrh
 							LEFT OUTER JOIN mozilla_disclosure md ON (mrh.CERTIFICATE_ID = md.CERTIFICATE_ID)
 						WHERE mcvs.BIN_NUMBER = mrh.BIN_NUMBER
 							AND mrh.DISPLAY_ORDER IS NOT NULL
-						GROUP BY md.CA_OWNER, mcvs.SUBMISSION_DATE
+						GROUP BY mrh.CA_OWNER, mcvs.SUBMISSION_DATE
 						ORDER BY mcvs.SUBMISSION_DATE, min(mrh.DISPLAY_ORDER)
 				) LOOP
 			IF l_record.DISPLAY_ORDER = 1 THEN