Database structure¶

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

Reflects database design from source:doc/db-schema.txt@167976a of branch
MVC (source:/?rev=MVC) now!

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 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.

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	Hash of CA subject, used for identification throughout application
parent	Foreign Key (authorities.hash)	Subject hash of issuing CA, used to build authority tree
name	Varchar	Friendly name for display in GUI, also reported via D-Bus property authorities
digest	Int	Digest used in all signing operations performed by authority
certificatedays	Int	Default validity timespan of new certificates in days
policy	Foreign Key (policies.id)	Id of policy to use while signing requests
crlnumber	Int	Number of last issued CRL, incremented each time a CRL is issued
crldays	Int	Validity timespan of issued CRLs
keyispath	Bool	Designates key to be a path instead of raw key data
certificate	Blob	DER-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 (parent)

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.
r

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 authority to connect to an extension
extension	Foreign Key (extensions.id)	ID of extension to connect to an authority

The following indexes are used:

UNIQUE (id)

UNIQUE (authority, extension)

INDEX (authority)

INDEX (extension)

For faster access a view is also added which combines this CAExtensions with Extensions table:

View name: AuthorityExtensions

Name	Source field
authority	CAExtensions.authority
id	Extensions.id
type	Extensions.type
critical	Extensions.critical
oid	Extensions.oid
value	Extensions.value

SQL SELECT statement from which the view is populated:

SELECT CAExtensions.authority AS authority, Extensions.id AS id, type, oid, critical, value
  FROM Extensions LEFT JOIN CAExtensions ON Extensions.id = CAExtensions.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
authority	Foreign Key (authorities.hash)	Subject hash of 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
owner	Varchar	Owner identifier from Akonadi to connect certificate with an Akonadi contact
status	Enum(valid, expired, revoked)
revocation	Datetime	Timestamp of revocation of certificate, can not be before issued or after expiration
reason	Short Int	Reason of revocation
certificate	Blob	DER-encoded raw certificate data for documentary purposes and later re-export if needed

The following indexes are used:

UNIQUE (id)

UNIQUE (authority, serial)

INDEX (authority)

INDEX (status ASC, id)

INDEX (status ASC, expiration ASC)

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.