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
|
/* certwatch_db - Database schema
* Written by Rob Stradling
* Copyright (C) 2015-2016 COMODO CA Limited
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
CREATE OR REPLACE FUNCTION lint_cached(
cert_id certificate.ID%TYPE,
v_linter linter_type
) RETURNS SETOF lint_issue.ID%TYPE
AS $$
DECLARE
t_certificate certificate.CERTIFICATE%TYPE;
t_linterDeployedAt linter_version.DEPLOYED_AT%TYPE;
t_lintCachedAt timestamp;
t_lintIssueID lint_issue.ID%TYPE;
t_issuerCAID certificate.ISSUER_CA_ID%TYPE;
t_query text;
t_certType integer;
t_count integer;
l_record RECORD;
BEGIN
SELECT c.CERTIFICATE, c.ISSUER_CA_ID,
CASE v_linter
WHEN 'cablint' THEN c.CABLINT_CACHED_AT AT TIME ZONE 'UTC'
WHEN 'x509lint' THEN c.X509LINT_CACHED_AT AT TIME ZONE 'UTC'
WHEN 'zlint' THEN c.ZLINT_CACHED_AT AT TIME ZONE 'UTC'
END
INTO t_certificate, t_issuerCAID,
t_lintCachedAt
FROM certificate c
WHERE c.ID = cert_id;
IF NOT FOUND THEN
RETURN;
END IF;
SELECT max(lv.DEPLOYED_AT AT TIME ZONE 'UTC')
INTO t_linterDeployedAt
FROM linter_version lv
WHERE lv.LINTER = v_linter;
IF coalesce(t_lintCachedAt, t_linterDeployedAt) > t_linterDeployedAt THEN
RETURN QUERY
SELECT lci.LINT_ISSUE_ID
FROM lint_cert_issue lci, lint_issue li
WHERE lci.CERTIFICATE_ID = cert_id
AND lci.LINT_ISSUE_ID = li.ID
AND li.LINTER = v_linter;
ELSE
DELETE FROM lint_cert_issue lci
USING lint_issue li
WHERE lci.CERTIFICATE_ID = cert_id
AND lci.LINT_ISSUE_ID = li.ID
AND li.LINTER = v_linter;
IF v_linter = 'cablint' THEN
t_query := 'SELECT cablint_embedded($1) LINT';
ELSIF v_linter = 'x509lint' THEN
t_query := 'SELECT x509lint_embedded($1,$2) LINT';
IF NOT x509_canIssueCerts(t_certificate) THEN
t_certType := 0;
ELSE
SELECT count(*)
INTO t_count
FROM ca_certificate cac
WHERE cac.CERTIFICATE_ID = cert_id
AND cac.CA_ID = t_issuerCAID;
IF t_count = 0 THEN
t_certType := 1;
ELSE
t_certType := 2;
END IF;
END IF;
ELSIF v_linter = 'zlint' THEN
t_query := 'SELECT zlint_embedded($1) LINT';
END IF;
FOR l_record IN EXECUTE t_query USING t_certificate, t_certType LOOP
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 (
cert_id, t_lintIssueID, t_issuerCAID, x509_notBefore(t_certificate)::date
);
RETURN NEXT t_lintIssueID;
END IF;
END LOOP;
IF v_linter = 'cablint' THEN
UPDATE certificate
SET CABLINT_CACHED_AT = statement_timestamp() AT TIME ZONE 'UTC'
WHERE ID = cert_id;
ELSIF v_linter = 'x509lint' THEN
UPDATE certificate
SET X509LINT_CACHED_AT = statement_timestamp() AT TIME ZONE 'UTC'
WHERE ID = cert_id;
ELSIF v_linter = 'zlint' THEN
UPDATE certificate
SET ZLINT_CACHED_AT = statement_timestamp() AT TIME ZONE 'UTC'
WHERE ID = cert_id;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql STRICT;
|
