Tighten uniqueness constraints on IRDB models.

svn path=/branches/tk100/; revision=4114

3 files changed, 67 insertions, 28 deletions

diff --git a/rpkid/rpki/irdb/models.py b/rpkid/rpki/irdb/models.py
index 5323e8d1..e10a9122 100644
--- a/rpkid/rpki/irdb/models.py
+++ b/rpkid/rpki/irdb/models.py
@@ -79,7 +79,7 @@ ip_version_choices = tuple((y, x) for (x, y) in ip_version_map.iteritems())
 ###
 
 class Identity(django.db.models.Model):
-  handle = HandleField()
+  handle = HandleField(unique = True)
 
 class CA(django.db.models.Model):
   identity = django.db.models.ForeignKey(Identity, related_name = "bpki_certificates")
@@ -92,41 +92,76 @@ class CA(django.db.models.Model):
   last_crl_update = django.db.models.DateTimeField()
   next_crl_update = django.db.models.DateTimeField()
 
+  class Meta:
+    unique_together = ("identity", "purpose")
+
 class Certificate(django.db.models.Model):
-  issuer = django.db.models.ForeignKey(CA, related_name = "certificates")
+  issuer = django.db.models.ForeignKey(CA)
   certificate = BinaryField()
 
+  # We used to use multi-table inheritance here, but that turns out
+  # not to work so well once we started applying uniqueness
+  # constraints.  So now we use an abstract base class.
+  #
+  # This is probably the right approach for data fields, so that we
+  # can share custom model methods for things like certificate
+  # issuance, but is a bit tricky for foreign keys due to the
+  # "related_name" reverse link.  See:
+  # https://docs.djangoproject.com/en/dev/topics/db/models/#model-inheritance
+
+  class Meta:
+    abstract = True
+
 class Revocation(django.db.models.Model):
   issuer = django.db.models.ForeignKey(CA, related_name = "revocations")
   serial = django.db.models.BigIntegerField()
   revoked = django.db.models.DateTimeField()
   expires = django.db.models.DateTimeField()
 
+  class Meta:
+    unique_together = ("issuer", "serial")
+
 class EECertificate(Certificate):
   purpose_map = ChoiceMap("rpkid", "pubd", "irdbd", "irbe", "rootd")
   purpose = django.db.models.PositiveSmallIntegerField(choices = purpose_map.choices)
   private_key = BinaryField()
 
+  class Meta:
+    unique_together = ("issuer", "purpose")
+
 class BSC(Certificate):
+  handle = HandleField()
   pkcs10 = BinaryField()
 
+  class Meta:
+    unique_together = ("issuer", "handle")
+
 class Child(Certificate):
   handle = HandleField()
   name = django.db.models.TextField(null = True, blank = True)
   valid_until = django.db.models.DateTimeField()
   ta = BinaryField()
 
+  class Meta:
+    unique_together = ("issuer", "handle")
+
 class ChildASN(django.db.models.Model):
+  child = django.db.models.ForeignKey(Child, related_name = "asns")
   start_as = django.db.models.BigIntegerField()
   end_as = django.db.models.BigIntegerField()
-  child = django.db.models.ForeignKey(Child, related_name = "asns")
+
+  class Meta:
+    unique_together = ("child", "start_as", "end_as")
 
 class ChildNet(django.db.models.Model):
+  child = django.db.models.ForeignKey(Child, related_name = "address_ranges")
   start_ip = django.db.models.CharField(max_length = 40)
   end_ip   = django.db.models.CharField(max_length = 40)
   version_map = ip_version_map
   version = django.db.models.PositiveSmallIntegerField(choices = ip_version_choices)
-  child = django.db.models.ForeignKey(Child, related_name = "address_ranges")
+
+  class Meta:
+    unique_together = ("child", "start_ip", "end_ip", "version")
 
 class Parent(Certificate):
   handle = HandleField()
@@ -139,6 +174,9 @@ class Parent(Certificate):
   referrer = HandleField(null = True, blank = True)
   referral_authorization = BinaryField(null = True, blank = True)
 
+  class Meta:
+    unique_together = ("issuer", "handle")
+
 class ROARequest(django.db.models.Model):
   identity = django.db.models.ForeignKey(Identity, related_name = "roa_requests")
   asn = django.db.models.BigIntegerField()
@@ -151,6 +189,9 @@ class ROARequestPrefix(django.db.models.Model):
   prefixlen = django.db.models.PositiveSmallIntegerField()
   max_prefixlen = django.db.models.PositiveSmallIntegerField()
 
+  class Meta:
+    unique_together = ("roa_request", "version", "prefix", "prefixlen", "max_prefixlen")
+
 class GhostbusterRequest(django.db.models.Model):
   identity = django.db.models.ForeignKey(Identity, related_name = "ghostbuster_requests")
   parent = django.db.models.ForeignKey(Parent,     related_name = "ghostbuster_requests", null = True)
