The database¶
Model¶
- The entity relation diagram shows 10 entities, related to certificates and their deployment. The normalized schema has rules and triggers to ensure integrity.
- Common columns - All relations have the following columns:
- id - synthetic primary key
- created - date and time of tuple creation
- updated - date and time of last tuple update
- remarks - arbitrary text
- columns, which together must be unique are in bold
This is the entity relation diagram:
Tables¶
- Subjects - holds all the subject names
- name - name of subject
- type - subject type, one of
- ‘server’ - server subject
- ‘client’ - client (or personal) subject
- ‘CA’ - certificate authority
- ‘reserved’ - type of a placeholder, initially loaded
- isAltName - true if subject is an alternate name
- certificate - reference to Certificates
- Certificates - one entry per defined certificate (holds cert meta data)
- type - type of certificate, one of
- LE - to be issued by Let’s Encrypt CA
- local - local cert (to be issued by local CA)
- disabled - true means: Do not issue/create or distribute this cert.
- authorized_until:
- if type is ‘LE’: Needing new authorization with Let’s Encrypt via DNS challenge after this date
- if type is ‘local’: date and time of last mail to admin, to ask him to issue a new local cert
- encryption_algo - encryption algorithm to be used by certs issued in the future, one of
- rsa
- ec
- rsa plus ec
- ocsp_must_staple - if true then the OCSP staple protocoll will be required by the cert (and server must be configured to support this)
- type - type of certificate, one of
- Certinstances - issued certificate instances.
- state - state of instance (see State Table), one of
- reserved - being issued
- issued - cert is issued (or renewed)
- prepublished - cert published in DNS (vis TLSA RR) prior to usage
- deployed - cert is distributed and in use by server
- revoked - cert is revoked
- expired - cert is expired
- archived - cert is archived (will be removed soon)
- not_before - start date and time for cert usage
- not_after - end date and time for cert usage
- certificate - reference to cert in Certificates
- cacert - reference to cacert instance in Certinstances, describing CA which issued this cert
- ocsp_must_staple - True, if this instance requires OCSP must staple
- state - state of instance (see State Table), one of
There may be more than one tuple per cert type, if cacerts are renewed.
Here is the state transition diagram:
- CertKeyData - the cert/key material (one tuple per algorithm).
- encryption_algo - encryption algorithm, used with this cert (unique together with certinstance)
- rsa
- ec
- cert - the certificate in binary PEM format
- key - the key in binary PEM format (encrypted, if DB encryption in use)
- hash - the binascii presentation of the SHA256 hash of the certificate
- certinstance - reference to cert in Certinstances (unique together with encryption_algo)
- encryption_algo - encryption algorithm, used with this cert (unique together with certinstance)
Services - stores service and port combinations for TLSA RR
name - name of service
port - tcp/udp port number of service
TLSAprefix - named zone resource record entry with place holder for hash, something like:
_443._tcp.{}. 3600 IN TLSA 3 0 1
Certificates_Services - junction relation between Certificates and Services
- certificate - reference to cert in Cerificates
- service - reference to service in Services
- Jails - One row describes one jail. A jail is a hosted entity on FreeBSD’s
lightweight virtualization environment. serverPKI connects to the jail host
(Disthost) and places certs and keys on the jail, using the filesystem view
of the host.
- name - name of jail
- disthost - reference to the disthost, hosting the jail in Disthosts
- Disthosts - One row per host to which cert and key should be distributed.
- FQDN - fully qualified domain name of disthost
- jailroot - optional path to root of jails on disthost. If empty, no jails are on this disthost.
- Places - Place, where to deploy cert deployment details, related to one
cert / disthost (or jail) combination.
- name - name of place
- cert_file_type - one of
- ‘cert only’ - deploy only cert, no key
- ‘separate’ - cert and key are in separate file
- ‘combine key’ - cert and key are combined in one file
- ‘combine cacert’ - cert is combined with cacert (intermediate if LE), key is in separate file
- ‘combine both’ - cert is combined with both key and cacert
- cert_path - absolute path of cert directory with placeholder ‘{}’ of login
- key_path - absolute path of key, if different from cert_path
- uid - let key file be owened by uid
- gid - let key file be owned by gid
- mode - mode of key file if different from 0o400
- chownboth - set owner of cert file to that of key file
- pglink - link cert / key file to postgresql.crt / postgresql.key
- reload_command - command to reload service after distribution of cert/key. In case of jail, ‘{}’ is the placeholder for the jail name.
- Targets - binds one place, disthost/jail to a certificate
- distHost - references distHost
- jail - references jail
- place - references place
- certificate - references certificate
- Revision - holds revision of schema and key encryption state of DB
- schemaVersion - Version of database schema
- keysEncrypted - True, if keys are encrypted
Views¶
Some views simplify common queries. For each view the result columns are listed.
- certs - display meta information about a certificate
- Subject - Subject type
- Cert Name - Subject name
- Type - Type of certificate
- algo - Cert encryption algorithm
- ocsp_ms - Cert ocsp_must_staple attribute
- authorized - authorized until
- Alt Name - Alternative cert name
- TLSA - Service name
- Port - Service port number
- Dist Host - Disthost name
- Jail - Jail name
- Place - Place name
- certs_ids - like certs, but include primary keys of referenced tables
- c_id - cert id
- s1_id - subject id of none-altname subject
- Subject Type - Subject type
- Cert Name - Subject name
- Type - Cert type
- algo - Cert encryption algorithm
- ocsp_ms - Cert ocsp_must_staple attribute
- authorized - authorized until
- s2_id - subject id of Alternative cert name subject
- Alt Name - Alternative cert name
- s_id - service id
- TLSA - Service name
- Port - Service port number
- t_id - target id
- d_id - disthost id
- FQDN - Disthost name
- j_id - jail id
- Jail - Jail name
- p_id - place id
- Place - Place name
- inst - display certificate instances (one row per issued cert instance per algorithm)
- id - serial of cert instance
- name - Subject name
- type - Cert type
- state - State of instance
- cacert - reference to cacert instance in Certinstances, describing CA which issued this cert
- ocsp_must_staple - if true then the OCSP staple protocol will be required by the cert
- not_before - Start date for cert usage
- not_after - End date for cert usage
- encryption_algo - Cert encryption algorithm
- hash - Hash of cert
Functions¶
Functions are provided for common operations to abstract foreign key handling. All arguments are text (mostly case insensitive [=citext]), exceptions are mentioned (e.g. boolean), to omit an argument, use null. Functions may be called with select in psql:
serverpki=> select * from add_cert('test.com', 'server', 'local', 'ec', false, 'www.test.com', NULL, NULL, NULL, NULL, NULL);
add_cert
--------------------------------------------
(server,test.com,local,,www.test.com,,,,,)
(1 row)
serverpki=> \q
- add_cert - add a new cert to the database
- the_name - Subject name
- the_subject_type - Subject type
- the_cert_type - Cert type
- the_encryption_algo - Cert encryption algorithm
- must_staple - if true then the OCSP staple protocoll will be required by the cert
- the_altname - optional Alternative cert name
- the_tlsa_name - optional Service name
- the_tlsa_port - optional Service port number
- the_disthost_name - optional :ref: Name of disthost
- the_jail - optional Jail name
- the_place - optional Place name
- remove_cert - delete a cert and all issued cert instances with there CertKeyData from the database
- the_cert_name - Subject name
- add_altname - add an alternative name to an existing cert in the database
- the_cert_name - Subject name to identify the cert, to which the altname should be added
- the_altname - Alternative cert name to add
- remove_altname - remove an alternative name from the database
- the_altname - Alternative cert name to be removed
- add_service - add an existing service to a certificate
- the_cert_name - Subject name to identify the cert, to which the service should be added
- the_service_name - Service name
- the_port - Service port number
- remove_service - remove a service from a certificate
- the_cert_name - Subject name to identify the cert, from which the service should be removed
- the_service_name - Service name
- the_port - Service port number
- add_target - add a target to a certificate
- the_name - Subject name to identify the cert, to which the target should be added
- the_disthost_name - Disthost name to identify the target
- the_jail - optional Jail name to identify the target
- the_place - optional Place name to identify the target
- remove_target - remove a target from a certificate
- the_cert_name - Subject name to identify the cert, from which the target should be removed
- the_disthost_name - Disthost name to identify the target
- the_jail - optional Jail name to identify the target
- the_place - optional Place name to identify the target