diff options
| -rwxr-xr-x | scripts/rp-sqlite | 302 |
1 files changed, 302 insertions, 0 deletions
diff --git a/scripts/rp-sqlite b/scripts/rp-sqlite new file mode 100755 index 00000000..ba892ae6 --- /dev/null +++ b/scripts/rp-sqlite @@ -0,0 +1,302 @@ +#!/usr/local/bin/python + +# $Id$ + +# Copyright (C) 2013 Dragon Research Labs ("DRL") +# +# Permission to use, copy, modify, and distribute this software for any +# purpose with or without fee is hereby granted, provided that the above +# copyright notice and this permission notice appear in all copies. +# +# THE SOFTWARE IS PROVIDED "AS IS" AND DRL DISCLAIMS ALL WARRANTIES WITH +# REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +# AND FITNESS. IN NO EVENT SHALL DRL BE LIABLE FOR ANY SPECIAL, DIRECT, +# INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM +# LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE +# OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +# PERFORMANCE OF THIS SOFTWARE. + +# Preliminary script to work out what's involved in building an +# SQLite3 database of RP objects. We haven't bothered with this until +# now in rcynic, because we mostly just walk the filesystem tree, but +# LTA and some of the ideas Tim is playing with require a lot of +# lookups based on things that are not the URIs we use as filenames, +# so some kind of indexing may become necessary. Given the complexity +# of building any kind of real index over RFC 3779 resources, +# otherwise fine lightweight tools like the Python shelve library +# probably won't cut it here, and I don't want to add a dependency on +# MySQL on the RP side (yet?), so let's see what we can do with SQLite3. + +import os +import sys +import sqlite3 +import rpki.POW +import rpki.x509 +import rpki.resource_set + +fn2map = dict(cer = rpki.x509.X509, + crl = rpki.x509.CRL, + mft = rpki.x509.SignedManifest, + roa = rpki.x509.ROA, + gbr = rpki.x509.Ghostbuster) + +def main(): + db, cur = initialize_database() + test(cur) + db.close() + +sqlite3.register_adapter(rpki.POW.IPAddress, lambda x: buffer(x.toBytes())) + +#sqlite3.register_converter("IPAddress", rpki.POW.IPAddress.fromBytes) + +def RangeVal_converter(s): + if s.isdigit(): + v = long(s) + else: + v = rpki.POW.IPAddress.fromBytes(s) + print "+ %r => %r" % (s, v) + return v + +sqlite3.register_converter("RangeVal", RangeVal_converter) + +def test(cur): + print "Testing" + print + print "Looking for range that should include adrilankha and psg again" + for r in find_by_ip_range(cur, "147.28.0.19", "147.28.0.62"): + print r, r.uris + print + print "Looking for range that should include adrilankha" + for r in find_by_ip_range(cur, "147.28.0.19", "147.28.0.19"): + print r, r.uris + print + print "Looking for range that should include ASN 3130" + for r in find_by_as_range(cur, 3130, 3130): + print r, r.uris + print + print "Moving on to resource sets" + for expr in ("147.28.0.19-147.28.0.62", + "3130", + "2001:418:1::19/128", + "147.28.0.19-147.28.0.62,198.180.150.50/32", + "3130,147.28.0.19-147.28.0.62,198.180.150.50/32", + "2001:418:1::62/128,198.180.150.50/32,2001:418:8006::50/128", + "147.28.0.19-147.28.0.62,2001:418:1::19/128,2001:418:1::62/128,198.180.150.50/32,2001:418:8006::50/128"): + print + print "Trying", expr + for r in find_by_resource_bag(cur, rpki.resource_set.resource_bag.from_str(expr)): + print r, r.uris + +def find_by_ski(cur, ski, fn2 = None): + return find_results(cur, fn2, + """ + SELECT id, fn2, der + FROM object + WHERE ski = ? + """, + buffer(ski)) + +def find_by_aki(cur, aki, fn2 = None): + return find_results(cur, fn2, + """ + SELECT id, fn2, der + FROM object + WHERE aki = ? + """, + buffer(aki)) + +# It's easiest to understand overlap conditions by understanding +# non-overlap then inverting and and applying De Morgan's law. Ranges +# A and B do not overlap if either A.min > B.max or A.max < B.min; +# therefore they do overlap if A.min <= B.max and A.max >= B.min. + +def find_by_as_range(cur, as_min, as_max, fn2 = None): + return find_results(cur, fn2, + """ + SELECT object.id, fn2, der + FROM object, range + WHERE ? <= max AND ? >= min AND object.id = range.id + """, + as_min, + as_max) + +def find_by_ip_range(cur, ip_min, ip_max, fn2 = None): + return find_results(cur, fn2, + """ + SELECT object.id, fn2, der + FROM object, range + WHERE ? <= max AND ? >= min AND object.id = range.id + """, + rpki.POW.IPAddress(ip_min), + rpki.POW.IPAddress(ip_max)) + +def find_by_resource_bag(cur, bag, fn2 = None): + qset = [] + aset = [] + + assert bag.asn or bag.v4 or bag.v6 + + for rset in (bag.asn, bag.v4, bag.v6): + if rset: + for r in rset: + qset.append("(? <= max AND ? >= min)") + aset.append(r.min) + aset.append(r.max) + + q = """ + SELECT object.id, fn2, der + FROM object, range + WHERE object.id = range.id AND (%s) + GROUP BY object.id + """ % (" OR ".join(qset)) + + if False: + print "+ query:", q + print "+ args: ", repr(aset) + + return find_results(*([cur, fn2, q] + aset)) + + +def find_results(cur, fn2, query, *args): + if fn2 is not None: + assert fn2 in fn2map + query += " AND fn2 = ?" + args = args + (fn2,) + results = [] + cur.execute(query, args) + selections = cur.fetchall() + for rowid, fn2, der in selections: + obj = fn2map[fn2](DER = der) + cur.execute("SELECT uri FROM uri WHERE id = ?", (rowid,)) + obj.uris = [u[0] for u in cur.fetchall()] + obj.uri = obj.uris[0] if len(obj.uris) == 1 else None + results.append(obj) + return results + +def initialize_database(db_name = "rp-sqlite.db", + rcynic_root = os.path.expanduser("~/rpki/subvert-rpki.hactrn.net/trunk/" + "rcynic/rcynic-data/unauthenticated"), + delete_old_db = True, + spinner = 100): + + # For now just wire in the database name and rcynic root, fix this + # later if overall approach seems usable. Might even end up just + # being an in-memory SQL database, who knows? + + if delete_old_db: + try: + os.unlink(db_name) + except: + pass + + db = sqlite3.connect(db_name, detect_types = sqlite3.PARSE_DECLTYPES) + db.text_factory = str + + cur = db.cursor() + cur.execute("PRAGMA foreign_keys = on") + + cur.execute(''' + CREATE TABLE object ( + id INTEGER PRIMARY KEY NOT NULL, + der BLOB NOT NULL, + fn2 TEXT NOT NULL, + ski BLOB, + aki BLOB, + UNIQUE (der)) + ''') + + cur.execute(''' + CREATE TABLE uri ( + id INTEGER NOT NULL, + uri TEXT NOT NULL, + UNIQUE (uri), + FOREIGN KEY (id) REFERENCES object(id) + ON DELETE CASCADE + ON UPDATE CASCADE) + ''') + + cur.execute("CREATE INDEX uri_index ON uri(id)") + + cur.execute(''' + CREATE TABLE range ( + id INTEGER NOT NULL, + min RangeVal NOT NULL, + max RangeVal NOT NULL, + UNIQUE (id, min, max), + FOREIGN KEY (id) REFERENCES object(id) + ON DELETE CASCADE + ON UPDATE CASCADE) + ''') + + cur.execute("CREATE INDEX range_index ON range(min, max)") + + nobj = 0 + + for root, dirs, files in os.walk(rcynic_root): + for fn in files: + fn = os.path.join(root, fn) + fn2 = os.path.splitext(fn)[1][1:] + + try: + obj = fn2map[fn2](DER_file = fn) + except: + continue + + if spinner and nobj % spinner == 0: + sys.stderr.write("\r%s %d..." % ("|\\-/"[(nobj/spinner) & 3], nobj)) + + nobj += 1 + + if fn2 == "crl": + ski = None + aki = buffer(obj.get_AKI()) + cer = None + + else: + if fn2 == "cer": + cer = obj + else: + cer = rpki.x509.X509(POW = obj.get_POW().certs()[0]) + ski = buffer(cer.get_SKI()) + try: + aki = buffer(cer.get_AKI()) + except: + aki = None + + der = buffer(obj.get_DER()) + uri = "rsync://" + fn[len(rcynic_root) + 1:] + + try: + cur.execute("INSERT INTO object (der, fn2, ski, aki) VALUES (?, ?, ?, ?)", + (der, fn2, ski, aki)) + rowid = cur.lastrowid + + except sqlite3.IntegrityError: + cur.execute("SELECT id FROM object WHERE der = ? AND fn2 = ?", (der, fn2)) + rows = cur.fetchall() + rowid = rows[0][0] + assert len(rows) == 1 + + else: + if cer is not None: + bag = cer.get_3779resources() + for rset in (bag.asn, bag.v4, bag.v6): + if rset is not None: + cur.executemany("REPLACE INTO range (id, min, max) VALUES (?, ?, ?)", + ((rowid, i.min, i.max) for i in rset)) + + cur.execute("INSERT INTO uri (id, uri) VALUES (?, ?)", + (rowid, uri)) + + if spinner: + sys.stderr.write("\r= %d objects, committing..." % nobj) + + db.commit() + + if spinner: + sys.stderr.write("done.\n") + + return db, cur + +if __name__ == "__main__": + main() |