| 1 | /* -*-sql-*- |
| 2 | * |
| 3 | * Set up the database for tracking certificates and requests |
| 4 | */ |
| 5 | |
| 6 | /*----- Metadata table ----------------------------------------------------*/ |
| 7 | |
| 8 | create table meta ( |
| 9 | version integer, |
| 10 | request_seq integer |
| 11 | ); |
| 12 | |
| 13 | insert into meta values (1, 0); |
| 14 | |
| 15 | /*----- Profiles ----------------------------------------------------------*/ |
| 16 | |
| 17 | create table profile ( |
| 18 | |
| 19 | -- A label by which this profile is known. |
| 20 | label varchar(16) primary key, |
| 21 | |
| 22 | -- The name of the extensions section in OpenSSL's configuration |
| 23 | -- file. |
| 24 | extensions varchar(32) not null, |
| 25 | |
| 26 | -- The time at which to issue the next certificate. This has the |
| 27 | -- form of an ISO8601 date (YYYY-MM-DD HH:MM:SS), though the |
| 28 | -- components can be wildcards. Specifically, * means any value, and |
| 29 | -- */n means any value which is a multiple of n. Sorry: there isn't |
| 30 | -- currently a way of saying `next Thursday'. |
| 31 | issue_time varchar(64) not null, |
| 32 | |
| 33 | -- The time, in hours, that a certificate should appear to have been |
| 34 | -- valid /before/ it was issued. This is useful to prevent |
| 35 | -- unnecessary problems with reliers whose clocks are broken. |
| 36 | start_skew integer not null, |
| 37 | |
| 38 | -- The time, in hours, that a certificate remains valid after issue. |
| 39 | expire_interval integer not null, |
| 40 | |
| 41 | -- A marker that this profile shouldn't be used any more. |
| 42 | tombstone boolean default 0 |
| 43 | ); |
| 44 | |
| 45 | /*----- Certificate requests ----------------------------------------------*/ |
| 46 | |
| 47 | create table request ( |
| 48 | |
| 49 | -- We need a stable way to talk about requests. Unfortunately, we |
| 50 | -- don't have an exernally stable name, so we'll get the database to |
| 51 | -- make one up for us. |
| 52 | id integer primary key, |
| 53 | |
| 54 | -- The tag is a vaguely human-readable name for this certificate |
| 55 | -- request. THere should only be one active request with a given |
| 56 | -- tag, though there may be several withdrawn or revoked requests. |
| 57 | tag varchar(128) not null, |
| 58 | |
| 59 | -- The distinguished name for the request. Again, there should only |
| 60 | -- be one active request with a given DN, but there may be several |
| 61 | -- withdrawn or revoked requests. The DN is given in OpenSSL's terse |
| 62 | -- form, as written by `openssl req -subject -noout'. |
| 63 | dn text not null, |
| 64 | |
| 65 | -- The distinguished name as it will appear in certificates. We |
| 66 | -- expect all certificates to have the same DN, though OpenSSL's |
| 67 | -- certificate construction will mangle the DN (in particular, it |
| 68 | -- moves email addresses to the `subjectAltName'). If this field is |
| 69 | -- null then no certificate has been issued for the request; |
| 70 | -- otherwise we fill it in with the certificate's DN. |
| 71 | cert_dn text, |
| 72 | |
| 73 | -- The key hash from the request. This is simply the SHA256 hash of |
| 74 | -- the DER-encoded public key, in lowercase hex. |
| 75 | hash varchar(64) not null, |
| 76 | |
| 77 | -- The state can be one of `active', `withdrawn', or `revoked'. |
| 78 | -- Requests in the `archived' state aren't stored in the database. |
| 79 | st varchar(12) not null, |
| 80 | |
| 81 | -- The profile tells us how we should generate certificates. It |
| 82 | -- refers to a chunk of the OpenSSL configuration file. |
| 83 | profile varchar(16) not null, |
| 84 | |
| 85 | -- If the state is `revoked' then we should have the reason and maybe |
| 86 | -- a detail here. The reason is one of the names: `unspecified', |
| 87 | -- `key-compromise', `ca-compromise', `affiliation-changed', |
| 88 | -- `superceded', `cessation-of-operation', `certificate-hold', or |
| 89 | -- `remove-from-crl'. The detail's contents depends on the reason. |
| 90 | -- If the reason is `certificate-hold' then the reason is the hold |
| 91 | -- instruction, probably `none', `call-issuer', or `reject'; if the |
| 92 | -- reason is `key-compromise' or `ca-compromise' then the detail is |
| 93 | -- the compromise time; other reasons don't carry details. |
| 94 | revoke_reason varchar(32), |
| 95 | revoke_detail text, |
| 96 | |
| 97 | -- The time at which we should issue the next certificate for this |
| 98 | -- request. |
| 99 | t_reissue timestamp default 0, |
| 100 | |
| 101 | -- Ensure that we have a revocation reason if the state is |
| 102 | -- `revoked'. |
| 103 | check (st != 'revoked' or revoke_reason is not null), |
| 104 | |
| 105 | -- Check that the profile matches one that's already known. |
| 106 | foreign key (profile) references profile(label) |
| 107 | ); |
| 108 | |
| 109 | create index request_tag on request(tag); |
| 110 | create index request_dn on request(dn); |
| 111 | create index request_hash on request(hash); |
| 112 | create index request_reissue on request(t_reissue asc); |
| 113 | create index request_prifile on request(profile); |
| 114 | |
| 115 | /*----- Certificates ------------------------------------------------------*/ |
| 116 | |
| 117 | create table certificate ( |
| 118 | |
| 119 | -- The sequence number of the certificate, as issued by OpenSSL's |
| 120 | -- `ca' program. |
| 121 | seq integer primary key, |
| 122 | |
| 123 | -- The associated certificate request. |
| 124 | req integer not null, |
| 125 | |
| 126 | -- The state can be one of `active', `superceded', `withdrawn', |
| 127 | -- `revoked', or `expired'. Certificates in the `archived' state |
| 128 | -- aren't stored in the database. |
| 129 | st varchar(12) not null, |
| 130 | |
| 131 | -- The expiry time for the certificate. This may be in the past. |
| 132 | t_expire timestamp not null, |
| 133 | |
| 134 | -- Match the certificate up with its request. |
| 135 | foreign key (req) references request(id) |
| 136 | ); |
| 137 | |
| 138 | create index cert_st on certificate(st); |
| 139 | create index cert_req on certificate(req); |
| 140 | create index cert_expire on certificate(t_expire asc); |
| 141 | |
| 142 | /*----- That's all, folks -------------------------------------------------*/ |