diff options
Diffstat (limited to 'scripts/convert-from-entitydb-to-sql.py')
| -rw-r--r-- | scripts/convert-from-entitydb-to-sql.py | 440 |
1 files changed, 440 insertions, 0 deletions
diff --git a/scripts/convert-from-entitydb-to-sql.py b/scripts/convert-from-entitydb-to-sql.py new file mode 100644 index 00000000..95e8e985 --- /dev/null +++ b/scripts/convert-from-entitydb-to-sql.py @@ -0,0 +1,440 @@ +""" +Merge XML entitydb and OpenSSL command-line BPKI into SQL IRDB. + +This is a work in progress, don't use it unless you really know what +you're doing. + +$Id$ + +Copyright (C) 2011 Internet Systems Consortium ("ISC") + +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 ISC DISCLAIMS ALL WARRANTIES WITH +REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +AND FITNESS. IN NO EVENT SHALL ISC 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. +""" + +import sys, os, time, getopt, glob, subprocess, base64 +import rpki.config, rpki.x509, rpki.relaxng, rpki.sundial +from rpki.mysql_import import MySQLdb +from lxml.etree import ElementTree + +if os.getlogin() != "sra": + sys.exit("I //said// this was a work in progress") + +cfg_file = "rpki.conf" +entitydb = "entitydb" +bpki = "bpki" +copy_csv_data = True + +opts, argv = getopt.getopt(sys.argv[1:], "c:h?", ["config=", "help"]) +for o, a in opts: + if o in ("-h", "--help", "-?"): + print __doc__ + sys.exit(0) + if o in ("-c", "--config"): + cfg_file = a +if argv: + sys.exit("Unexpected arguments %s" % argv) + +cfg = rpki.config.parser(cfg_file) + +sql_database = cfg.get("sql-database", section = "irdbd") +sql_username = cfg.get("sql-username", section = "irdbd") +sql_password = cfg.get("sql-password", section = "irdbd") + +db = MySQLdb.connect(user = sql_username, db = sql_database, passwd = sql_password) +cur = db.cursor() + +# Configure the Django model system + +from django.conf import settings + +settings.configure( + DATABASES = { "default" : { + "ENGINE" : "django.db.backends.mysql", + "NAME" : sql_database, + "USER" : sql_username, + "PASSWORD" : sql_password, + "HOST" : "", + "PORT" : "", + "OPTIONS" : { "init_command": "SET storage_engine=INNODB" }}}, + INSTALLED_APPS = ("rpki.irdb",), +) + +import rpki.irdb + +# Create the model-based tables if they don't already exist + +import django.core.management + +django.core.management.call_command("syncdb", verbosity = 4, load_initial_data = False) + +# From here down will be an awful lot of messing about with XML and +# X.509 data, extracting stuff from the old SQL database and whacking +# it into the new. Still working out these bits. + +xmlns = "{http://www.hactrn.net/uris/rpki/myrpki/}" + +tag_authorization = xmlns + "authorization" +tag_bpki_child_ta = xmlns + "bpki_child_ta" +tag_bpki_client_ta = xmlns + "bpki_client_ta" +tag_bpki_resource_ta = xmlns + "bpki_resource_ta" +tag_bpki_server_ta = xmlns + "bpki_server_ta" +tag_bpki_ta = xmlns + "bpki_ta" +tag_contact_info = xmlns + "contact_info" +tag_identity = xmlns + "identity" +tag_parent = xmlns + "parent" +tag_repository = xmlns + "repository" + +e = ElementTree(file = os.path.join(entitydb, "identity.xml")).getroot() +rpki.relaxng.myrpki.assertValid(e) +assert e.tag == tag_identity + +self_handle = e.get("handle") +assert self_handle == cfg.get("handle", section = "myrpki") + +# Some BPKI utillity routines + +def read_openssl_serial(filename): + f = open(filename, "r") + text = f.read() + f.close() + return int(text.strip(), 16) + +def get_or_create_ServerEE(issuer, purpose): + cer = rpki.x509.X509(Auto_file = os.path.join(bpki, "servers", purpose + ".cer")) + key = rpki.x509.RSA(Auto_file = os.path.join(bpki, "servers", purpose + ".key")) + rpki.irdb.ServerEE.objects.get_or_create( + issuer = issuer, + purpose = purpose, + certificate = cer, + private_key = key) + +# Load BPKI CAs and directly certified EEs + +cer = rpki.x509.X509(Auto_file = os.path.join(bpki, "resources", "ca.cer")) +key = rpki.x509.RSA(Auto_file = os.path.join(bpki, "resources", "ca.key")) +crl = rpki.x509.CRL(Auto_file = os.path.join(bpki, "resources", "ca.crl")) +serial = read_openssl_serial(os.path.join(bpki, "resources", "serial")) +crl_number = read_openssl_serial(os.path.join(bpki, "resources", "crl_number")) + +resource_ca = rpki.irdb.ResourceHolderCA.objects.get_or_create( + handle = self_handle, + certificate = cer, + private_key = key, + latest_crl = crl, + next_serial = serial, + next_crl_number = crl_number, + last_crl_update = crl.getThisUpdate().to_sql(), + next_crl_update = crl.getNextUpdate().to_sql())[0] + +if os.path.exists(os.path.join(bpki, "resources", "referral.cer")): + cer = rpki.x509.X509(Auto_file = os.path.join(bpki, "resources", "referral.cer")) + key = rpki.x509.RSA(Auto_file = os.path.join(bpki, "resources", "referral.key")) + rpki.irdb.Referral.objects.get_or_create( + issuer = resource_ca, + certificate = cer, + private_key = key) + +run_rpkid = cfg.getboolean("run_rpkid", section = "myrpki") +run_pubd = cfg.getboolean("run_pubd", section = "myrpki") +run_rootd = cfg.getboolean("run_rootd", section = "myrpki") + +if run_rpkid or run_pubd: + cer = rpki.x509.X509(Auto_file = os.path.join(bpki, "servers", "ca.cer")) + key = rpki.x509.RSA(Auto_file = os.path.join(bpki, "servers", "ca.key")) + crl = rpki.x509.CRL(Auto_file = os.path.join(bpki, "servers", "ca.crl")) + serial = read_openssl_serial(os.path.join(bpki, "servers", "serial")) + crl_number = read_openssl_serial(os.path.join(bpki, "servers", "crl_number")) + server_ca = rpki.irdb.ServerCA.objects.get_or_create( + certificate = cer, + private_key = key, + latest_crl = crl, + next_serial = serial, + next_crl_number = crl_number, + last_crl_update = crl.getThisUpdate().to_sql(), + next_crl_update = crl.getNextUpdate().to_sql())[0] + get_or_create_ServerEE(server_ca, "irbe") + +else: + server_ca = None + +if run_rpkid: + get_or_create_ServerEE(server_ca, "rpkid") + get_or_create_ServerEE(server_ca, "irdbd") + +if run_pubd: + get_or_create_ServerEE(server_ca, "pubd") + +# Certification model for rootd has changed. We can reuse the old +# key, but we have to recertify under a different CA than previously. +# Yes, we're pulling a key from the servers BPKI tree and certifying +# it under the resource holder CA, that's part of the change. + +if run_rootd: + rpki.irdb.Rootd.objects.get_or_certify( + issuer = resource_ca, + service_uri = "http://localhost:%s/" % cfg.get("rootd_server_port", section = "myrpki"), + private_key = rpki.x509.RSA(Auto_file = os.path.join(bpki, "servers", "rootd.key"))) + +# Load BSC certificates and requests. Yes, this currently wires in +# exactly one BSC handle, "bsc". So does the old myrpki code. Ick. + +for fn in glob.iglob(os.path.join(bpki, "resources", "bsc.*.cer")): + rpki.irdb.BSC.objects.get_or_create( + issuer = resource_ca, + handle = "bsc", + certificate = rpki.x509.X509(Auto_file = fn), + pkcs10 = rpki.x509.PKCS10(Auto_file = fn[:-4] + ".req")) + +def xcert_hash(cert): + """ + Generate the filename hash that myrpki would have generated for a + cross-certification. This is nasty, don't look. + """ + + cmd1 = ("openssl", "x509", "-noout", "-pubkey", "-subject") + cmd2 = ("openssl", "dgst", "-md5") + + env = { "PATH" : os.environ["PATH"], "OPENSSL_CONF" : "/dev/null" } + p1 = subprocess.Popen(cmd1, env = env, stdin = subprocess.PIPE, stdout = subprocess.PIPE) + p2 = subprocess.Popen(cmd2, env = env, stdin = p1.stdout, stdout = subprocess.PIPE) + p1.stdin.write(cert.get_PEM()) + p1.stdin.close() + hash = p2.stdout.read() + if p1.wait() != 0: + raise subprocess.CalledProcessError(returncode = p1.returncode, cmd = cmd1) + if p2.wait() != 0: + raise subprocess.CalledProcessError(returncode = p2.returncode, cmd = cmd2) + + hash = "".join(hash.split()) + if hash.startswith("(stdin)="): + hash = hash[len("(stdin)="):] + return hash + +# Let's try keeping track of all the xcert filenames we use, so we can +# list the ones we didn't. + +xcert_filenames = set(glob.iglob(os.path.join(bpki, "*", "xcert.*.cer"))) + +# Scrape child data out of the entitydb. + +for filename in glob.iglob(os.path.join(entitydb, "children", "*.xml")): + child_handle = os.path.splitext(os.path.split(filename)[1])[0] + + e = ElementTree(file = filename).getroot() + rpki.relaxng.myrpki.assertValid(e) + assert e.tag == tag_parent + + ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_child_ta)) + xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta)) + xcert_filenames.discard(xcfn) + xcert = rpki.x509.X509(Auto_file = xcfn) + + cur.execute(""" + SELECT registrant_id, valid_until FROM registrant + WHERE registry_handle = %s AND registrant_handle = %s + """, (self_handle, child_handle)) + assert cur.rowcount == 1 + registrant_id, valid_until = cur.fetchone() + + valid_until = rpki.sundial.datetime.fromdatetime(valid_until) + if valid_until != rpki.sundial.datetime.fromXMLtime(e.get("valid_until")): + print "WARNING: valid_until dates in XML and SQL do not match for child", child_handle + print " SQL:", str(valid_until) + print " XML:", str(rpki.sundial.datetime.fromXMLtime(e.get("valid_until"))) + print "Blundering onwards" + + child = rpki.irdb.Child.objects.get_or_create( + handle = child_handle, + valid_until = valid_until.to_sql(), + ta = ta, + certificate = xcert, + issuer = resource_ca)[0] + + if copy_csv_data: + + cur.execute(""" + SELECT start_as, end_as FROM registrant_asn WHERE registrant_id = %s + """, (registrant_id,)) + for start_as, end_as in cur.fetchall(): + rpki.irdb.ChildASN.objects.get_or_create( + start_as = start_as, + end_as = end_as, + child = child) + + cur.execute(""" + SELECT start_ip, end_ip, version FROM registrant_net WHERE registrant_id = %s + """, (registrant_id,)) + for start_ip, end_ip, version in cur.fetchall(): + rpki.irdb.ChildNet.objects.get_or_create( + start_ip = start_ip, + end_ip = end_ip, + version = version, + child = child) + +# Scrape parent data out of the entitydb. + +for filename in glob.iglob(os.path.join(entitydb, "parents", "*.xml")): + parent_handle = os.path.splitext(os.path.split(filename)[1])[0] + + e = ElementTree(file = filename).getroot() + rpki.relaxng.myrpki.assertValid(e) + assert e.tag == tag_parent + + if parent_handle == self_handle: + assert run_rootd + assert e.get("service_uri") == "http://localhost:%s/" % cfg.get("rootd_server_port", section = "myrpki") + continue + + ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_resource_ta)) + xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta)) + xcert_filenames.discard(xcfn) + xcert = rpki.x509.X509(Auto_file = xcfn) + + r = e.find(tag_repository) + repository_type = r.get("type") + if repository_type == "referral": + a = r.find(tag_authorization) + referrer = a.get("referrer") + referral_authorization = base64.b64decode(a.text) + else: + referrer = None + referral_authorization = None + + parent = rpki.irdb.Parent.objects.get_or_create( + handle = parent_handle, + parent_handle = e.get("parent_handle"), + child_handle = e.get("child_handle"), + ta = ta, + certificate = xcert, + service_uri = e.get("service_uri"), + repository_type = repository_type, + referrer = referrer, + referral_authorization = referral_authorization, + issuer = resource_ca)[0] + + # While we have the parent object in hand, load any Ghostbuster + # entries specific to this parent. + + if copy_csv_data: + cur.execute(""" + SELECT vcard FROM ghostbuster_request + WHERE self_handle = %s AND parent_handle = %s + """, (self_handle, parent_handle)) + for row in cur.fetchall(): + rpki.irdb.GhostbusterRequest.objects.get_or_create( + issuer = resource_ca, + parent = parent, + vcard = row[0]) + +# Scrape repository data out of the entitydb. + +for filename in glob.iglob(os.path.join(entitydb, "repositories", "*.xml")): + repository_handle = os.path.splitext(os.path.split(filename)[1])[0] + + e = ElementTree(file = filename).getroot() + rpki.relaxng.myrpki.assertValid(e) + assert e.tag == tag_repository + + if e.get("type") != "confirmed": + continue + + ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_server_ta)) + xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta)) + xcert_filenames.discard(xcfn) + xcert = rpki.x509.X509(Auto_file = xcfn) + + parent_handle = e.get("parent_handle") + if parent_handle == self_handle: + turtle = resource_ca.rootd + else: + turtle = rpki.irdb.Parent.objects.get(handle = parent_handle, issuer = resource_ca) + + rpki.irdb.Repository.objects.get_or_create( + handle = repository_handle, + client_handle = e.get("client_handle"), + ta = ta, + certificate = xcert, + service_uri = e.get("service_uri"), + sia_base = e.get("sia_base"), + turtle = turtle, + issuer = resource_ca) + +# Scrape client data out of the entitydb. + +for filename in glob.iglob(os.path.join(entitydb, "pubclients", "*.xml")): + client_handle = os.path.splitext(os.path.split(filename)[1])[0].replace(".", "/") + + e = ElementTree(file = filename).getroot() + rpki.relaxng.myrpki.assertValid(e) + assert e.tag == tag_repository + + assert e.get("type") == "confirmed" + + ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_client_ta)) + xcfn = os.path.join(bpki, "servers", "xcert.%s.cer" % xcert_hash(ta)) + xcert_filenames.discard(xcfn) + xcert = rpki.x509.X509(Auto_file = xcfn) + + rpki.irdb.Client.objects.get_or_create( + handle = client_handle, + ta = ta, + certificate = xcert, + issuer = server_ca, + sia_base = e.get("sia_base")) + +if copy_csv_data: + + # Copy over any ROA requests + + cur.execute(""" + SELECT roa_request_id, asn FROM roa_request + WHERE roa_request_handle = %s + """, (self_handle,)) + for roa_request_id, asn in cur.fetchall(): + roa_request = rpki.irdb.ROARequest.objects.get_or_create(issuer = resource_ca, asn = asn)[0] + cur.execute(""" + SELECT prefix, prefixlen, max_prefixlen, version FROM roa_request_prefix + WHERE roa_request_id = %s + """, (roa_request_id,)) + for prefix, prefixlen, max_prefixlen, version in cur.fetchall(): + rpki.irdb.ROARequestPrefix.objects.get_or_create( + roa_request = roa_request, + version = version, + prefix = prefix, + prefixlen = prefixlen, + max_prefixlen = max_prefixlen) + + # Copy over any non-parent-specific Ghostbuster requests. + + cur.execute(""" + SELECT vcard FROM ghostbuster_request + WHERE self_handle = %s AND parent_handle IS NULL + """, (self_handle,)) + for row in cur.fetchall(): + rpki.irdb.GhostbusterRequest.objects.get_or_create( + issuer = resource_ca, + parent = None, + vcard = row[0]) + +# List cross certifications we didn't use. + +if False: + for filename in sorted(xcert_filenames): + cer = rpki.x509.X509(Auto_file = filename) + #print "Unused cross-certificate:", filename, cer.getSubject() + print "Unused cross-certificate:", filename, cer.get_POW().pprint() + +# Done! + +cur.close() +db.close() |