* ULOGD2 PostgresSql errors
@ 2008-04-11 11:05 Anton
2008-04-11 19:33 ` Eric Leblond
0 siblings, 1 reply; 6+ messages in thread
From: Anton @ 2008-04-11 11:05 UTC (permalink / raw)
To: Pablo Neira Ayuso, Netfilter Developer Mailing List
Hello!
There is some misbehaviour of ULOGD2. I managed to log connection data to the DB - but while it's logging something - at the same time I see this
output in the ulogd.log - Looks abnormal.
The following I use to get logging.
RANGE="--nflog-range 100"
THRESHOLD="--nflog-threshold 10"
iptables -F INPUT
iptables -F OUTPUT
iptables -A INPUT -j NFLOG -s 82.198.21.17/32 --nflog-group 0 --nflog-prefix ANTON_IN $RANGE $THRESHOLD
iptables -A OUTPUT -j NFLOG -d 82.198.21.17/32 --nflog-group 0 --nflog-prefix ANTON_OUT $RANGE $THRESHOLD
Database is PGSQL 8.3
Kernel 2.6.24.4 with NFLOG
NFLOG libraries are the latest from the netfilter.org
ULOGD - Latest SVN (7450)
Iptables 1.4.0
Please let me know if you need any additional info.
ulogd.log output is below:
Fri Apr 11 16:00:10 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
Fri Apr 11 16:00:10 2008 <5> ulogd.c:488 error during propagate_results
Fri Apr 11 16:00:12 2008 <1> ulogd_output_PGSQL.c:99 SELECT nspname FROM pg_namespace n WHERE n.nspname='public'
Fri Apr 11 16:00:12 2008 <1> ulogd_output_PGSQL.c:109 using schema public
Fri Apr 11 16:00:12 2008 <7> ulogd_output_PGSQL.c:295 execute failed (ERROR: function insert_packet_full(unknown, unknown, integer,
unknown, integer, unknown, unknown, integer, unknown, unknown, integer, integer, integer, integer, integer, integer, integer, integ
er, integer, integer, integer, bigint, integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unk
nown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not
exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
)
Fri Apr 11 16:00:12 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
Fri Apr 11 16:00:12 2008 <5> ulogd.c:488 error during propagate_results
Fri Apr 11 16:00:14 2008 <1> ulogd_output_PGSQL.c:99 SELECT nspname FROM pg_namespace n WHERE n.nspname='public'
Fri Apr 11 16:00:14 2008 <1> ulogd_output_PGSQL.c:109 using schema public
Fri Apr 11 16:00:14 2008 <7> ulogd_output_PGSQL.c:295 execute failed (ERROR: function insert_packet_full(unknown, unknown, integer,
unknown, integer, unknown, unknown, integer, unknown, unknown, integer, integer, integer, integer, integer, integer, integer, integ
er, integer, integer, integer, bigint, integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unk
nown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not
exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
)
Fri Apr 11 16:00:14 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
Fri Apr 11 16:00:14 2008 <5> ulogd.c:488 error during propagate_results
Fri Apr 11 16:00:17 2008 <1> ulogd_output_PGSQL.c:99 SELECT nspname FROM pg_namespace n WHERE n.nspname='public'
Fri Apr 11 16:00:17 2008 <1> ulogd_output_PGSQL.c:109 using schema public
Fri Apr 11 16:00:17 2008 <7> ulogd_output_PGSQL.c:295 execute failed (ERROR: smallint out of range
CONTEXT: SQL function "insert_icmp" statement 1
SQL statement "SELECT INSERT_ICMP( $1 , $2 , $3 , $4 , $5 , $6 , $7 )"
PL/pgSQL function "insert_packet_full" line 10 at PERFORM
)
Fri Apr 11 16:00:17 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
Fri Apr 11 16:00:17 2008 <5> ulogd.c:488 error during propagate_results
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: ULOGD2 PostgresSql errors
2008-04-11 11:05 ULOGD2 PostgresSql errors Anton
@ 2008-04-11 19:33 ` Eric Leblond
2008-04-12 7:43 ` Anton
0 siblings, 1 reply; 6+ messages in thread
From: Eric Leblond @ 2008-04-11 19:33 UTC (permalink / raw)
To: Anton; +Cc: Pablo Neira Ayuso, Netfilter Developer Mailing List
[-- Attachment #1.1: Type: text/plain, Size: 2041 bytes --]
Hello,
On Friday, 2008 April 11 at 16:05:39 +0500, Anton wrote:
> Hello!
>
> ulogd.log output is below:
>
> Fri Apr 11 16:00:10 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
> Fri Apr 11 16:00:10 2008 <5> ulogd.c:488 error during propagate_results
> Fri Apr 11 16:00:12 2008 <1> ulogd_output_PGSQL.c:99 SELECT nspname FROM pg_namespace n WHERE n.nspname='public'
> Fri Apr 11 16:00:12 2008 <1> ulogd_output_PGSQL.c:109 using schema public
> Fri Apr 11 16:00:12 2008 <7> ulogd_output_PGSQL.c:295 execute failed (ERROR: function insert_packet_full(unknown, unknown, integer,
> unknown, integer, unknown, unknown, integer, unknown, unknown, integer, integer, integer, integer, integer, integer, integer, integ
> er, integer, integer, integer, bigint, integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unk
> nown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not
> exist at character 8
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> )
This one is already spotted, we will try to work on this next week.
> Fri Apr 11 16:00:14 2008 <7> ../../util/db.c:185 no connection to database, attempting to reconnect after 2 seconds
> Fri Apr 11 16:00:14 2008 <5> ulogd.c:488 error during propagate_results
> Fri Apr 11 16:00:17 2008 <1> ulogd_output_PGSQL.c:99 SELECT nspname FROM pg_namespace n WHERE n.nspname='public'
> Fri Apr 11 16:00:17 2008 <1> ulogd_output_PGSQL.c:109 using schema public
> Fri Apr 11 16:00:17 2008 <7> ulogd_output_PGSQL.c:295 execute failed (ERROR: smallint out of range
> CONTEXT: SQL function "insert_icmp" statement 1
> SQL statement "SELECT INSERT_ICMP( $1 , $2 , $3 , $4 , $5 , $6 , $7 )"
This one is new, could you try the attached patch ? It should fix this
last problem.
BR,
--
Eric Leblond
INL: http://www.inl.fr/
NuFW: http://www.nufw.org/
[-- Attachment #1.2: pgsql-smallint-hunting.diff --]
[-- Type: text/x-diff, Size: 1298 bytes --]
diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index fc4aa1b..a451b5e 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -112,8 +112,8 @@ CREATE TABLE icmp (
_icmp_id bigint PRIMARY KEY UNIQUE NOT NULL,
icmp_type smallint default NULL,
icmp_code smallint default NULL,
- icmp_echoid smallint default NULL,
- icmp_echoseq smallint default NULL,
+ icmp_echoid integer default NULL,
+ icmp_echoseq integer default NULL,
icmp_gateway integer default NULL,
icmp_fragmtu smallint default NULL
) WITH (OIDS=FALSE);
@@ -122,8 +122,8 @@ CREATE TABLE icmpv6 (
_icmpv6_id bigint PRIMARY KEY UNIQUE NOT NULL,
icmpv6_type smallint default NULL,
icmpv6_code smallint default NULL,
- icmpv6_echoid smallint default NULL,
- icmpv6_echoseq smallint default NULL,
+ icmpv6_echoid integer default NULL,
+ icmpv6_echoseq integer default NULL,
icmpv6_csum integer default NULL
) WITH (OIDS=FALSE);
@@ -284,7 +284,7 @@ DROP TABLE IF EXISTS nufw;
CREATE TABLE nufw (
_nufw_id bigint PRIMARY KEY UNIQUE NOT NULL,
username varchar(30) default NULL,
- user_id smallint default NULL,
+ user_id integer default NULL,
client_os varchar(100) default NULL,
client_app varchar(256) default NULL
) WITH (OIDS=FALSE);
[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]
^ permalink raw reply related [flat|nested] 6+ messages in thread
* Re: ULOGD2 PostgresSql errors
2008-04-11 19:33 ` Eric Leblond
@ 2008-04-12 7:43 ` Anton
2008-04-12 9:49 ` Eric Leblond
0 siblings, 1 reply; 6+ messages in thread
From: Anton @ 2008-04-12 7:43 UTC (permalink / raw)
To: Eric Leblond; +Cc: Pablo Neira Ayuso, Netfilter Developer Mailing List
Erik,
Seems it fixes ICMP inserts. Just for testing I tried MYSQL
logging too - it works without errors in log file, and
looks it logs more data than PGSQL.
Regards,
Anton.
On Saturday 12 April 2008 00:33, Eric Leblond wrote:
> Hello,
>
> On Friday, 2008 April 11 at 16:05:39 +0500, Anton wrote:
> > Hello!
> >
> > ulogd.log output is below:
> >
> > Fri Apr 11 16:00:10 2008 <7> ../../util/db.c:185 no
> > connection to database, attempting to reconnect after 2
> > seconds Fri Apr 11 16:00:10 2008 <5> ulogd.c:488 error
> > during propagate_results Fri Apr 11 16:00:12 2008 <1>
> > ulogd_output_PGSQL.c:99 SELECT nspname FROM
> > pg_namespace n WHERE n.nspname='public' Fri Apr 11
> > 16:00:12 2008 <1> ulogd_output_PGSQL.c:109 using schema
> > public Fri Apr 11 16:00:12 2008 <7>
> > ulogd_output_PGSQL.c:295 execute failed (ERROR:
> > function insert_packet_full(unknown, unknown, integer,
> > unknown, integer, unknown, unknown, integer, unknown,
> > unknown, integer, integer, integer, integer, integer,
> > integer, integer, integ er, integer, integer, integer,
> > bigint, integer, unknown, unknown, unknown, unknown,
> > unknown, unknown, unknown, unknown, unknown, unk nown,
> > unknown, unknown, unknown, unknown, unknown, unknown,
> > unknown, unknown, unknown, unknown, unknown, unknown,
> > integer) does not exist at character 8
> > HINT: No function matches the given name and argument
> > types. You might need to add explicit type casts. )
>
> This one is already spotted, we will try to work on this
> next week.
>
> > Fri Apr 11 16:00:14 2008 <7> ../../util/db.c:185 no
> > connection to database, attempting to reconnect after 2
> > seconds Fri Apr 11 16:00:14 2008 <5> ulogd.c:488 error
> > during propagate_results Fri Apr 11 16:00:17 2008 <1>
> > ulogd_output_PGSQL.c:99 SELECT nspname FROM
> > pg_namespace n WHERE n.nspname='public' Fri Apr 11
> > 16:00:17 2008 <1> ulogd_output_PGSQL.c:109 using schema
> > public Fri Apr 11 16:00:17 2008 <7>
> > ulogd_output_PGSQL.c:295 execute failed (ERROR:
> > smallint out of range CONTEXT: SQL function
> > "insert_icmp" statement 1 SQL statement "SELECT
> > INSERT_ICMP( $1 , $2 , $3 , $4 , $5 , $6 , $7 )"
>
> This one is new, could you try the attached patch ? It
> should fix this last problem.
>
> BR,
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: ULOGD2 PostgresSql errors
2008-04-12 7:43 ` Anton
@ 2008-04-12 9:49 ` Eric Leblond
2008-04-12 10:42 ` Eric Leblond
0 siblings, 1 reply; 6+ messages in thread
From: Eric Leblond @ 2008-04-12 9:49 UTC (permalink / raw)
To: Anton; +Cc: Pablo Neira Ayuso, Netfilter Developer Mailing List
[-- Attachment #1: Type: text/plain, Size: 3011 bytes --]
Hello,
On Saturday, 2008 April 12 at 12:43:08 +0500, Anton wrote:
> Erik,
>
> Seems it fixes ICMP inserts.
Ok, fine.
> Just for testing I tried MYSQL
> logging too - it works without errors in log file, and
> looks it logs more data than PGSQL.
Yes, we're working on this issue. It seems PGSQL do not manage to find
the called SQL function due to type detection problem. This cause PGSQL
to loose some packets.
BR,
>
> Regards,
> Anton.
>
> On Saturday 12 April 2008 00:33, Eric Leblond wrote:
> > Hello,
> >
> > On Friday, 2008 April 11 at 16:05:39 +0500, Anton wrote:
> > > Hello!
> > >
> > > ulogd.log output is below:
> > >
> > > Fri Apr 11 16:00:10 2008 <7> ../../util/db.c:185 no
> > > connection to database, attempting to reconnect after 2
> > > seconds Fri Apr 11 16:00:10 2008 <5> ulogd.c:488 error
> > > during propagate_results Fri Apr 11 16:00:12 2008 <1>
> > > ulogd_output_PGSQL.c:99 SELECT nspname FROM
> > > pg_namespace n WHERE n.nspname='public' Fri Apr 11
> > > 16:00:12 2008 <1> ulogd_output_PGSQL.c:109 using schema
> > > public Fri Apr 11 16:00:12 2008 <7>
> > > ulogd_output_PGSQL.c:295 execute failed (ERROR:
> > > function insert_packet_full(unknown, unknown, integer,
> > > unknown, integer, unknown, unknown, integer, unknown,
> > > unknown, integer, integer, integer, integer, integer,
> > > integer, integer, integ er, integer, integer, integer,
> > > bigint, integer, unknown, unknown, unknown, unknown,
> > > unknown, unknown, unknown, unknown, unknown, unk nown,
> > > unknown, unknown, unknown, unknown, unknown, unknown,
> > > unknown, unknown, unknown, unknown, unknown, unknown,
> > > integer) does not exist at character 8
> > > HINT: No function matches the given name and argument
> > > types. You might need to add explicit type casts. )
> >
> > This one is already spotted, we will try to work on this
> > next week.
> >
> > > Fri Apr 11 16:00:14 2008 <7> ../../util/db.c:185 no
> > > connection to database, attempting to reconnect after 2
> > > seconds Fri Apr 11 16:00:14 2008 <5> ulogd.c:488 error
> > > during propagate_results Fri Apr 11 16:00:17 2008 <1>
> > > ulogd_output_PGSQL.c:99 SELECT nspname FROM
> > > pg_namespace n WHERE n.nspname='public' Fri Apr 11
> > > 16:00:17 2008 <1> ulogd_output_PGSQL.c:109 using schema
> > > public Fri Apr 11 16:00:17 2008 <7>
> > > ulogd_output_PGSQL.c:295 execute failed (ERROR:
> > > smallint out of range CONTEXT: SQL function
> > > "insert_icmp" statement 1 SQL statement "SELECT
> > > INSERT_ICMP( $1 , $2 , $3 , $4 , $5 , $6 , $7 )"
> >
> > This one is new, could you try the attached patch ? It
> > should fix this last problem.
> >
> > BR,
> --
> To unsubscribe from this list: send the line "unsubscribe netfilter-devel" in
> the body of a message to majordomo@vger.kernel.org
> More majordomo info at http://vger.kernel.org/majordomo-info.html
--
Eric Leblond
INL: http://www.inl.fr/
NuFW: http://www.nufw.org/
[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: ULOGD2 PostgresSql errors
2008-04-12 9:49 ` Eric Leblond
@ 2008-04-12 10:42 ` Eric Leblond
2008-04-13 1:25 ` Pablo Neira Ayuso
0 siblings, 1 reply; 6+ messages in thread
From: Eric Leblond @ 2008-04-12 10:42 UTC (permalink / raw)
To: Anton, Pablo Neira Ayuso, Netfilter Developer Mailing List
[-- Attachment #1.1: Type: text/plain, Size: 835 bytes --]
Hello,
On Saturday, 2008 April 12 at 11:49:25 +0200, Eric Leblond wrote:
> Hello,
>
> On Saturday, 2008 April 12 at 12:43:08 +0500, Anton wrote:
> > Erik,
> >
> > Seems it fixes ICMP inserts.
>
> Ok, fine.
>
> > Just for testing I tried MYSQL
> > logging too - it works without errors in log file, and
> > looks it logs more data than PGSQL.
>
> Yes, we're working on this issue. It seems PGSQL do not manage to find
> the called SQL function due to type detection problem. This cause PGSQL
> to loose some packets.
The attached patch should fix the ICMP problem and the non-logging
problem. It is ok now on my test system.
Please note that you need to drop functions in the PGSQL schema before
applying the new schema.
BR,
--
Eric Leblond
INL: http://www.inl.fr/
NuFW: http://www.nufw.org/
[-- Attachment #1.2: pgsql-types.diff --]
[-- Type: text/x-diff, Size: 2388 bytes --]
diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index b18f4a8..6f1d9f4 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -86,7 +86,7 @@ CREATE TABLE tcp (
tcp_sport integer default NULL,
tcp_dport integer default NULL,
tcp_seq bigint default NULL,
- tcp_ackseq integer default NULL,
+ tcp_ackseq bigint default NULL,
tcp_window integer default NULL,
tcp_urg boolean default NULL,
tcp_urgp integer default NULL,
@@ -120,8 +120,8 @@ CREATE TABLE icmp (
_icmp_id bigint PRIMARY KEY UNIQUE NOT NULL,
icmp_type smallint default NULL,
icmp_code smallint default NULL,
- icmp_echoid smallint default NULL,
- icmp_echoseq smallint default NULL,
+ icmp_echoid integer default NULL,
+ icmp_echoseq integer default NULL,
icmp_gateway integer default NULL,
icmp_fragmtu smallint default NULL
) WITH (OIDS=FALSE);
@@ -130,8 +130,8 @@ CREATE TABLE icmpv6 (
_icmpv6_id bigint PRIMARY KEY UNIQUE NOT NULL,
icmpv6_type smallint default NULL,
icmpv6_code smallint default NULL,
- icmpv6_echoid smallint default NULL,
- icmpv6_echoseq smallint default NULL,
+ icmpv6_echoid integer default NULL,
+ icmpv6_echoseq integer default NULL,
icmpv6_csum integer default NULL
) WITH (OIDS=FALSE);
@@ -294,7 +294,7 @@ DROP TABLE IF EXISTS nufw;
CREATE TABLE nufw (
_nufw_id bigint PRIMARY KEY UNIQUE NOT NULL,
username varchar(30) default NULL,
- user_id smallint default NULL,
+ user_id integer default NULL,
client_os varchar(100) default NULL,
client_app varchar(256) default NULL
) WITH (OIDS=FALSE);
@@ -389,7 +389,7 @@ CREATE OR REPLACE FUNCTION INSERT_TCP_FULL(
IN tcp_sport integer,
IN tcp_dport integer,
IN tcp_seq bigint,
- IN tcp_ackseq integer,
+ IN tcp_ackseq bigint,
IN tcp_window integer,
IN tcp_urg boolean,
IN tcp_urgp integer ,
@@ -492,7 +492,7 @@ CREATE OR REPLACE FUNCTION INSERT_PACKET_FULL(
IN tcp_sport integer,
IN tcp_dport integer,
IN tcp_seq bigint,
- IN tcp_ackseq integer,
+ IN tcp_ackseq bigint,
IN tcp_window integer,
IN tcp_urg boolean,
IN tcp_urgp integer ,
[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]
^ permalink raw reply related [flat|nested] 6+ messages in thread
* Re: ULOGD2 PostgresSql errors
2008-04-12 10:42 ` Eric Leblond
@ 2008-04-13 1:25 ` Pablo Neira Ayuso
0 siblings, 0 replies; 6+ messages in thread
From: Pablo Neira Ayuso @ 2008-04-13 1:25 UTC (permalink / raw)
To: Eric Leblond, Anton, Pablo Neira Ayuso,
Netfilter Developer Mailing List
Eric Leblond wrote:
> Hello,
>
> On Saturday, 2008 April 12 at 11:49:25 +0200, Eric Leblond wrote:
>> Hello,
>>
>> On Saturday, 2008 April 12 at 12:43:08 +0500, Anton wrote:
>>> Erik,
>>>
>>> Seems it fixes ICMP inserts.
>> Ok, fine.
>>
>>> Just for testing I tried MYSQL
>>> logging too - it works without errors in log file, and
>>> looks it logs more data than PGSQL.
>> Yes, we're working on this issue. It seems PGSQL do not manage to find
>> the called SQL function due to type detection problem. This cause PGSQL
>> to loose some packets.
>
> The attached patch should fix the ICMP problem and the non-logging
> problem. It is ok now on my test system.
I have applied this patch to SVN. Thanks Eric.
--
"Los honestos son inadaptados sociales" -- Les Luthiers
^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2008-04-13 1:25 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2008-04-11 11:05 ULOGD2 PostgresSql errors Anton
2008-04-11 19:33 ` Eric Leblond
2008-04-12 7:43 ` Anton
2008-04-12 9:49 ` Eric Leblond
2008-04-12 10:42 ` Eric Leblond
2008-04-13 1:25 ` Pablo Neira Ayuso
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox