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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
|
-- vi: et ai ts=2
--
-- Warning: postgresql >= 8.2 is required for the 'DROP .. IF EXISTS'
-- Warning: this script DESTROYS EVERYTHING !
--
DROP TABLE IF EXISTS _format;
CREATE TABLE _format (
version integer
) WITH (OIDS=FALSE);
INSERT INTO _format (version) VALUES (1);
-- this table could be used to know which user-defined tables are linked
-- to ulog
DROP TABLE IF EXISTS _extensions;
CREATE TABLE _extensions (
ext_id serial PRIMARY KEY UNIQUE NOT NULL,
ext_name varchar(64) NOT NULL,
table_name varchar(64) NOT NULL,
join_name varchar(64) NOT NULL
) WITH (OIDS=FALSE);
DROP TABLE IF EXISTS nufw CASCADE;
DROP TABLE IF EXISTS ulog2_ct CASCADE;
DROP TABLE IF EXISTS ulog2 CASCADE;
DROP SEQUENCE IF EXISTS ulog2__id_seq;
CREATE SEQUENCE ulog2__id_seq;
CREATE TABLE ulog2 (
_id bigint PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('ulog2__id_seq'),
oob_time_sec integer default NULL,
oob_time_usec integer default NULL,
oob_hook smallint default NULL,
oob_prefix varchar(32) default NULL,
oob_mark integer default NULL,
oob_in varchar(32) default NULL,
oob_out varchar(32) default NULL,
oob_family smallint default NULL,
ip_saddr_str inet default NULL,
ip_daddr_str inet default NULL,
ip_protocol smallint default NULL,
ip_tos smallint default NULL,
ip_ttl smallint default NULL,
ip_totlen integer default NULL,
ip_ihl smallint default NULL,
ip_csum integer default NULL,
ip_id integer default NULL,
ip_fragoff smallint default NULL,
ip6_payloadlen bigint default NULL,
ip6_priority smallint default NULL,
ip6_hoplimit smallint default NULL,
ip6_flowlabel bigint default NULL,
ip6_fragoff integer default NULL,
ip6_fragid bigint default NULL,
raw_label smallint default NULL,
-- timestamp timestamp NOT NULL default 'now',
mac_saddr_str macaddr default NULL,
mac_daddr_str macaddr default NULL,
oob_protocol integer default NULL,
raw_type integer default NULL,
mac_str varchar(256) default NULL,
tcp_sport integer default NULL,
tcp_dport integer default NULL,
tcp_seq bigint default NULL,
tcp_ackseq bigint default NULL,
tcp_window integer default NULL,
tcp_urg boolean default NULL,
tcp_urgp integer default NULL,
tcp_ack boolean default NULL,
tcp_psh boolean default NULL,
tcp_rst boolean default NULL,
tcp_syn boolean default NULL,
tcp_fin boolean default NULL,
udp_sport integer default NULL,
udp_dport integer default NULL,
udp_len smallint default NULL,
sctp_sport integer default NULL,
sctp_dport integer default NULL,
sctp_csum smallint default NULL,
icmp_type smallint default NULL,
icmp_code smallint default NULL,
icmp_echoid integer default NULL,
icmp_echoseq integer default NULL,
icmp_gateway integer default NULL,
icmp_fragmtu smallint default NULL,
icmpv6_type smallint default NULL,
icmpv6_code smallint default NULL,
icmpv6_echoid integer default NULL,
icmpv6_echoseq integer default NULL,
icmpv6_csum integer default NULL
) WITH (OIDS=FALSE);
CREATE INDEX ulog2_oob_family ON ulog2(oob_family);
CREATE INDEX ulog2_ip_saddr ON ulog2(ip_saddr_str);
CREATE INDEX ulog2_ip_daddr ON ulog2(ip_daddr_str);
-- CREATE INDEX ulog2_timestamp ON ulog2(timestamp);
CREATE INDEX mac_saddr ON ulog2(mac_saddr_str);
CREATE INDEX mac_daddr ON ulog2(mac_daddr_str);
CREATE INDEX tcp_sport ON ulog2(tcp_sport);
CREATE INDEX tcp_dport ON ulog2(tcp_dport);
CREATE INDEX udp_sport ON ulog2(udp_sport);
CREATE INDEX udp_dport ON ulog2(udp_dport);
CREATE INDEX sctp_sport ON ulog2(sctp_sport);
CREATE INDEX sctp_dport ON ulog2(sctp_dport);
--
-- VIEWS
--
CREATE OR REPLACE VIEW view_tcp AS
SELECT * FROM ulog2 WHERE ulog2.ip_protocol = 6;
CREATE OR REPLACE VIEW view_udp AS
SELECT * FROM ulog2 WHERE ulog2.ip_protocol = 17;
CREATE OR REPLACE VIEW view_icmp AS
SELECT * FROM ulog2 WHERE ulog2.ip_protocol = 1;
CREATE OR REPLACE VIEW view_icmpv6 AS
SELECT * FROM ulog2 WHERE ulog2.ip_protocol = 58;
-- complete view
CREATE OR REPLACE VIEW ulog AS
SELECT _id,
oob_time_sec,
oob_time_usec,
oob_hook,
oob_prefix,
oob_mark,
oob_in,
oob_out,
oob_family,
ip_saddr_str,
ip_daddr_str,
ip_protocol,
ip_tos,
ip_ttl,
ip_totlen,
ip_ihl,
ip_csum,
ip_id,
ip_fragoff,
ip6_payloadlen,
ip6_priority,
ip6_hoplimit,
ip6_flowlabel,
ip6_fragoff,
ip6_fragid,
tcp_sport,
tcp_dport,
tcp_seq,
tcp_ackseq,
tcp_window,
tcp_urg,
tcp_urgp,
tcp_ack,
tcp_psh,
tcp_rst,
tcp_syn,
tcp_fin,
udp_sport,
udp_dport,
udp_len,
icmp_type,
icmp_code,
icmp_echoid,
icmp_echoseq,
icmp_gateway,
icmp_fragmtu,
icmpv6_type,
icmpv6_code,
icmpv6_echoid,
icmpv6_echoseq,
icmpv6_csum,
raw_type,
mac_str,
mac_saddr_str,
mac_daddr_str,
oob_protocol,
raw_label,
sctp_sport,
sctp_dport,
sctp_csum
FROM ulog2;
-- shortcuts
CREATE OR REPLACE VIEW view_tcp_quad AS
SELECT _id,ip_saddr_str,tcp_sport,ip_daddr_str,tcp_dport FROM ulog2 WHERE ulog2.ip_protocol = 6;
CREATE OR REPLACE VIEW view_udp_quad AS
SELECT _id,ip_saddr_str,udp_sport,ip_daddr_str,udp_dport FROM ulog2 WHERE ulog2.ip_protocol = 17;
--
-- conntrack
--
DROP SEQUENCE IF EXISTS ulog2_ct__ct_id_seq;
CREATE SEQUENCE ulog2_ct__ct_id_seq;
CREATE TABLE ulog2_ct (
_ct_id bigint PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('ulog2_ct__ct_id_seq'),
oob_family smallint default NULL,
orig_ip_saddr_str inet default NULL,
orig_ip_daddr_str inet default NULL,
orig_ip_protocol smallint default NULL,
orig_l4_sport integer default NULL,
orig_l4_dport integer default NULL,
orig_raw_pktlen bigint default 0,
orig_raw_pktcount bigint default 0,
reply_ip_saddr_str inet default NULL,
reply_ip_daddr_str inet default NULL,
reply_ip_protocol smallint default NULL,
reply_l4_sport integer default NULL,
reply_l4_dport integer default NULL,
reply_raw_pktlen bigint default 0,
reply_raw_pktcount bigint default 0,
icmp_code smallint default NULL,
icmp_type smallint default NULL,
ct_mark bigint default 0,
flow_start_sec bigint default 0,
flow_start_usec bigint default 0,
flow_end_sec bigint default 0,
flow_end_usec bigint default 0,
ct_event smallint default 0
) WITH (OIDS=FALSE);
CREATE INDEX ulog2_ct_oob_family ON ulog2_ct(oob_family);
CREATE INDEX ulog2_ct_orig_ip_saddr ON ulog2_ct(orig_ip_saddr_str);
CREATE INDEX ulog2_ct_orig_ip_daddr ON ulog2_ct(orig_ip_daddr_str);
CREATE INDEX ulog2_ct_reply_ip_saddr ON ulog2_ct(reply_ip_saddr_str);
CREATE INDEX ulog2_ct_reply_ip_daddr ON ulog2_ct(reply_ip_daddr_str);
CREATE INDEX ulog2_ct_orig_l4_sport ON ulog2_ct(orig_l4_sport);
CREATE INDEX ulog2_ct_orig_l4_dport ON ulog2_ct(orig_l4_dport);
CREATE INDEX ulog2_ct_reply_l4_sport ON ulog2_ct(reply_l4_sport);
CREATE INDEX ulog2_ct_reply_l4_dport ON ulog2_ct(reply_l4_dport);
CREATE INDEX ulog2_ct_event ON ulog2_ct(ct_event);
--
-- Helper table
--
DROP TABLE IF EXISTS ip_proto;
CREATE TABLE ip_proto (
_proto_id serial PRIMARY KEY UNIQUE NOT NULL,
proto_name varchar(16) default NULL,
proto_desc varchar(255) default NULL
) WITH (OIDS=FALSE);
-- see files /etc/protocols
-- or /usr/share/nmap/nmap-protocols
INSERT INTO ip_proto (_proto_id,proto_name,proto_desc) VALUES
(0,'ip','internet protocol, pseudo protocol number'),
(1,'icmp','internet control message protocol'),
(2,'igmp','Internet Group Management'),
(3,'ggp','gateway-gateway protocol'),
(4,'ipencap',E'IP encapsulated in IP (officially \'IP\')'),
(5,'st','ST datagram mode'),
(6,'tcp','transmission control protocol'),
(17,'udp','user datagram protocol'),
(41,'ipv6','Internet Protocol, version 6'),
(132,'sctp','Stream Control Transmission Protocol'),
(58,'ipv6-icmp','ICMP for IPv6');
--
-- NuFW specific
--
DROP TABLE IF EXISTS nufw;
CREATE TABLE nufw (
_nufw_id bigint PRIMARY KEY UNIQUE NOT NULL,
username varchar(30) default NULL,
user_id integer default NULL,
client_os varchar(100) default NULL,
client_app varchar(256) default NULL
) WITH (OIDS=FALSE);
CREATE INDEX nufw_user_id ON nufw(user_id);
ALTER TABLE nufw ADD CONSTRAINT nufw_id_fk FOREIGN KEY (_nufw_id) REFERENCES ulog2(_id);
CREATE OR REPLACE VIEW view_nufw AS
SELECT * FROM ulog2 INNER JOIN nufw ON ulog2._id = nufw._nufw_id;
INSERT INTO _extensions (ext_name,table_name,join_name) VALUES
('nufw','nufw','_nufw_id');
--
-- Procedures
--
CREATE OR REPLACE FUNCTION INSERT_CT(
IN _oob_family integer,
IN _orig_ip_saddr inet,
IN _orig_ip_daddr inet,
IN _orig_ip_protocol integer,
IN _orig_l4_sport integer,
IN _orig_l4_dport integer,
IN _orig_raw_pktlen bigint,
IN _orig_raw_pktcount bigint,
IN _reply_ip_saddr inet,
IN _reply_ip_daddr inet,
IN _reply_ip_protocol integer,
IN _reply_l4_sport integer,
IN _reply_l4_dport integer,
IN _reply_raw_pktlen bigint,
IN _reply_raw_pktcount bigint,
IN _icmp_code integer,
IN _icmp_type integer,
IN _ct_mark bigint,
IN _flow_start_sec bigint,
IN _flow_start_usec bigint,
IN _flow_end_sec bigint,
IN _flow_end_usec bigint,
IN _ct_event integer
)
RETURNS bigint AS $$
INSERT INTO ulog2_ct (oob_family, orig_ip_saddr_str, orig_ip_daddr_str, orig_ip_protocol,
orig_l4_sport, orig_l4_dport, orig_raw_pktlen, orig_raw_pktcount,
reply_ip_saddr_str, reply_ip_daddr_str, reply_ip_protocol,
reply_l4_sport, reply_l4_dport, reply_raw_pktlen, reply_raw_pktcount,
icmp_code, icmp_type, ct_mark,
flow_start_sec, flow_start_usec,
flow_end_sec, flow_end_usec, ct_event)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23);
SELECT currval('ulog2_ct__ct_id_seq');
$$ LANGUAGE SQL SECURITY INVOKER;
CREATE OR REPLACE FUNCTION INSERT_OR_REPLACE_CT(
IN _oob_family integer,
IN _orig_ip_saddr inet,
IN _orig_ip_daddr inet,
IN _orig_ip_protocol integer,
IN _orig_l4_sport integer,
IN _orig_l4_dport integer,
IN _orig_raw_pktlen bigint,
IN _orig_raw_pktcount bigint,
IN _reply_ip_saddr inet,
IN _reply_ip_daddr inet,
IN _reply_ip_protocol integer,
IN _reply_l4_sport integer,
IN _reply_l4_dport integer,
IN _reply_raw_pktlen bigint,
IN _reply_raw_pktcount bigint,
IN _icmp_code integer,
IN _icmp_type integer,
IN _ct_mark bigint,
IN _flow_start_sec bigint,
IN _flow_start_usec bigint,
IN _flow_end_sec bigint,
IN _flow_end_usec bigint,
IN _ct_event integer
)
RETURNS bigint AS $$
DECLARE
_id bigint;
BEGIN
IF (_ct_event = 4) THEN
if (_orig_ip_protocol = 1) THEN
UPDATE ulog2_ct SET (orig_raw_pktlen, orig_raw_pktcount,
reply_raw_pktlen, reply_raw_pktcount,
ct_mark, flow_end_sec, flow_end_usec, ct_event)
= ($7,$8,$14,$15,$18,$21,$22,$23)
WHERE oob_family=$1 AND orig_ip_saddr_str = $2
AND orig_ip_daddr_str = $3 AND orig_ip_protocol = $4
AND reply_ip_saddr_str = $9 AND reply_ip_daddr_str = $10
AND reply_ip_protocol = $11
AND icmp_code = $16 AND icmp_type = $17
AND ct_event < 4;
ELSE
UPDATE ulog2_ct SET (orig_raw_pktlen, orig_raw_pktcount,
reply_raw_pktlen, reply_raw_pktcount,
ct_mark, flow_end_sec, flow_end_usec, ct_event)
= ($7,$8,$14,$15,$18,$21,$22,$23)
WHERE oob_family=$1 AND orig_ip_saddr_str = $2
AND orig_ip_daddr_str = $3 AND orig_ip_protocol = $4
AND orig_l4_sport = $5 AND orig_l4_dport = $6
AND reply_ip_saddr_str = $9 AND reply_ip_daddr_str = $10
AND reply_ip_protocol = $11 AND reply_l4_sport = $12
AND reply_l4_dport = $13
AND ct_event < 4;
END IF;
ELSE
_id := INSERT_CT($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23);
END IF;
RETURN _id;
END
$$ LANGUAGE plpgsql SECURITY INVOKER;
CREATE OR REPLACE FUNCTION DELETE_PACKET(
IN _packet_id bigint
)
RETURNS void AS $$
DELETE FROM ulog2 WHERE ulog2._id = $1;
$$ LANGUAGE SQL SECURITY INVOKER;
CREATE OR REPLACE FUNCTION DELETE_CT_FLOW(
IN _ct_packet_id bigint
)
RETURNS void AS $$
DELETE FROM ulog2_ct WHERE ulog2_ct._ct_id = $1;
$$ LANGUAGE SQL SECURITY INVOKER;
-- Pierre Chifflier <chifflier AT inl DOT fr>
|
