ACME: Fleet identity CA & ACME CA related migrations

[JordanMontgomery]

Related user story

#31289

Task

Tables:

nano_enrollments:

ALTER TABLE nano_enrollments ADD COLUMN hardware_attested TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'

scep_serials/scep_certificates rename

ALTER TABLE scep_serials RENAME TO identity_serials;
ALTER TABLE scep_certificates RENAME TO identity_certificates;

acme_enrollments

CREATE TABLE `acme_enrollments` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  identifier VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  host_identifier VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  not_valid_after DATETIME DEFAULT NULL,
  revoked TINYINT(1) NOT NULL DEFAULT '0',
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

acme_accounts

CREATE TABLE `acme_accounts` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  acme_enrollment_id INT UNSIGNED NOT NULL,
  jwk json NOT NULL,
  revoked TINYINT(1) NOT NULL DEFAULT '0',
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
  FOREIGN KEY (acme_enrollment_id) REFERENCES acme_enrollments(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

acme_orders

CREATE TABLE `acme_orders` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  acme_account_id INT UNSIGNED NOT NULL,
  finalized TINYINT(1) NOT NULL DEFAULT '0',
  csr TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  identifiers json NOT NULL,
  status enum('pending', 'ready', 'processing', 'valid', 'invalid') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  issued_certificate_serial BIGINT DEFAULT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (acme_account_id) REFERENCES acme_accounts(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY `idx_issued_certificate_serial ` (`issued_certificate_serial `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

acme_authorizations

CREATE TABLE `acme_authorizations` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    identifier_type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    identifier_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    acme_order_id INT UNSIGNED NOT NULL,
    status enum('pending', 'valid', 'invalid', 'deactivated', 'expired', 'revoked') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

acme_challenges

CREATE TABLE `acme_challenges` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    token varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    acme_authorization_id INT UNSIGNED NOT NULL,
    status enum('pending', 'valid', 'invalid', 'processing') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Condition of satisfaction

DB migrations created to support initial ACME endpoints