-
Notifications
You must be signed in to change notification settings - Fork 56
/
queries.go
71 lines (67 loc) · 3.27 KB
/
queries.go
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
package localmetrics
const (
CRT_SH_PG_DB_HOSTNAME string = "crt.sh"
CRT_SH_PG_DB_PORT int = 5432
CRT_SH_PG_DB_USERNAME string = "guest"
CRT_SH_PG_DB_NAME string = "certwatch"
GET_COUNT_CERTS_ISSUED_BY_LE_SQL string = `
select count(*) as certs_issued from (SELECT ci.ISSUER_CA_ID,
ca.NAME ISSUER_NAME,
ci.NAME_VALUE NAME_VALUE,
min(c.ID) MIN_CERT_ID,
min(ctle.ENTRY_TIMESTAMP) MIN_ENTRY_TIMESTAMP,
x509_notBefore(c.CERTIFICATE) NOT_BEFORE,
x509_notAfter(c.CERTIFICATE) NOT_AFTER
FROM ca,
ct_log_entry ctle,
certificate_identity ci,
certificate c
WHERE ca.ID in (SELECT id FROM ca WHERE lower(ca.NAME) LIKE lower('%Let''s Encrypt%'))
AND ci.ISSUER_CA_ID = ca.ID
AND c.ID = ctle.CERTIFICATE_ID
AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower($1))
AND ci.CERTIFICATE_ID = c.ID
GROUP BY c.ID, ci.ISSUER_CA_ID, ISSUER_NAME, NAME_VALUE
) AS certs WHERE certs.MIN_ENTRY_TIMESTAMP >= $2
`
GET_LIST_CERTS_ISSUED_BY_LE_SQL string = `
select name_value, not_before, not_after from (SELECT ci.ISSUER_CA_ID,
ca.NAME ISSUER_NAME,
ci.NAME_VALUE NAME_VALUE,
min(c.ID) MIN_CERT_ID,
min(ctle.ENTRY_TIMESTAMP) MIN_ENTRY_TIMESTAMP,
x509_notBefore(c.CERTIFICATE) NOT_BEFORE,
x509_notAfter(c.CERTIFICATE) NOT_AFTER
FROM ca,
ct_log_entry ctle,
certificate_identity ci,
certificate c
WHERE ca.ID in (SELECT id FROM ca WHERE lower(ca.NAME) LIKE lower('%Let''s Encrypt%'))
AND ci.ISSUER_CA_ID = ca.ID
AND c.ID = ctle.CERTIFICATE_ID
AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower($1))
AND ci.CERTIFICATE_ID = c.ID
GROUP BY c.ID, ci.ISSUER_CA_ID, ISSUER_NAME, NAME_VALUE
) AS certs WHERE certs.MIN_ENTRY_TIMESTAMP >= $2
`
GET_LIST_CERTS_ISSUED_BY_LE_SQL_EXPIRING_SOON string = `
select name_value, not_before, not_after from (SELECT ci.ISSUER_CA_ID,
ca.NAME ISSUER_NAME,
ci.NAME_VALUE NAME_VALUE,
min(c.ID) MIN_CERT_ID,
min(ctle.ENTRY_TIMESTAMP) MIN_ENTRY_TIMESTAMP,
x509_notBefore(c.CERTIFICATE) NOT_BEFORE,
x509_notAfter(c.CERTIFICATE) NOT_AFTER
FROM ca,
ct_log_entry ctle,
certificate_identity ci,
certificate c
WHERE ca.ID in (SELECT id FROM ca WHERE lower(ca.NAME) LIKE lower('%Let''s Encrypt%'))
AND ci.ISSUER_CA_ID = ca.ID
AND c.ID = ctle.CERTIFICATE_ID
AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower($1))
AND ci.CERTIFICATE_ID = c.ID
GROUP BY c.ID, ci.ISSUER_CA_ID, ISSUER_NAME, NAME_VALUE
) AS certs WHERE certs.NOT_AFTER >= $2 AND certs.NOT_AFTER <= $3
`
)