@@ -164,6 +205,12 @@ class Repository(Certificate):
   sia_base = django.db.models.TextField()
   parent = django.db.models.OneToOneField(Parent, related_name = "repository")
 
+  class Meta:
+    unique_together = ("issuer", "handle")
+
 class Client(Certificate):
   handle = HandleField()
   ta = BinaryField()
+
+  class Meta:
+    unique_together = ("issuer", "handle")
diff --git a/rpkid/rpki/myrpki.py b/rpkid/rpki/myrpki.py
index 2fa2f8cb..ec36371c 100644
--- a/rpkid/rpki/myrpki.py
+++ b/rpkid/rpki/myrpki.py
@@ -793,9 +793,17 @@ class CA(object):
     Write PEM certificate to file, then cross-certify.
     """
     fn = os.path.join(self.dir, filename or "temp.%s.cer" % os.getpid())
+    der = base64.b64decode(b64)
+    if True:
+      try:
+        text = self.run_openssl("x509", "-inform", "DER", "-noout",
+                                "-issuer", "-subject", stdin = der)
+      except:
+        text = ""
+      print "fxcert():", self.dir, filename, text
     try:
       self.run_openssl("x509", "-inform", "DER", "-out", fn,
-                       stdin = base64.b64decode(b64))
+                       stdin = der)
       return self.xcert(fn, path_restriction)
     finally:
       if not filename and os.path.exists(fn):
diff --git a/scripts/convert-from-entitydb-to-sql.py b/scripts/convert-from-entitydb-to-sql.py
index 11fc4b98..9de2edf2 100644
--- a/scripts/convert-from-entitydb-to-sql.py
+++ b/scripts/convert-from-entitydb-to-sql.py
@@ -49,25 +49,9 @@ sql_database = cfg.get("sql-database", section = "irdbd")
 sql_username = cfg.get("sql-username", section = "irdbd")
 sql_password = cfg.get("sql-password", section = "irdbd")
 
-# Rename the old SQL tables, if they exist
-
 db = MySQLdb.connect(user = sql_username, db = sql_database, passwd = sql_password)
 cur = db.cursor()
 
-cur.execute("SHOW TABLES")
-
-tables = [r[0] for r in cur.fetchall()]
-
-for table in tables:
-  if "old_" + table not in tables and table in ("registrant",
-                                                "registrant_asn",
-                                                "registrant_net",
-                                                "roa_request",
-                                                "roa_request_prefix",
-                                                "ghostbuster_request"):
-    print "Renaming %s to old_%s" % (table, table)
-    cur.execute("ALTER TABLE %s RENAME TO old_%s" % (table, table))
-
 # Configure the Django model system
 
 from django.conf import settings
@@ -249,7 +233,7 @@ for filename in glob.iglob(os.path.join(entitydb, "children", "*.xml")):
   xcert = rpki.x509.X509(Auto_file = xcfn)
 
   cur.execute("""
-              SELECT registrant_id, valid_until FROM old_registrant
+              SELECT registrant_id, valid_until FROM registrant
               WHERE registry_handle = %s AND registrant_handle = %s
               """, (self_handle, child_handle))
   assert cur.rowcount == 1
@@ -266,7 +250,7 @@ for filename in glob.iglob(os.path.join(entitydb, "children", "*.xml")):
     issuer = resource_ca)[0]
 
   cur.execute("""
-              SELECT start_as, end_as FROM old_registrant_asn WHERE registrant_id = %s
+              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(
@@ -275,7 +259,7 @@ for filename in glob.iglob(os.path.join(entitydb, "children", "*.xml")):
       child = child)
 
   cur.execute("""
-              SELECT start_ip, end_ip, version FROM old_registrant_net WHERE registrant_id = %s
+              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(
@@ -323,7 +307,7 @@ for filename in glob.iglob(os.path.join(entitydb, "parents", "*.xml")):
   # entries specific to this parent.
 
   cur.execute("""
-              SELECT vcard FROM old_ghostbuster_request
+              SELECT vcard FROM ghostbuster_request
               WHERE self_handle = %s AND parent_handle = %s
               """, (self_handle, parent_handle))
   for row in cur.fetchall():
@@ -386,13 +370,13 @@ for filename in glob.iglob(os.path.join(entitydb, "pubclients", "*.xml")):
 # Copy over any ROA requests
 
 cur.execute("""
-            SELECT roa_request_id, asn FROM old_roa_request
+            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(identity = identity, asn = asn)[0]
   cur.execute("""
-              SELECT prefix, prefixlen, max_prefixlen, version FROM old_roa_request_prefix
+              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():
@@ -406,7 +390,7 @@ for roa_request_id, asn in cur.fetchall():
 # Copy over any non-parent-specific Ghostbuster requests.
 
 cur.execute("""
-            SELECT vcard FROM old_ghostbuster_request
+            SELECT vcard FROM ghostbuster_request
             WHERE self_handle = %s AND parent_handle IS NULL
             """, (self_handle,))
 for row in cur.fetchall():