1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
CREATE TABLE lint_1week_summary_temp AS
SELECT linter,
c.ISSUER_CA_ID,
count(*)::bigint CERTS_ISSUED,
0::bigint ALL_CERTS,
0::bigint ALL_ISSUES,
0::bigint FATAL_CERTS,
0::bigint FATAL_ISSUES,
0::bigint ERROR_CERTS,
0::bigint ERROR_ISSUES,
0::bigint WARNING_CERTS,
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
AND ca.LINTING_APPLIES
GROUP BY linter, c.ISSUER_CA_ID;
UPDATE lint_1week_summary_temp l1st
SET ALL_CERTS = sub.ALL_CERTS,
ALL_ISSUES = sub.ALL_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) ALL_CERTS,
count(*) ALL_ISSUES,
lci.ISSUER_CA_ID,
li.LINTER
FROM lint_cert_issue lci, lint_issue li
WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
AND lci.LINT_ISSUE_ID = li.ID
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
AND l1st.LINTER = sub.LINTER;
UPDATE lint_1week_summary_temp l1st
SET ALL_CERTS = sub.ALL_CERTS,
ALL_ISSUES = sub.ALL_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) ALL_CERTS,
count(*) ALL_ISSUES,
lci.ISSUER_CA_ID
FROM lint_cert_issue lci, lint_issue li
WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
AND lci.LINT_ISSUE_ID = li.ID
AND li.SEVERITY IN ('W', 'E', 'F')
GROUP BY lci.ISSUER_CA_ID
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER IS NULL;
UPDATE lint_1week_summary_temp l1st
SET FATAL_CERTS = sub.FATAL_CERTS,
FATAL_ISSUES = sub.FATAL_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) FATAL_CERTS,
count(*) FATAL_ISSUES,
lci.ISSUER_CA_ID,
li.LINTER
FROM lint_cert_issue lci, lint_issue li
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
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER = sub.LINTER;
UPDATE lint_1week_summary_temp l1st
SET FATAL_CERTS = sub.FATAL_CERTS,
FATAL_ISSUES = sub.FATAL_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) FATAL_CERTS,
count(*) FATAL_ISSUES,
lci.ISSUER_CA_ID
FROM lint_cert_issue lci, lint_issue li
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
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER IS NULL;
UPDATE lint_1week_summary_temp l1st
SET ERROR_CERTS = sub.ERROR_CERTS,
ERROR_ISSUES = sub.ERROR_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) ERROR_CERTS,
count(*) ERROR_ISSUES,
lci.ISSUER_CA_ID,
li.LINTER
FROM lint_cert_issue lci, lint_issue li
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
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER = sub.LINTER;
UPDATE lint_1week_summary_temp l1st
SET ERROR_CERTS = sub.ERROR_CERTS,
ERROR_ISSUES = sub.ERROR_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) ERROR_CERTS,
count(*) ERROR_ISSUES,
lci.ISSUER_CA_ID
FROM lint_cert_issue lci, lint_issue li
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
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER IS NULL;
UPDATE lint_1week_summary_temp l1st
SET WARNING_CERTS = sub.WARNING_CERTS,
WARNING_ISSUES = sub.WARNING_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) WARNING_CERTS,
count(*) WARNING_ISSUES,
lci.ISSUER_CA_ID,
li.LINTER
FROM lint_cert_issue lci, lint_issue li
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
) sub
WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
AND l1st.LINTER = sub.LINTER;
UPDATE lint_1week_summary_temp l1st
SET WARNING_CERTS = sub.WARNING_CERTS,
WARNING_ISSUES = sub.WARNING_ISSUES
FROM (
SELECT count(DISTINCT lci.CERTIFICATE_ID) WARNING_CERTS,
count(*) WARNING_ISSUES,
lci.ISSUER_CA_ID
FROM lint_cert_issue lci, lint_issue li
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
) 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;
GRANT SELECT ON lint_1week_summary_temp TO guest;
DROP TABLE lint_1week_summary;
ALTER TABLE lint_1week_summary_temp RENAME TO lint_1week_summary;
|
