-- 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 -- Adapted by Thomas Chevalier