Database structure¶

• Table of contents
• Database structure
  • Settings
  • Certification Authorities
  • Policies
  • Extensions
  • Authority Extensions
  • Certificates
  • Triggers

kCA relies on a SQL
database backend to store information on known certification authorities, issued certificates and some settings. It is the responsibility of the user to make sure database access rights are only granted to those allowed to perform certain operations. kCA authorizes some operations by asking for an authority's key, but if the user has the key, he is able to do any operation, whether he should or not.

In any case [[source:doc/db-schema.txt documentation in the sources]] is authoritative. This wiki page is only for additional documentation purposes.

Currently only SQLite backend is supported!

Following are the table structures required by kCA.

Settings¶

Contains information general information which is not associated with any other information in the database.

Table name: Settings

Name	Type	Comment
name	Varchar	Name of stored setting
value	Varchar	Stored value

Currently this table is used solely for the purpose of preventing
damage to the database by mismatch between application and database structure (see #78).

Certification Authorities¶

Contains main information about certification authorities, basically the heart of the application.

Table name: `Authorities`

'''Name'''

'''Type'''

'''Comment'''

`id`

Int (Auto-Increment, Unique)

Unique CA ID

`hash`

Long Int Unique

Subject hash of CA, used for identification throughout application

`name`

Varchar

Friendly name for display in GUI, also reported via D-Bus property `authorities`

`policy`

Foreign Key (`policies.id`)

Id of [#Policies policy] to use while signing requests

`certificatedays`

Int

Default validity timespan of new certificates in days

`crlnumber`

Int

Number of last issued CRL, incremented each time a CRL is issued

`crldays`

Int

Validity timespan of issued CRLs

`digest`

Int

Digest used in all signing operations performed by authority

n

`certificate`

Blob

PEM-encoded raw data of authority certificate, used in every signing operation

`key`

Blob

Filename to authority's private key or PEM-encoded raw data of authority's private key, possibly passphrase protected

The following indexes are used:

	`UNIQUE (id)`
	`UNIQUE (hash)`
	`INDEX (name ASC)`

Policies¶

Contains all policies to use at request signing to simplify authority configuration. Allows for just selecting another name instead of setting all values.
A policy decides whether a specific part of a request's subject must match the related part of the signing authority's subject to allow signing, must be just supplied (allows for any value except blank) or is entirely optional (allows also blank values). Capitalized letters of field names show the key of the subject part in question.

Table name: `Policies`

	'''Name'''		'''Type'''		'''Comment'''
	`id`		Int (Auto-Increment, Unique)		Unique policy ID
	`name`		Varchar (Unique)		Friendly name for display in authority configuration
	`Country`		Enum(match, supplied, optional)
	`STate`		Enum(match, supplied, optional)
	`Location`		Enum(match, supplied, optional)
	`Organization`		Enum(match, supplied, optional)
	`OrganizationalUnit`		Enum(match, supplied, optional)
	`CommonName`		Enum(match, supplied, optional)
	`Email`		Enum(match, supplied, optional)		Relates to subject key '`emailAddress`'

The following indexes are used:

	`UNIQUE (id)`
	`UNIQUE (name)`
	`UNIQUE (Country, STate, Location, Organization, OrganizationalUnit, CommonName, Email)`
	`INDEX (name ASC)`

Extensions¶

Extensions are added to certificates and CRLs on creation by a certification authority. This table contains all available extensions in use.

Table name: `Extensions`

	'''Name'''		'''Type'''		'''Comment'''
	`id`		Long Int (Auto-Increment, Unique)		Unique extension ID
	`type`		Enum(cert, crl)		Whether an extension is to be used for certificates or CRLs
	`critical`		Bool
	`oid`		Varchar		Numerical object ID of extension
	`value`		Varchar		Text value of extension

The following indexes are used:

	`UNIQUE (id)`
	`UNIQUE (type, oid, value)`
	`INDEX (oid)`

Authority Extensions¶

Connects extensions to authorities. Since one authority uses an arbitrary number of extensions, this table is needed.

Table name: `CAExtensions`

	'''Name'''		'''Type'''		'''Comment'''
	`id`		Long Int (Auto-Increment, Unique)		Unique connection ID
	`authority`		Foreign Key (`authorities.id`)		ID of [#Authorities authority] to connect to an extension
	`extension`		Foreign Key (`extensions.id`)		ID of [#Extensions extension] to connect to an authority

The following indexes are used:index

	`UNIQUE (id)`
	`UNIQUE (authority, extension)`		n		`INDEX (authority)`
	`INDEX (extension)`

Certificates¶

Stores every certificate issued by a managed certification authority and additional information.

Table name: `Certificates`

'''Name'''

'''Type'''

'''Comment'''

`id`

Long Int (Auto-Increment, Unique)

Unique certificate ID

`serial`

Unsigned Long Long Int

64 bit serial number of certificate

`fingerprint`

Char(64) (Unique)

Unique fingerprint of certificate lower case without octet separation

`authority`

Foreign Key (`authorities.hash`)

Subject hash value of [#Authorities authority] which issued the certificate

`subject`

Varchar

Certificate subject

`issued`

Datetime

Timestamp when certificate was issued, not necessarily identical to `validfrom`

`validfrom`

Datetime

Timestamp when certificate becomes valid, can not be before `issued`

`expiration`

Datetime

Timestamp when certificate becomes expired, can not be before `validfrom`

r

`owner`

Varchar

Owner identifier from !Akonadi to connect certificate with an !Akonadi contact

`state`

Enum(valid, expired, revoked)

`revocation`

Datetime

Timestamp of revocation of certificate, can not be before `issued` and after `expiration`

`reason`

Short Int

Reason of revocation

`certificate`

Blob

PEM-encoded raw certificate data for documentary purposes and later re-export if needed

The following indexes are used:

	`UNIQUE (id)`
	`UNIQUE (fingerprint)`
	`UNIQUE (authority, serial)`
	`INDEX (authority)`
	`INDEX (status ASC, expiration ASC)`
	`INDEX (status ASC, id)`

Triggers¶

The database should - if somehow possible - auto-expire certificates. This should set a certificate's state to expired, if it is not revoked but the timestamp in expiration is in the past. Only exception should be certificates revoked by CertificateOnHold, which should also be auto-expired. At best it should do so on any
database operation.