summaryrefslogtreecommitdiffstats
path: root/schema
diff options
context:
space:
mode:
Diffstat (limited to 'schema')
-rw-r--r--schema/mysql-upgrades/1.0.0.sql27
-rw-r--r--schema/mysql-upgrades/1.1.0.sql4
-rw-r--r--schema/mysql-upgrades/1.2.0.sql103
-rw-r--r--schema/mysql-upgrades/1.3.0.sql51
-rw-r--r--schema/mysql.schema.sql136
-rw-r--r--schema/pgsql-upgrades/1.3.0.sql49
-rw-r--r--schema/pgsql.schema.sql162
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');