The Confusa Database
History Key
- New content
Removed content
Recent Versions
Choose two versions to compare, or click the link to view it.
Confusa rely heavily upon the database. From accepting new signing-requests to managing NRENs, Subscribers and admins. The total schema is seen below.
Looking at the database, we can split the roles in two rough parts, the certificate business and the administrative part.
Certificate part
Whenever a user wants a new certificate, the signing request must be uploaded to the portal. This is regardless of how it is created (in browser or manually uploaded), or which mode the portal operates in (standalone vs. online/comodo).
Signing requests
Common for all Cert_Manager modules, is the csr_cache. This is where the uploaded CSR is stored before the user approves it for signing. In most cases (browser-signing), this is done transparently for the user.
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| csr_id | int(11) | NO | PRI | NULL | auto_increment |
| csr | text | NO | | NULL | |
| uploaded_date | datetime | NO | | NULL | |
| from_ip | varchar(64) | NO | | NULL | |
| common_name | varchar(128) | NO | | NULL | |
| auth_key | char(40) | NO | UNI | NULL | |
+---------------+--------------+------+-----+---------+----------------+
Cert_Manager_Comodo
The Comodo module uses only the database for a very small number of operations.
This is largely due to Comodo’s interface. Comodo keeps track of all certificates, and to avoid synchronization problems should Confusa ever run in multiple instances to leverage the load, only basic caching (through session-objects) is used. The API-credentials is stored in the account_map
order store
The order-store is used to keep track of the order-number (a unique number assigned to each certificate request by Comodo). We use this in the database to keep track of the relationship between order-number and the legacy auth_key. In the upcoming refactoring, this will be removed and we expect this table to be removed completely.
+--------------+---------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------------------------------+------+-----+---------+-------+
| order_number | int(11) | NO | PRI | NULL | |
| auth_key | char(64) | NO | | NULL | |
| owner | varchar(128) | NO | | NULL | |
| order_date | datetime | NO | | NULL | |
| authorized | enum('authorized','unauthorized','unknown') | YES | | unknown | |
| expires | datetime | NO | | NULL | |
+--------------+---------------------------------------------+------+-----+---------+-------+
Cert_Manager_Standalone
The standalone module use the database more extensively. In this mode, Confusa will include basic CA operations, and must keep track of issued certificates and CRLs.
cert_cache
Confusa, when acting as an CA, must keep all certificates for as long as they are valid. They will be stored in this table. The name is legacy, and after the refactoring, it will be renamed to cert_store to reflect the role better.
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| cert_id | int(11) | NO | PRI | NULL | auto_increment |
| cert | text | NO | | NULL | |
| fingerprint | char(40) | NO | | NULL | |
| auth_key | char(64) | NO | UNI | NULL | |
| cert_owner | varchar(64) | NO | | NULL | |
| organization | varchar(64) | NO | | NULL | |
| valid_untill | datetime | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+user_crls
Used to keep track of the subject for issued certificates so a user can revoke them at a later stage. Only applicable in standalone-mode, and since standalone is requried to keep all certificates for as long as they are valid, this table is not really needed and will be removed in the future.
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| crl_id | int(11) | NO | PRI | NULL | auto_increment |
| owner | varchar(128) | YES | | NULL | |
| cert_sn | int(11) | NO | | NULL | |
| valid_untill | datetime | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+Administrative part
This is the busy part of the database. Not in the number of entries, but in the complexity. Here, all the NRENs, Subscribers, administrators and account-information is stored.
NREN
This table contains all information about the NREN we will show to the users and administrators in the portal. It contains element such as general contact information, which login-credentials to use (if applicable), CERT-information and help/about/privacy-notice.
+----------------+--------------------------+------+-----+---------+----------------++-------------------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------------------+------+-----+---------+----------------++-------------------+-------------------------------------+------+-----+---------+----------------+
| nren_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| login_account | int(11) | YES | MUL | NULL | |
| help | text | YES | | NULL | |
| about | text | YES | | NULL | |
| lang | varchar(5) | YES | | NULL | |
| contact_email | varchar(64) | YES | | | |
| cert_email | varchar(64) | YES | | NULL | |
| cert_phone | varchar(16) | YES | | NULL | |
| url | varchar(128) | YES | | NULL | |
| contact_phone | varchar(24) | NO | | NULL | |
| country | char(2) | NO | | NULL | |
| privacy_notice | text | YES | | NULL | |
| enable_email | enum('0','1','n','m') | YES | | NULL | |
| cert_validity | enum('365','730','1095') | YES | | NULL | |
+----------------+--------------------------+------+-----+---------+----------------+Subscriber
As with the NREN-table, this table contains a lot of subscriber-specific information.
+-------------------+-----------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------------------------------+------+-----+---------+----------------+
| subscriber_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | UNI | NULL | |
| nren_id login_account | int(11) | YES | MUL | NULL | |
| org_state help | text | YES | | NULL | |
| about | text | YES | | NULL | |
| lang | varchar(5) | YES | | NULL | |
| contact_email | varchar(64) | NO | | NULL | |
| contact_phone | varchar(24) | NO | | NULL | |
| cert_email | varchar(64) | YES | | NULL | |
| cert_phone | varchar(16) | YES | | NULL | |
| url | varchar(128) | YES | | NULL | |
| enum('subscribed','suspended','unsubscribed')country | char(2) | NO | | NULL | |
| lang enable_email | varchar(5) enum('0','1','n','m') | YES | | NULL | |
| dn_name cert_validity | varchar(64) | NO | UNIenum('14','365','395','730','1095') | YES | | NULL | |
| privacy_notice | text | YES | | NULL | |
| subscr_emailshow_portal_title | tinyint(1) | YES | | 1 | |
| portal_title | varchar(64) varchar(20) | NO YES | | NULL | |
| subscr_phone wayf_url | varchar(24) | YES | | | |
| subscr_resp_name | varchar(24) | NO | | NULL | |
| subscr_resp_email | varchar(24) | NO | | NULL | |
| subscr_comment | text varchar(128) | YES | | NULL | |
| subscr_help_url reauth_timeout | varchar(128) int(11) | YES | | 10 | |
| subscr_help_email | varchar(64) | YES | | | |
+-------------------+-----------------------------------------------+------+-----+---------+----------------++-------------------+-------------------------------------+------+-----+---------+----------------+
The account map
This is where the login credentials for the Comodo API resides. The data is encrypted using the MCrypt module, Rijndael 256 bits CFB-mode. The key is stored in confusa_config::capi_enc_pw.
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| account_map_id | int(11) | NO | PRI | NULL | auto_increment |
| login_name | varchar(128) | NO | UNI | NULL | |
| password | tinyblob | NO | | NULL | |
| ivector | tinyblob | NO | | NULL | |
| ap_name | varchar(30) | NO | | NULL | |
| nren_id | int(11) | NO | MUL | NULL | |
+----------------+--------------+------+-----+---------+----------------+
Attribute map
The attribute map is used to couple the attributes exported by the IdP to the values Confusa expects. The map contains the expected names of the attributes. So if NREN-A exports ‘urn:mace:dir:attribute-def:displayName’ as the full name, the map will store this string under the key ‘cn’, so when we look for the name, we do the following:
$name = $attributes[$map['cn']];See Operational_Requirements for details surrounding the attributes.
Each NREN must define a map, and a subscriber may define a map. If an NREN has no map, no user can use the portal as we have no way of retrieving attribute information. If a Subscriber has defined a map, this map will overrule the NREN-map, thus a Subscriber can choose other attributes for its users than the ‘official’ NREN attribute-set.
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nren_id | int(11) | NO | MUL | NULL | |
| subscriber_id | int(11) | YES | MUL | NULL | |
| eppn | varchar(64) | NO | | NULL | |
| epodn | varchar(64) | NO | | NULL | |
| cn | varchar(64) | NO | | NULL | |
| mail | varchar(64) | NO | | NULL | |
| entitlement | varchar(64) | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+Robot certs
To enble the RI, a subscriber must upload a certificate. This is then associated with the administrator that uploaded it, thus all RI-actions taken with a particular certificate is done on behalf of that administrator. This also means that when an administrator is removed, associated certificates are also removed.
+-------------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| subscriber_id | int(11) | NO | MUL | NULL | |
| uploaded_by | int(11) | NO | MUL | NULL | |
| uploaded_date | datetime | NO | | NULL | |
| valid_until | datetime | NO | | NULL | |
| last_warning_sent | datetime | YES | | NULL | |
| cert | text | NO | | NULL | |
| comment | text | YES | | NULL | |
| fingerprint | char(60) | NO | | NULL | |
| serial | char(60) | NO | | NULL | |
+-------------------+----------+------+-----+---------+----------------+