-
Notifications
You must be signed in to change notification settings - Fork 36
/
process_expirations.fnc
77 lines (72 loc) · 3.06 KB
/
process_expirations.fnc
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
/* certwatch_db - Database schema
* Written by Rob Stradling
* Copyright (C) 2015-2023 Sectigo 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 process_expirations(
_new_expirations OUT bigint,
_cas_affected OUT bigint,
_min_last_not_after OUT ca.NEXT_NOT_AFTER%TYPE
)
AS $$
DECLARE
t_newExpirationsC bigint;
t_newExpirationsP bigint;
t_nextNotAfter_new ca.NEXT_NOT_AFTER%TYPE;
l_ca RECORD;
BEGIN
_new_expirations := 0;
_cas_affected := 0;
FOR l_ca IN (
SELECT ca.ID, ca.NEXT_NOT_AFTER, coalesce(ca.LAST_NOT_AFTER, '-infinity'::timestamp) LAST_NOT_AFTER_OLD,
least(date_trunc('second', now() AT TIME ZONE 'UTC') - interval '1 second', ca.NEXT_NOT_AFTER + interval '10 minutes') LAST_NOT_AFTER_NEW,
ca.LAST_CERTIFICATE_ID
FROM ca
WHERE ca.NEXT_NOT_AFTER < date_trunc('second', now() AT TIME ZONE 'UTC')
ORDER BY ca.NEXT_NOT_AFTER
FOR NO KEY UPDATE SKIP LOCKED
LIMIT 10
) LOOP
SELECT coalesce(sum(CASE WHEN is_precertificate THEN 0 ELSE 1 END), 0),
coalesce(sum(CASE WHEN is_precertificate THEN 1 ELSE 0 END), 0)
INTO t_newExpirationsC,
t_newExpirationsP
FROM certificate c
INNER JOIN x509_hasExtension(c.CERTIFICATE, '1.3.6.1.4.1.11129.2.4.3', TRUE) is_precertificate ON TRUE
WHERE c.ISSUER_CA_ID = l_ca.ID
AND c.ID <= l_ca.LAST_CERTIFICATE_ID
AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) > l_ca.LAST_NOT_AFTER_OLD
AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) <= l_ca.LAST_NOT_AFTER_NEW;
SELECT x509_notAfter(c.CERTIFICATE)
INTO t_nextNotAfter_new
FROM certificate c
WHERE c.ISSUER_CA_ID = l_ca.ID
AND c.ID <= l_ca.LAST_CERTIFICATE_ID
AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) > l_ca.LAST_NOT_AFTER_NEW
AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) < 'infinity'::timestamp
ORDER BY coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp)
LIMIT 1;
UPDATE ca
SET NUM_EXPIRED[1] = coalesce(ca.NUM_EXPIRED[1], 0) + t_newExpirationsC,
NUM_EXPIRED[2] = coalesce(ca.NUM_EXPIRED[2], 0) + t_newExpirationsP,
LAST_NOT_AFTER = l_ca.LAST_NOT_AFTER_NEW,
NEXT_NOT_AFTER = t_nextNotAfter_new
WHERE ca.ID = l_ca.ID;
_new_expirations := _new_expirations + t_newExpirationsC + t_newExpirationsP;
_cas_affected := _cas_affected + 1;
_min_last_not_after := least(coalesce(_min_last_not_after, l_ca.LAST_NOT_AFTER_NEW), l_ca.LAST_NOT_AFTER_NEW);
END LOOP;
END;
$$ LANGUAGE plpgsql;