Commit | Line | Data |
---|---|---|
69ab55f7 MW |
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 -------------------------------------------------*/ |