included pools and leases tables to ipsec.sql database
[strongswan.git] / testing / tests / sql / rw-psk-ipv6 / hosts / moon / 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 ( /* fec0::1 */
208 5 , X'fec00000000000000000000000000001'
209 );
210
211 INSERT INTO identities (
212 type, data
213 ) VALUES ( /* fec0::10 */
214 5 , X'fec00000000000000000000000000010'
215 );
216
217 INSERT INTO identities (
218 type, data
219 ) VALUES ( /* fec0::20 */
220 5 , X'fec00000000000000000000000000020'
221 );
222
223 INSERT INTO identities (
224 type, data
225 ) VALUES ( /* %any */
226 0, '%any'
227 );
228
229 /* Shared Secrets */
230
231 INSERT INTO shared_secrets (
232 type, data
233 ) VALUES (
234 1, X'16964066a10de938bdb2ab7864fe4459cab1'
235 );
236
237 INSERT INTO shared_secrets (
238 type, data
239 ) VALUES (
240 1, X'8d5cce342174da772c8224a59885deaa118d'
241 );
242
243 INSERT INTO shared_secret_identity (
244 shared_secret, identity
245 ) VALUES (
246 1, 1
247 );
248
249 INSERT INTO shared_secret_identity (
250 shared_secret, identity
251 ) VALUES (
252 1, 2
253 );
254
255 INSERT INTO shared_secret_identity (
256 shared_secret, identity
257 ) VALUES (
258 2, 1
259 );
260
261 INSERT INTO shared_secret_identity (
262 shared_secret, identity
263 ) VALUES (
264 2, 3
265 );
266
267 /* Configurations */
268
269 INSERT INTO ike_configs (
270 local, remote
271 ) VALUES (
272 'PH_IP6_MOON', '0::0'
273 );
274
275 INSERT INTO peer_configs (
276 name, ike_cfg, local_id, remote_id, auth_method
277 ) VALUES (
278 'rw', 1, 1, 4, 2
279 );
280
281 INSERT INTO child_configs (
282 name, updown
283 ) VALUES (
284 'rw', 'ipsec _updown iptables'
285 );
286
287 INSERT INTO peer_config_child_config (
288 peer_cfg, child_cfg
289 ) VALUES (
290 1, 1
291 );
292
293 INSERT INTO traffic_selectors (
294 type, start_addr, end_addr
295 ) VALUES ( /* fec1::/16 */
296 8, X'fec10000000000000000000000000000', X'fec1ffffffffffffffffffffffffffff'
297 );
298
299 INSERT INTO traffic_selectors (
300 type
301 ) VALUES ( /* dynamic/128 */
302 8
303 );
304
305 INSERT INTO child_config_traffic_selector (
306 child_cfg, traffic_selector, kind
307 ) VALUES (
308 1, 1, 0
309 );
310
311 INSERT INTO child_config_traffic_selector (
312 child_cfg, traffic_selector, kind
313 ) VALUES (
314 1, 2, 3
315 );
316