Database update: add label, update views and conntrack

Database update: add label, update views and conntrack

[Pierre Chifflier]

This patchset adds support for the 'label' option, converts INSERT_CT
to a function for consistency, and adds a function to the MySQL schema
to display binary addresses as strings.
It also adds the INSERT_CT function to the PostgreSQL schema.

[ULOGD PATCH 1/4] Convert INSERT_CT to a function, returning the id (MySQL)

[Pierre Chifflier]

Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
:100644 100644 ed8d982... 00017be... M	doc/mysql-ulogd2.sql
 doc/mysql-ulogd2.sql |   56 +++++++++++++++++++++++++------------------------
 1 files changed, 29 insertions(+), 27 deletions(-)

diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index ed8d982..00017be 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -672,46 +672,48 @@ END
 $$
 
 delimiter $$
-DROP PROCEDURE IF EXISTS INSERT_CT;
-CREATE PROCEDURE INSERT_CT(
-		IN `_orig_ip_saddr` binary(16),
-		IN `_orig_ip_daddr` binary(16),
-		IN `_orig_ip_protocol` tinyint(3) unsigned,
-		IN `_orig_l4_sport` int(5),
-		IN `_orig_l4_dport` int(5),
-		IN `_orig_bytes` bigint,
-		IN `_orig_packets` bigint,
-		IN `_reply_ip_saddr` binary(16),
-		IN `_reply_ip_daddr` binary(16),
-		IN `_reply_ip_protocol` tinyint(3) unsigned,
-		IN `_reply_l4_sport` int(5),
-		IN `_reply_l4_dport` int(5),
-		IN `_reply_bytes` bigint,
-		IN `_reply_packets` bigint,
-		IN `_icmp_code` tinyint(3),
-		IN `_icmp_type` tinyint(3),
-		IN `_ct_mark` bigint,
-		IN `_flow_start_sec` int(10),
-		IN `_flow_start_usec` int(10),
-		IN `_flow_end_sec` int(10),
-		IN `_flow_end_usec` int(10)
-		)
+DROP FUNCTION IF EXISTS INSERT_CT;
+CREATE FUNCTION INSERT_CT(
+		`_oob_family` bigint,
+		`_orig_ip_saddr` binary(16),
+		`_orig_ip_daddr` binary(16),
+		`_orig_ip_protocol` tinyint(3) unsigned,
+		`_orig_l4_sport` int(5),
+		`_orig_l4_dport` int(5),
+		`_orig_bytes` bigint,
+		`_orig_packets` bigint,
+		`_reply_ip_saddr` binary(16),
+		`_reply_ip_daddr` binary(16),
+		`_reply_ip_protocol` tinyint(3) unsigned,
+		`_reply_l4_sport` int(5),
+		`_reply_l4_dport` int(5),
+		`_reply_bytes` bigint,
+		`_reply_packets` bigint,
+		`_icmp_code` tinyint(3),
+		`_icmp_type` tinyint(3),
+		`_ct_mark` bigint,
+		`_flow_start_sec` int(10),
+		`_flow_start_usec` int(10),
+		`_flow_end_sec` int(10),
+		`_flow_end_usec` int(10)
+		) RETURNS bigint unsigned
+READS SQL DATA
 BEGIN
