/* The "32bit hash" in public_keys, x509super, requests, certs and crls
* is used to quickly find items in the DB by reference.
* It consists of the first 4 bytes of a SHA1 hash.
* Collisions are of course possible.
*
* All binaries are stored Base64 encoded in a column of type
* " B64_BLOB " It is defined here as "VARCHAR(10000)"
*/
#define B64_BLOB "VARCHAR(10000)"
/*
* The B64(DER(something)) function means DER encode something
* and then Base64 encode that.
* So finally this is PEM without newlines, header and footer
*
* Dates are alway stored as 'CHAR(15)' in the
* ASN.1 Generalized time 'yyyyMMddHHmmssZ' format
*/
#define DB_DATE "CHAR(15)"
/*
* Configuration settings from
* the Options dialog, window size, last export directory,
* default key type and size,
* table column (position, sort order, visibility)
*/
schemas[0]
<< "CREATE TABLE settings ("
"key_ CHAR(20) UNIQUE, " /* mySql does not like "key" or "option" */
"value VARCHAR(1024))"
<< "INSERT INTO settings (key_, value) VALUES ('schema', '1')"
/*
* All items (keys, tokens, requests, certs, crls, templates)
* are stored here with the primary key and some common data
* The other tables containing the details reference the "id"
* as FOREIGN KEY.
*/
<< "CREATE TABLE items("
"id INTEGER PRIMARY KEY, "
"name VARCHAR(128), " /* Internal name of the item */
"type INTEGER, " /* enum pki_type */
"source INTEGER, " /* enum pki_source */
"date " DB_DATE ", " /* Time of insertion (creation/import) */
"comment VARCHAR(2048))"
/*
* Storage of public keys. Private keys and tokens also store
* their public part here.
*/
<< "CREATE TABLE public_keys ("
"item INTEGER, " /* reference to items(id) */
"type CHAR(4), " /* RSA DSA EC (as text) */
"hash INTEGER, " /* 32 bit hash */
"len INTEGER, " /* key size in bits */
"public " B64_BLOB ", " /* B64(DER(public key)) */
"FOREIGN KEY (item) REFERENCES items (id))"
/*
* The private part of RSA, DSA, EC keys.
* references to "items" and "public_keys"
*/
<< "CREATE TABLE private_keys ("
"item INTEGER, " /* reference to items(id) */
"ownPass INTEGER, " /* Encrypted by DB pwd or own pwd */
"private " B64_BLOB ", " /* B64(Encrypt(DER(private key))) */
"FOREIGN KEY (item) REFERENCES items (id))"
/*
* Smart cards or other PKCS#11 tokens
* references to "items" and "public_keys"
*/
<< "CREATE TABLE tokens ("
"item INTEGER, " /* reference to items(id) */
"card_manufacturer VARCHAR(64), " /* Card location data */
"card_serial VARCHAR(64), " /* as text */
"card_model VARCHAR(64), "
"card_label VARCHAR(64), "
"slot_label VARCHAR(64), "
"object_id VARCHAR(64), " /* Unique ID on the token */
"FOREIGN KEY (item) REFERENCES items (id))"
/*
* Encryption and hash mechanisms supported by a token
*/
<< "CREATE TABLE token_mechanism ("
"item INTEGER, " /* reference to items(id) */
"mechanism INTEGER, " /* PKCS#11: CK_MECHANISM_TYPE */
"FOREIGN KEY (item) REFERENCES items (id))"
/*
* An X509 Super class, consisting of a
* - Distinguishd name hash
* - Referenced key in the database
* - hash of the public key, used for lookups if there
* is no key to reference
* used by Requests and certificates and the use-counter of keys:
* "SELECT from x509super WHERE pkey=?"
*/
<< "CREATE TABLE x509super ("
"item INTEGER, " /* reference to items(id) */
"subj_hash INTEGER, " /* 32 bit hash of the Distinguished name */
"pkey INTEGER, " /* reference to the key items(id) */
"key_hash INTEGER, " /* 32 bit hash of the public key */
"FOREIGN KEY (item) REFERENCES items (id), "
"FOREIGN KEY (pkey) REFERENCES items (id)) "
/*
* PKCS#10 Certificate request details
* also takes information from the "x509super" table.
*/
<< "CREATE TABLE requests ("
"item INTEGER, " /* reference to items(id) */
"hash INTEGER, " /* 32 bit hash of the request */
"signed INTEGER, " /* Whether it was once signed. */
"request " B64_BLOB ", " /* B64(DER(PKCS#10 request)) */
"FOREIGN KEY (item) REFERENCES items (id)) "
/*
* X509 certificate details
* also takes information from the "x509super" table.
* The content of the columns: hash, iss_hash, serial, ca
* can also be retrieved directly from the certificate, but are good
* to lurk around for faster lookup
*/
<< "CREATE TABLE certs ("
"item INTEGER, " /* reference to items(id) */
"hash INTEGER, " /* 32 bit hash of the cert */
"iss_hash INTEGER, " /* 32 bit hash of the issuer DN */
"serial VARCHAR(64), " /* Serial number of the certificate */
"issuer INTEGER, " /* The items(id) of the issuer or NULL */
"ca INTEGER, " /* CA: yes / no from BasicConstraints */
"cert " B64_BLOB ", " /* B64(DER(certificate)) */
"FOREIGN KEY (item) REFERENCES items (id), "
"FOREIGN KEY (issuer) REFERENCES items (id)) "
/*
* X509 cartificate Authority data
*/
<< "CREATE TABLE authority ("
"item INTEGER, " /* reference to items(id) */
"template INTEGER, " /* reference to items(id) of the default template*/
"crlExpire " DB_DATE ", " /* CRL expiry date */
"crlNo INTEGER, " /* Last CRL Number */
"crlDays INTEGER, " /* CRL days until renewal */
"dnPolicy VARCHAR(1024), " /* DistinguishedName policy (UNUSED) */
"FOREIGN KEY (item) REFERENCES items (id), "
"FOREIGN KEY (template) REFERENCES items (id)) "
/*
* Storage of CRLs
*/
<< "CREATE TABLE crls ("
"item INTEGER, " /* reference to items(id) */
"hash INTEGER, " /* 32 bit hash of the CRL */
"num INTEGER, " /* Number of revoked certificates */
"iss_hash INTEGER, " /* 32 bit hash of the issuer DN */
"issuer INTEGER, " /* The items(id) of the issuer or NULL */
"crl " B64_BLOB ", " /* B64(DER(revocation list)) */
"FOREIGN KEY (item) REFERENCES items (id), "
"FOREIGN KEY (issuer) REFERENCES items (id)) "
/*
* Revocations (serial, date, reason, issuer) used to create new
* CRLs. "Manage revocations"
*/
<< "CREATE TABLE revocations ("
"caId INTEGER, " /* reference to certs(item) */
"serial VARCHAR(64), " /* Serial number of the revoked certificate */
"date " DB_DATE ", " /* Time of creating the revocation */
"invaldate " DB_DATE ", " /* Time of invalidation */
"crlNo INTEGER, " /* Crl Number of CRL of first appearance */
"reasonBit INTEGER, " /* Bit number of the revocation reason */
"FOREIGN KEY (caId) REFERENCES items (id))"
/*
* Templates
*/
<< "CREATE TABLE templates ("
"item INTEGER, " /* reference to items(id) */
"version INTEGER, " /* Version of the template format */
"template " B64_BLOB ", " /* The base64 encoded template */
"FOREIGN KEY (item) REFERENCES items (id))"
;
/* Schema Version 2: Views added to quickly load the data */
schemas[1]
/* Views */
<< "CREATE VIEW view_public_keys AS SELECT "
"items.id, items.name, items.type AS item_type, items.date, "
"items.source, items.comment, "
"public_keys.type as key_type, public_keys.len, public_keys.public, "
"private_keys.ownPass, "
"tokens.card_manufacturer, tokens.card_serial, tokens.card_model, "
"tokens.card_label, tokens.slot_label, tokens.object_id "
"FROM public_keys LEFT JOIN items ON public_keys.item = items.id "
"LEFT JOIN private_keys ON private_keys.item = public_keys.item "
"LEFT JOIN tokens ON public_keys.item = tokens.item"
<< "CREATE VIEW view_certs AS SELECT "
"items.id, items.name, items.type, items.date AS item_date, "
"items.source, items.comment, "
"x509super.pkey, "
"certs.serial AS certs_serial, certs.issuer, certs.ca, certs.cert, "
"authority.template, authority.crlExpire, "
"authority.crlNo AS auth_crlno, authority.crlDays, authority.dnPolicy, "
"revocations.serial, revocations.date, revocations.invaldate, "
"revocations.crlNo, revocations.reasonBit "
"FROM certs LEFT JOIN items ON certs.item = items.id "
"LEFT JOIN x509super ON x509super.item = certs.item "
"LEFT JOIN authority ON authority.item = certs.item "
"LEFT JOIN revocations ON revocations.caId = certs.issuer "
"AND revocations.serial = certs.serial"
<< "CREATE VIEW view_requests AS SELECT "
"items.id, items.name, items.type, items.date, "
"items.source, items.comment, "
"x509super.pkey, "
"requests.request, requests.signed "
"FROM requests LEFT JOIN items ON requests.item = items.id "
"LEFT JOIN x509super ON x509super.item = requests.item"
<< "CREATE VIEW view_crls AS SELECT "
"items.id, items.name, items.type, items.date, "
"items.source, items.comment, "
"crls.num, crls.issuer, crls.crl "
"FROM crls LEFT JOIN items ON crls.item = items.id "
<< "CREATE VIEW view_templates AS SELECT "
"items.id, items.name, items.type, items.date, "
"items.source, items.comment, "
"templates.version, templates.template "
"FROM templates LEFT JOIN items ON templates.item = items.id"
<< "UPDATE settings SET value='2' WHERE key_='schema'"
;
/* Schema Version 3: Add indexes over hashes and primary, foreign keys */
schemas[2]
<< "CREATE INDEX i_settings_key_ ON settings (key_)"
<< "CREATE INDEX i_items_id ON items (id)"
<< "CREATE INDEX i_public_keys_item ON public_keys (item)"
<< "CREATE INDEX i_public_keys_hash ON public_keys (hash)"
<< "CREATE INDEX i_private_keys_item ON private_keys (item)"
<< "CREATE INDEX i_tokens_item ON tokens (item)"
<< "CREATE INDEX i_token_mechanism_item ON token_mechanism (item)"
<< "CREATE INDEX i_x509super_item ON x509super (item)"
<< "CREATE INDEX i_x509super_subj_hash ON x509super (subj_hash)"
<< "CREATE INDEX i_x509super_key_hash ON x509super (key_hash)"
<< "CREATE INDEX i_x509super_pkey ON x509super (pkey)"
<< "CREATE INDEX i_requests_item ON requests (item)"
<< "CREATE INDEX i_requests_hash ON requests (hash)"
<< "CREATE INDEX i_certs_item ON certs (item)"
<< "CREATE INDEX i_certs_hash ON certs (hash)"
<< "CREATE INDEX i_certs_iss_hash ON certs (iss_hash)"
<< "CREATE INDEX i_certs_serial ON certs (serial)"
<< "CREATE INDEX i_certs_issuer ON certs (issuer)"
<< "CREATE INDEX i_certs_ca ON certs (ca)"
<< "CREATE INDEX i_authority_item ON authority (item)"
<< "CREATE INDEX i_crls_item ON crls (item)"
<< "CREATE INDEX i_crls_hash ON crls (hash)"
<< "CREATE INDEX i_crls_iss_hash ON crls (iss_hash)"
<< "CREATE INDEX i_crls_issuer ON crls (issuer)"
<< "CREATE INDEX i_revocations_caId_serial ON revocations (caId, serial)"
<< "CREATE INDEX i_templates_item ON templates (item)"
<< "UPDATE settings SET value='3' WHERE key_='schema'"
;
/* Schema Version 4: Add private key view to extract a private key with:
mysql: mysql -sNp -u xca xca_msq -e
or sqlite: sqlite3 ~/sqlxdb.xdb
or psql: psql -t -h 192.168.140.7 -U xca -d xca_pg -c
"SELECT private FROM view_private WHERE name='pk8key';" |\
base64 -d | openssl pkcs8 -inform DER
* First mysql/psql will ask for a password and then OpenSSL will ask for
* the database password.
*/
schemas[3]
<< "CREATE VIEW view_private AS SELECT "
"name, private FROM private_keys JOIN items ON "
"items.id = private_keys.item"
<< "UPDATE settings SET value='4' WHERE key_='schema'"
;
schemas[4]
<< "ALTER TABLE items ADD stamp INTEGER NOT NULL DEFAULT 0"
<< "CREATE INDEX i_items_stamp ON items (stamp)"
<< "UPDATE settings SET value='5' WHERE key_='schema'"
;
/* Extend settings value size from 1024 to B64_BLOB
* SQLite does not support "ALTER TABLE settings MODIFY ..."
*/
schemas[5]
<< "ALTER TABLE settings RENAME TO __settings"
<< "CREATE TABLE settings ("
"key_ CHAR(20) UNIQUE, " /* mySql does not like "key" or "option" */
"value " B64_BLOB ")"
<< "INSERT INTO settings(key_, value) "
"SELECT key_, value "
"FROM __settings"
<< "DROP TABLE __settings"
<< "UPDATE settings SET value='6' WHERE key_='schema'"
;
/* When adding new tables or views, also add them to the list
* in XSqlQuery::rewriteQuery(QString) in lib/sql.cpp
*/
syntax highlighted by Code2HTML, v. 0.9.1