Database tables

This page describes the tables used by the KMS server to persist its data, and the links between them. It applies to the SQL backends: SQLite, PostgreSQL, MySQL, MariaDB, and Percona XtraDB Cluster.

The Redis-with-Findex backend does not use relational tables; see Redis with Findex.

Overview

The KMS object-store schema is small and consists of six tables. The optional PostgreSQL audit backend adds two more, kms_audit_events and kms_audit_control, in its own, separate database.

The links between tables are logical relationships (enforced by the application, not by SQL foreign-key constraints).

erDiagram
    OBJECTS ||--o{ READ_ACCESS : "grants (read_access.id = objects.id)"
    OBJECTS ||--o{ TAGS : "tagged (tags.id = objects.id)"
    OBJECTS ||--o{ OBJECTS : "wraps (objects.wrapping_key_id = objects.id)"
    OBJECTS ||--o| CRLS : "signs (crls.issuer_id = objects.id)"
    PARAMETERS {
        string name PK
        string value
    }
    OBJECTS {
        string id PK
        string object
        string attributes
        string state
        string owner
        string wrapping_key_id FK
    }
    READ_ACCESS {
        string id FK
        string userid
        string permissions
    }
    TAGS {
        string id FK
        string tag
    }
    CRLS {
        string issuer_id PK
        bytes  crl_der
        int    crl_number
        string generated_at
        string next_update
    }
    CRYPTO_OFFICER_ACTIVATIONS {
        timestamp activated_at
        text sealed_record
        timestamp revoked_at
        varchar revoked_by
    }

objects

The central table. One row per KMIP object.

The following secondary indexes are created on objects:

Indexes are created at server startup. On an existing large database, the first start after an upgrade takes proportionally longer, and PostgreSQL blocks writes to objects while each new index builds.

read_access

Stores the operations that a given user is allowed to perform on a given object.

The pair (id, userid) is unique. In PostgreSQL and SQLite it is declared UNIQUE (id, userid); in MySQL (since 5.13.0) it is the composite PRIMARY KEY (id, userid).

A secondary index idx_read_access_userid is created on userid.

tags

Stores the tags attached to objects. Tags are used to locate objects by tag.

The pair (id, tag) is unique. In PostgreSQL and SQLite it is declared UNIQUE (id, tag); in MySQL (since 5.13.0) it is the composite PRIMARY KEY (id, tag).

A secondary index idx_tags_tag_id on (tag, id) supports Locate-by-tags lookups.

parameters

A generic key/value store used internally by the KMS for database metadata.

Known parameters:

crypto_officer_activations

Records the Crypto Officer activation ceremony. One row is added each time the Crypto Officer role is activated via a split-key ceremony.

In MySQL, an additional id INTEGER PRIMARY KEY AUTO_INCREMENT column is added. In PostgreSQL and SQLite there is no explicit id column; the active activation is the latest row where revoked_at IS NULL.

crls

Stores the most recently generated CRL for each issuer CA, persisted so that the public CDP endpoint (GET /public/certificates/{issuer_id}/crl) can serve the last signed CRL immediately after a server restart without requiring a manual generate-crl call.

One row per CA certificate. The row is replaced atomically on every CRL regeneration (upsert on issuer_id).

The Redis-with-Findex backend stores each CRL as a JSON value under the key crl:<issuer_id>.

Links between tables

• objects.id is referenced by read_access.id and tags.id: one object can have many access rows and many tags.
• objects.wrapping_key_id points to objects.id: a wrapping key is itself an object, and many objects can be wrapped by the same key.
• crls.issuer_id logically references objects.id (the CA certificate): one CA has at most one current CRL row.
• objects.owner and read_access.userid hold user identifiers. Users are authenticated identities and are not stored in a dedicated table.
• parameters and crypto_officer_activations are standalone and do not reference objects.

These relationships are managed by the application layer (crate/server_database/) rather than database foreign-key constraints.

PostgreSQL audit database

Used only by the PostgreSQL audit backend (--audit-postgres-url), in a database distinct from the object store. Its schema is defined in audit.sql.

kms_audit_events

Append-only, hash-chained audit events. One row per event.

The primary key is (instance_id, chain_generation, id). A secondary index idx_kms_audit_events_timestamp on timestamp serves auditors' time-range queries; the KMS does not use it.

These triggers keep the table append-only:

UPDATE, DELETE and TRUNCATE are also revoked from PUBLIC.

kms_audit_control

The generation currently accepting writes, per instance.

kms_audit_events.instance_id logically references kms_audit_control.instance_id; as for the object-store tables, there is no foreign-key constraint.