Add revoked_cert table and rototill child_cert revocation code to use

it.  Enable MySQLdb exceptions, whack resulting problem with MySQL
DATETIME object conversion repeatedly with a blunt object.

svn path=/docs/rpki-db-schema.pdf; revision=1564

7 files changed, 120 insertions, 42 deletions

diff --git a/docs/rpki-db-schema.pdf b/docs/rpki-db-schema.pdf
index d8436d26..d9b4e595 100644
--- a/docs/rpki-db-schema.pdf
+++ b/docs/rpki-db-schema.pdf
diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql
index 17fa1541..53421e1f 100644
--- a/docs/rpki-db-schema.sql
+++ b/docs/rpki-db-schema.sql
@@ -141,7 +141,6 @@ CREATE TABLE child_cert (
        child_cert_id        SERIAL NOT NULL,
        cert                 LONGBLOB NOT NULL,
        ski                  TINYBLOB NOT NULL,
-       revoked              DATETIME,
        child_id             BIGINT unsigned NOT NULL,
        ca_detail_id         BIGINT unsigned NOT NULL,
        PRIMARY KEY          (child_cert_id),
@@ -149,6 +148,18 @@ CREATE TABLE child_cert (
        FOREIGN KEY          (child_id) REFERENCES child
 );
 
+DROP TABLE IF EXISTS revoked_cert;
+
+CREATE TABLE revoked_cert (
+       revoked_cert_id      SERIAL NOT NULL,
+       serial               BIGINT unsigned NOT NULL,
+       revoked              DATETIME NOT NULL,
+       expires              DATETIME NOT NULL,
+       ca_detail_id         BIGINT unsigned NOT NULL,
+       PRIMARY KEY          (revoked_cert_id),
+       FOREIGN KEY          (ca_detail_id) REFERENCES ca_detail
+);
+
 DROP TABLE IF EXISTS route_origin;
 
 CREATE TABLE route_origin (
diff --git a/rpkid/rootd.py b/rpkid/rootd.py
index 212e6469..06819ff0 100755
--- a/rpkid/rootd.py
+++ b/rpkid/rootd.py
@@ -89,8 +89,8 @@ class issue_pdu(rpki.up_down.issue_pdu):
                                          aia         = rootd_cert,
                                          crldp       = crldp,
                                          resources   = resources,
-                                         notAfter    = rpki.sundial.datetime.utcnow() + rpki_subject_lifetime))
-      now = rpki.sundial.datetime.utcnow()
+                                         notAfter    = rpki.sundial.now() + rpki_subject_lifetime))
+      now = rpki.sundial.now()
       crl = rpki.x509.CRL.generate(
         keypair             = rpki_key,
         issuer              = rpki_issuer,
diff --git a/rpkid/rpki/left_right.py b/rpkid/rpki/left_right.py
index 4be71369..ca4aedc0 100644
--- a/rpkid/rpki/left_right.py
+++ b/rpkid/rpki/left_right.py
@@ -361,7 +361,7 @@ class self_elt(data_elt):
 
     rpki.log.trace()
 
-    now = rpki.sundial.datetime.utcnow()
+    now = rpki.sundial.now()
 
     for child in self.children(gctx):
       child_certs = child.child_certs(gctx)
@@ -403,7 +403,7 @@ class self_elt(data_elt):
 
     rpki.log.trace()
 
-    now = rpki.sundial.datetime.utcnow()
+    now = rpki.sundial.now()
     for parent in self.parents(gctx):
       repository = parent.repository(gctx)
       for ca in parent.cas(gctx):
@@ -633,9 +633,9 @@ class child_elt(data_elt):
 
   cms_ta = None
 
-  def child_certs(self, gctx, ca_detail = None, ski = None, revoked = False, unique = False):
+  def child_certs(self, gctx, ca_detail = None, ski = None, unique = False):
     """Fetch all child_cert objects that link to this child object."""
-    return rpki.sql.child_cert_obj.fetch(gctx, self, ca_detail, ski, revoked, unique)
+    return rpki.sql.child_cert_obj.fetch(gctx, self, ca_detail, ski, unique)
 
   def parents(self, gctx):
     """Fetch all parent objects that link to self object to which this child object links."""
diff --git a/rpkid/rpki/sql.py b/rpkid/rpki/sql.py
index 0b8cd45b..1244a80f 100644
--- a/rpkid/rpki/sql.py
+++ b/rpkid/rpki/sql.py
@@ -17,6 +17,11 @@
 import MySQLdb, time
 import rpki.x509, rpki.resource_set, rpki.sundial
 
+# Turn all MySQL warnings into exceptions, at least for now
+#
+import warnings, _mysql_exceptions
+warnings.simplefilter("error", _mysql_exceptions.Warning)
+
 def connect(cfg):
   """Connect to a MySQL database using connection parameters from an
      rpki.config.parser object.
@@ -66,7 +71,10 @@ def sql_sweep(gctx):
   """Write any dirty objects out to SQL."""
   for s in sql_dirty.copy():
     rpki.log.debug("Sweeping %s" % repr(s))
-    s.sql_store(gctx)
+    if s.sql_deleted:
+      s.sql_delete(gctx)
+    else:
+      s.sql_store(gctx)
   sql_assert_pristine()
 
 class sql_persistant(object):
@@ -77,6 +85,10 @@ class sql_persistant(object):
   # Whether this object is already in SQL or not.
   sql_in_db = False
 
+  ## @var sql_deleted
+  # Whether our cached copy of this object has been deleted.
+  sql_deleted = False
+
   @classmethod
   def sql_fetch(cls, gctx, id):
     """Fetch one object from SQL, based on its primary key.  Since in
@@ -146,6 +158,10 @@ class sql_persistant(object):
     """Query whether this object needs to be written back to SQL."""
     return self in sql_dirty
 
+  def sql_mark_deleted(self):
+    """Mark this object as needing to be deleted in SQL."""
+    self.sql_deleted = True
+
   def sql_store(self, gctx):
     """Store this object to SQL."""
     if not self.sql_in_db:
@@ -171,7 +187,7 @@ class sql_persistant(object):
       if sql_cache.get(key) == self:
         del sql_cache[key]
       self.sql_in_db = False
-      self.sql_mark_clean()
+    self.sql_mark_clean()
 
   def sql_encode(self):
     """Convert object attributes into a dict for use with canned SQL
@@ -426,9 +442,13 @@ class ca_detail_obj(sql_persistant):
     """Fetch CA object to which this ca_detail links."""
     return ca_obj.sql_fetch(gctx, self.ca_id)
 
-  def child_certs(self, gctx, child = None, ski = None, revoked = False, unique = False):
+  def child_certs(self, gctx, child = None, ski = None, unique = False):
     """Fetch all child_cert objects that link to this ca_detail."""
-    return rpki.sql.child_cert_obj.fetch(gctx, child, self, ski, revoked, unique)
+    return rpki.sql.child_cert_obj.fetch(gctx, child, self, ski, unique)
+
+  def revoked_certs(self, gctx):
+    """Fetch all revoked_cert objects that link to this ca_detail."""
+    return revoked_cert_obj.sql_fetch_where(gctx, "ca_detail_id = %s", (self.ca_detail_id,))
 
   def route_origins(self, gctx):
     """Fetch all route_origin objects that link to this ca_detail."""
@@ -467,8 +487,8 @@ class ca_detail_obj(sql_persistant):
     for child_cert in self.child_certs(gctx):
       repository.withdraw(gctx, child_cert.cert, child_cert.uri(ca))
       child_cert.sql_delete(gctx)
-    for child_cert in self.child_certs(gctx, revoked = True):
-      child_cert.sql_delete(gctx)
+    for revoked__cert in self.revoked_certs(gctx):
+      revoked_cert.sql_delete(gctx)
     for route_origin in self.route_origins(gctx):
       raise rpki.exceptions.NotImplementedYet, "Don't (yet) know how to withdraw ROAs"
     repository.withdraw(gctx, self.latest_manifest, self.manifest_uri(ca))
@@ -503,7 +523,7 @@ class ca_detail_obj(sql_persistant):
     parent = ca.parent(gctx)
     crl_interval = rpki.sundial.timedelta(seconds = parent.self(gctx).crl_interval)
 
-    nextUpdate = rpki.sundial.datetime.utcnow()
+    nextUpdate = rpki.sundial.now()
 
     if self.latest_manifest is not None:
       nextUpdate = nextUpdate.later(self.latest_manifest.getNextUpdate())
@@ -640,17 +660,17 @@ class ca_detail_obj(sql_persistant):
     parent = ca.parent(gctx)
     repository = parent.repository(gctx)
     crl_interval = rpki.sundial.timedelta(seconds = parent.self(gctx).crl_interval)
-    now = rpki.sundial.datetime.utcnow()
+    now = rpki.sundial.now()
 
     if nextUpdate is None:
       nextUpdate = now + crl_interval
 
     certlist = []
-    for child_cert in self.child_certs(gctx, revoked = True):
-      if now > child_cert.cert.getNotAfter() + crl_interval:
-        child_cert.sql_delete()
+    for revoked_cert in self.revoked_certs(gctx):
+      if now > revoked_cert.expires + crl_interval:
+        revoked_cert.sql_delete()
       else:
-        certlist.append((child_cert.cert.getSerial(), child_cert.revoked.toASN1tuple(), ()))
+        certlist.append((revoked_cert.serial, revoked_cert.revoked.toASN1tuple(), ()))
     certlist.sort()
 
     self.latest_crl = rpki.x509.CRL.generate(
@@ -670,7 +690,7 @@ class ca_detail_obj(sql_persistant):
     parent = ca.parent(gctx)
     repository = parent.repository(gctx)
     crl_interval = rpki.sundial.timedelta(seconds = parent.self(gctx).crl_interval)
-    now = rpki.sundial.datetime.utcnow()
+    now = rpki.sundial.now()
 
     if nextUpdate is None:
       nextUpdate = now + crl_interval
@@ -693,14 +713,13 @@ class ca_detail_obj(sql_persistant):
 class child_cert_obj(sql_persistant):
   """Certificate that has been issued to a child."""
 
-  sql_template = template("child_cert", "child_cert_id", ("cert", rpki.x509.X509), "child_id", "ca_detail_id", "ski", ("revoked", rpki.sundial.datetime))
+  sql_template = template("child_cert", "child_cert_id", ("cert", rpki.x509.X509), "child_id", "ca_detail_id", "ski")
 
   def __init__(self, child_id = None, ca_detail_id = None, cert = None):
     """Initialize a child_cert_obj."""
     self.child_id = child_id
     self.ca_detail_id = ca_detail_id
     self.cert = cert
-    self.revoked = None
     if child_id or ca_detail_id or cert:
       self.sql_mark_dirty()
 
@@ -721,14 +740,15 @@ class child_cert_obj(sql_persistant):
     return ca.sia_uri + self.uri_tail()
 
   def revoke(self, gctx):
-    """Mark a child cert as revoked."""
-    if self.revoked is None:
-      rpki.log.debug("Revoking %s" % repr(self))
-      self.revoked = rpki.sundial.datetime.utcnow()
-      ca = self.ca_detail(gctx).ca(gctx)
-      repository = ca.parent(gctx).repository(gctx)
-      repository.withdraw(gctx, self.cert, self.uri(ca))
-      self.sql_mark_dirty()
+    """Revoke a child cert."""
+    rpki.log.debug("Revoking %s" % repr(self))
+    ca_detail = self.ca_detail(gctx)
+    ca = ca_detail.ca(gctx)
+    revoked_cert_obj.revoke(cert = self.cert, ca_detail = ca_detail)
+    repository = ca.parent(gctx).repository(gctx)
+    repository.withdraw(gctx, self.cert, self.uri(ca))
+    sql_sweep(gctx)
+    self.sql_delete(gctx)
 
   def reissue(self, gctx, ca_detail, resources = None, sia = None):
     """Reissue an existing cert, reusing the public key.  If the cert
@@ -785,7 +805,7 @@ class child_cert_obj(sql_persistant):
     return child_cert
 
   @classmethod
-  def fetch(cls, gctx, child = None, ca_detail = None, ski = None, revoked = False, unique = False):
+  def fetch(cls, gctx, child = None, ca_detail = None, ski = None, unique = False):
     """Fetch all child_cert objects matching a particular set of
     parameters.  This is a wrapper to consolidate various queries that
     would otherwise be inline SQL WHERE expressions.  In most cases
@@ -793,20 +813,53 @@ class child_cert_obj(sql_persistant):
     """
 
     args = []
-    where = "revoked IS"
-    if revoked:
-      where += " NOT"
-    where += " NULL"
+    where = []
+
     if child:
-      where += " AND child_id = %s"
+      where.append("child_id = %s")
       args.append(child.child_id)
+
     if ca_detail:
-      where += " AND ca_detail_id = %s"
+      where.append("ca_detail_id = %s")
       args.append(ca_detail.ca_detail_id)
+
     if ski:
-      where += " AND ski = %s"
+      where.append("ski = %s")
       args.append(ski)
+
+    where = " AND ".join(where)
+
     if unique:
       return cls.sql_fetch_where1(gctx, where, args)
     else:
       return cls.sql_fetch_where(gctx, where, args)
+
+class revoked_cert_obj(sql_persistant):
+  """Tombstone for a revoked certificate."""
+
+  sql_template = template("revoked_cert", "revoked_cert_id",
+                          "serial", "ca_detail_id",
+                          ("revoked", rpki.sundial.datetime),
+                          ("expires", rpki.sundial.datetime))
+
+  def __init__(self, serial = None, revoked = None, expires = None, ca_detail_id = None):
+    """Initialize a revoked_cert_obj."""
+    self.serial = serial
+    self.revoked = revoked
+    self.expires = expires
+    self.ca_detail_id = ca_detail_id
+    if serial or revoked or expires or ca_detail_id:
+      self.sql_mark_dirty()
+
+  def ca_detail(self, gctx):
+    """Fetch ca_detail object to which this revoked_cert_obj links."""
+    return ca_detail_obj.sql_fetch(gctx, self.ca_detail_id)
+
+  @classmethod
+  def revoke(cls, cert, ca_detail):
+    """Revoke a certificate."""
+    return cls(
+      serial       = cert.getSerial(),
+      expires      = cert.getNotAfter(),
+      revoked      = rpki.sundial.now(),
+      ca_detail_id = ca_detail.ca_detail_id)
diff --git a/rpkid/rpki/sundial.py b/rpkid/rpki/sundial.py
index a1ffde62..d7394327 100644
--- a/rpkid/rpki/sundial.py
+++ b/rpkid/rpki/sundial.py
@@ -22,6 +22,10 @@ that occur with the more obvious module names.
 
 import datetime as pydatetime
 
+def now():
+  """Get current timestamp."""
+  return datetime.utcnow()
+
 class datetime(pydatetime.datetime):
   """RPKI extensions to standard datetime.datetime class.  All work
   here is in UTC, so we use naive datetime objects.
@@ -108,8 +112,18 @@ class datetime(pydatetime.datetime):
     return cls.fromdatetime(x)
 
   def to_sql(self):
-    """Convert to SQL storage format."""
-    return self
+    """Convert to SQL storage format.
+
+    There's something whacky going on in the MySQLdb module, it throws
+    range errors when storing a derived type into a DATETIME column.
+    Investigate some day, but for now brute force this by copying the
+    relevant fields into a datetime.datetime for MySQLdb's
+    consumption.
+
+    """
+    return pydatetime.datetime(year = self.year, month = self.month, day = self.day,
+                               hour = self.hour, minute = self.minute, second = self.second,
+                               microsecond = 0, tzinfo = None)
 
   def later(self, other):
     """Return the later of two timestamps."""
@@ -125,7 +139,7 @@ timedelta = pydatetime.timedelta
 
 if __name__ == "__main__":
 
-  now = datetime.utcnow()
+  now = datetime.now()
   print now
   print repr(now)
   print now.strftime("%s")
diff --git a/rpkid/rpki/x509.py b/rpkid/rpki/x509.py
index 70e79ba6..a2e81a67 100644
--- a/rpkid/rpki/x509.py
+++ b/rpkid/rpki/x509.py
@@ -286,7 +286,7 @@ class X509(DER_object):
             cn = None, resources = None, is_ca = True):
     """Issue a certificate."""
 
-    now = rpki.sundial.datetime.utcnow()
+    now = rpki.sundial.now()
     aki = self.get_SKI()
     ski = subject_key.get_SKI()