1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
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');
|
