3 * Set up the database for tracking certificates and requests
6 /*----- Metadata table ----------------------------------------------------*/
13 insert into meta values (1, 0);
15 /*----- Profiles ----------------------------------------------------------*/
17 create table profile (
19 -- A label by which this profile is known.
20 label varchar(16) primary key,
22 -- The name of the extensions section in OpenSSL's configuration
24 extensions varchar(32) not null,
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,
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,
38 -- The time, in hours, that a certificate remains valid after issue.
39 expire_interval integer not null,
41 -- A marker that this profile shouldn't be used any more.
42 tombstone boolean default 0
45 /*----- Certificate requests ----------------------------------------------*/
47 create table request (
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,
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,
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'.
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.
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,
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,
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,
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),
97 -- The time at which we should issue the next certificate for this
99 t_reissue timestamp default 0,
101 -- Ensure that we have a revocation reason if the state is
103 check (st != 'revoked' or revoke_reason is not null),
105 -- Check that the profile matches one that's already known.
106 foreign key (profile) references profile(label)
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);
115 /*----- Certificates ------------------------------------------------------*/
117 create table certificate (
119 -- The sequence number of the certificate, as issued by OpenSSL's
121 seq integer primary key,
123 -- The associated certificate request.
124 req integer not null,
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,
131 -- The expiry time for the certificate. This may be in the past.
132 t_expire timestamp not null,
134 -- Match the certificate up with its request.
135 foreign key (req) references request(id)
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);
142 /*----- That's all, folks -------------------------------------------------*/