summaryrefslogtreecommitdiffstats
path: root/schema/mysql.schema.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-14 13:31:28 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-14 13:31:28 +0000
commit067008c5f094ba9606daacbe540f6b929dc124ea (patch)
tree3092ce2cd8bf1ac6db6c97f4c98c7f71a51c6ac8 /schema/mysql.schema.sql
parentInitial commit. (diff)
downloadicingaweb2-module-x509-067008c5f094ba9606daacbe540f6b929dc124ea.tar.xz
icingaweb2-module-x509-067008c5f094ba9606daacbe540f6b929dc124ea.zip
Adding upstream version 1:1.3.2.upstream/1%1.3.2upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--schema/mysql.schema.sql136
1 files changed, 136 insertions, 0 deletions
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');