DB Design » History » Version 8
Felix Tiede, 01/03/2015 07:45 AM
1 | 1 | Felix Tiede | h1. Database structure |
---|---|---|---|
2 | |||
3 | 4 | Felix Tiede | {{>toc}} |
4 | 1 | Felix Tiede | |
5 | 8 | Felix Tiede | *Reflects database design from source:doc/db-schema.txt@167976a of branch MVC (source:/?rev=MVC) now!* |
6 | 1 | Felix Tiede | |
7 | 5 | Felix Tiede | 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. |
8 | 6 | Felix Tiede | |
9 | 7 | Felix Tiede | In any case source:doc/db-schema.txt is authoritative. This wiki page is only for additional documentation purposes. |
10 | 1 | Felix Tiede | |
11 | *Currently only SQLite backend is supported!* |
||
12 | |||
13 | Following are the table structures required by kCA. |
||
14 | |||
15 | h2. Settings |
||
16 | |||
17 | Contains information general information which is not associated with any other information in the database. |
||
18 | 2 | Felix Tiede | |
19 | 1 | Felix Tiede | Table name: @Settings@ |
20 | |*Name*|*Type*|*Comment*| |
||
21 | |@name@|Varchar|Name of stored setting| |
||
22 | |@value@|Varchar|Stored value| |
||
23 | |||
24 | 8 | Felix Tiede | Currently this |
25 | table is used solely for the purpose of preventing damage to the database by mismatch between application and database structure. |
||
26 | 1 | Felix Tiede | |
27 | 6 | Felix Tiede | h2(#Authorities). Certification Authorities |
28 | 2 | Felix Tiede | |
29 | 1 | Felix Tiede | Contains main information about certification authorities, basically the heart of the application. |
30 | |||
31 | Table name: @Authorities@ |
||
32 | |*Name*|*Type*|*Comment*| |
||
33 | 2 | Felix Tiede | |@id@|Int (Auto-Increment, Unique)|Unique CA ID| |
34 | 1 | Felix Tiede | |@hash@|Long Int Unique|Hash of CA subject, used for identification throughout application| |
35 | 7 | Felix Tiede | |@parent@|Foreign Key (@authorities.hash@)|Subject hash of issuing CA, used to build authority tree| |
36 | 1 | Felix Tiede | |@name@|Varchar|Friendly name for display in GUI, also reported via D-Bus property @authorities@| |
37 | 6 | Felix Tiede | |@digest@|Int|Digest used in all signing operations performed by authority| |
38 | 1 | Felix Tiede | |@certificatedays@|Int|Default validity timespan of new certificates in days| |
39 | |@policy@|Foreign Key (@policies.id@)|Id of [[DB_Design#Policies|policy]] to use while signing requests| |
||
40 | 8 | Felix Tiede | |@crlnumber@|Int|Number of last issued CRL, |
41 | incremented each time a CRL is issued| |
||
42 | 4 | Felix Tiede | |@crldays@|Int|Validity timespan of issued CRLs| |
43 | 7 | Felix Tiede | |@keyispath@|Bool|Designates @key@ to be a path instead of raw key data| |
44 | 6 | Felix Tiede | |@certificate@|Blob|DER-encoded raw data of authority certificate, used in every signing operation| |
45 | 1 | Felix Tiede | |@key@|Blob|Filename to authority's private key or PEM-encoded raw data of authority's private key, possibly passphrase protected| |
46 | |||
47 | The following indexes are used: |
||
48 | 2 | Felix Tiede | |@UNIQUE (id)@| |
49 | 7 | Felix Tiede | |@UNIQUE (hash)@| |
50 | 1 | Felix Tiede | |@INDEX (parent)@| |
51 | |@INDEX (name ASC)@| |
||
52 | |||
53 | h2. Policies |
||
54 | |||
55 | 7 | Felix Tiede | Contains all policies to use at request signing to simplify authority configuration. Allows for just selecting another name instead of setting all values. |
56 | 8 | Felix Tiede | 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 |
57 | the subject part in question. |
||
58 | 2 | Felix Tiede | |
59 | Table name: @Policies@ |
||
60 | 1 | Felix Tiede | |*Name*|*Type*|*Comment*| |
61 | 6 | Felix Tiede | |@id@|Int (Auto-Increment, Unique)|Unique policy ID| |
62 | 5 | Felix Tiede | |@name@|Varchar (Unique)|Friendly name for display in authority configuration| |
63 | 2 | Felix Tiede | |@Country@|Enum(match, supplied, optional)|| |
64 | |@STate@|Enum(match, supplied, optional)|| |
||
65 | |@Location@|Enum(match, supplied, optional)|| |
||
66 | 1 | Felix Tiede | |@Organization@|Enum(match, supplied, optional)|| |
67 | |@OrganizationalUnit@|Enum(match, supplied, optional)|| |
||
68 | 2 | Felix Tiede | |@CommonName@|Enum(match, supplied, optional)|| |
69 | 1 | Felix Tiede | |@Email@|Enum(match, supplied, optional)|Relates to subject key '@emailAddress@'| |
70 | |||
71 | The following indexes are used: |
||
72 | |@UNIQUE (id)@| |
||
73 | 2 | Felix Tiede | |@UNIQUE (name)@| |
74 | 1 | Felix Tiede | |@UNIQUE (Country, STate, Location, Organization, OrganizationalUnit, CommonName, Email)@| |
75 | |@INDEX (name ASC)@| |
||
76 | |||
77 | h2. Extensions |
||
78 | |||
79 | 2 | Felix Tiede | Extensions are added to certificates and CRLs on creation by a certification authority. This table contains all available extensions in use. |
80 | |||
81 | Table name: @Extensions@ |
||
82 | 8 | Felix Tiede | |* |
83 | Name*|*Type*|*Comment*| |
||
84 | |@id@|Long Int (Auto-Increment, Unique)|Unique extension ID| |
||
85 | 4 | Felix Tiede | |@type@|Enum(cert, crl)|Whether an extension is to be used for certificates or CRLs| |
86 | 5 | Felix Tiede | |@critical@|Bool| |
87 | 1 | Felix Tiede | |@oid@|Varchar|Numerical object ID of extension| |
88 | |@value@|Varchar|Text value of extension| |
||
89 | 2 | Felix Tiede | |
90 | 1 | Felix Tiede | The following indexes are used: |
91 | |@UNIQUE (id)@| |
||
92 | |@UNIQUE (type, oid, value)@| |
||
93 | |@INDEX (oid)@| |
||
94 | |||
95 | h2(#CAExtensions). Authority Extensions |
||
96 | |||
97 | Connects extensions to authorities. Since one authority uses an arbitrary number of extensions, this table is needed. |
||
98 | |||
99 | 2 | Felix Tiede | Table name: @CAExtensions@ |
100 | 1 | Felix Tiede | |*Name*|*Type*|*Comment*| |
101 | 7 | Felix Tiede | |@id@|Long Int (Auto-Increment, Unique)|Unique connection ID| |
102 | 6 | Felix Tiede | |@authority@|Foreign Key (@authorities.id@)|ID of [[DB_Design#Authorities|authority]] to connect to an extension| |
103 | 1 | Felix Tiede | |@extension@|Foreign Key (@extensions.id@)|ID of [[DB_Design#Extensions|extension]] to connect to an authority| |
104 | |||
105 | The following indexes are used: |
||
106 | |@UNIQUE (id)@| |
||
107 | 8 | Felix Tiede | |@UNIQUE (authority, |
108 | extension)@| |
||
109 | |@INDEX (authority)@| |
||
110 | 1 | Felix Tiede | |@INDEX (extension)@| |
111 | |||
112 | 5 | Felix Tiede | For faster access a view is also added which combines this @CAExtensions@ with @Extensions@ table: |
113 | |||
114 | 6 | Felix Tiede | View name: @AuthorityExtensions@ |
115 | 5 | Felix Tiede | |*Name*|*Source field*| |
116 | |@authority@|@CAExtensions.authority@| |
||
117 | |@id@|@Extensions.id@| |
||
118 | |@type@|@Extensions.type@| |
||
119 | 1 | Felix Tiede | |@critical@|@Extensions.critical@| |
120 | |@oid@|@Extensions.oid@| |
||
121 | |@value@|@Extensions.value@| |
||
122 | |||
123 | 5 | Felix Tiede | SQL SELECT statement from which the view is populated: |
124 | <pre><code class="sql"> |
||
125 | 1 | Felix Tiede | SELECT CAExtensions.authority AS authority, Extensions.id AS id, type, oid, critical, value |
126 | 7 | Felix Tiede | FROM Extensions LEFT JOIN CAExtensions ON Extensions.id = CAExtensions.extension |
127 | 5 | Felix Tiede | </code></pre> |
128 | |||
129 | 4 | Felix Tiede | h2. Certificates |
130 | 1 | Felix Tiede | |
131 | 5 | Felix Tiede | Stores every certificate issued by a managed certification authority and additional information. |
132 | 1 | Felix Tiede | |
133 | Table name: @Certificates@ |
||
134 | 2 | Felix Tiede | |*Name*|*Type*|*Comment*| |
135 | 1 | Felix Tiede | |@id@|Long Int (Auto-Increment, Unique)|Unique certificate ID| |
136 | 8 | Felix Tiede | |@serial@|Unsigned Long Long Int|64 bit |
137 | serial number of certificate| |
||
138 | |@authority@|Foreign Key (@authorities.hash@)|Subject hash of [[DB_Design#Authorities|authority]] which issued the certificate| |
||
139 | 5 | Felix Tiede | |@subject@|Varchar|Certificate subject| |
140 | 6 | Felix Tiede | |@issued@|Datetime|Timestamp when certificate was issued, not necessarily identical to @validfrom@| |
141 | 1 | Felix Tiede | |@validfrom@|Datetime|Timestamp when certificate becomes valid, can not be before @issued@| |
142 | 2 | Felix Tiede | |@expiration@|Datetime|Timestamp when certificate becomes expired, can not be before @validfrom@| |
143 | 3 | Felix Tiede | |@owner@|Varchar|Owner identifier from Akonadi to connect certificate with an Akonadi contact| |
144 | |@status@|Enum(valid, expired, revoked)| |
||
145 | 7 | Felix Tiede | |@revocation@|Datetime|Timestamp of revocation of certificate, can not be before @issued@ or after @expiration@| |
146 | 6 | Felix Tiede | |@reason@|Short Int|Reason of revocation| |
147 | 5 | Felix Tiede | |@certificate@|Blob|DER-encoded raw certificate data for documentary purposes and later re-export if needed| |
148 | 4 | Felix Tiede | |
149 | 2 | Felix Tiede | The following indexes are used: |
150 | |@UNIQUE (id)@| |
||
151 | |@UNIQUE (authority, serial)@| |
||
152 | 8 | Felix Tiede | |@INDEX ( |
153 | authority)@| |
||
154 | |@INDEX (status ASC, id)@| |
||
155 | 3 | Felix Tiede | |@INDEX (status ASC, expiration ASC)@| |
156 | 1 | Felix Tiede | |
157 | 2 | Felix Tiede | h2. Triggers |
158 | 5 | Felix Tiede | |
159 | 6 | Felix Tiede | 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. |