Project (Master) Internship: Designing a Certificate Revocation Datastore and Query Interface

Alexandre Dulaunoy (CIRCL) - Internship Supervisor

Radu State (University of Luxembourg) and Ralph Holz (Technische Universität München) - Reviewers

The project objective is to use an existing dataset of Certificate Revocation List (CRL) containing the CRL output of the most well-known CA. With the list of CRLs, you’ll design a relational database structure to hold the historical data of each CA revocation list. The datastore should allow efficient queries on complete time series per CA for their whole revocations including reasons and serial numbers allocation.

Existing datastore

The CRL are stored in a git repository containing each CRL file using the name of the SHA-1 value from the CRL URL. The datastore is composed of a raw directory containing each CRL file in a raw format and commited at regular interval. The datastore also contains the parsed CRL using OpenSSL and the name of the file is the same SHA-1 value from the CRL URL.

The sample output of a parsed CRL db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt

issuer=/C=si/O=state-institutions/OU=sigen-ca
SHA1 Fingerprint=0E:55:D7:DA:77:10:6D:7A:7D:A0:73:20:4B:0E:26:16:D4:07:58:A4
9d0e314c
crlNumber=6123
lastUpdate=Jan  7 10:45:00 2013 GMT
nextUpdate=Jan 10 10:45:00 2013 GMT
Certificate Revocation List (CRL):
        Version 2 (0x1)
        Signature Algorithm: sha1WithRSAEncryption
        Issuer: /C=si/O=state-institutions/OU=sigen-ca
        Last Update: Jan  7 10:45:00 2013 GMT
        Next Update: Jan 10 10:45:00 2013 GMT
        CRL extensions:
            X509v3 CRL Number:
                24867
            X509v3 Authority Key Identifier:
                keyid:71:7B:8A:06:1F:31:05:55:AB:60:12:77:47:20:1E:03:88:18:EC:89
Revoked Certificates:
    Serial Number: 3B425D57
        Revocation Date: Jan  5 08:02:21 2011 GMT
        CRL entry extensions:
            X509v3 CRL Reason Code:
                Unspecified
...

The history of the parsed CRL db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt

commit 5f080f712c498dc580c569345d94a80c602e565f
Author: Alexandre Dulaunoy <adulau@somestuff>
Date:   Mon Jan 7 12:20:03 2013 +0100

    Update http://www.sigen-ca.si/crl/sigen-ca.crl into db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl and parsed into ../crl-parsed/db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt

diff --git a/crl-parsed/db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt b/crl-parsed/db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt
index 2863b15..8ad8f54 100644
--- a/crl-parsed/db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt
+++ b/crl-parsed/db4a4c0b2b6f6d8d470001dde7c02d9bf25bde64.crl2txt
@@ -1,18 +1,18 @@
 issuer=/C=si/O=state-institutions/OU=sigen-ca
-SHA1 Fingerprint=7C:B7:5E:99:EA:E0:F8:46:B7:A0:77:C6:05:A8:A4:6D:7C:EA:25:78
+SHA1 Fingerprint=0E:55:D7:DA:77:10:6D:7A:7D:A0:73:20:4B:0E:26:16:D4:07:58:A4
 9d0e314c
-crlNumber=6116
-lastUpdate=Jan  4 12:55:28 2013 GMT
-nextUpdate=Jan  7 12:55:28 2013 GMT
+crlNumber=6123
+lastUpdate=Jan  7 10:45:00 2013 GMT
+nextUpdate=Jan 10 10:45:00 2013 GMT
 Certificate Revocation List (CRL):
         Version 2 (0x1)
         Signature Algorithm: sha1WithRSAEncryption
         Issuer: /C=si/O=state-institutions/OU=sigen-ca
-        Last Update: Jan  4 12:55:28 2013 GMT
-        Next Update: Jan  7 12:55:28 2013 GMT
+        Last Update: Jan  7 10:45:00 2013 GMT
+        Next Update: Jan 10 10:45:00 2013 GMT
         CRL extensions:
             X509v3 CRL Number:
-                24854
+                24867
             X509v3 Authority Key Identifier:
                 keyid:71:7B:8A:06:1F:31:05:55:AB:60:12:77:47:20:1E:03:88:18:EC:89

@@ -71422,19 +71422,74 @@ Revoked Certificates:
         CRL entry extensions:
             X509v3 CRL Reason Code:
                 Unspecified
+    Serial Number: 3B40C75B
+        Revocation Date: Jan  4 15:57:32 2013 GMT
+        CRL entry extensions:
+            X509v3 CRL Reason Code:
+                Unspecified
+    Serial Number: 3B40E8DB
+        Revocation Date: Jan  7 08:42:23 2013 GMT
+        CRL entry extensions:
+            X509v3 CRL Reason Code:
+                Unspecified
+    Serial Number: 3B40630C
+        Revocation Date: Jan  7 09:44:23 2013 GMT
+        CRL entry extensions:
+            X509v3 CRL Reason Code:
+                Unspecified

Project Proposal Requirements

Student must submit an one-page summary to describe their project and what approaches they will use in order to design and implement a CRL datastore and a query interface.

Technical Requirements

Python (2.7 or 3) will be used for the development of the project.
Existing import of the datastore (in git format) must be supported.
Datastore must use a relational mode and be implemented in MariaDB.
Code and implementation must be as simple as possible.

Project Deliverables

Software implementing the datastore.
Software implementing the query interface (API).
Documentation of the datastore.