-	INSERT INTO ulog2_ct (orig_ip_saddr, orig_ip_daddr, orig_ip_protocol,
+	INSERT INTO ulog2_ct (oob_family, orig_ip_saddr, orig_ip_daddr, orig_ip_protocol,
 		orig_l4_sport, orig_l4_dport, orig_bytes, orig_packets,
 		reply_ip_saddr, reply_ip_daddr, reply_ip_protocol,
 		reply_l4_sport, reply_l4_dport, reply_bytes, reply_packets,
 		icmp_code, icmp_type, ct_mark, 
 		flow_start_sec, flow_start_usec,
 		flow_end_sec, flow_end_usec)
- 	VALUES (_orig_ip_saddr, _orig_ip_daddr, _orig_ip_protocol,
+ 	VALUES (_oob_family, _orig_ip_saddr, _orig_ip_daddr, _orig_ip_protocol,
 		_orig_l4_sport, _orig_l4_dport, _orig_bytes, _orig_packets,
 		_reply_ip_saddr, _reply_ip_daddr, _reply_ip_protocol,
 		_reply_l4_sport, _reply_l4_dport, _reply_bytes, _reply_packets,
 		_icmp_code, _icmp_type, _ct_mark,
 		_flow_start_sec, _flow_start_usec,
 		_flow_end_sec, _flow_end_usec);
-
+	RETURN LAST_INSERT_ID();
 END
 $$
 
-- 
1.5.5

Re: [ULOGD PATCH 1/4] Convert INSERT_CT to a function, returning the id (MySQL)

[Pablo Neira Ayuso]

Pierre Chifflier wrote:
> Signed-off-by: Pierre Chifflier <chifflier@inl.fr>

Applied. Thanks.

-- 
"Los honestos son inadaptados sociales" -- Les Luthiers

[ULOGD PATCH 2/4] Add label option (SQL part)

[Pierre Chifflier]

This patch adds support for "label" option in the SQL schema. For example, it
can be used by another module to determine if the packet has been dropped,
rejected or accepted.

Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
:100644 100644 00017be... bcd3ab8... M	doc/mysql-ulogd2.sql
:100644 100644 cfef267... 8e8c823... M	doc/pgsql-ulogd2.sql
 doc/mysql-ulogd2.sql |   16 ++++++++++------
 doc/pgsql-ulogd2.sql |   16 ++++++++++------
 2 files changed, 20 insertions(+), 12 deletions(-)

diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index 00017be..bcd3ab8 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -58,6 +58,7 @@ CREATE TABLE `ulog2` (
   `ip_csum` smallint(5) unsigned default NULL,
   `ip_id` smallint(5) unsigned default NULL,
   `ip_fragoff` smallint(5) unsigned default NULL,
+  `label` tinyint(3) unsigned default NULL,
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   UNIQUE KEY `key_id` (`_id`)
 ) ENGINE=INNODB COMMENT='Table for IP packets';
@@ -213,7 +214,8 @@ CREATE SQL SECURITY INVOKER VIEW `ulog` AS
 	icmpv6_echoseq,
 	icmpv6_csum,
 	mac_saddr as mac_saddr_str,
-	mac_protocol as oob_protocol
+	mac_protocol as oob_protocol,
+	label as raw_label
         FROM ulog2 LEFT JOIN tcp ON ulog2._id = tcp._tcp_id LEFT JOIN udp ON ulog2._id = udp._udp_id
                 LEFT JOIN icmp ON ulog2._id = icmp._icmp_id LEFT JOIN mac ON ulog2._id = mac._mac_id
                 LEFT JOIN icmpv6 ON ulog2._id = icmpv6._icmpv6_id;
@@ -458,7 +460,8 @@ CREATE FUNCTION INSERT_IP_PACKET_FULL(
 	  	_ip_ihl tinyint(3) unsigned,
 	  	_ip_csum smallint(5) unsigned,
 	  	_ip_id smallint(5) unsigned,
-	  	_ip_fragoff smallint(5) unsigned
+	  	_ip_fragoff smallint(5) unsigned,
+		_label tinyint(4) unsigned
 		) RETURNS int(10) unsigned
 SQL SECURITY INVOKER
 NOT DETERMINISTIC
@@ -466,10 +469,10 @@ READS SQL DATA
 BEGIN
 	INSERT INTO ulog2 (oob_time_sec, oob_time_usec, oob_hook, oob_prefix, oob_mark, oob_in, oob_out, oob_family,
 			   ip_saddr, ip_daddr, ip_protocol, ip_tos, ip_ttl, ip_totlen, ip_ihl,
-		 	   ip_csum, ip_id, ip_fragoff ) VALUES 
+		 	   ip_csum, ip_id, ip_fragoff, label ) VALUES 
 		(_oob_time_sec, _oob_time_usec, _oob_hook, _oob_prefix, _oob_mark, _oob_in, _oob_out, _oob_family,
 		 _ip_saddr, _ip_daddr, _ip_protocol, _ip_tos, _ip_ttl, _ip_totlen, _ip_ihl,
-		 _ip_csum, _ip_id, _ip_fragoff);
+		 _ip_csum, _ip_id, _ip_fragoff, _label);
 	RETURN LAST_INSERT_ID();
 END
 $$
@@ -625,7 +628,8 @@ CREATE FUNCTION INSERT_PACKET_FULL(
 		icmpv6_echoseq smallint(5) unsigned,
 		icmpv6_csum int(10) unsigned,
 		mac_saddr varchar(32),
-		mac_protocol smallint(5)
+		mac_protocol smallint(5),
+		_label tinyint(4) unsigned
 		) RETURNS bigint unsigned
 READS SQL DATA
 BEGIN
@@ -633,7 +637,7 @@ BEGIN
 					   _oob_mark, _oob_in, _oob_out, _oob_family, 
 					   _ip_saddr, _ip_daddr, _ip_protocol, _ip_tos,
 					   _ip_ttl, _ip_totlen, _ip_ihl, _ip_csum, _ip_id,
