included pools and leases tables to ipsec.sql database
[strongswan.git] / testing / tests / sql / rw-psk-rsa-split / hosts / carol / etc / ipsec.d / ipsec.sql
1 DROP TABLE IF EXISTS identities;
2 CREATE TABLE identities (
3 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
4 type INTEGER NOT NULL,
5 data BLOB NOT NULL,
6 UNIQUE (type, data)
7 );
8
9 DROP TABLE IF EXISTS child_configs;
10 CREATE TABLE child_configs (
11 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
12 name TEXT NOT NULL,
13 lifetime INTEGER NOT NULL DEFAULT '1200',
14 rekeytime INTEGER NOT NULL DEFAULT '1020',
15 jitter INTEGER NOT NULL DEFAULT '180',
16 updown TEXT DEFAULT NULL,
17 hostaccess INTEGER NOT NULL DEFAULT '0',
18 mode INTEGER NOT NULL DEFAULT '1',
19 dpd_action INTEGER NOT NULL DEFAULT '0',
20 close_action INTEGER NOT NULL DEFAULT '0',
21 ipcomp INTEGER NOT NULL DEFAULT '0'
22 );
23 DROP INDEX IF EXISTS child_configs_name;
24 CREATE INDEX child_configs_name ON child_configs (
25 name
26 );
27
28 DROP TABLE IF EXISTS child_config_traffic_selector;
29 CREATE TABLE child_config_traffic_selector (
30 child_cfg INTEGER NOT NULL,
31 traffic_selector INTEGER NOT NULL,
32 kind INTEGER NOT NULL
33 );
34 DROP INDEX IF EXISTS child_config_traffic_selector;
35 CREATE INDEX child_config_traffic_selector_all ON child_config_traffic_selector (
36 child_cfg, traffic_selector
37 );
38
39 DROP TABLE IF EXISTS ike_configs;
40 CREATE TABLE ike_configs (
41 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
42 certreq INTEGER NOT NULL DEFAULT '1',
43 force_encap INTEGER NOT NULL DEFAULT '0',
44 local TEXT NOT NULL,
45 remote TEXT NOT NULL
46 );
47
48 DROP TABLE IF EXISTS peer_configs;
49 CREATE TABLE peer_configs (
50 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
51 name TEXT NOT NULL,
52 ike_version INTEGER NOT NULL DEFAULT '2',
53 ike_cfg INTEGER NOT NULL,
54 local_id TEXT NOT NULL,
55 remote_id TEXT NOT NULL,
56 cert_policy INTEGER NOT NULL DEFAULT '1',
57 uniqueid INTEGER NOT NULL DEFAULT '0',
58 auth_method INTEGER NOT NULL DEFAULT '1',
59 eap_type INTEGER NOT NULL DEFAULT '0',
60 eap_vendor INTEGER NOT NULL DEFAULT '0',
61 keyingtries INTEGER NOT NULL DEFAULT '1',
62 rekeytime INTEGER NOT NULL DEFAULT '0',
63 reauthtime INTEGER NOT NULL DEFAULT '3600',
64 jitter INTEGER NOT NULL DEFAULT '180',
65 overtime INTEGER NOT NULL DEFAULT '300',
66 mobike INTEGER NOT NULL DEFAULT '1',
67 dpd_delay INTEGER NOT NULL DEFAULT '120',
68 virtual TEXT DEFAULT NULL,
69 pool TEXT DEFAULT NULL,
70 mediation INTEGER NOT NULL DEFAULT '0',
71 mediated_by INTEGER NOT NULL DEFAULT '0',
72 peer_id INTEGER NOT NULL DEFAULT '0'
73 );
74 DROP INDEX IF EXISTS peer_configs_name;
75 CREATE INDEX peer_configs_name ON peer_configs (
76 name
77 );
78
79 DROP TABLE IF EXISTS peer_config_child_config;
80 CREATE TABLE peer_config_child_config (
81 peer_cfg INTEGER NOT NULL,
82 child_cfg INTEGER NOT NULL,
83 PRIMARY KEY (peer_cfg, child_cfg)
84 );
85
86 DROP TABLE IF EXISTS traffic_selectors;
87 CREATE TABLE traffic_selectors (
88 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
89 type INTEGER NOT NULL DEFAULT '7',
90 protocol INTEGER NOT NULL DEFAULT '0',
91 start_addr BLOB DEFAULT NULL,
92 end_addr BLOB DEFAULT NULL,
93 start_port INTEGER NOT NULL DEFAULT '0',
94 end_port INTEGER NOT NULL DEFAULT '65535'
95 );
96
97 DROP TABLE IF EXISTS certificates;
98 CREATE TABLE certificates (
99 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
100 type INTEGER NOT NULL,
101 keytype INTEGER NOT NULL,
102 data BLOB NOT NULL
103 );
104
105 DROP TABLE IF EXISTS certificate_identity;
106 CREATE TABLE certificate_identity (
107 certificate INTEGER NOT NULL,
108 identity INTEGER NOT NULL,
109 PRIMARY KEY (certificate, identity)
110 );
111
112 DROP TABLE IF EXISTS private_keys;
113 CREATE TABLE private_keys (
114 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
115 type INTEGER NOT NULL,
116 data BLOB NOT NULL
117 );
118
119 DROP TABLE IF EXISTS private_key_identity;
120 CREATE TABLE private_key_identity (
121 private_key INTEGER NOT NULL,
122 identity INTEGER NOT NULL,
123 PRIMARY KEY (private_key, identity)
124 );
125
126 DROP TABLE IF EXISTS shared_secrets;
127 CREATE TABLE shared_secrets (
128 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
129 type INTEGER NOT NULL,
130 data BLOB NOT NULL
131 );
132
133 DROP TABLE IF EXISTS shared_secret_identity;
134 CREATE TABLE shared_secret_identity (
135 shared_secret INTEGER NOT NULL,
136 identity INTEGER NOT NULL,
137 PRIMARY KEY (shared_secret, identity)
138 );
139
140 DROP TABLE IF EXISTS pools;
141 CREATE TABLE pools (
142 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
143 name TEXT NOT NULL,
144 start BLOB NOT NULL,
145 end BLOB NOT NULL,
146 next BLOB NOT NULL,
147 timeout INTEGER DEFAULT NULL,
148 UNIQUE (name)
149 );
150 DROP INDEX IF EXISTS pools_name;
151 CREATE INDEX pools_name ON pools (
152 name
153 );
154
155 DROP TABLE IF EXISTS leases;
156 CREATE TABLE leases (
157 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
158 pool INTEGER NOT NULL,
159 address BLOB NOT NULL,
160 identity INTEGER NOT NULL,
161 acquired INTEGER NOT NULL,
162 released INTEGER DEFAULT NULL
163 );
164 DROP INDEX IF EXISTS leases_pool;
165 CREATE INDEX leases_pool ON leases (
166 pool
167 );
168 DROP INDEX IF EXISTS leases_identity;
169 CREATE INDEX leases_identity ON leases (
170 identity
171 );
172 DROP INDEX IF EXISTS leases_released;
173 CREATE INDEX leases_released ON leases (
174 released
175 );
176
177 DROP TABLE IF EXISTS ike_sas;
178 CREATE TABLE ike_sas (
179 local_spi BLOB NOT NULL PRIMARY KEY,
180 remote_spi BLOB NOT NULL,
181 id INTEGER NOT NULL,
182 initiator INTEGER NOT NULL,
183 local_id_type INTEGER NOT NULL,
184 local_id_data BLOB NOT NULL,
185 remote_id_type INTEGER NOT NULL,
186 remote_id_data BLOB NOT NULL,
187 host_family INTEGER NOT NULL,
188 local_host_data BLOB NOT NULL,
189 remote_host_data BLOB NOT NULL,
190 created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
191 );
192
193 DROP TABLE IF EXISTS logs;
194 CREATE TABLE logs (
195 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
196 local_spi BLOB NOT NULL,
197 signal INTEGER NOT NULL,
198 level INTEGER NOT NULL,
199 msg TEXT NOT NULL,
200 time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
201 );
202
203 /* Identities */
204
205 INSERT INTO identities (
206 type, data
207 ) VALUES ( /* C=CH, O=Linux strongSwan, CN=strongSwan Root CA */
208 9, X'3045310B300906035504061302434831193017060355040A13104C696E7578207374726F6E675377616E311B3019060355040313127374726F6E675377616E20526F6F74204341'
209 );
210
211 INSERT INTO identities (
212 type, data
213 ) VALUES ( /* keyid of 'C=CH, O=Linux strongSwan, CN=strongSwan Root CA' */
214 202, X'ae096b87b44886d3b820978623dabd0eae22ebbc'
215 );
216
217 INSERT INTO identities (
218 type, data
219 ) VALUES ( /* carol@strongswan.org */
220 3, X'6361726f6c407374726f6e677377616e2e6f7267'
221 );
222
223 INSERT INTO identities (
224 type, data
225 ) VALUES ( /* moon.strongswan.org */
226 2, X'6d6f6f6e2e7374726f6e677377616e2e6f7267'
227 );
228
229 /* Certificates */
230
231 INSERT INTO certificates (
232 type, keytype, data
233 ) VALUES ( /* C=CH, O=Linux strongSwan, CN=strongSwan Root CA */
234 1, 1, X'308203b53082029da003020102020100300d06092a864886f70d01010405003045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341301e170d3034303931303131303134355a170d3134303930383131303134355a3045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f7420434130820122300d06092a864886f70d01010105000382010f003082010a0282010100bff25f62ea3d566e58b3c87a49caf3ac61cfa96377734d842db3f8fd6ea023f7b0132e66265012317386729c6d7c427a8d9f167be138e8ebae2b12b95933baef36a315c3ddf224cee4bb9bd578135d0467382629621ff96b8d45f6e002e5083662dce181805c140b3f2ce93f83aee3c861cff610a39f0189cb3a3c7cb9bf7e2a09544e2170efaa18fdd4ff20fa94be176d7fecff821f68d17152041d9b46f0cfcfc1e4cf43de5d3f3a587763afe9267f53b11699b3264fc55c5189f5682871166cb98307950569641fa30ffb50de134fed2f973cef1a392827862bc4ddaa97bbb01442e293c41070d07224d4be47ae2753eb2bed4bc1da91c68ec780c4620f0f0203010001a381af3081ac300f0603551d130101ff040530030101ff300b0603551d0f040403020106301d0603551d0e041604145da7dd700651327ee7b66db3b5e5e060ea2e4def306d0603551d230466306480145da7dd700651327ee7b66db3b5e5e060ea2e4defa149a4473045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341820100300d06092a864886f70d010104050003820101009ad74e3e60592dfb9b21c78628bd76b63090c1720c74bf94753cad6fddadc9c776eb39d3bfaa52136bf528840078386308fcf79503bd3d1ad6c15ac38e10c846bff7888a03cfe7fa0e644b522b2af5aedf0bbc508dc48330a180757772771095059b2be148f58dc0c753b59e9d6bfb02e9b685a928a284531b187313fd2b835bc9ea27d0020739a8d485e88bdede9a45cde6d28ed553b0e8e92dabf877bed59abf9d151f15e4f2d00b5e6e49fcb665293d2296697926c2954dae367542ef6e98053e76d2728732f6ce69f284f0b856aa6c2823a9ee29b280a66f50828f9b5cf27f84feca3c31c24897db156c7a833768ab306f51286457a51f09dd53bbb4190f'
235 );
236
237 INSERT INTO certificate_identity (
238 certificate, identity
239 ) VALUES (
240 1, 1
241 );
242
243 INSERT INTO certificate_identity (
244 certificate, identity
245 ) VALUES (
246 1, 2
247 );
248
249 /* Shared Secrets */
250
251 INSERT INTO shared_secrets (
252 type, data
253 ) VALUES (
254 1, X'16964066a10de938bdb2ab7864fe4459cab1'
255 );
256
257 INSERT INTO shared_secret_identity (
258 shared_secret, identity
259 ) VALUES (
260 1, 3
261 );
262
263 INSERT INTO shared_secret_identity (
264 shared_secret, identity
265 ) VALUES (
266 1, 4
267 );
268
269 /* Configurations */
270
271 INSERT INTO ike_configs (
272 local, remote
273 ) VALUES (
274 'PH_IP_CAROL', 'PH_IP_MOON'
275 );
276
277 INSERT INTO peer_configs (
278 name, ike_cfg, local_id, remote_id, auth_method
279 ) VALUES (
280 'home', 1, 3, 4, 2
281 );
282
283 INSERT INTO child_configs (
284 name, updown
285 ) VALUES (
286 'home', 'ipsec _updown iptables'
287 );
288
289 INSERT INTO peer_config_child_config (
290 peer_cfg, child_cfg
291 ) VALUES (
292 1, 1
293 );
294
295 INSERT INTO traffic_selectors (
296 type, start_addr, end_addr
297 ) VALUES ( /* 10.1.0.0/16 */
298 7, X'0a010000', X'0a01ffff'
299 );
300
301 INSERT INTO traffic_selectors (
302 type
303 ) VALUES ( /* dynamic/32 */
304 7
305 );
306
307 INSERT INTO child_config_traffic_selector (
308 child_cfg, traffic_selector, kind
309 ) VALUES (
310 1, 1, 1
311 );
312
313 INSERT INTO child_config_traffic_selector (
314 child_cfg, traffic_selector, kind
315 ) VALUES (
316 1, 2, 2
317 );
318