Database Schema¶

This document describes the Kerbside MySQL/MariaDB database schema.

Entity Relationship Diagram¶

erDiagram
    sources {
        string name PK
        string type
        datetime last_seen
        string seen_by
        boolean errored
        string url
        text ca_cert
        string username
        string password
        string project_name "OpenStack only"
        string user_domain_id "OpenStack only"
        string project_domain_id "OpenStack only"
        boolean deleted
    }

    sources ||--o{ consoles : "sources provide consoles"
    consoles {
        string source PK, FK
        string uuid PK
        datetime discovered
        string hypervisor
        string hypervisor_ip
        integer insecure_port
        integer secure_port
        string name
        string host_subject
        string ticket
    }

    sources ||--o{ consoletokens: "sources enable consoletokens"
    consoles ||--o{ consoletokens: "consoles own consoletokens"
    consoletokens {
        string token PK
        string session_id
        string uuid FK "with source, to consoles"
        string source FK
        integer created "epoch seconds"
        integer expires "epoch seconds"
        string backend_ticket "nullable, minting sources only"
        integer backend_ticket_expires "nullable, epoch seconds"
        string backend_proxy_uri "nullable, tunnelled consoles only"
        string backend_connect_host "nullable, secret"
        integer backend_port "nullable, TLS port through the tunnel"
    }

    consoletokens ||--o{ proxychannels: "consoletokens authenticate proxychannels"
    proxychannels {
        string node PK
        string pid PK
        datetime created
        string client_ip
        integer client_port
        integer connection_id
        string channel_type
        integer channel_id
        string session_id "No FK; dropped in migration f7b2e9c4a1d8"
    }

    consoles ||--o{ auditevents: "consoles create auditevents"
    auditevents {
        string source PK "No FK to avoid cascading delete"
        string uuid PK "No FK to avoid cascading delete"
        string session_id "No FK to avoid cascading delete"
        string channel
        datetime timestamp PK "microsecond precision"
        string node
        string pid
        text message
    }

    sf_token_jtis {
        string jti PK "uuid4 hex, from the token's jti claim"
        double expiry "epoch seconds, matches the exp claim"
    }

    sources ||--o{ sf_token_keys: "No FK; sources reloaded from YAML"
    sf_token_keys {
        string source PK "No FK; sources reloaded from YAML"
        text keys_json "Shaken Fist's public_view payload, verbatim JSON"
        double fetched_at "epoch seconds"
    }

Table Descriptions¶

sources¶

Console sources (cloud platforms) that provide virtual machines.

Column	Type	Description
name	string	Primary key, source identifier
type	string	Source type: `shakenfist`, `ovirt`, `openstack`, `proxmox` or `static`
last_seen	datetime	Last successful poll time
seen_by	string	Node that last polled this source
errored	boolean	Whether the source is in error state
url	string	API URL for the source
ca_cert	text	CA certificate for TLS validation
username	string	Authentication username, or a Proxmox VE API token ID (`user@realm!tokenid`); up to 255 characters
password	string	Authentication password/API key
project_name	string	OpenStack project name (OpenStack only)
user_domain_id	string	OpenStack user domain (OpenStack only)
project_domain_id	string	OpenStack project domain (OpenStack only)
deleted	boolean	Soft delete flag

password is a live credential for the backend cloud's management plane, and is never returned by the REST API or rendered by the web UI. db.get_source() and db.get_sources() return only the fields listed in db.SOURCE_PUBLIC_FIELDS unless a caller opts in with include_secrets=True, which only the code paths that authenticate to a backend cloud do. That list is an allowlist rather than a list of things to strip, so a column added to the table is private until somebody decides otherwise.

Three of the public fields are worth naming explicitly, because each could plausibly have gone the other way:

ca_cert is the public half of the backend's TLS identity rather than a credential. The sources page renders it and clients need it to validate the connection they are pointed at.
url is the backend's management plane endpoint. It is public because the list endpoint has always returned it and because a console client that cannot see which cloud a source is cannot do much with it.
username is half of a credential pair. It is public for the same historical reason as url. Narrowing either is an API behaviour change rather than part of withholding the password, and is tracked in issue #449.

consoles¶

Virtual machine consoles discovered from sources.

Column	Type	Description
source	string	Primary key with uuid; foreign key to sources.name
uuid	string	Primary key with source; the source's identifier for the VM, up to 255 characters. Usually the VM's UUID; a Proxmox VE console's is `<vmid>-<SMBIOS UUID>`
discovered	datetime	When the console was first discovered
hypervisor	string	Hypervisor hostname
hypervisor_ip	string	Hypervisor IP address
insecure_port	integer	Non-TLS SPICE port
secure_port	integer	TLS SPICE port
name	string	VM display name
host_subject	string	Expected TLS certificate subject
ticket	string	SPICE ticket for authentication (static sources only)

A console is identified by its source and its identifier together, as it is everywhere else a console is named: in consoletokens, in auditevents and in the maintenance pass. An identifier is only unique within the source that published it -- a static source's identifiers are whatever the operator wrote -- so two sources publishing the same one have two rows, and every lookup, update and removal names both columns (issue #468).

ticket is the password the SPICE server on the hypervisor will accept for this console, and it gets the same treatment as a source's password. db.get_console() and db.get_consoles() return only the fields in db.CONSOLE_PUBLIC_FIELDS unless a caller opts in with include_secrets=True, so it reaches neither the REST API nor the web UI. Only static sources keep a ticket here: it is the console password from the source's configuration, persisted at enumeration time and valid indefinitely. oVirt and Proxmox consoles have an empty ticket. A minted ticket is requested when the first channel of a session connects, and held on that session's consoletokens row until it expires (see below), so it is never shared between sessions and never outlives its session's token by long.

There are exactly two callers entitled to read a console's ticket back out of the database, and git grep include_secrets finds them alongside the source ones: the static branch of the direct .vv handler, which writes it into the file it returns, and the gRPC servicer, which hands it to the proxy to present to the hypervisor for sources that do not mint tickets.

The allowlist governs what leaves the database, which is not the whole story for logs. Discovery holds a ticket before there is a database row to read: the source driver yields it and main._parse_sources() passes it to db.add_console(). A static source's driver builds that dict straight from sources.yaml, so for the one source type whose ticket does not expire, the credential is in process memory every maintenance pass. Two log lines on that path are therefore redacted by hand rather than by the allowlist, and they are the reason db.CONSOLE_SECRET_FIELDS exists separately from db.CONSOLE_PUBLIC_FIELDS:

main._parse_sources() drops CONSOLE_SECRET_FIELDS from the Found console line, which would otherwise write a static source's SPICE password to the daemon log every 60 seconds.
The static driver reports a malformed console entry by naming the keys it found rather than dumping the entry, which would otherwise log the ticket of any entry that supplied one and omitted something else.

Both are pinned by unit tests, because neither is protected by the allowlist that protects everything else.

consoletokens¶

Time-limited access tokens for console connections.

Column	Type	Description
token	string	Primary key, 48-character access token
session_id	string	12-character session identifier
uuid	string	With source, foreign key to consoles (source, uuid)
source	string	Foreign key to sources.name
created	integer	Token creation time (epoch seconds)
expires	integer	Token expiration time (epoch seconds)
backend_ticket	string	Nullable; the session's minted SPICE ticket
backend_ticket_expires	integer	Nullable; when that ticket expires (epoch seconds)
backend_proxy_uri	string	Nullable; the HTTP CONNECT proxy minted with the ticket
backend_connect_host	string	Nullable; the signed host to ask that proxy for
backend_port	integer	Nullable; the console's TLS port through the tunnel

There is one row per session: the .vv file a user is issued carries the token, and every channel of the session presents it to the proxy. A channel is only authorized while expires is in the future, but the row outlives that while the session has channels open, because it is what maps a session back to its console. db.reap_expired_tokens() deletes it once it has expired and the session's last channel has closed.

backend_ticket holds the SPICE password for a source that mints tickets, which oVirt and Proxmox VE do. Each mint revokes the previous ticket for new connections, so channels of one session minting independently would lock each other out. Instead a session mints once and shares the result with every channel, on every proxy node. db.session_ticket() selects the row FOR UPDATE: the first channel to connect holds the row lock while it asks the source for a ticket, and the session's other channels wait for it and then reuse what it stored. A stored ticket is reused while enough of its lifetime remains, and replaced otherwise. A failed mint stores nothing, so the next channel tries again. The lock is per session, so nothing but that session's own channels waits on it.

The ticket gets the treatment consoles.ticket gets. ConsoleToken.export() leaves out the fields in db.CONSOLE_TOKEN_SECRET_FIELDS unless a caller passes include_secrets=True, so none of the token getters, and no API handler built on them, can return it; its expiry is not a credential and is included. The maintenance pass's db.clear_expired_backend_tickets() nulls both columns once the ticket has expired or the token has, since after either no channel can use it. Both columns are NULL for sources that do not mint tickets.

Some sources hand out consoles that are only reachable through an HTTP CONNECT proxy, and mint the tunnel along with the ticket: backend_proxy_uri is the proxy to dial, backend_connect_host is the opaque host to ask it for, and backend_port is the console's TLS port behind it. The connect host is signed by the source, so it is a credential like the ticket: it is in db.CONSOLE_TOKEN_SECRET_FIELDS, while the proxy and the port are exported. The three columns are written with the ticket by db.session_ticket(), reused with it, and cleared with it by db.clear_expired_backend_tickets(). A ticket minted without a tunnel stores NULL in all three, so a session whose ticket is re-minted never keeps a stale tunnel, and a stored ticket whose backend_connect_host is NULL is dialled directly.

These columns hold live credentials, for as long as a ticket lasts: 30 seconds for Proxmox VE, and the token's remaining life plus 30 seconds for oVirt. Anything that copies the table therefore copies them too, including binary logs, replicas and backups. Each copied value expires on that schedule, but treat a backup or binlog taken while sessions were opening as holding credentials until then.

proxychannels¶

Active SPICE channel connections being proxied.

Column	Type	Description
node	string	Primary key part, proxy node hostname
pid	string	Primary key part, worker process ID
created	datetime	Connection establishment time
client_ip	string	Client IP address
client_port	integer	Client source port
connection_id	integer	SPICE connection ID
channel_type	string	Channel type name (main, display, etc.)
channel_id	integer	Channel instance ID
session_id	string	Matches consoletokens.session_id (no FK; dropped in migration f7b2e9c4a1d8)

auditevents¶

Audit log for console access and protocol events.

Column	Type	Description
source	string	Primary key part, source name (no FK to avoid cascade)
uuid	string	Primary key part, console UUID (no FK to avoid cascade)
session_id	string	Session identifier (no FK to avoid cascade)
channel	string	Channel type or "session" for session events
timestamp	datetime	Event time with microsecond precision (PK part)
node	string	Proxy node that recorded the event
pid	string	Worker process ID
message	text	Event description

sf_token_jtis¶

Single-use tracking for Shaken Fist VDI console JWTs (the /sf-console.vv exchange). A row is inserted once a token's signature verifies; a jti already present means the token is being replayed.

Column	Type	Description
jti	string	Primary key, uuid4 hex from the token's `jti` claim
expiry	double	Token expiration time (epoch seconds, matches the `exp` claim)

sf_token_keys¶

Cached Shaken Fist signing public keys, one row per shakenfist source, so /sf-console.vv verifies tokens offline without calling Shaken Fist except on an unknown-kid cache miss.

Column	Type	Description
source	string	Primary key, source name (no FK; sources are reloaded from YAML)
keys_json	text	Shaken Fist's `public_view` key payload, verbatim JSON
fetched_at	double	Time the keys were last fetched (epoch seconds)

Relationships¶

sources → consoles: One source provides many consoles
sources → consoletokens: Tokens reference a source for validation
consoles → consoletokens: Tokens grant access to a specific console, referenced by (source, uuid); removing a console deletes its tokens
consoletokens → proxychannels: Active channels reference their auth token
consoles → auditevents: Audit events record console access (no FK to preserve audit history when consoles are deleted)
sources → sf_token_keys: Each shakenfist source's cached signing keys, keyed by source name (no FK; sources are reloaded from YAML)

Notes¶

The auditevents table intentionally avoids foreign key constraints to preserve audit history even when sources or consoles are deleted.
Timestamps in consoletokens use epoch seconds for easy expiration checks.
The proxychannels table is used for connection tracking and cleanup.
sf_token_jtis and sf_token_keys support offline verification of Shaken Fist's Ed25519-signed VDI console JWTs at /sf-console.vv: the jti table blocks replay, the keys table avoids calling Shaken Fist on the exchange path except when an unknown kid forces a refetch.

Configuration - Database connection settings
Console Sources - Source configuration

📝 Report an issue with this page