You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
268 lines
9.8 KiB
268 lines
9.8 KiB
-- vi: et ai ts=2
|
|
--
|
|
-- Warning: postgresql >= 9.1 is required for the 'CREATE TABLE IF NOT EXISTS'
|
|
--
|
|
-- NOTE : - we could / should use types cidr / inet / macaddr for IP ? (see http://www.postgresql.org/docs/8.2/static/datatype-net-types.html)
|
|
-- - ON UPDATE is not supported ?
|
|
-- - type 'integer' is used (we have to check for overflows ..)
|
|
-- - type 'datetime' has been replaced by 'timestamp'
|
|
|
|
--
|
|
-- conntrack
|
|
--
|
|
CREATE TABLE IF NOT EXISTS ct_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
ct_id bigint NOT NULL,
|
|
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 NULL,
|
|
orig_raw_pktcount bigint default NULL,
|
|
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 NULL,
|
|
reply_raw_pktcount bigint default NULL,
|
|
icmp_code smallint default NULL,
|
|
icmp_type smallint default NULL,
|
|
ct_mark bigint default NULL,
|
|
flow_start timestamp default NULL,
|
|
flow_end timestamp default NULL,
|
|
ct_event smallint default NULL
|
|
);
|
|
|
|
-- Table used to build the argument list passed by ulog
|
|
-- It contains the flow_{start, end}_{sec, usec} variables
|
|
-- instead of the timestamps
|
|
DROP TABLE IF EXISTS ulog2_ct_model;
|
|
CREATE TABLE ulog2_ct_model (
|
|
ct_id bigint PRIMARY KEY UNIQUE NOT NULL,
|
|
oob_family smallint,
|
|
orig_ip_saddr_str inet,
|
|
orig_ip_daddr_str inet,
|
|
orig_ip_protocol smallint,
|
|
orig_l4_sport integer,
|
|
orig_l4_dport integer,
|
|
orig_raw_pktlen bigint,
|
|
orig_raw_pktcount bigint,
|
|
reply_ip_saddr_str inet,
|
|
reply_ip_daddr_str inet,
|
|
reply_ip_protocol smallint,
|
|
reply_l4_sport integer,
|
|
reply_l4_dport integer,
|
|
reply_raw_pktlen bigint,
|
|
reply_raw_pktcount bigint,
|
|
icmp_code smallint,
|
|
icmp_type smallint,
|
|
ct_mark bigint,
|
|
flow_start_sec bigint,
|
|
flow_start_usec bigint,
|
|
flow_end_sec bigint,
|
|
flow_end_usec bigint,
|
|
ct_event smallint
|
|
);
|
|
|
|
--
|
|
-- Additional INDEX
|
|
--
|
|
|
|
CREATE INDEX IF NOT EXISTS ct_logs_ct_id ON ct_logs(ct_id);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_orig_ip_saddr ON ct_logs(orig_ip_saddr_str);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_orig_ip_daddr ON ct_logs(orig_ip_daddr_str);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_reply_ip_saddr ON ct_logs(reply_ip_saddr_str);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_reply_ip_daddr ON ct_logs(reply_ip_daddr_str);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_orig_l4_sport ON ct_logs(orig_l4_sport);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_orig_l4_dport ON ct_logs(orig_l4_dport);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_reply_l4_sport ON ct_logs(reply_l4_sport);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_reply_l4_dport ON ct_logs(reply_l4_dport);
|
|
-- CREATE INDEX IF NOT EXISTS ct_logs_event ON ct_logs(ct_event);
|
|
|
|
--
|
|
-- Helper tables
|
|
--
|
|
|
|
DROP TABLE IF EXISTS ip_proto CASCADE;
|
|
CREATE TABLE ip_proto (
|
|
proto_id serial PRIMARY KEY UNIQUE NOT NULL,
|
|
proto_name varchar(16) default NULL,
|
|
proto_desc varchar(255) default NULL
|
|
);
|
|
-- 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');
|
|
|
|
DROP TABLE IF EXISTS ct_event CASCADE;
|
|
CREATE TABLE ct_event (
|
|
event_id serial PRIMARY KEY UNIQUE NOT NULL,
|
|
event_name varchar(16) default NULL
|
|
);
|
|
INSERT INTO ct_event (event_id,event_name) VALUES
|
|
(1,'NEW'),
|
|
(2,'UPDATE'),
|
|
(4,'DESTROY');
|
|
|
|
--
|
|
-- Views
|
|
--
|
|
|
|
CREATE OR REPLACE VIEW view_log AS
|
|
SELECT
|
|
id,
|
|
ct_id,
|
|
flow_start,
|
|
flow_end,
|
|
orig_ip_saddr_str as origin_ip_saddr,
|
|
orig_ip_daddr_str as origin_ip_daddr,
|
|
orig_ip_protocol,
|
|
ip_proto1.proto_name as orig_ip_proto_name,
|
|
orig_l4_sport,
|
|
orig_l4_dport,
|
|
orig_raw_pktlen as orig_bytes,
|
|
orig_raw_pktcount as orig_pktcount,
|
|
reply_ip_saddr_str as reply_ip_saddr,
|
|
reply_ip_daddr_str as reply_ip_daddr,
|
|
reply_ip_protocol,
|
|
ip_proto2.proto_name as reply_ip_proto_name,
|
|
reply_l4_sport,
|
|
reply_l4_dport,
|
|
reply_raw_pktlen as reply_bytes,
|
|
reply_raw_pktcount as reply_pktcount,
|
|
icmp_code,
|
|
icmp_type,
|
|
ct_event,
|
|
event_name
|
|
FROM ct_logs
|
|
LEFT JOIN ip_proto ip_proto1 ON ct_logs.orig_ip_protocol = ip_proto1.proto_id
|
|
LEFT JOIN ip_proto ip_proto2 ON ct_logs.reply_ip_protocol = ip_proto2.proto_id
|
|
LEFT JOIN ct_event ON ct_logs.ct_event = ct_event.event_id;
|
|
|
|
|
|
--
|
|
-- Procedures
|
|
--
|
|
|
|
CREATE OR REPLACE FUNCTION INSERT_CT(
|
|
IN _ct_id bigint,
|
|
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
|
|
INSERT INTO ct_logs (ct_id, 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, flow_end, ct_event)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,
|
|
to_timestamp($20) + $21 * interval '1 microsecond',
|
|
to_timestamp($22) + $23 * interval '1 microsecond',
|
|
$24);
|
|
_id = $1;
|
|
RETURN _id;
|
|
END
|
|
$$ LANGUAGE plpgsql SECURITY INVOKER;
|
|
|
|
CREATE OR REPLACE FUNCTION INSERT_OR_REPLACE_CT(
|
|
IN _ct_id bigint,
|
|
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 ct_logs SET (orig_raw_pktlen, orig_raw_pktcount,
|
|
reply_raw_pktlen, reply_raw_pktcount,
|
|
ct_mark, flow_end, ct_event)
|
|
= ($8,$9,$15,$16,$19,to_timestamp($22) + $23 * interval '1 microsecond',$24)
|
|
WHERE ct_id=$1 AND oob_family=$2 AND orig_ip_saddr_str = $3
|
|
AND orig_ip_daddr_str = $4 AND orig_ip_protocol = $5
|
|
AND reply_ip_saddr_str = $10 AND reply_ip_daddr_str = $11
|
|
AND reply_ip_protocol = $12
|
|
AND icmp_code = $17 AND icmp_type = $18
|
|
AND ct_event < 4;
|
|
ELSE
|
|
UPDATE ct_logs SET (orig_raw_pktlen, orig_raw_pktcount,
|
|
reply_raw_pktlen, reply_raw_pktcount,
|
|
ct_mark, flow_end, ct_event)
|
|
= ($8,$9,$15,$16,$19,to_timestamp($22) + $23 * interval '1 microsecond',$24)
|
|
WHERE ct_id=$1 AND oob_family=$2 AND orig_ip_saddr_str = $3
|
|
AND orig_ip_daddr_str = $4 AND orig_ip_protocol = $5
|
|
AND orig_l4_sport = $6 AND orig_l4_dport = $7
|
|
AND reply_ip_saddr_str = $10 AND reply_ip_daddr_str = $11
|
|
AND reply_ip_protocol = $12 AND reply_l4_sport = $13
|
|
AND reply_l4_dport = $14
|
|
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,$24);
|
|
END IF;
|
|
RETURN _id;
|
|
END
|
|
$$ LANGUAGE plpgsql SECURITY INVOKER;
|
|
|
|
|
|
-- Created by Pierre Chifflier <chifflier AT inl DOT fr>
|
|
-- Adapted by Thomas Chevalier <contact AT tchevalier DOT fr>
|