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
|
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');
|
