chiark - git - mdw - ca/blob - sql/create.sql

   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 -------------------------------------------------*/