Project

General

Profile

DB Design » History » Revision 6

Revision 5 (Felix Tiede, 12/24/2011 06:55 AM) → Revision 6/8 (Felix Tiede, 12/26/2011 08:30 AM)

h1. Database structure 

 {{>toc}} 

 *Reflects database design from source:doc/db-schema.txt@a42b9563 source:doc/db-schema.txt@6a552297 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. 

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

 h2(#Authorities). nh2(#Authorities). 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 [[DB_Design#Policies|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| 
 |@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 (keyid)@| 
 |@INDEX (parent)@| 
 |@INDEX (name ASC)@| 

 h2. 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: 
 r 
 
 |@UNIQUE (id)@| 
 |@UNIQUE (name)@| 
 |@UNIQUE (Country, STate, Location, Organization, OrganizationalUnit, CommonName, Email)@| 
 |@INDEX (name ASC)@| 

 h2. 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 |@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)@| 

 h2(#CAExtensions). 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, (Auto-Increment, Unique)|Unique connection ID| 
 |@authority@|Foreign Key (@authorities.id@)|ID of [[DB_Design#Authorities|authority]] to connect to an extension| 
 |@extension@|Foreign Key (@extensions.id@)|ID of [[DB_Design#Extensions|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 n|*Name*|*Source field*| 
 |@authority@|@CAExtensions.authority@| 
 |@id@|@Extensions.id@| 
 |@type@|@Extensions.type@| 
 |@oid@|@Extensions.oid@| 
 |@critical@|@Extensions.critical@| 
 |@oid@|@Extensions.oid@| 
 |@value@|@Extensions.value@| 

 SQL SELECT statement from which the view is populated: 
 <pre><code class="sql"> 
 SELECT CAExtensions.authority AS authority, Extensions.id AS id, type, oid, critical, value 
   FROM Extensions LEFT JOIN CAExtensions  
 ON Extensions.id = CAExtensions.extension 
 </code></pre> 

 h2. 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 [[DB_Design#Authorities|authority]] which issued the certificate| 
 |@subject@|Varchar|Certificate subject| 
 |@issued@|Datetime|Timestamp |@ 
 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 |@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)@| 

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