ProofedMesh/deploy/trillian/10-mysql.yaml

apiVersion: v1
kind: Secret
metadata:
  name: trillian-mysql
  namespace: agent-trust
type: Opaque
stringData:
  # NOTE: committed in plain form for the first deploy. Rotate to a Sealed
  # Secret before the first real attestation lands; see deploy/trillian/README.md.
  MYSQL_ROOT_PASSWORD: trillian-root-dev-only
  MYSQL_USER: trillian
  MYSQL_PASSWORD: trillian-dev-only
  MYSQL_DATABASE: trillian
  # DSN consumed by the Trillian log server/signer MySQL driver.
  MYSQL_URI: trillian:trillian-dev-only@tcp(trillian-mysql.agent-trust.svc.cluster.local:3306)/trillian?parseTime=true
---
# Schema ConfigMap. Content is deploy/trillian/storage.sql (vendored verbatim
# from https://github.com/google/trillian/blob/v1.6.1/storage/mysql/schema/storage.sql).
# Applied via a mysql init-container at MySQL first start.
#
# This block MUST be re-generated when deploy/trillian/storage.sql changes:
#   kubectl -n agent-trust create configmap trillian-mysql-init \
#     --from-file=storage.sql=deploy/trillian/storage.sql \
#     --dry-run=client -o yaml > deploy/trillian/10-mysql-init-cm.yaml
#
# For the first deploy we inline it here so a plain `kubectl apply -f deploy/trillian/`
# brings up a working database on its own.
apiVersion: v1
kind: ConfigMap
metadata:
  name: trillian-mysql-init
  namespace: agent-trust
