diff options
| -rw-r--r-- | create_schema.sql | 54 | ||||
| -rw-r--r-- | drop_schema.sql | 8 | ||||
| -rw-r--r-- | lint_cached.fnc | 52 | ||||
| -rw-r--r-- | lint_summarizer.fnc | 35 | ||||
| -rw-r--r-- | lint_update_1week_stats.sql | 56 | ||||
| -rw-r--r-- | web_apis.fnc | 165 |
6 files changed, 155 insertions, 215 deletions
diff --git a/create_schema.sql b/create_schema.sql index bec3756..0c34c50 100644 --- a/create_schema.sql +++ b/create_schema.sql @@ -242,10 +242,13 @@ CREATE TYPE linter_type AS ENUM ( ); CREATE TABLE linter_version ( + ID smallint, VERSION_STRING text, GIT_COMMIT bytea, DEPLOYED_AT timestamp, - LINTER linter_type + LINTER linter_type, + CONSTRAINT lv_pk + PRIMARY KEY (ID) ); CREATE UNIQUE INDEX lv_li_da @@ -266,35 +269,47 @@ CREATE TABLE lint_issue ( ); CREATE TABLE lint_cert_issue ( - ID bigserial, - CERTIFICATE_ID integer, + CERTIFICATE_ID bigint, LINT_ISSUE_ID integer, ISSUER_CA_ID integer, - NOT_BEFORE timestamp, + NOT_BEFORE_DATE date, CONSTRAINT lci_pk - PRIMARY KEY (ID), + PRIMARY KEY (ISSUER_CA_ID, LINT_ISSUE_ID, NOT_BEFORE_DATE, CERTIFICATE_ID), + CONSTRAINT lci_ca_fk + FOREIGN KEY (ISSUER_CA_ID) + REFERENCES ca(ID), + CONSTRAINT lci_li_fk + FOREIGN KEY (LINT_ISSUE_ID) + REFERENCES lint_issue(ID), CONSTRAINT lci_c_fk FOREIGN KEY (CERTIFICATE_ID) - REFERENCES certificate(ID), - CONSTRAINT lci_ci_fk + REFERENCES certificate(ID) +); + +CREATE INDEX lci_c + ON lint_cert_issue (CERTIFICATE_ID); + +CREATE TABLE lint_summary ( + LINT_ISSUE_ID integer, + ISSUER_CA_ID integer, + NOT_BEFORE_DATE date, + NO_OF_CERTS integer, + CONSTRAINT ls_pk + PRIMARY KEY (LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE), + CONSTRAINT ls_li_fk FOREIGN KEY (LINT_ISSUE_ID) REFERENCES lint_issue(ID), - CONSTRAINT lci_ca_fk + CONSTRAINT ls_ca_fk FOREIGN KEY (ISSUER_CA_ID) REFERENCES ca(ID) ); -CREATE INDEX lci_c_ci - ON lint_cert_issue (CERTIFICATE_ID, LINT_ISSUE_ID); - -CREATE INDEX lci_ca_ci_nb_c - ON lint_cert_issue (ISSUER_CA_ID, LINT_ISSUE_ID, NOT_BEFORE, CERTIFICATE_ID); +\i lint_summarizer.fnc -CREATE INDEX lci_ci_nb - ON lint_cert_issue (LINT_ISSUE_ID, NOT_BEFORE); - -CREATE INDEX lci_nb_ca_ci - ON lint_cert_issue (NOT_BEFORE, ISSUER_CA_ID, LINT_ISSUE_ID); +CREATE TRIGGER lint_summarizer + BEFORE INSERT OR DELETE on lint_cert_issue + FOR EACH ROW + EXECUTE PROCEDURE lint_summarizer(); CREATE TABLE trust_context ( @@ -450,6 +465,7 @@ INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 1 INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 195, 'EV Server Authentication', '2.16.756.1.17.3.22.51', 1 ); INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 196, 'EV Server Authentication', '1.3.171.1.1.1.10.8', 1 ); INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 197, 'EV Server Authentication', '1.2.616.1.113527.2.5.1.7', 1 ); +INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 198, 'EV Server Authentication', '1.3.6.1.4.1.4146.1.2', 1 ); CREATE TABLE applicable_purpose( @@ -701,6 +717,8 @@ GRANT SELECT ON lint_issue TO crtsh; GRANT SELECT ON lint_cert_issue TO crtsh; +GRANT SELECT ON lint_summary TO crtsh; + GRANT SELECT ON trust_context TO crtsh; GRANT SELECT ON trust_purpose TO crtsh; diff --git a/drop_schema.sql b/drop_schema.sql index bbe89f4..a9838bc 100644 --- a/drop_schema.sql +++ b/drop_schema.sql @@ -45,6 +45,12 @@ DROP FUNCTION lint_cached( ); +DROP TRIGGER lint_summarizer; + +DROP FUNCTION lint_summarizer( +); + + DROP TABLE cached_response; DROP TABLE mozilla_root_hashes; @@ -79,6 +85,8 @@ DROP TABLE trust_purpose; DROP TABLE trust_context; +DROP TABLE lint_summary; + DROP TABLE lint_cert_issue; DROP TABLE lint_issue; diff --git a/lint_cached.fnc b/lint_cached.fnc index 939f904..00f8965 100644 --- a/lint_cached.fnc +++ b/lint_cached.fnc @@ -88,29 +88,41 @@ BEGIN END IF; FOR l_record IN EXECUTE t_query USING t_certificate, t_certType LOOP - SELECT li.ID - INTO t_lintIssueID - FROM lint_issue li - WHERE li.LINTER = v_linter - AND li.SEVERITY = substr(l_record.LINT, 1, 1) - AND li.ISSUE_TEXT = substr(l_record.LINT, 4); - IF NOT FOUND THEN - INSERT INTO lint_issue ( - LINTER, SEVERITY, ISSUE_TEXT + IF substr(l_record.LINT, 1, 1) IN ('W', 'E', 'F') THEN + SELECT li.ID + INTO t_lintIssueID + FROM lint_issue li + WHERE li.LINTER = v_linter + AND li.SEVERITY = substr(l_record.LINT, 1, 1) + AND li.ISSUE_TEXT = substr(l_record.LINT, 4); + IF NOT FOUND THEN + BEGIN + INSERT INTO lint_issue ( + LINTER, SEVERITY, ISSUE_TEXT + ) + VALUES ( + v_linter, substr(l_record.LINT, 1, 1), substr(l_record.LINT, 4) + ) + RETURNING ID + INTO t_lintIssueID; + EXCEPTION + WHEN unique_violation THEN + SELECT li.ID + INTO t_lintIssueID + FROM lint_issue li + WHERE li.LINTER = v_linter + AND li.SEVERITY = substr(l_record.LINT, 1, 1) + AND li.ISSUE_TEXT = substr(l_record.LINT, 4); + END; + END IF; + INSERT INTO lint_cert_issue ( + CERTIFICATE_ID, lint_issue_ID, ISSUER_CA_ID, NOT_BEFORE_DATE ) VALUES ( - v_linter, substr(l_record.LINT, 1, 1), substr(l_record.LINT, 4) - ) - RETURNING ID - INTO t_lintIssueID; + cert_id, t_lintIssueID, t_issuerCAID, x509_notBefore(t_certificate)::date + ); + RETURN NEXT t_lintIssueID; END IF; - INSERT INTO lint_cert_issue ( - CERTIFICATE_ID, lint_issue_ID, ISSUER_CA_ID, NOT_BEFORE - ) - VALUES ( - cert_id, t_lintIssueID, t_issuerCAID, x509_notBefore(t_certificate) - ); - RETURN NEXT t_lintIssueID; END LOOP; IF v_linter = 'cablint' THEN diff --git a/lint_summarizer.fnc b/lint_summarizer.fnc new file mode 100644 index 0000000..240eff3 --- /dev/null +++ b/lint_summarizer.fnc @@ -0,0 +1,35 @@ +CREATE OR REPLACE FUNCTION lint_summarizer( +) RETURNS TRIGGER +AS $$ +DECLARE + t_noOfCerts lint_summary.NO_OF_CERTS%TYPE; +BEGIN + IF TG_OP = 'INSERT' THEN + INSERT INTO lint_summary ( + LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE, NO_OF_CERTS + ) + VALUES ( + new.LINT_ISSUE_ID, new.ISSUER_CA_ID, new.NOT_BEFORE_DATE, 1 + ) + ON CONFLICT (LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE) DO UPDATE + SET NO_OF_CERTS = lint_summary.NO_OF_CERTS + 1; + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + UPDATE lint_summary + SET NO_OF_CERTS = NO_OF_CERTS - 1 + WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID + AND ISSUER_CA_ID = old.ISSUER_CA_ID + AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE + RETURNING NO_OF_CERTS + INTO t_noOfCerts; + IF FOUND AND (t_noOfCerts = 0) THEN + DELETE FROM lint_summary + WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID + AND ISSUER_CA_ID = old.ISSUER_CA_ID + AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE + AND NO_OF_CERTS = 0; + END IF; + RETURN OLD; + END IF; +END; +$$ LANGUAGE plpgsql STRICT; diff --git a/lint_update_1week_stats.sql b/lint_update_1week_stats.sql index bb8294f..8543ef2 100644 --- a/lint_update_1week_stats.sql +++ b/lint_update_1week_stats.sql @@ -9,9 +9,7 @@ SELECT linter, 0::bigint ERROR_CERTS, 0::bigint ERROR_ISSUES, 0::bigint WARNING_CERTS, - 0::bigint WARNING_ISSUES, - 0::bigint NOTICE_CERTS, - 0::bigint NOTICE_ISSUES + 0::bigint WARNING_ISSUES FROM certificate c, ca, unnest(array_append(enum_range(NULL::linter_type), NULL)) linter WHERE x509_notBefore(c.CERTIFICATE) >= date_trunc('day', statement_timestamp() - interval '1 week') AND c.ISSUER_CA_ID = ca.ID @@ -27,9 +25,9 @@ UPDATE lint_1week_summary_temp l1st lci.ISSUER_CA_ID, li.LINTER FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID - AND li.SEVERITY NOT IN ('I', 'B') + AND li.SEVERITY IN ('W', 'E', 'F') GROUP BY lci.ISSUER_CA_ID, li.LINTER ) sub WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID @@ -42,9 +40,9 @@ UPDATE lint_1week_summary_temp l1st count(*) ALL_ISSUES, lci.ISSUER_CA_ID FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID - AND li.SEVERITY NOT IN ('I', 'B') + AND li.SEVERITY IN ('W', 'E', 'F') GROUP BY lci.ISSUER_CA_ID ) sub WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID @@ -59,7 +57,7 @@ UPDATE lint_1week_summary_temp l1st lci.ISSUER_CA_ID, li.LINTER FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'F' GROUP BY lci.ISSUER_CA_ID, li.LINTER @@ -74,7 +72,7 @@ UPDATE lint_1week_summary_temp l1st count(*) FATAL_ISSUES, lci.ISSUER_CA_ID FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'F' GROUP BY lci.ISSUER_CA_ID @@ -91,7 +89,7 @@ UPDATE lint_1week_summary_temp l1st lci.ISSUER_CA_ID, li.LINTER FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'E' GROUP BY lci.ISSUER_CA_ID, li.LINTER @@ -106,7 +104,7 @@ UPDATE lint_1week_summary_temp l1st count(*) ERROR_ISSUES, lci.ISSUER_CA_ID FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'E' GROUP BY lci.ISSUER_CA_ID @@ -123,7 +121,7 @@ UPDATE lint_1week_summary_temp l1st lci.ISSUER_CA_ID, li.LINTER FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'W' GROUP BY lci.ISSUER_CA_ID, li.LINTER @@ -138,7 +136,7 @@ UPDATE lint_1week_summary_temp l1st count(*) WARNING_ISSUES, lci.ISSUER_CA_ID FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') + WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week') AND lci.LINT_ISSUE_ID = li.ID AND li.SEVERITY = 'W' GROUP BY lci.ISSUER_CA_ID @@ -146,38 +144,6 @@ UPDATE lint_1week_summary_temp l1st WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID AND l1st.LINTER IS NULL; -UPDATE lint_1week_summary_temp l1st - SET NOTICE_CERTS = sub.NOTICE_CERTS, - NOTICE_ISSUES = sub.NOTICE_ISSUES - FROM ( - SELECT count(DISTINCT lci.CERTIFICATE_ID) NOTICE_CERTS, - count(*) NOTICE_ISSUES, - lci.ISSUER_CA_ID, - li.LINTER - FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') - AND lci.LINT_ISSUE_ID = li.ID - AND li.SEVERITY = 'N' - GROUP BY lci.ISSUER_CA_ID, li.LINTER - ) sub - WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID - AND l1st.LINTER = sub.LINTER; -UPDATE lint_1week_summary_temp l1st - SET NOTICE_CERTS = sub.NOTICE_CERTS, - NOTICE_ISSUES = sub.NOTICE_ISSUES - FROM ( - SELECT count(DISTINCT lci.CERTIFICATE_ID) NOTICE_CERTS, - count(*) NOTICE_ISSUES, - lci.ISSUER_CA_ID - FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week') - AND lci.LINT_ISSUE_ID = li.ID - AND li.SEVERITY = 'N' - GROUP BY lci.ISSUER_CA_ID - ) sub - WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID - AND l1st.LINTER IS NULL; - ANALYZE lint_1week_summary_temp; GRANT SELECT ON lint_1week_summary_temp TO httpd; diff --git a/web_apis.fnc b/web_apis.fnc index 6e3a335..b05c02c 100644 --- a/web_apis.fnc +++ b/web_apis.fnc @@ -126,7 +126,7 @@ DECLARE t_joinToCertificate_table text; t_joinToCTLogEntry text; t_showIdentity boolean; - t_minNotBefore timestamp; + t_minNotBefore date; t_minNotBeforeString text; t_excludeExpired text; t_excludeAffectedCerts text; @@ -308,10 +308,10 @@ BEGIN t_temp := get_parameter('minNotBefore', paramNames, paramValues); IF t_temp IS NULL THEN - t_minNotBefore := date_trunc('day', statement_timestamp() - interval '1 week'); + t_minNotBefore := (statement_timestamp() at time zone 'UTC' - interval '1 week')::date; t_minNotBeforeString := ''; ELSE - t_minNotBefore := t_temp::timestamp; + t_minNotBefore := t_temp::date; t_minNotBeforeString := '&minNotBefore=' || t_temp; END IF; @@ -2465,29 +2465,23 @@ Content-Type: text/plain; charset=UTF-8 </TR> '; FOR l_record IN ( - SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, + SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, CASE li.SEVERITY WHEN 'F' THEN 1 WHEN 'E' THEN 2 WHEN 'W' THEN 3 - WHEN 'N' THEN 4 - WHEN 'I' THEN 5 - WHEN 'B' THEN 6 - ELSE 7 + ELSE 4 END ISSUE_TYPE, CASE li.SEVERITY WHEN 'F' THEN '<SPAN class="fatal"> FATAL:' WHEN 'E' THEN '<SPAN class="error"> ERROR:' WHEN 'W' THEN '<SPAN class="warning"> WARNING:' - WHEN 'N' THEN '<SPAN class="notice"> NOTICE:' - WHEN 'I' THEN '<SPAN> INFO:' - WHEN 'B' THEN '<SPAN> BUG:' ELSE '<SPAN> ' || li.SEVERITY || ':' END ISSUE_HEADING - FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= t_minNotBefore - AND lci.ISSUER_CA_ID = t_value::integer - AND lci.LINT_ISSUE_ID = li.ID + FROM lint_summary ls, lint_issue li + WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore + AND ls.ISSUER_CA_ID = t_value::integer + AND ls.LINT_ISSUE_ID = li.ID AND li.LINTER = 'cablint' GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT ORDER BY ISSUE_TYPE, NUM_CERTS DESC @@ -2520,29 +2514,23 @@ Content-Type: text/plain; charset=UTF-8 </TR> '; FOR l_record IN ( - SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, + SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, CASE li.SEVERITY WHEN 'F' THEN 1 WHEN 'E' THEN 2 WHEN 'W' THEN 3 - WHEN 'N' THEN 4 - WHEN 'I' THEN 5 - WHEN 'B' THEN 6 - ELSE 7 + ELSE 4 END ISSUE_TYPE, CASE li.SEVERITY WHEN 'F' THEN '<SPAN class="fatal"> FATAL:' WHEN 'E' THEN '<SPAN class="error"> ERROR:' WHEN 'W' THEN '<SPAN class="warning"> WARNING:' - WHEN 'N' THEN '<SPAN class="notice"> NOTICE:' - WHEN 'I' THEN '<SPAN> INFO:' - WHEN 'B' THEN '<SPAN> BUG:' ELSE '<SPAN> ' || li.SEVERITY || ':' END ISSUE_HEADING - FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= t_minNotBefore - AND lci.ISSUER_CA_ID = t_value::integer - AND lci.LINT_ISSUE_ID = li.ID + FROM lint_summary ls, lint_issue li + WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore + AND ls.ISSUER_CA_ID = t_value::integer + AND ls.LINT_ISSUE_ID = li.ID AND li.LINTER = 'x509lint' GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT ORDER BY ISSUE_TYPE, NUM_CERTS DESC @@ -2575,29 +2563,23 @@ Content-Type: text/plain; charset=UTF-8 </TR> '; FOR l_record IN ( - SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, + SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT, CASE li.SEVERITY WHEN 'F' THEN 1 WHEN 'E' THEN 2 WHEN 'W' THEN 3 - WHEN 'N' THEN 4 - WHEN 'I' THEN 5 - WHEN 'B' THEN 6 - ELSE 7 + ELSE 4 END ISSUE_TYPE, CASE li.SEVERITY WHEN 'F' THEN '<SPAN class="fatal"> FATAL:' WHEN 'E' THEN '<SPAN class="error"> ERROR:' WHEN 'W' THEN '<SPAN class="warning"> WARNING:' - WHEN 'N' THEN '<SPAN class="notice"> NOTICE:' - WHEN 'I' THEN '<SPAN> INFO:' - WHEN 'B' THEN '<SPAN> BUG:' ELSE '<SPAN> ' || li.SEVERITY || ':' END ISSUE_HEADING - FROM lint_cert_issue lci, lint_issue li - WHERE lci.NOT_BEFORE >= t_minNotBefore - AND lci.ISSUER_CA_ID = t_value::integer - AND lci.LINT_ISSUE_ID = li.ID + FROM lint_summary ls, lint_issue li + WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore + AND ls.ISSUER_CA_ID = t_value::integer + AND ls.LINT_ISSUE_ID = li.ID AND li.LINTER = 'zlint' GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT ORDER BY ISSUE_TYPE, NUM_CERTS DESC @@ -3075,9 +3057,6 @@ Content-Type: text/plain; charset=UTF-8 WHEN 'F' THEN '<SPAN class="fatal"> FATAL:' WHEN 'E' THEN '<SPAN class="error"> ERROR:' WHEN 'W' THEN '<SPAN class="warning"> WARNING:' - WHEN 'N' THEN '<SPAN class="notice"> NOTICE:' - WHEN 'I' THEN '<SPAN> INFO:' - WHEN 'B' THEN '<SPAN> BUG:' ELSE '<SPAN> ' || li.SEVERITY || ':' END || ' ' || li.ISSUE_TEXT || ' </SPAN>' INTO t_temp @@ -3151,7 +3130,7 @@ Content-Type: text/plain; charset=UTF-8 ' WHERE c.ISSUER_CA_ID = $1::integer' || chr(10) || ' AND c.ID = lci.CERTIFICATE_ID' || chr(10) || ' AND lci.ISSUER_CA_ID = $1::integer' || chr(10) || - ' AND lci.NOT_BEFORE >= $3' || chr(10) || + ' AND lci.NOT_BEFORE_DATE >= $3' || chr(10) || ' AND lci.LINT_ISSUE_ID = $2::integer' || chr(10) || ' AND lci.LINT_ISSUE_ID = li.ID' || chr(10); IF t_linter IS NOT NULL THEN @@ -3406,7 +3385,7 @@ Content-Type: text/plain; charset=UTF-8 END IF; t_where := t_where || chr(10) || ' AND lci.LINT_ISSUE_ID = $1::integer' || chr(10) || - ' AND lci.NOT_BEFORE >= $2' || chr(10) || + ' AND lci.NOT_BEFORE_DATE >= $2' || chr(10) || ' AND lci.LINT_ISSUE_ID = li.ID' || chr(10) || ' AND ca.LINTING_APPLIES'; IF t_linter IS NOT NULL THEN @@ -3784,11 +3763,10 @@ Content-Type: application/atom+xml t_output := t_output || ' ' || t_dirSymbol; END IF; t_output := t_output || '</TH> - <TH colspan="3"><A title="These errors are fatal to the checks and prevent most further checks from being executed. These are extremely bad errors."><SPAN class="fatal"> FATAL </SPAN></A></TH> - <TH colspan="3"><A title="These are issues where the certificate is not compliant with the standard."><SPAN class="error"> ERROR </SPAN></A></TH> - <TH colspan="3"><A title="These are issues where a standard recommends differently but the standard uses terms such as ''SHOULD'' or ''MAY''."><SPAN class="warning"> WARNING </SPAN></A></TH> - <TH colspan="3"><A title="These are items known to cause issues with one or more implementations of certificate processing but are not errors according to the standard."><SPAN class="notice"> NOTICE </SPAN></A></TH> - <TH colspan="3"><A title="FATAL + ERROR + WARNING + NOTICE">ALL</A></TH> + <TH colspan="2"><A title="These errors are fatal to the checks and prevent most further checks from being executed. These are extremely bad errors."><SPAN class="fatal"> FATAL </SPAN></A></TH> + <TH colspan="2"><A title="These are issues where the certificate is not compliant with the standard."><SPAN class="error"> ERROR </SPAN></A></TH> + <TH colspan="2"><A title="These are issues where a standard recommends differently but the standard uses terms such as ''SHOULD'' or ''MAY''."><SPAN class="warning"> WARNING </SPAN></A></TH> + <TH colspan="2"><A title="FATAL + ERROR + WARNING">ALL</A></TH> </TR> <TR> <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=4' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>'; @@ -3801,11 +3779,6 @@ Content-Type: application/atom+xml t_output := t_output || ' ' || t_dirSymbol; END IF; t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=6' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>'; - IF t_sort = 6 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=7' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>'; IF t_sort = 7 THEN t_output := t_output || ' ' || t_dirSymbol; @@ -3816,11 +3789,6 @@ Content-Type: application/atom+xml t_output := t_output || ' ' || t_dirSymbol; END IF; t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=9' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>'; - IF t_sort = 9 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=10' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>'; IF t_sort = 10 THEN t_output := t_output || ' ' || t_dirSymbol; @@ -3831,26 +3799,6 @@ Content-Type: application/atom+xml t_output := t_output || ' ' || t_dirSymbol; END IF; t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=12' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>'; - IF t_sort = 12 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=13' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>'; - IF t_sort = 13 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=14' || t_groupByParameter || t_issuerOParameter || '">%</A>'; - IF t_sort = 14 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=15' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>'; - IF t_sort = 15 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=16' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>'; IF t_sort = 16 THEN t_output := t_output || ' ' || t_dirSymbol; @@ -3861,11 +3809,6 @@ Content-Type: application/atom+xml t_output := t_output || ' ' || t_dirSymbol; END IF; t_output := t_output || '</TH> - <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=18' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>'; - IF t_sort = 18 THEN - t_output := t_output || ' ' || t_dirSymbol; - END IF; - t_output := t_output || '</TH> </TR> '; END IF; @@ -3874,40 +3817,26 @@ Content-Type: application/atom+xml t_query := 'SELECT NULL::integer ISSUER_CA_ID,' || chr(10) || ' (sum(l1s.CERTS_ISSUED))::bigint CERTS_ISSUED,' || chr(10) || ' (sum(l1s.ALL_CERTS))::bigint ALL_CERTS,' || chr(10) || - ' (sum(l1s.ALL_ISSUES))::bigint ALL_ISSUES,' || chr(10) || ' ((sum(l1s.ALL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ALL_PERC,' || chr(10) || ' (sum(l1s.FATAL_CERTS))::bigint FATAL_CERTS,' || chr(10) || - ' (sum(l1s.FATAL_ISSUES))::bigint FATAL_ISSUES,' || chr(10) || ' ((sum(l1s.FATAL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric FATAL_PERC,' || chr(10) || ' (sum(l1s.ERROR_CERTS))::bigint ERROR_CERTS,' || chr(10) || - ' (sum(l1s.ERROR_ISSUES))::bigint ERROR_ISSUES,' || chr(10) || ' ((sum(l1s.ERROR_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ERROR_PERC,' || chr(10) || ' (sum(l1s.WARNING_CERTS))::bigint WARNING_CERTS,' || chr(10) || - ' (sum(l1s.WARNING_ISSUES))::bigint WARNING_ISSUES,' || chr(10) || ' ((sum(l1s.WARNING_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric WARNING_PERC,' || chr(10) || - ' (sum(l1s.NOTICE_CERTS))::bigint NOTICE_CERTS,' || chr(10) || - ' (sum(l1s.NOTICE_ISSUES))::bigint NOTICE_ISSUES,' || chr(10) || - ' ((sum(l1s.NOTICE_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric NOTICE_PERC,' || chr(10) || ' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) || ' NULL ISSUER_FRIENDLY_NAME' || chr(10); ELSE t_query := 'SELECT l1s.ISSUER_CA_ID::integer,' || chr(10) || ' l1s.CERTS_ISSUED::bigint,' || chr(10) || ' l1s.ALL_CERTS::bigint,' || chr(10) || - ' l1s.ALL_ISSUES::bigint,' || chr(10) || ' ((l1s.ALL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ALL_PERC,' || chr(10) || ' l1s.FATAL_CERTS::bigint,' || chr(10) || - ' l1s.FATAL_ISSUES::bigint,' || chr(10) || ' ((l1s.FATAL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) FATAL_PERC,' || chr(10) || ' l1s.ERROR_CERTS::bigint,' || chr(10) || - ' l1s.ERROR_ISSUES::bigint,' || chr(10) || ' ((l1s.ERROR_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ERROR_PERC,' || chr(10) || ' l1s.WARNING_CERTS::bigint,' || chr(10) || - ' l1s.WARNING_ISSUES::bigint,' || chr(10) || ' ((l1s.WARNING_CERTS * 100) / l1s.CERTS_ISSUED::numeric) WARNING_PERC,' || chr(10) || - ' l1s.NOTICE_CERTS::bigint,' || chr(10) || - ' l1s.NOTICE_ISSUES::bigint,' || chr(10) || - ' ((l1s.NOTICE_CERTS * 100) / l1s.CERTS_ISSUED::numeric) NOTICE_PERC,' || chr(10) || ' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) || ' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''_friendlyName_'') ISSUER_FRIENDLY_NAME' || chr(10); END IF; @@ -3939,32 +3868,18 @@ Content-Type: application/atom+xml t_query := t_query || 'ORDER BY FATAL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 5 THEN t_query := t_query || 'ORDER BY FATAL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 6 THEN - t_query := t_query || 'ORDER BY FATAL_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 7 THEN t_query := t_query || 'ORDER BY ERROR_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 8 THEN t_query := t_query || 'ORDER BY ERROR_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 9 THEN - t_query := t_query || 'ORDER BY ERROR_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 10 THEN t_query := t_query || 'ORDER BY WARNING_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 11 THEN t_query := t_query || 'ORDER BY WARNING_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 12 THEN - t_query := t_query || 'ORDER BY WARNING_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 13 THEN - t_query := t_query || 'ORDER BY NOTICE_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 14 THEN - t_query := t_query || 'ORDER BY NOTICE_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 15 THEN - t_query := t_query || 'ORDER BY NOTICE_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 16 THEN t_query := t_query || 'ORDER BY ALL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; ELSIF t_sort = 17 THEN t_query := t_query || 'ORDER BY ALL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; - ELSIF t_sort = 18 THEN - t_query := t_query || 'ORDER BY ALL_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME'; END IF; FOR l_record IN EXECUTE t_query USING t_issuerO LOOP @@ -3993,19 +3908,12 @@ Content-Type: application/atom+xml ' <TD>' || l_record.CERTS_ISSUED::text || '</TD> <TD>' || l_record.FATAL_CERTS::text || '</TD> <TD>' || replace(round(l_record.FATAL_PERC, 2)::text, '.00', '') || '</TD> - <TD>' || l_record.FATAL_ISSUES::text || '</TD> <TD>' || l_record.ERROR_CERTS::text || '</TD> <TD>' || replace(round(l_record.ERROR_PERC, 2)::text, '.00', '') || '</TD> - <TD>' || l_record.ERROR_ISSUES::text || '</TD> <TD>' || l_record.WARNING_CERTS::text || '</TD> <TD>' || replace(round(l_record.WARNING_PERC, 2)::text, '.00', '') || '</TD> - <TD>' || l_record.WARNING_ISSUES::text || '</TD> - <TD>' || l_record.NOTICE_CERTS::text || '</TD> - <TD>' || replace(round(l_record.NOTICE_PERC, 2)::text, '.00', '') || '</TD> - <TD>' || l_record.NOTICE_ISSUES::text || '</TD> <TD>' || l_record.ALL_CERTS::text || '</TD> <TD>' || replace(round(l_record.ALL_PERC, 2)::text, '.00', '') || '</TD> - <TD>' || l_record.ALL_ISSUES::text || '</TD> </TR> '; END IF; @@ -4057,7 +3965,7 @@ Content-Type: application/atom+xml t_query := 'SELECT li.ID, li.ISSUE_TEXT,'; IF t_excludeAffectedCerts IS NULL THEN - t_query := t_query || ' count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS,'; + t_query := t_query || ' sum(ls.NO_OF_CERTS) NUM_CERTS,'; ELSE t_query := t_query || ' -1::bigint NUM_CERTS,'; END IF; @@ -4066,39 +3974,32 @@ Content-Type: application/atom+xml ' WHEN ''F'' THEN 1' || chr(10) || ' WHEN ''E'' THEN 2' || chr(10) || ' WHEN ''W'' THEN 3' || chr(10) || - ' WHEN ''N'' THEN 4' || chr(10) || - ' WHEN ''I'' THEN 5' || chr(10) || - ' WHEN ''B'' THEN 6' || chr(10) || - ' ELSE 7' || chr(10) || + ' ELSE 4' || chr(10) || ' END ISSUE_TYPE,' || chr(10) || ' CASE li.SEVERITY' || chr(10) || ' WHEN ''F'' THEN ''FATAL''' || chr(10) || ' WHEN ''E'' THEN ''ERROR''' || chr(10) || ' WHEN ''W'' THEN ''WARNING''' || chr(10) || - ' WHEN ''N'' THEN ''NOTICE''' || chr(10) || - ' WHEN ''I'' THEN ''INFO''' || chr(10) || - ' WHEN ''B'' THEN ''BUG''' || chr(10) || ' ELSE li.SEVERITY ' || chr(10) || ' END ISSUE_HEADING,' || chr(10) || ' CASE li.SEVERITY' || chr(10) || ' WHEN ''F'' THEN ''class="fatal"''' || chr(10) || ' WHEN ''E'' THEN ''class="error"''' || chr(10) || ' WHEN ''W'' THEN ''class="warning"''' || chr(10) || - ' WHEN ''N'' THEN ''class="notice"''' || chr(10) || ' ELSE ''''' || chr(10) || ' END ISSUE_CLASS' || chr(10); IF t_excludeAffectedCerts IS NULL THEN t_query := t_query || - ' FROM lint_cert_issue lci, lint_issue li, ca' || chr(10) || - ' WHERE lci.LINT_ISSUE_ID = li.ID' || chr(10) || - ' AND lci.ISSUER_CA_ID = ca.ID' || chr(10) || + ' FROM lint_summary ls, lint_issue li, ca' || chr(10) || + ' WHERE ls.LINT_ISSUE_ID = li.ID' || chr(10) || + ' AND ls.ISSUER_CA_ID = ca.ID' || chr(10) || ' AND ca.LINTING_APPLIES' || chr(10); IF t_linter IS NOT NULL THEN t_query := t_query || ' AND li.LINTER = ''' || t_linter || '''' || chr(10); END IF; t_query := t_query || - ' AND lci.NOT_BEFORE >= $1' || chr(10) || + ' AND ls.NOT_BEFORE_DATE >= $1' || chr(10) || ' GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT' || chr(10); ELSE t_query := t_query || |