Database¶
Remote Databases¶
XCA supports connections to network databases. Tested engines are:
MariaDB / mySQL
PostgreSQL
Microsoft SQLserver
Table prefix¶
The table prefix can be used to store more than one XCA database in the same remote database.
Database Drivers¶
The SQL backend drivers provided by the manufacturer of the database must be installed additionally to the Database support in XCA.
Linux¶
The backend drivers are provided by your distribution:
Debian: libqt6sql6-psql, libqt6sql6-mysql or libqt6sql6-odbc.
RPM: libqt6-database-plugin-pgsql, libqt6-database-plugin-mysql, libqt6-database-plugin-odbc
They should pull in all necessary dependencies.
Apple macos¶
PostgreSQL: Install the https://postgresapp.com/
ODBC: It requires the /usr/local/opt/libiodbc/lib/libiodbc.2.dylib. When installing unixodbc via brew the library must be symlinked from /opt/homebrew/Cellar/libiodbc/3.52.16/lib/libiodbc.2.dylib
MariaDB: Since XCA-2.8.0 the MariaDB plugin is included in the xca.app bundle. No additional installation is required.
Windows¶
PostgreSQL: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads (Commandline tools are sufficient). Add the bin directory of the Postgres installation directory to your PATH (C:\Program Files\PostgreSQL\16)
ODBC: Use the ODBC Datasources 64bit app to configure the SQL Server. If the data source is configured completel, only the matching DSN is required in the XCA connection settings.
MariaDB (MySQL): Install the Plugin from here: https://github.com/thecodemonkey86/qt_mysql_driver. Select the MinGW variant and install it as documented.
Driver specific configurations¶
Additional options for the database connection can be set in configuration files. They must be put in the XCA configuration directory, which is displayed in the about dialog. If there is already a file called dbhistory then you know you are in the right place. The options-file must be named after the database driver, e.g. QPSQL, QMYSQL or QODBC optionally followed by a dash and the database-hostname (exactly as used in the connection settings including an optional port number) and a .options extension.
Examples:
QMYSQL-192.168.12.13.options
QPSQL.options
Also the environment variable XCA_<driver-name>_OPTIONS may be used to set the options.
The file must contain the options as ; separated key=value pairs. The recognized options depend on the database driver. See: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS for PostgreSQL and https://doc.qt.io/qt-6/sql-driver.html#connection-options for MySQL and https://doc.qt.io/qt-6/sql-driver.html#odbc-unicode-support for ODBC.
Extracting items¶
The sqlitebrowser may be used to examine the elements of the database.
The database schema is documented Schema All cryptographic items are stored as base64 DER format and must be decoded before feeding them to OpenSSL:
| base64 -d | openssl <x509|req|crl|pkcs8> -inform DER ...
Extract the private key with internal name ‘pk8key’ from the database:
sqlite3 ~/xca.xdb "SELECT private FROM view_private WHERE name='pk8key'" | base64 -d | openssl pkcs8 -inform DER
Extract the certificate with internal name ‘rootca’ from the database:
sqlite3 ~/xca.xdb "SELECT cert FROM view_certs WHERE name='rootca'" | base64 -d | openssl x509 -inform DER
Extract the public part of a key by database primary key:
sqlite3 ~/xca.xdb "SELECT public from view_public_keys WHERE id=3" | base64 -d | openssl pkey -inform DER -pubin
Extract a CRL:
sqlite3 ~/xca.xdb "SELECT crl FROM view_crls WHERE name='ca'" | base64 -d | openssl crl -inform DER
Modify the comment of an item with id 3:
sqlite3 ~/xca.xdb "UPDATE items SET comment='My notes' WHERE id=3"
The item names are not required to be unique anymore. Each table view in XCA has an optional column “Primary key” that may be shown to get the ID of the item in the database.
Schema¶
/* 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 'TEXT' which is huge, except
* on mysql where LONGTEXT is used.
*/
#define B64_BLOB _B64_BLOB_
/*
* 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 B64_BLOB )
INSERT INTO settings (key_, value) VALUES ('schema', ' INITIAL_SCHEMA_VERSION ')
/*
* 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),
stamp INTEGER NOT NULL DEFAULT 0, -- indicate concurrent access
del SMALLINT NOT NULL DEFAULT 0)
/*
* 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, -- 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 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))
/* 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
CREATE VIEW view_private AS SELECT
name, private FROM private_keys JOIN items ON
items.id = private_keys.item
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)
CREATE INDEX i_items_stamp ON items (stamp)
;
/* Schema Version 2: Views added to quickly load the data */
/* Schema Version 3: Add indexes over hashes and primary, foreign keys */
/* 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.
*/
/* Schema Version 5: 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'
;
schemas[6]
ALTER TABLE items ADD del SMALLINT NOT NULL DEFAULT 0
CREATE INDEX i_items_del ON items (del)
UPDATE settings SET value='7' WHERE key_='schema'
;
schemas[7]
-- OpenVPN TA (tls-auth) keys associated to the CA to be
-- the same for all issued certificates
CREATE TABLE takeys (
item INTEGER UNIQUE, -- reference to items(id) of the CA
value B64_BLOB , -- The base64 encoded 2048 bit key
FOREIGN KEY (item) REFERENCES items (id))
UPDATE settings SET value='8' WHERE key_='schema'
;
/* When adding new tables or views, also add them to the list
* in XSqlQuery::rewriteQuery(QString) in lib/sql.cpp
*/
