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/pgsql.schema.sql | 162 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 162 insertions(+) create mode 100644 schema/pgsql.schema.sql (limited to 'schema/pgsql.schema.sql') 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'); -- cgit v1.2.3