1 /* The "32bit hash" in public_keys, x509super, requests, certs and crls 2 * is used to quickly find items in the DB by reference. 3 * It consists of the first 4 bytes of a SHA1 hash. 4 * Collisions are of course possible. 5 * 6 * All binaries are stored Base64 encoded in a column of type 7 * ' B64_BLOB ' It is defined here as 'VARCHAR(8000)' 8 */ 9 10 #define B64_BLOB "VARCHAR(8000)" 11 12 /* 13 * The B64(DER(something)) function means DER encode something 14 * and then Base64 encode that. 15 * So finally this is PEM without newlines, header and footer 16 * 17 * Dates are alway stored as 'CHAR(15)' in the 18 * ASN.1 Generalized time 'yyyyMMddHHmmssZ' format 19 */ 20 21 #define DB_DATE "CHAR(15)" 22 23 /* 24 * Configuration settings from 25 * the Options dialog, window size, last export directory, 26 * default key type and size, 27 * table column (position, sort order, visibility) 28 */ 29 30 schemas[0] 31 << "CREATE TABLE settings (" 32 "key_ CHAR(20) UNIQUE, " // mySql does not like 'key' or 'option" 33 "value " B64_BLOB ")" 34 << "INSERT INTO settings (key_, value) VALUES ('schema', '" SCHEMA_VERSION "')" 35 36 /* 37 * All items (keys, tokens, requests, certs, crls, templates) 38 * are stored here with the primary key and some common data 39 * The other tables containing the details reference the 'id' 40 * as FOREIGN KEY. 41 */ 42 << "CREATE TABLE items(" 43 "id INTEGER PRIMARY KEY, " 44 "name VARCHAR(128), " // Internal name of the item 45 "type INTEGER, " // enum pki_type 46 "source INTEGER, " // enum pki_source 47 "date " DB_DATE ", " // Time of insertion (creation/import) 48 "comment VARCHAR(2048), " 49 "stamp INTEGER NOT NULL DEFAULT 0, " // indicate concurrent access 50 "del SMALLINT NOT NULL DEFAULT 0)" 51 52 /* 53 * Storage of public keys. Private keys and tokens also store 54 * their public part here. 55 */ 56 << "CREATE TABLE public_keys (" 57 "item INTEGER, " // reference to items(id) 58 "type CHAR(4), " // RSA DSA EC (as text) 59 "hash INTEGER, " // 32 bit hash 60 "len INTEGER, " // key size in bits 61 "\"public\" " B64_BLOB ", " // B64(DER(public key)) 62 "FOREIGN KEY (item) REFERENCES items (id))" 63 64 /* 65 * The private part of RSA, DSA, EC keys. 66 * references to 'items' and 'public_keys' 67 */ 68 << "CREATE TABLE private_keys (" 69 "item INTEGER, " // reference to items(id) 70 "ownPass INTEGER, " // Encrypted by DB pwd or own pwd 71 "private " B64_BLOB ", " // B64(Encrypt(DER(private key))) 72 "FOREIGN KEY (item) REFERENCES items (id))" 73 74 /* 75 * Smart cards or other PKCS#11 tokens 76 * references to 'items' and 'public_keys' 77 */ 78 << "CREATE TABLE tokens (" 79 "item INTEGER, " // reference to items(id) 80 "card_manufacturer VARCHAR(64), " // Card location data 81 "card_serial VARCHAR(64), " // as text 82 "card_model VARCHAR(64), " 83 "card_label VARCHAR(64), " 84 "slot_label VARCHAR(64), " 85 "object_id VARCHAR(64), " // Unique ID on the token 86 "FOREIGN KEY (item) REFERENCES items (id))" 87 88 /* 89 * Encryption and hash mechanisms supported by a token 90 */ 91 << "CREATE TABLE token_mechanism (" 92 "item INTEGER, " // reference to items(id) 93 "mechanism INTEGER, " // PKCS#11: CK_MECHANISM_TYPE 94 "FOREIGN KEY (item) REFERENCES items (id))" 95 96 /* 97 * An X509 Super class, consisting of a 98 * - Distinguishd name hash 99 * - Referenced key in the database 100 * - hash of the public key, used for lookups if there 101 * is no key to reference 102 * used by Requests and certificates and the use-counter of keys: 103 * 'SELECT from x509super WHERE pkey=?' 104 */ 105 << "CREATE TABLE x509super (" 106 "item INTEGER, " // reference to items(id) 107 "subj_hash INTEGER, " // 32 bit hash of the Distinguished name 108 "pkey INTEGER, " // reference to the key items(id) 109 "key_hash INTEGER, " // 32 bit hash of the public key 110 "FOREIGN KEY (item) REFERENCES items (id), " 111 "FOREIGN KEY (pkey) REFERENCES items (id)) " 112 113 /* 114 * PKCS#10 Certificate request details 115 * also takes information from the 'x509super' table. 116 */ 117 << "CREATE TABLE requests (" 118 "item INTEGER, " // reference to items(id) 119 "hash INTEGER, " // 32 bit hash of the request 120 "signed INTEGER, " // Whether it was once signed. 121 "request " B64_BLOB ", " // B64(DER(PKCS#10 request)) 122 "FOREIGN KEY (item) REFERENCES items (id)) " 123 124 /* 125 * X509 certificate details 126 * also takes information from the 'x509super' table. 127 * The content of the columns: hash, iss_hash, serial, ca 128 * can also be retrieved directly from the certificate, but are good 129 * to lurk around for faster lookup 130 */ 131 << "CREATE TABLE certs (" 132 "item INTEGER, " // reference to items(id) 133 "hash INTEGER, " // 32 bit hash of the cert 134 "iss_hash INTEGER, " // 32 bit hash of the issuer DN 135 "serial VARCHAR(64), " // Serial number of the certificate 136 "issuer INTEGER, " // The items(id) of the issuer or NULL 137 "ca INTEGER, " // CA: yes / no from BasicConstraints 138 "cert " B64_BLOB ", " // B64(DER(certificate)) 139 "FOREIGN KEY (item) REFERENCES items (id), " 140 "FOREIGN KEY (issuer) REFERENCES items (id)) " 141 142 /* 143 * X509 cartificate Authority data 144 */ 145 << "CREATE TABLE authority (" 146 "item INTEGER, " // reference to items(id) 147 "template INTEGER, " // items(id) of the default template 148 "crlExpire " DB_DATE ", " // CRL expiry date 149 "crlNo INTEGER, " // Last CRL Number 150 "crlDays INTEGER, " // CRL days until renewal 151 "dnPolicy VARCHAR(1024), " // DistinguishedName policy (UNUSED) 152 "FOREIGN KEY (item) REFERENCES items (id), " 153 "FOREIGN KEY (template) REFERENCES items (id)) " 154 155 /* 156 * Storage of CRLs 157 */ 158 << "CREATE TABLE crls (" 159 "item INTEGER, " // reference to items(id) 160 "hash INTEGER, " // 32 bit hash of the CRL 161 "num INTEGER, " // Number of revoked certificates 162 "iss_hash INTEGER, " // 32 bit hash of the issuer DN 163 "issuer INTEGER, " // The items(id) of the issuer or NULL 164 "crl " B64_BLOB ", " // B64(DER(revocation list)) 165 "FOREIGN KEY (item) REFERENCES items (id), " 166 "FOREIGN KEY (issuer) REFERENCES items (id)) " 167 168 /* 169 * Revocations (serial, date, reason, issuer) used to create new 170 * CRLs. 'Manage revocations' 171 */ 172 << "CREATE TABLE revocations (" 173 "caId INTEGER, " // reference to certs(item) 174 "serial VARCHAR(64), " // Serial of the revoked certificate 175 "date " DB_DATE ", " // Time of creating the revocation 176 "invaldate " DB_DATE ", " // Time of invalidation 177 "crlNo INTEGER, " // Crl Number of CRL of first appearance 178 "reasonBit INTEGER, " // Bit number of the revocation reason 179 "FOREIGN KEY (caId) REFERENCES items (id))" 180 181 /* 182 * Templates 183 */ 184 << "CREATE TABLE templates (" 185 "item INTEGER, " // reference to items(id) 186 "version INTEGER, " // Version of the template format 187 "template " B64_BLOB ", " // The base64 encoded template 188 "FOREIGN KEY (item) REFERENCES items (id))" 189 190 /* Views */ 191 << "CREATE VIEW view_public_keys AS SELECT " 192 "items.id, items.name, items.type AS item_type, items.date, " 193 "items.source, items.comment, " 194 "public_keys.type as key_type, public_keys.len, public_keys.\"public\", " 195 "private_keys.ownPass, " 196 "tokens.card_manufacturer, tokens.card_serial, tokens.card_model, " 197 "tokens.card_label, tokens.slot_label, tokens.object_id " 198 "FROM public_keys LEFT JOIN items ON public_keys.item = items.id " 199 "LEFT JOIN private_keys ON private_keys.item = public_keys.item " 200 "LEFT JOIN tokens ON public_keys.item = tokens.item" 201 202 << "CREATE VIEW view_certs AS SELECT " 203 "items.id, items.name, items.type, items.date AS item_date, " 204 "items.source, items.comment, " 205 "x509super.pkey, " 206 "certs.serial AS certs_serial, certs.issuer, certs.ca, certs.cert, " 207 "authority.template, authority.crlExpire, " 208 "authority.crlNo AS auth_crlno, authority.crlDays, authority.dnPolicy, " 209 "revocations.serial, revocations.date, revocations.invaldate, " 210 "revocations.crlNo, revocations.reasonBit " 211 "FROM certs LEFT JOIN items ON certs.item = items.id " 212 "LEFT JOIN x509super ON x509super.item = certs.item " 213 "LEFT JOIN authority ON authority.item = certs.item " 214 "LEFT JOIN revocations ON revocations.caId = certs.issuer " 215 "AND revocations.serial = certs.serial" 216 217 << "CREATE VIEW view_requests AS SELECT " 218 "items.id, items.name, items.type, items.date, " 219 "items.source, items.comment, " 220 "x509super.pkey, " 221 "requests.request, requests.signed " 222 "FROM requests LEFT JOIN items ON requests.item = items.id " 223 "LEFT JOIN x509super ON x509super.item = requests.item" 224 225 << "CREATE VIEW view_crls AS SELECT " 226 "items.id, items.name, items.type, items.date, " 227 "items.source, items.comment, " 228 "crls.num, crls.issuer, crls.crl " 229 "FROM crls LEFT JOIN items ON crls.item = items.id " 230 231 << "CREATE VIEW view_templates AS SELECT " 232 "items.id, items.name, items.type, items.date, " 233 "items.source, items.comment, " 234 "templates.version, templates.template " 235 "FROM templates LEFT JOIN items ON templates.item = items.id" 236 237 << "CREATE VIEW view_private AS SELECT " 238 "name, private FROM private_keys JOIN items ON " 239 "items.id = private_keys.item" 240 241 242 << "CREATE INDEX i_settings_key_ ON settings (key_)" 243 << "CREATE INDEX i_items_id ON items (id)" 244 << "CREATE INDEX i_public_keys_item ON public_keys (item)" 245 << "CREATE INDEX i_public_keys_hash ON public_keys (hash)" 246 << "CREATE INDEX i_private_keys_item ON private_keys (item)" 247 << "CREATE INDEX i_tokens_item ON tokens (item)" 248 << "CREATE INDEX i_token_mechanism_item ON token_mechanism (item)" 249 << "CREATE INDEX i_x509super_item ON x509super (item)" 250 << "CREATE INDEX i_x509super_subj_hash ON x509super (subj_hash)" 251 << "CREATE INDEX i_x509super_key_hash ON x509super (key_hash)" 252 << "CREATE INDEX i_x509super_pkey ON x509super (pkey)" 253 << "CREATE INDEX i_requests_item ON requests (item)" 254 << "CREATE INDEX i_requests_hash ON requests (hash)" 255 << "CREATE INDEX i_certs_item ON certs (item)" 256 << "CREATE INDEX i_certs_hash ON certs (hash)" 257 << "CREATE INDEX i_certs_iss_hash ON certs (iss_hash)" 258 << "CREATE INDEX i_certs_serial ON certs (serial)" 259 << "CREATE INDEX i_certs_issuer ON certs (issuer)" 260 << "CREATE INDEX i_certs_ca ON certs (ca)" 261 << "CREATE INDEX i_authority_item ON authority (item)" 262 << "CREATE INDEX i_crls_item ON crls (item)" 263 << "CREATE INDEX i_crls_hash ON crls (hash)" 264 << "CREATE INDEX i_crls_iss_hash ON crls (iss_hash)" 265 << "CREATE INDEX i_crls_issuer ON crls (issuer)" 266 << "CREATE INDEX i_revocations_caId_serial ON revocations (caId, serial)" 267 << "CREATE INDEX i_templates_item ON templates (item)" 268 << "CREATE INDEX i_items_stamp ON items (stamp)" 269 270 ; 271 /* Schema Version 2: Views added to quickly load the data */ 272 273 /* Schema Version 3: Add indexes over hashes and primary, foreign keys */ 274 275 /* Schema Version 4: Add private key view to extract a private key with: 276 mysql: mysql -sNp -u xca xca_msq -e 277 or sqlite: sqlite3 ~/sqlxdb.xdb 278 or psql: psql -t -h 192.168.140.7 -U xca -d xca_pg -c 279 'SELECT private FROM view_private WHERE name=\"pk8key\";' |\ 280 base64 -d | openssl pkcs8 -inform DER 281 * First mysql/psql will ask for a password and then OpenSSL will ask for 282 * the database password. 283 */ 284 285 /* Schema Version 5: Extend settings value size from 1024 to B64_BLOB 286 * SQLite does not support 'ALTER TABLE settings MODIFY ...' 287 */ 288 289 schemas[5] 290 << "ALTER TABLE settings RENAME TO __settings" 291 << "CREATE TABLE settings (" 292 "key_ CHAR(20) UNIQUE, " // mySql does not like 'key' or 'option' 293 "value " B64_BLOB ")" 294 << "INSERT INTO settings(key_, value) " 295 "SELECT key_, value " 296 "FROM __settings" 297 << "DROP TABLE __settings" 298 << "UPDATE settings SET value='6' WHERE key_='schema'" 299 ; 300 301 schemas[6] 302 << "ALTER TABLE items ADD del SMALLINT NOT NULL DEFAULT 0" 303 << "CREATE INDEX i_items_del ON items (del)" 304 << "UPDATE settings SET value='7' WHERE key_='schema'" 305 ; 306 307 /* When adding new tables or views, also add them to the list 308 * in XSqlQuery::rewriteQuery(QString) in lib/sql.cpp 309 */ 310