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:

_images/ERD.png

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)
  • 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

There may be more than one tuple per cert type, if cacerts are renewed.

Here is the state transition diagram:

_images/States.png
  • 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)

  • 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.

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
  • remove_cert - delete a cert and all issued cert instances with there CertKeyData from the database
  • add_altname - add an alternative name to an existing cert in the database
  • remove_altname - remove an alternative name from the database