Creating the database schema

If you plan to use a database to store the keys, you must create a PostgreSQL 16 database schema and set the "persistence_type" parameter to "database" in the config.json file. For more information, see database parameter.

1. To create the schema, connect to your database server, then run the following database creation scripts:

Copy

SET search_path TO <_SCHEMA_>;

-- TENANT
CREATE TABLE IF NOT EXISTS tenant (
  tenant_id UUID PRIMARY KEY,
  display_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- MODULE
CREATE TABLE IF NOT EXISTS module_ref (
  module_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL
);

INSERT INTO module_ref (module_id, display_name)
    VALUES
        (1, 'kacls'),
        (2, 'kas'),
        (3, 'crypto_api'),
        (4, 'pki'),
        (5, 'dke');

-- KEY_ALGORITHM
CREATE TABLE IF NOT EXISTS key_algorithm_ref (
    key_algorithm_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL
);
INSERT INTO key_algorithm_ref (key_algorithm_id, display_name)
    VALUES
        (1, 'AES-GCM'),
        (2, 'RSA-OAEP');

-- KEY_STATUS
CREATE TABLE IF NOT EXISTS key_status_ref (
    key_status_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL
);
INSERT INTO key_status_ref (key_status_id, display_name)
    VALUES
        (1, 'pre-activation'),
        (2, 'active'),
        (3, 'deactivated'),
        (4, 'compromised'),
        (5, 'suspended'),
        (6, 'destroyed');

-- KEY_LOCATION_TYPE
CREATE TABLE IF NOT EXISTS key_location_ref (
  key_location_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL
);
INSERT INTO key_location_ref (key_location_id, display_name)
    VALUES
        (1, 'env'),
        (2, 'database');

-- MODULE_TENANT
CREATE TABLE IF NOT EXISTS MODULE (
    MODULE_ID INTEGER REFERENCES MODULE_REF (MODULE_ID),
    TENANT_ID UUID REFERENCES TENANT (TENANT_ID),
    DEFAULT_ENCRYPTION_KEY_ID UUID,
    PRIMARY KEY (MODULE_ID, TENANT_ID)
);

-- KEY
CREATE TABLE IF NOT EXISTS KEY (
    KEY_ID UUID PRIMARY KEY,
    TENANT_ID UUID NOT NULL,
    MODULE_ID INTEGER NOT NULL,
    KEY_ALGORITHM_ID INTEGER NOT NULL REFERENCES KEY_ALGORITHM_REF (KEY_ALGORITHM_ID),
    KEY_ALGORITHM_PARAMS JSONB NOT NULL,
    DISPLAY_NAME TEXT NOT NULL,
    KEY_STATUS_ID INTEGER NOT NULL,
    KEY_USAGE INTEGER NOT NULL,
    CREATED_AT TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UPDATED_AT TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ADD FK CONSTRAINTS
ALTER TABLE module
    ADD CONSTRAINT module_key_fk FOREIGN KEY (default_encryption_key_id) REFERENCES key(KEY_ID);

ALTER TABLE key
    ADD CONSTRAINT key_module_fk FOREIGN KEY (tenant_id, module_id) REFERENCES module(tenant_id, module_id);

-- MKEK
CREATE TABLE IF NOT EXISTS mkek (
    MKEK_ID UUID PRIMARY KEY,
    TENANT_ID UUID NOT NULL REFERENCES TENANT (TENANT_ID),
    KEY_ALGORITHM_ID INTEGER NOT NULL REFERENCES KEY_ALGORITHM_REF (KEY_ALGORITHM_ID),
    KEY_STATUS_ID INTEGER NOT NULL REFERENCES KEY_STATUS_REF (KEY_STATUS_ID),
    DISPLAY_NAME TEXT NOT NULL,
    key_location_id INTEGER NOT NULL REFERENCES KEY_LOCATION_REF (key_location_id),
    key_value BYTEA NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SYMMETRIC VERSION
CREATE TABLE IF NOT EXISTS symmetric_version (
    sym_key_version_id UUID PRIMARY KEY,
    key_id UUID REFERENCES key(key_id),
    encrypted_by_mkek_id UUID REFERENCES mkek(mkek_id),
    encrypted_with_crypto_context JSONB NOT NULL,
    key_location_id INTEGER NOT NULL REFERENCES KEY_LOCATION_REF (key_location_id),
    key_value BYTEA NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ASYMMETRIC VERSION
CREATE TABLE IF NOT EXISTS asymmetric_version (
    asym_key_version_id UUID PRIMARY KEY,
    key_id UUID REFERENCES key(key_id),
  encrypted_by_mkek_id UUID REFERENCES mkek(mkek_id),
    encrypted_with_crypto_context JSONB NOT NULL,
    key_location_id INTEGER NOT NULL REFERENCES KEY_LOCATION_REF (key_location_id),
    private_value BYTEA NOT NULL,
    public_value BYTEA NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Grant privileges to the database user:

CREATE USER _USER_ WITH PASSWORD _PASSWORD_;
GRANT USAGE on SCHEMA _SCHEMA_ TO _USER_;
ALTER DEFAULT PRIVILEGES IN SCHEMA _SCHEMA_ GRANT ALL PRIVILEGES ON TABLES TO _USER_;