From 067008c5f094ba9606daacbe540f6b929dc124ea Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 14 Apr 2024 15:31:28 +0200 Subject: Adding upstream version 1:1.3.2. Signed-off-by: Daniel Baumann --- schema/mysql.schema.sql | 136 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 136 insertions(+) create mode 100644 schema/mysql.schema.sql (limited to 'schema/mysql.schema.sql') diff --git a/schema/mysql.schema.sql b/schema/mysql.schema.sql new file mode 100644 index 0000000..7e56746 --- /dev/null +++ b/schema/mysql.schema.sql @@ -0,0 +1,136 @@ +CREATE TABLE x509_certificate ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + `subject` varchar(255) NOT NULL COMMENT 'CN of the subject DN if present else full subject DN', + subject_hash binary(32) NOT NULL COMMENT 'sha256 hash of the full subject DN', + `issuer` varchar(255) NOT NULL COMMENT 'CN of the issuer DN if present else full issuer DN', + issuer_hash binary(32) NOT NULL COMMENT 'sha256 hash of the full issuer DN', + issuer_certificate_id int(10) unsigned DEFAULT NULL, + version enum('1','2','3') NOT NULL, + self_signed enum('n', 'y') NOT NULL DEFAULT 'n', + ca enum('n', 'y') NOT NULL, + trusted enum('n', 'y') NOT NULL DEFAULT 'n', + pubkey_algo enum('unknown','RSA','DSA','DH','EC') NOT NULL, + pubkey_bits smallint(6) unsigned NOT NULL, + signature_algo varchar(255) NOT NULL, + signature_hash_algo varchar(255) NOT NULL, + valid_from bigint unsigned NOT NULL, + valid_to bigint unsigned NOT NULL, + fingerprint binary(32) NOT NULL COMMENT 'sha256 hash', + `serial` blob NOT NULL, + certificate blob NOT NULL COMMENT 'DER encoded certificate', + ctime bigint unsigned DEFAULT NULL, + mtime bigint unsigned DEFAULT NULL, + PRIMARY KEY (id), + UNIQUE KEY x509_idx_certificate_fingerprint (fingerprint), + KEY x509_fk_certificate_issuer_certificate_id (issuer_certificate_id), + CONSTRAINT x509_fk_certificate_issuer_certificate_id FOREIGN KEY (issuer_certificate_id) REFERENCES x509_certificate (id) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_certificate_chain ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + target_id int(10) unsigned NOT NULL, + length smallint(6) NOT NULL, + valid enum('n', 'y') NOT NULL DEFAULT 'n', + invalid_reason varchar(255) NULL DEFAULT NULL, + ctime bigint unsigned NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_certificate_chain_link ( + certificate_chain_id int(10) unsigned NOT NULL, + certificate_id int(10) unsigned NOT NULL, + `order` tinyint(4) NOT NULL, + ctime bigint unsigned NOT NULL, + PRIMARY KEY (certificate_chain_id,certificate_id,`order`), + KEY x509_fk_certificate_chain_link_certificate_id (certificate_id), + CONSTRAINT x509_fk_certificate_chain_link_certificate_chain_id FOREIGN KEY (certificate_chain_id) REFERENCES x509_certificate_chain (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT x509_fk_certificate_chain_link_certificate_id FOREIGN KEY (certificate_id) REFERENCES x509_certificate (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_certificate_subject_alt_name ( + certificate_id int(10) unsigned NOT NULL, + hash binary(32) NOT NULL COMMENT 'sha256 hash of type=value', + `type` varchar(255) NOT NULL, + `value` varchar(255) NOT NULL, + ctime bigint unsigned NOT NULL, + PRIMARY KEY (certificate_id,hash), + CONSTRAINT x509_fk_certificate_subject_alt_name_certificate_id FOREIGN KEY (certificate_id) REFERENCES x509_certificate (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_dn ( + `hash` binary(32) NOT NULL, + `type` enum('issuer','subject') NOT NULL, + `order` tinyint(4) unsigned NOT NULL, + `key` varchar(255) NOT NULL, + `value` varchar(255) NOT NULL, + ctime bigint unsigned NOT NULL, + PRIMARY KEY (`hash`,`type`,`order`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_target ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + ip binary(16) NOT NULL, + `port` smallint unsigned NOT NULL, + hostname varchar(255) NULL DEFAULT NULL, + latest_certificate_chain_id int(10) unsigned NULL DEFAULT NULL, + last_scan bigint unsigned NOT NULL, + ctime bigint unsigned DEFAULT NULL, + mtime bigint unsigned DEFAULT NULL, + PRIMARY KEY (id), + INDEX x509_idx_target_ip_port (ip, port) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_job ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + cidrs text NOT NULL, + ports text NOT NULL, + exclude_targets text DEFAULT NULL, + ctime bigint unsigned NOT NULL, + mtime bigint unsigned NOT NULL, + + PRIMARY KEY (id), + UNIQUE (name) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_schedule ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + job_id int(10) unsigned NOT NULL, + name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + config text NOT NULL, -- json + ctime bigint unsigned NOT NULL, + mtime bigint unsigned NOT NULL, + + PRIMARY KEY (id), + CONSTRAINT fk_x509_schedule_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_job_run ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + job_id int(10) unsigned NOT NULL, + schedule_id int(10) unsigned DEFAULT NULL, + total_targets int(10) NOT NULL, + finished_targets int(10) NOT NULL, + start_time bigint unsigned DEFAULT NULL, + end_time bigint unsigned DEFAULT NULL, + + PRIMARY KEY (id), + CONSTRAINT fk_x509_job_run_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE, + CONSTRAINT fk_x509_job_run_schedule FOREIGN KEY (schedule_id) REFERENCES x509_schedule (id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_schema ( + id int unsigned NOT NULL AUTO_INCREMENT, + version varchar(64) NOT NULL, + timestamp bigint unsigned NOT NULL, + success enum ('n', 'y') DEFAULT NULL, + reason text DEFAULT NULL, + + PRIMARY KEY (id), + CONSTRAINT idx_x509_schema_version UNIQUE (version) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +INSERT INTO x509_schema (version, timestamp, success) + VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y'); -- cgit v1.2.3