Re: [OpenCA-Devel] Database schema

[Michael Bell]

Martin Bartosch wrote:

- introduce a new table, e. g. CA
 Purpose:
 - identification and reference of CA certificate for internal CAs
 - mapping between internal and external CAs
 Attributes:
 INTERNAL_CA: internal CA
 EXTERNAL_CA: external CA this CA belongs to
 CA_CERT_SERIAL: serial number of the CA certificate (foreign key to
   the CERTIFICATE table)

No comments on this? I think this is the most important change, the
other stuff is more or less cleanup and avoidance of redundancy.

There was no comment because I have no comment on this - except its good.

Change the attributes for the other tables:
- table REQUEST: should include only the EXTERNAL_CA attribute,
 remove INTERNAL_CA attribute
 Reason: a request is issued from the outside. By definition from
 the outside only the external CA is visible, OpenCA dispatches
 the request to the matching internal CA itself based on the
 CA cert validity.
- table CRR: see REQUEST

This is no problem because INTERNAL_CA is completely unused.

Yes, you can keep it NULL. But then why not remove the attribute...?

This was a comment to the software not the design - sorry. We have the fields but they are not used today. So we can remove them without any trouble.

- table DATA: not sure, but I think same as CERTIFICATE
- table PRIVATE: see DATA
- table SIGNATURE: see DATA
- table VOTING: see DATA
- table DATAEXCHANGE: see DATA

A more radical idea, does it be necessary that other data than
certificates and requests has external_ca or internal_ca flags? Perhaps
the CRLs should be marked but all other data does not need such flags.

Yes, you are probably right. What I don't understand: how do you associate
e. g. a key or some data with a certificate? Don't we have to have
a foreign key, such as PRIVATE_SERIAL or DATA_SERIAL in the CERTIFICATE
table as well?

grep for CSR_REF, KEY_REF and CERT_REF. I used the data table as a global reference store. A global_id is a unique ID for a complete workflow including renewal and all related data.

Another thing is that (in my opinion) we should be careful not to
mix internal IDs with externally visible serial numbers.
The CRL serial number, for example, can of course be used as an index
to the table, but only in conjunction with the INTERNAL_CA, otherwise
it would not be unique.
In my understanding, SERIALs would only be unique within the same
INTERNAL_CA.

Michael
--
_______________________________________________________________

Michael Bell                    Humboldt-Universitaet zu Berlin

Tel.: +49 (0)30-2093 2482       ZE Computer- und Medienservice
Fax:  +49 (0)30-2093 2704       Unter den Linden 6
[EMAIL PROTECTED]   D-10099 Berlin
_______________________________________________________________

This was a long time fear of me :( If we need indexes with more than one table then we have to rewrite parts of DBI but I expected this much earlier. After OpenCA::DBM is gone it is time to review this module completely. A lot of the stuff was done because DBM must be handled too.

smime.p7s
Description: S/MIME Cryptographic Signature