diff options
Diffstat (limited to 'schema')
-rw-r--r-- | schema/mysql-upgrades/1.0.0.sql | 27 | ||||
-rw-r--r-- | schema/mysql-upgrades/1.1.0.sql | 4 | ||||
-rw-r--r-- | schema/mysql-upgrades/1.2.0.sql | 103 | ||||
-rw-r--r-- | schema/mysql-upgrades/1.3.0.sql | 51 | ||||
-rw-r--r-- | schema/mysql.schema.sql | 136 | ||||
-rw-r--r-- | schema/pgsql-upgrades/1.3.0.sql | 49 | ||||
-rw-r--r-- | schema/pgsql.schema.sql | 162 |
7 files changed, 532 insertions, 0 deletions
diff --git a/schema/mysql-upgrades/1.0.0.sql b/schema/mysql-upgrades/1.0.0.sql new file mode 100644 index 0000000..28b3e7d --- /dev/null +++ b/schema/mysql-upgrades/1.0.0.sql @@ -0,0 +1,27 @@ +ALTER TABLE x509_target MODIFY COLUMN `port` smallint unsigned NOT NULL; + +ALTER TABLE x509_certificate_subject_alt_name DROP FOREIGN KEY x509_fk_certificate_subject_alt_name_certificate_id; + +ALTER TABLE x509_certificate_subject_alt_name DROP PRIMARY KEY; + +ALTER TABLE x509_certificate_subject_alt_name ADD COLUMN hash binary(32) NOT NULL + COMMENT 'sha256 hash of type=value' + AFTER certificate_id; + +UPDATE x509_certificate_subject_alt_name SET hash = UNHEX(SHA2(CONCAT(type, '=', value), 256)); + +ALTER TABLE x509_certificate_subject_alt_name ADD PRIMARY KEY(certificate_id, hash); + +ALTER TABLE x509_certificate_subject_alt_name ADD + CONSTRAINT x509_fk_certificate_subject_alt_name_certificate_id + FOREIGN KEY (certificate_id) + REFERENCES x509_certificate (id) + ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE x509_certificate_subject_alt_name ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default; + +ALTER TABLE x509_target DROP INDEX x509_idx_target_ip_port_hostname; + +ALTER TABLE x509_target ADD INDEX x509_idx_target_ip_port_hostname(ip,port,hostname(191)); + +ALTER TABLE x509_target ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default; diff --git a/schema/mysql-upgrades/1.1.0.sql b/schema/mysql-upgrades/1.1.0.sql new file mode 100644 index 0000000..055d783 --- /dev/null +++ b/schema/mysql-upgrades/1.1.0.sql @@ -0,0 +1,4 @@ +ALTER TABLE x509_target DROP INDEX x509_idx_target_ip_port_hostname; +ALTER TABLE x509_target ADD INDEX x509_idx_target_ip_port (ip, port); +ALTER TABLE x509_certificate MODIFY COLUMN valid_from bigint(20) NOT NULL; +ALTER TABLE x509_certificate MODIFY COLUMN valid_to bigint(20) NOT NULL; diff --git a/schema/mysql-upgrades/1.2.0.sql b/schema/mysql-upgrades/1.2.0.sql new file mode 100644 index 0000000..1fdd74f --- /dev/null +++ b/schema/mysql-upgrades/1.2.0.sql @@ -0,0 +1,103 @@ +ALTER TABLE x509_certificate + MODIFY self_signed enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + MODIFY ca enum('n', 'y', 'yes', 'no') NOT NULL, + MODIFY trusted enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_certificate SET self_signed = 'y' WHERE self_signed = 'yes'; +UPDATE x509_certificate SET self_signed = 'n' WHERE self_signed = 'no'; + +UPDATE x509_certificate SET ca = 'y' WHERE ca = 'yes'; +UPDATE x509_certificate SET ca = 'n' WHERE ca = 'no'; + +UPDATE x509_certificate SET trusted = 'y' WHERE trusted = 'yes'; +UPDATE x509_certificate SET trusted = 'n' WHERE trusted = 'no'; + +UPDATE x509_certificate SET mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0, ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; +UPDATE x509_certificate SET valid_from = valid_from * 1000.0, valid_to = valid_to * 1000.0; + +ALTER TABLE x509_certificate + MODIFY self_signed enum('n', 'y') NOT NULL DEFAULT 'n', + MODIFY ca enum('n', 'y') NOT NULL, + MODIFY trusted enum('n', 'y') NOT NULL DEFAULT 'n', + DROP COLUMN mtime, + DROP COLUMN ctime, + CHANGE COLUMN ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE COLUMN mtime_tmp mtime bigint unsigned DEFAULT NULL; + +ALTER TABLE x509_certificate_chain + MODIFY valid enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_chain SET valid = 'y' WHERE valid = 'yes'; +UPDATE x509_certificate_chain SET valid = 'n' WHERE valid = 'no'; + +UPDATE x509_certificate_chain SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_chain + MODIFY valid enum('n', 'y') NOT NULL DEFAULT 'n', + DROP ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_certificate_chain_link ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_chain_link SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_chain_link + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_certificate_subject_alt_name ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_subject_alt_name SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_subject_alt_name + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_dn ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_dn SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_dn + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_job_run + ADD COLUMN starttime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN endtime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_job_run SET + starttime_tmp = UNIX_TIMESTAMP(start_time) * 1000.0, + endtime_tmp = UNIX_TIMESTAMP(end_time) * 1000.0, + ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0, + mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0; + +ALTER TABLE x509_job_run + DROP COLUMN start_time, + DROP COLUMN end_time, + DROP COLUMN mtime, + DROP COLUMN ctime, + CHANGE starttime_tmp start_time bigint unsigned DEFAULT NULL, + CHANGE endtime_tmp end_time bigint unsigned DEFAULT NULL, + CHANGE ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE mtime_tmp mtime bigint unsigned DEFAULT NULL; + +ALTER TABLE x509_target ADD COLUMN last_scan bigint unsigned DEFAULT NULL AFTER latest_certificate_chain_id; +UPDATE x509_target SET last_scan = UNIX_TIMESTAMP() * 1000.0; +ALTER TABLE x509_target MODIFY COLUMN last_scan bigint unsigned NOT NULL; + +ALTER TABLE x509_target + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_target SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0, mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0; + +ALTER TABLE x509_target + DROP COLUMN ctime, + DROP COLUMN mtime, + CHANGE ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE mtime_tmp mtime bigint unsigned DEFAULT NULL; diff --git a/schema/mysql-upgrades/1.3.0.sql b/schema/mysql-upgrades/1.3.0.sql new file mode 100644 index 0000000..f31e8bd --- /dev/null +++ b/schema/mysql-upgrades/1.3.0.sql @@ -0,0 +1,51 @@ +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; + +DELETE FROM x509_job_run; +ALTER TABLE x509_job_run + ADD COLUMN job_id int(10) unsigned NOT NULL AFTER id, + ADD COLUMN schedule_id int(10) unsigned DEFAULT NULL AFTER job_id, + DROP COLUMN `name`, + DROP COLUMN ctime, + DROP COLUMN mtime; +ALTER TABLE x509_job_run + ADD CONSTRAINT fk_x509_job_run_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE, + ADD CONSTRAINT fk_x509_job_run_schedule FOREIGN KEY (schedule_id) REFERENCES x509_schedule (id) ON DELETE CASCADE; + +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, reason) + VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y', NULL); 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'); diff --git a/schema/pgsql-upgrades/1.3.0.sql b/schema/pgsql-upgrades/1.3.0.sql new file mode 100644 index 0000000..7e1f43a --- /dev/null +++ b/schema/pgsql-upgrades/1.3.0.sql @@ -0,0 +1,49 @@ +CREATE TABLE x509_job ( + id serial PRIMARY KEY, + name varchar(255) NOT NULL, + author varchar(255) NOT NULL, + cidrs text NOT NULL, + ports text NOT NULL, + exclude_targets text DEFAULT NULL, + ctime bigint NOT NULL, + mtime bigint NOT NULL, + + UNIQUE (name) +); + +CREATE TABLE x509_schedule ( + id serial PRIMARY KEY, + job_id int NOT NULL, + name varchar(255) NOT NULL, + author varchar(255) NOT NULL, + config text NOT NULL, -- json + ctime bigint NOT NULL, + mtime bigint NOT NULL, + + CONSTRAINT fk_x509_schedule_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE +); + +DELETE FROM x509_job_run; +ALTER TABLE x509_job_run + ADD COLUMN job_id int NOT NULL, + ADD COLUMN schedule_id int DEFAULT NULL, + DROP COLUMN name, + DROP COLUMN ctime, + DROP COLUMN mtime; +ALTER TABLE x509_job_run + ADD CONSTRAINT fk_x509_job_run_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE, + ADD CONSTRAINT fk_x509_job_run_schedule FOREIGN KEY (schedule_id) REFERENCES x509_schedule (id) ON DELETE CASCADE; + +CREATE TABLE x509_schema ( + id serial, + version varchar(64) NOT NULL, + timestamp bigint NOT NULL, + success boolenum DEFAULT NULL, + reason text DEFAULT NULL, + + CONSTRAINT pk_x509_schema PRIMARY KEY (id), + CONSTRAINT idx_x509_schema_version UNIQUE (version) +); + +INSERT INTO x509_schema (version, timestamp, success, reason) + VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y', NULL); diff --git a/schema/pgsql.schema.sql b/schema/pgsql.schema.sql new file mode 100644 index 0000000..1d93ef3 --- /dev/null +++ b/schema/pgsql.schema.sql @@ -0,0 +1,162 @@ +CREATE DOMAIN uint2 AS int4 + CHECK(VALUE >= 0 AND VALUE < 65536); +CREATE DOMAIN biguint AS bigint CONSTRAINT positive CHECK ( VALUE IS NULL OR 0 <= VALUE ); +CREATE TYPE boolenum AS ENUM ('n', 'y'); +CREATE TYPE certificate_version AS ENUM('1','2','3'); +CREATE TYPE dn_type AS ENUM('issuer','subject'); +CREATE TYPE pubkey_algo AS ENUM('unknown','RSA','DSA','DH','EC'); + +-- Used when sorting certificates by expiration date. +CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(datetime timestamptz DEFAULT NOW()) + RETURNS biguint + LANGUAGE plpgsql + PARALLEL SAFE + AS $$ +BEGIN + RETURN EXTRACT(EPOCH FROM datetime); +END; +$$; + +-- IPL ORM renders SQL queries with LIKE operators for all suggestions in the search bar, +-- which fails for numeric and enum types on PostgreSQL. Just like in Icinga DB Web. +CREATE OR REPLACE FUNCTION anynonarrayliketext(anynonarray, text) + RETURNS bool + LANGUAGE plpgsql + IMMUTABLE + PARALLEL SAFE + AS $$ +BEGIN + RETURN $1::TEXT LIKE $2; +END; +$$; +CREATE OPERATOR ~~ (LEFTARG=anynonarray, RIGHTARG=text, PROCEDURE=anynonarrayliketext); + +CREATE TABLE x509_certificate ( + id serial PRIMARY KEY, + subject varchar(255) NOT NULL, + subject_hash bytea NOT NULL, + issuer varchar(255) NOT NULL, + issuer_hash bytea NOT NULL, + issuer_certificate_id int DEFAULT NULL, + version certificate_version NOT NULL, + self_signed boolenum NOT NULL DEFAULT 'n', + ca boolenum NOT NULL, + trusted boolenum NOT NULL DEFAULT 'n', + pubkey_algo pubkey_algo NOT NULL, + pubkey_bits uint2 NOT NULL, + signature_algo varchar(255) NOT NULL, + signature_hash_algo varchar(255) NOT NULL, + valid_from biguint NOT NULL, + valid_to biguint NOT NULL, + fingerprint bytea NOT NULL, + serial bytea NOT NULL, + certificate bytea NOT NULL, + ctime biguint NOT NULL, + mtime biguint DEFAULT NULL, + CONSTRAINT x509_idx_certificate_fingerprint UNIQUE(fingerprint), + CONSTRAINT x509_fk_certificate_issuer_certificate_id FOREIGN KEY (issuer_certificate_id) REFERENCES x509_certificate (id) ON DELETE SET NULL ON UPDATE CASCADE +); + +CREATE TABLE x509_certificate_chain ( + id serial PRIMARY KEY, + target_id int NOT NULL, + length uint2 NOT NULL, + valid boolenum NOT NULL DEFAULT 'n', + invalid_reason varchar(255) NULL DEFAULT NULL, + ctime biguint NOT NULL +); + +CREATE TABLE x509_certificate_chain_link ( + certificate_chain_id int NOT NULL, + certificate_id int NOT NULL, + "order" uint2 NOT NULL, + ctime biguint NOT NULL, + PRIMARY KEY(certificate_chain_id,certificate_id,"order"), + 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 +); + +CREATE TABLE x509_certificate_subject_alt_name ( + certificate_id int NOT NULL, + hash bytea NOT NULL, + type varchar(255) NOT NULL, + value varchar(255) NOT NULL, + ctime biguint 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 +); + +CREATE TABLE x509_dn ( + hash bytea NOT NULL, + type dn_type NOT NULL, + "order" uint2 NOT NULL, + key varchar(255) NOT NULL, + value varchar(255) NOT NULL, + ctime biguint NOT NULL, + PRIMARY KEY (hash,type,"order") +); + +CREATE TABLE x509_target ( + id serial PRIMARY KEY, + ip bytea NOT NULL, + port uint2 NOT NULL, + hostname varchar(255) NULL DEFAULT NULL, + latest_certificate_chain_id int NULL DEFAULT NULL, + last_scan biguint NOT NULL, + ctime biguint NOT NULL, + mtime biguint DEFAULT NULL +); + +CREATE INDEX x509_idx_target ON x509_target (ip,port,hostname); + +CREATE TABLE x509_job ( + id serial PRIMARY KEY, + name varchar(255) NOT NULL, + author varchar(255) NOT NULL, + cidrs text NOT NULL, + ports text NOT NULL, + exclude_targets text DEFAULT NULL, + ctime bigint NOT NULL, + mtime bigint NOT NULL, + + UNIQUE (name) +); + +CREATE TABLE x509_schedule ( + id serial PRIMARY KEY, + job_id int NOT NULL, + name varchar(255) NOT NULL, + author varchar(255) NOT NULL, + config text NOT NULL, -- json + ctime bigint NOT NULL, + mtime bigint NOT NULL, + + CONSTRAINT fk_x509_schedule_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE +); + +CREATE TABLE x509_job_run ( + id serial PRIMARY KEY, + job_id int NOT NULL, + schedule_id int DEFAULT NULL, + total_targets int NOT NULL, + finished_targets int NOT NULL, + start_time biguint NULL DEFAULT NULL, + end_time biguint NULL DEFAULT NULL, + + 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 +); + +CREATE TABLE x509_schema ( + id serial, + version varchar(64) NOT NULL, + timestamp bigint NOT NULL, + success boolenum DEFAULT NULL, + reason text DEFAULT NULL, + + CONSTRAINT pk_x509_schema PRIMARY KEY (id), + CONSTRAINT idx_x509_schema_version UNIQUE (version) +); + +INSERT INTO x509_schema (version, timestamp, success) + VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y'); |