Project

General

Profile

DB Design » History » Version 1

Felix Tiede, 11/25/2011 03:13 PM
Moved in from trac wiki - Formatting needs to be adapted to redmine/textile

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