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
|
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('yes','no') NOT NULL DEFAULT 'no',
ca enum('yes','no') NOT NULL,
trusted enum('yes','no') NOT NULL DEFAULT 'no',
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(20) NOT NULL,
valid_to bigint(20) NOT NULL,
fingerprint binary(32) NOT NULL COMMENT 'sha256 hash',
`serial` blob NOT NULL,
certificate blob NOT NULL COMMENT 'DER encoded certificate',
ctime timestamp NULL DEFAULT NULL,
mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
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('yes','no') NOT NULL DEFAULT 'no',
invalid_reason varchar(255) NULL DEFAULT NULL,
ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
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 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
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 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
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 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`hash`,`type`,`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE x509_job_run (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
total_targets int(10) NOT NULL,
finished_targets int(10) NOT NULL,
start_time timestamp NULL DEFAULT NULL,
end_time timestamp NULL DEFAULT NULL,
ctime timestamp NULL DEFAULT NULL,
mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) 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,
ctime timestamp NULL DEFAULT NULL,
mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX x509_idx_target_ip_port (ip, port)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
