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
|
#!/usr/bin/env python
# Make a list of the current top 1,000 certs to whitelist from the
# Decentralized SSL Observatory's client submissions, to live in
# https-everywhere/src/chrome/content/code/X509ChainWhitelist.js
import dbconnect
import sys
db, dbc = dbconnect.dbconnect()
import time
few_days_ago = time.gmtime(time.time() - 3600 * 24 * 3)
cutoff = time.strftime("%Y-%m-%d", few_days_ago)
# This currently runs on the decentralized observatory server, but will also
# run on the published datasets and read-only SQL servers too...
q = """
SELECT hex(reports.chain_fp), count, `Validity:Not After`, Subject
FROM reports JOIN chains ON reports.chain_fp = chains.chain_fp
JOIN parsed_certs ON reports.cert_fp = parsed_certs.cert_fp
WHERE count > 1000 AND
`Validity:Not After` > "%s"
GROUP BY reports.chain_fp
ORDER BY count DESC
LIMIT 1000
""" % (cutoff,)
dbc.execute(q)
results = dbc.fetchmany(1000)
sys.stderr.write("TOP 100:\n")
for n in range(100):
sys.stderr.write(repr(results[n][1:4]) + '\n')
header = """
// These are SHA256 fingerprints for the most common chains observed by the
// Decentralized SSL Observatory. These should not be resubmitted.
// This file is automatically generated by utils/mk_client_whitelist.py
const X509ChainWhitelist = {"""
print(header)
for chain_fp in sorted([row[0] for row in results]):
print(" '%s' : true," % chain_fp)
footer = "} ;"
print(footer)
|