data:
  storage.sql: |
    # MySQL / MariaDB version of the tree schema

    -- ---------------------------------------------
    -- Tree stuff here
    -- ---------------------------------------------

    -- Tree parameters should not be changed after creation. Doing so can
    -- render the data in the tree unusable or inconsistent.
    CREATE TABLE IF NOT EXISTS Trees(
      TreeId                BIGINT NOT NULL,
      TreeState             ENUM('ACTIVE', 'FROZEN', 'DRAINING') NOT NULL,
      TreeType              ENUM('LOG', 'MAP', 'PREORDERED_LOG') NOT NULL,
      HashStrategy          ENUM('RFC6962_SHA256', 'TEST_MAP_HASHER', 'OBJECT_RFC6962_SHA256', 'CONIKS_SHA512_256', 'CONIKS_SHA256') NOT NULL,
      HashAlgorithm         ENUM('SHA256') NOT NULL,
      SignatureAlgorithm    ENUM('ECDSA', 'RSA', 'ED25519') NOT NULL,
      DisplayName           VARCHAR(20),
      Description           VARCHAR(200),
      CreateTimeMillis      BIGINT NOT NULL,
      UpdateTimeMillis      BIGINT NOT NULL,
      MaxRootDurationMillis BIGINT NOT NULL,
      PrivateKey            MEDIUMBLOB NOT NULL, -- Unused.
      PublicKey             MEDIUMBLOB NOT NULL, -- This is now used to store settings.
      Deleted               BOOLEAN,
      DeleteTimeMillis      BIGINT,
      PRIMARY KEY(TreeId)
    );

    CREATE TABLE IF NOT EXISTS TreeControl(
      TreeId                  BIGINT NOT NULL,
      SigningEnabled          BOOLEAN NOT NULL,
      SequencingEnabled       BOOLEAN NOT NULL,
      SequenceIntervalSeconds INTEGER NOT NULL,
      PRIMARY KEY(TreeId),
      FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS Subtree(
      TreeId               BIGINT NOT NULL,
      SubtreeId            VARBINARY(255) NOT NULL,
      Nodes                MEDIUMBLOB NOT NULL,
      SubtreeRevision      INTEGER NOT NULL,
      PRIMARY KEY(TreeId, SubtreeId, SubtreeRevision),
      FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS TreeHead(
      TreeId               BIGINT NOT NULL,
      TreeHeadTimestamp    BIGINT,
      TreeSize             BIGINT,
      RootHash             VARBINARY(255) NOT NULL,
      RootSignature        VARBINARY(1024) NOT NULL,
      TreeRevision         BIGINT,
      PRIMARY KEY(TreeId, TreeHeadTimestamp),
      FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
    );

    CREATE UNIQUE INDEX TreeHeadRevisionIdx
      ON TreeHead(TreeId, TreeRevision);

    CREATE TABLE IF NOT EXISTS LeafData(
      TreeId               BIGINT NOT NULL,
      LeafIdentityHash     VARBINARY(255) NOT NULL,
      LeafValue            LONGBLOB NOT NULL,
      ExtraData            LONGBLOB,
      QueueTimestampNanos  BIGINT NOT NULL,
      PRIMARY KEY(TreeId, LeafIdentityHash),
      FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS SequencedLeafData(
      TreeId               BIGINT NOT NULL,
      SequenceNumber       BIGINT UNSIGNED NOT NULL,
      LeafIdentityHash     VARBINARY(255) NOT NULL,
      MerkleLeafHash       VARBINARY(255) NOT NULL,
      IntegrateTimestampNanos BIGINT NOT NULL,
      PRIMARY KEY(TreeId, SequenceNumber),
      FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE,
      FOREIGN KEY(TreeId, LeafIdentityHash) REFERENCES LeafData(TreeId, LeafIdentityHash) ON DELETE CASCADE
    );

    CREATE INDEX SequencedLeafMerkleIdx
      ON SequencedLeafData(TreeId, MerkleLeafHash);

    CREATE TABLE IF NOT EXISTS Unsequenced(
      TreeId               BIGINT NOT NULL,
      Bucket               INTEGER NOT NULL,
      LeafIdentityHash     VARBINARY(255) NOT NULL,
      MerkleLeafHash       VARBINARY(255) NOT NULL,
      QueueTimestampNanos  BIGINT NOT NULL,
      QueueID VARBINARY(32) DEFAULT NULL UNIQUE,
      PRIMARY KEY (TreeId, Bucket, QueueTimestampNanos, LeafIdentityHash)
    );
---
apiVersion: v1
kind: Service
metadata:
  name: trillian-mysql
  namespace: agent-trust
spec:
  selector:
    app.kubernetes.io/name: trillian-mysql
  ports:
    - name: mysql
      port: 3306
      targetPort: 3306
  clusterIP: None
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: trillian-mysql
  namespace: agent-trust
spec:
  serviceName: trillian-mysql
  replicas: 1
  selector:
    matchLabels:
      app.kubernetes.io/name: trillian-mysql
  template:
    metadata:
      labels:
        app.kubernetes.io/name: trillian-mysql
    spec:
      initContainers:
        - name: copy-init-sql
          image: busybox:1.36.1
          command:
            - /bin/sh
            - -c
            - cp /src/storage.sql /dst/01-storage.sql
          volumeMounts:
            - name: init-sql-src
              mountPath: /src
            - name: init-sql
              mountPath: /dst
      containers:
        - name: mysql
          image: mysql:8.4
          args:
            # MySQL 8.4 removed --default-authentication-plugin. The server
            # defaults to caching_sha2_password; the Trillian MySQL driver
            # (go-sql-driver/mysql) supports it, so we leave the default.
            - --character-set-server=utf8mb4
            - --collation-server=utf8mb4_unicode_ci
          envFrom:
            - secretRef:
                name: trillian-mysql
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - name: data
              mountPath: /var/lib/mysql
            - name: init-sql
              mountPath: /docker-entrypoint-initdb.d
          readinessProbe:
            exec:
              command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
            initialDelaySeconds: 10
            periodSeconds: 5
          livenessProbe:
            exec:
              command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
            initialDelaySeconds: 60
            periodSeconds: 20
          resources:
            requests:
              cpu: "100m"
              memory: "256Mi"
            limits:
              memory: "1Gi"
      volumes:
        - name: init-sql-src
          configMap:
            name: trillian-mysql-init
        - name: init-sql
          emptyDir: {}
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 10Gi
        storageClassName: local-path