-					   _ip_fragoff);
+					   _ip_fragoff, _label);
 	IF _ip_protocol = 6 THEN
 		CALL PACKET_ADD_TCP_FULL(@lastid, tcp_sport, tcp_dport, tcp_seq, tcp_ackseq,
 					 tcp_window, tcp_urg, tcp_urgp, tcp_ack, tcp_psh,
diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index cfef267..8e8c823 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -57,6 +57,7 @@ CREATE TABLE ulog2 (
   ip_csum integer default NULL,
   ip_id integer default NULL,
   ip_fragoff smallint default NULL,
+  label smallint default NULL,
   timestamp timestamp NOT NULL default 'now'
 ) WITH (OIDS=FALSE);
 
@@ -191,7 +192,8 @@ CREATE OR REPLACE VIEW ulog AS
         icmpv6_echoseq,
         icmpv6_csum,
         mac_saddr AS mac_saddr_str,
-        mac_protocol AS oob_protocol
+        mac_protocol AS oob_protocol,
+        label AS raw_label
         FROM ulog2 LEFT JOIN tcp ON ulog2._id = tcp._tcp_id LEFT JOIN udp ON ulog2._id = udp._udp_id
                 LEFT JOIN icmp ON ulog2._id = icmp._icmp_id LEFT JOIN mac ON ulog2._id = mac._mac_id
                 LEFT JOIN icmpv6 ON ulog2._id = icmpv6._icmpv6_id;
@@ -360,13 +362,14 @@ CREATE OR REPLACE FUNCTION INSERT_IP_PACKET_FULL(
                 IN ip_ihl integer,
                 IN ip_csum integer,
                 IN ip_id integer,
-                IN ip_fragoff integer
+                IN ip_fragoff integer,
+                IN label integer
         )
 RETURNS bigint AS $$
         INSERT INTO ulog2 (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)
-                VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18);
+                        ip_tos,ip_ttl,ip_totlen,ip_ihl,ip_csum,ip_id,ip_fragoff,label)
+                VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19);
         SELECT currval('ulog2__id_seq');
 $$ LANGUAGE SQL SECURITY INVOKER;
 
@@ -492,13 +495,14 @@ CREATE OR REPLACE FUNCTION INSERT_PACKET_FULL(
                 IN icmpv6_echoseq integer,
                 IN icmpv6_csum integer,
                 IN mac_saddr varchar(32),
-                IN mac_protocol integer
+                IN mac_protocol integer,
+                IN label integer
         )
 RETURNS bigint AS $$
 DECLARE
         _id bigint;
 BEGIN
-        _id := INSERT_IP_PACKET_FULL($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18) ;
+        _id := INSERT_IP_PACKET_FULL($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$47);
         IF (ip_protocol = 6) THEN
                 PERFORM INSERT_TCP_FULL(_id,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30);
         ELSIF (ip_protocol = 17) THEN
-- 
1.5.5

Re: [ULOGD PATCH 2/4] Add label option (SQL part)

[Pablo Neira Ayuso]

Pierre Chifflier wrote:
> This patch adds support for "label" option in the SQL schema. For example, it
> can be used by another module to determine if the packet has been dropped,
> rejected or accepted.

I have kept this back until we discuss if it would be better to have a
string instead of an integer.

-- 
"Los honestos son inadaptados sociales" -- Les Luthiers

Re: [ULOGD PATCH 2/4] Add label option (SQL part)

[Anton]

Just IMHO,

String is more visual and understandable for human view, but 
for how much of the actual processing of the loged data 
done by hands? I suppose majority is somehow automated, 
scripted, etc. Integer allows much faster inserts, in-DB 
processing and lower traffic. I would vote for interger :)

Regards,
Anton.

On Sunday 27 April 2008 12:42, Pablo Neira Ayuso wrote:
> Pierre Chifflier wrote:
> > This patch adds support for "label" option in the SQL
> > schema. For example, it can be used by another module
> > to determine if the packet has been dropped, rejected
> > or accepted.
>
> I have kept this back until we discuss if it would be
> better to have a string instead of an integer.

Re: [ULOGD PATCH 2/4] Add label option (SQL part)

[Pablo Neira Ayuso]

Anton wrote:
> Just IMHO,
> 
> String is more visual and understandable for human view, but 
> for how much of the actual processing of the loged data 
> done by hands? I suppose majority is somehow automated, 
> scripted, etc. Integer allows much faster inserts, in-DB 
> processing and lower traffic. I would vote for interger :)

Indeed. The patch was applied yesterday with a minor revision (rename
label to numberic_label)

-- 
"Los honestos son inadaptados sociales" -- Les Luthiers

[ULOGD PATCH 3/4] Add function and some view to display IP addresses as strings (MySQL)

[Pierre Chifflier]

Introduce function to convert binary data to printable strings.
Update view_tcp_quad and view_udp_quad.

Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
:100644 100644 bcd3ab8... ba50f48... M	doc/mysql-ulogd2.sql
 doc/mysql-ulogd2.sql |   39 +++++++++++++++++++++++++++++++++++++--
 1 files changed, 37 insertions(+), 2 deletions(-)

diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index bcd3ab8..ba50f48 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -222,13 +222,48 @@ CREATE SQL SECURITY INVOKER VIEW `ulog` AS
 
 
 -- shortcuts
+DROP FUNCTION IF EXISTS BIN_TO_IPV6;
+delimiter $$
+CREATE FUNCTION BIN_TO_IPV6(
+		_in binary(16)
+                ) RETURNS varchar(64)
+SQL SECURITY INVOKER
+DETERMINISTIC
+COMMENT 'Convert binary ip to printable string'
+BEGIN
+	-- IPv4 address in IPv6 form
+	IF HEX(SUBSTRING(_in, 1, 12)) = '00000000000000000000FFFF' THEN
+		RETURN CONCAT(
+			'::ffff:',
+			ASCII(SUBSTRING(_in, 13, 1)), '.',
+			ASCII(SUBSTRING(_in, 14, 1)), '.',
+			ASCII(SUBSTRING(_in, 15, 1)), '.',
+			ASCII(SUBSTRING(_in, 16, 1))
+		);
+	END IF;
+	-- return the full IPv6 form
+	RETURN LOWER(CONCAT(
+		HEX(SUBSTRING(_in,  1, 2)), ':',
+		HEX(SUBSTRING(_in,  3, 2)), ':',
+		HEX(SUBSTRING(_in,  5, 2)), ':',
+		HEX(SUBSTRING(_in,  7, 2)), ':',
+		HEX(SUBSTRING(_in,  9, 2)), ':',
+		HEX(SUBSTRING(_in, 11, 2)), ':',
+		HEX(SUBSTRING(_in, 13, 2)), ':',
+		HEX(SUBSTRING(_in, 15, 2))
+	));
+END
+$$
+delimiter ;
+
+
 DROP VIEW IF EXISTS `view_tcp_quad`;
 CREATE SQL SECURITY INVOKER VIEW `view_tcp_quad` AS
-        SELECT ulog2._id,ulog2.ip_saddr,tcp.tcp_sport,ulog2.ip_daddr,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id;
+	SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,tcp.tcp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id;
 
 DROP VIEW IF EXISTS `view_udp_quad`;
 CREATE SQL SECURITY INVOKER VIEW `view_udp_quad` AS
-        SELECT ulog2._id,ulog2.ip_saddr,udp.udp_sport,ulog2.ip_daddr,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id;
+	SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,udp.udp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id;
 
 
 
-- 
1.5.5

Re: [ULOGD PATCH 3/4] Add function and some view to display IP addresses as strings (MySQL)

[Pablo Neira Ayuso]

Pierre Chifflier wrote:
> Introduce function to convert binary data to printable strings.
> Update view_tcp_quad and view_udp_quad.

This is nice. Applied. Thanks.

-- 
"Los honestos son inadaptados sociales" -- Les Luthiers

[ULOGD PATCH 4/4] Add function INSERT_CT for conntrack (PostgreSQL)

[Pierre Chifflier]

Update PostgreSQL schema to add the insert procedure for conntrack
connections.

Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
:100644 100644 8e8c823... cd2d911... M	doc/pgsql-ulogd2.sql
 doc/pgsql-ulogd2.sql |   48 ++++++++++++++++++++++++++++++++++++++++++++----
 1 files changed, 44 insertions(+), 4 deletions(-)

diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index 8e8c823..cd2d911 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -230,10 +230,10 @@ CREATE TABLE ulog2_ct (
   icmp_code smallint default NULL,
   icmp_type smallint default NULL,
   ct_mark bigint default 0,
-  flow_start_sec integer default 0,
-  flow_start_usec integer default 0,
-  flow_end_sec integer default 0,
-  flow_end_usec integer 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,
   state smallint default 0
 ) WITH (OIDS=FALSE);
 
@@ -522,6 +522,46 @@ $$ LANGUAGE plpgsql SECURITY INVOKER;
 
 
 
+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_bytes bigint,
+                IN _orig_packets 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_bytes bigint,
+                IN _reply_packets 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 _state smallint
+        )
+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_bytes, orig_packets,
+                        reply_ip_saddr_str, reply_ip_daddr_str, reply_ip_protocol,
+                        reply_l4_sport, reply_l4_dport, reply_bytes, reply_packets,
+                        icmp_code, icmp_type, ct_mark, 
+                        flow_start_sec, flow_start_usec,
+                        flow_end_sec, flow_end_usec, state)
+                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 DELETE_PACKET(
                 IN _packet_id bigint
         )
-- 
1.5.5

Re: [ULOGD PATCH 4/4] Add function INSERT_CT for conntrack (PostgreSQL)

[Pablo Neira Ayuso]

Pierre Chifflier wrote:
> Update PostgreSQL schema to add the insert procedure for conntrack
> connections.

Applied. Thanks Pierre.

-- 
"Los honestos son inadaptados sociales" -- Les Luthiers