sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Elio T. <el...@to...>]

Karl O. Pinc wrote:

>> I tried with memtest, but I exclude hardware problem - I have same
>> segmentation on two identical hardware.
> 
> You sometimes have to run memtest for several passes,
> I usually run it overnight, before getting an error.

I have a laptop which runs memtest forever with no errors, but segfaults when 
booting Linux... and hangs often with XP. It seems to be a problem in the 
processor.

Elio

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Marco F. <fa...@cs...>]

Yann Cezard wrote:
> Marco Favero a écrit :
>> Hi all,
>>   
> Hi
>> I run daemon sqlgrey 1.6.8 with Mysql DBMS. DB access is "sync", not
>> "async".
>>
>> I have just installed it in test environment with no connections:
>>
>> Red Hat Enterprise Linux Server release 5.2
>> Linux vm-greylist1 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 
>> x86_64 x86_64 x86_64 GNU/Linux
>>
>>   
> The name of the host and the kernel version makes me ask :
> Isn't it a Xen Virtual Machine ?
> If yes, aren't you running a 64 bits kernel with a 32 bits installation ?
> 
> That sometimes gives problem like the one you have. I believe it's a
> compilation problem of some binaries/libraries.

Yes, it's a virtual machine with 64 bits kernel. But also the
installation is 64 bits RH server 5 standard.
Probably there are also 32bits libraries, but following "configure" and
"make" it seems that 64bits libraries are used. I don't notice compile
errors.
Of course, I can try to compile on a 32bit kernel and installation.

Thank you very much
Regars
Marco

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Yann C. <yan...@un...>]

Marco Favero a écrit :
> Hi all,
>   
Hi
> I run daemon sqlgrey 1.6.8 with Mysql DBMS. DB access is "sync", not
> "async".
>
> I have just installed it in test environment with no connections:
>
> Red Hat Enterprise Linux Server release 5.2
> Linux vm-greylist1 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 
> x86_64 x86_64 x86_64 GNU/Linux
>
>   
The name of the host and the kernel version makes me ask :
Isn't it a Xen Virtual Machine ?
If yes, aren't you running a 64 bits kernel with a 32 bits installation ?

That sometimes gives problem like the one you have. I believe it's a
compilation problem of some binaries/libraries.
> kernel:<6>sqlgrey[9861]: segfault at 0000000000000008 rip 
> 0000003d2ca489d5 rsp 00007fff031e68e0 error 
Regards,

-- 
Yann Cézard - Administrateur Systèmes Serveurs
Centre de Ressources Informatiques - http://cri.univ-pau.fr
Université de Pau et des Pays de l'Adour - http://www.univ-pau.fr

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Karl O. P. <ko...@me...>]

On 07/14/2008 04:29:57 AM, Marco Favero wrote:
> Lionel Bouton wrote:
> 
> I tried with memtest, but I exclude hardware problem - I have same
> segmentation on two identical hardware.

You sometimes have to run memtest for several passes,
I usually run it overnight, before getting an error.

You could (possibly) have a design flaw in the hardware.
Heat related problems are especially tricky.

You could also try isolating the problem, somewhat,
by using something besides MySQL for the db backend.
If the problem goes away you've made progress.
Likewise, if you move the MySQL backend to the local
box and the problem goes away that says something too.
About all you can do is take the divide and conquer
approach until you've got something reproducible
that somebody else can work on.

Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Chris S. <ch...@cs...>]

On Mon, 14 Jul 2008, Marco Favero wrote:

> I tried with memtest, but I exclude hardware problem - I have same
> segmentation on two identical hardware.

You could just be very unlucky..

Might be worth trying to run it under Valgrind if you can, see if it 
catches anything.  There's also the glibc debugging malloc stuff you could 
try too, have a look at malloc(3) and its MALLOC_CHECK_ environment 
variable.

cheers,
Chris
-- 
 Chris Samuel  :  http://www.csamuel.org/  :  Melbourne, VIC

This email may come with a PGP signature as a file. Do not panic.
For more info see: http://en.wikipedia.org/wiki/OpenPGP

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Marco F. <fa...@cs...>]

Lionel Bouton wrote:
> Marco Favero a écrit, le 07/11/2008 01:43 PM :
>> Hi all,
>> I run daemon sqlgrey 1.6.8 with Mysql DBMS. DB access is "sync", not
>> "async".
>>
>> I have just installed it in test environment with no connections:
>>
>> Red Hat Enterprise Linux Server release 5.2
>> Linux vm-greylist1 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 
>> x86_64 x86_64 x86_64 GNU/Linux
>>
>> I start sqlgrey and it works fine. I make SMTP session: it's ok, like 
>> expected.
>> After a day, I made another smtp session: sqlgrey segfault! This is the 
>> only log written:
>>
>> kernel:<6>sqlgrey[9861]: segfault at 0000000000000008 rip 
>> 0000003d2ca489d5 rsp 00007fff031e68e0 error 4
>>
>> Could you help me?
>>   
> 
> It's most probably hardware related. Segfaults happen when a process 
> tries to access memory at an address it hasn't access too. SQLgrey is 
> coded in Perl and doesn't deal with memory access at all leaving it to 
> the Perl interpreter. So there's really two possible sources :
> - Buggy perl interpreter or native library (the DBD MySQL driver for 
> example is probably coded with both Perl and C to interface with the 
> MySQL client library),
> - Dodgy hardware.
> 
> As you are the first to report such a problem it's far more likely that 
> it's a problem specific to your installation : RedHat being used widely 
> the only remaining possibility is the hardware.
> 
> Running memtest86+ might confirm the problem.
> 
> Lionel

I tried with memtest, but I exclude hardware problem - I have same 
segmentation on two identical hardware.

My systems are:
perl 5.8.8

Modules installed:
IO::Multiplex is up to date (1.09)
Net::Server is up to date (0.97)
DBD::mysql is up to date (4.007)
DBI is up to date (1.605)

Mysql local to DBD and sqlgrey is:
mysql.x86_64      5.0.22-2.1.0.1
but db_host is not localhost: remote DBMS could be another subversion.

I don't known what thinking about...
For debugging purpose I also tried to reduce db_cleandelay to few 
seconds, but it is useless. Segmentation fault happens only after 
several hours from sqlgrey start, I notice after a day during first 
connection I make.

Maybe it's useful to know which versions of module above surely works 
together?

I notice this problem only with sqlgrey, so I've tried to ask here.
Thank you very much again
Marco

Re: [Sqlgrey-users] sqlgrey segmentation fault!

[Lionel B. <lio...@bo...>]

Marco Favero a écrit, le 07/11/2008 01:43 PM :
> Hi all,
> I run daemon sqlgrey 1.6.8 with Mysql DBMS. DB access is "sync", not
> "async".
>
> I have just installed it in test environment with no connections:
>
> Red Hat Enterprise Linux Server release 5.2
> Linux vm-greylist1 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 
> x86_64 x86_64 x86_64 GNU/Linux
>
> I start sqlgrey and it works fine. I make SMTP session: it's ok, like 
> expected.
> After a day, I made another smtp session: sqlgrey segfault! This is the 
> only log written:
>
> kernel:<6>sqlgrey[9861]: segfault at 0000000000000008 rip 
> 0000003d2ca489d5 rsp 00007fff031e68e0 error 4
>
> Could you help me?
>   

It's most probably hardware related. Segfaults happen when a process 
tries to access memory at an address it hasn't access too. SQLgrey is 
coded in Perl and doesn't deal with memory access at all leaving it to 
the Perl interpreter. So there's really two possible sources :
- Buggy perl interpreter or native library (the DBD MySQL driver for 
example is probably coded with both Perl and C to interface with the 
MySQL client library),
- Dodgy hardware.

As you are the first to report such a problem it's far more likely that 
it's a problem specific to your installation : RedHat being used widely 
the only remaining possibility is the hardware.

Running memtest86+ might confirm the problem.

Lionel

[Sqlgrey-users] sqlgrey segmentation fault!

[Marco F. <fa...@cs...>]

Hi all,
I run daemon sqlgrey 1.6.8 with Mysql DBMS. DB access is "sync", not
"async".

I have just installed it in test environment with no connections:

Red Hat Enterprise Linux Server release 5.2
Linux vm-greylist1 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 
x86_64 x86_64 x86_64 GNU/Linux

I start sqlgrey and it works fine. I make SMTP session: it's ok, like 
expected.
After a day, I made another smtp session: sqlgrey segfault! This is the 
only log written:

kernel:<6>sqlgrey[9861]: segfault at 0000000000000008 rip 
0000003d2ca489d5 rsp 00007fff031e68e0 error 4

Could you help me?

Thank you very much
Regards
Marco

[Sqlgrey-users] Db Load Reduction

[Dan F. <da...@ha...>]

Hey all..

During "bounce storms" like backscatter and such, we startet getting 
timeouts between postfix and sqlgrey. After a little poking, i loosly 
concluded that sql hangs for a bit and thus sqlgrey hans for a bit.

The sql-hang occurs, afaik, when the sql-master (we run in clustering 
mode) gets hammered with inserts and updates.
Im gonna upgrade the master sql-server at some point, but i also looked 
a bit at what "write" statements that hammers the master.

Of course inserts into the connect (new connection triplet) is by far 
the top hitter when it comes to writes. But during a bounce storm, 
UPDATES to From_AWL actually makes out around 20-25% of all writes.
But its not necessary to update EVERY time, since in a bounce scenario, 
most sender adresses will be blank. (ie NULL senders) and many IPs will 
return often.

So yesterday i whipped up a way of not updating every time for the same 
record. And it turns out it made a huge difference.
From_AWL writings are now reduced by 97%

What ive done:
is_in_from_awl normally SELECT's "1" if record exists in AWL.
I now select the "last_seen" timestamp. The function now also returns 
this timestamp, instead of 1 for all "true" scenarios.
It will still return 0 on false, thus no logic needs changing elsewhere 
due to this change.

The timestamp is compared to time() to see how long ago last update was 
done. If enough time has passed, it makes the UPDATE to the table.

Right now the UPDATE delay is configured as $dflt{awl_age}(in seconds) * 
0.004167.
This makes ~6 hours, if awl_age=60 days and ~3 hours if awl_age=30 days 
and so forth.

I dont really see a downside to this approach. All From_AWL entries will 
still be updated on the first hit within the time period.  so worst case 
scenario, would be that a from_awl entry would be cleaned out 3 hours 
early. Compared to a period of 30 days, it doenst really matter.
In the log it will say: "from awl match: updating" (as before) when 
actually updating the table and when NOT updating it will simply say 
"from awl match:" (without the word "updating")


Oh yeah, and the feature can be enabled/disabled with the flag 
"db_load_reduction".

So.. This is a "request for comments" to make sure i didnt miss any 
pitfalls and such.  A patch for consideration is attached.


- Dan Faerch

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Dan F. <da...@ha...>]

Is it even legal to use international chars in an email-address?


Well  anyway. I assume its this line you want to grep out:

May 30 14:23:26 hobbes sqlgrey: grey: from awl match: updating 
190.172.137.78(190.172.137.78), 
m<FC>ll...@ly...(m<FC>ll...@ly...)


If so, try this:
$ grep "from awl match:" mail.log | grep -viE "\([[:graph:]]+@"

If it fails, use this as a "less accurate" alternative:
$ grep "from awl match:" mail.log | grep -viE "\([a-z?~*=.#%&+/_0-9-]+@"

(replace "mail.log" with whatever file youre grepping)

- Dan


Philippe Chaintreuil wrote:
> Lionel Bouton wrote:
>  > Without patching SQLgrey, you might have luck changing the my.cnf file
>  > to change the default client charset (assuming you don't have other
>  > clients needing to connect with a different charset to the same
>  > system).  If you want to hack SQLgrey, look for the end of the
>  > connectdb method, it already has a special case for MySQL
>  > (auto-reconnect as MySQL routinely drops conections by default...).
>  > You can then send the SQL query setting the character set you need at
>  > this point :
>  >
>  >  $self->{sqlgrey}{dbh}->do("SET CHARACTER SET LATIN1")
>  >
>  > The MySQL doc isn't clear about what happens when the client
>  > auto-reconnects... so your mileage may vary. It would be great if the
>  > DBD driver would allow to set the character set at connection-time,
>  > but I had no luck browsing it's documentation either.
>
> 	So I made this change back on April 3rd:
>
> ------------------------------------------------------------------------
>      # mysql drops the connection, we have some glue code
>      # to reinit the connection, but better use mysql DBD code
>      if ($self->MySQL()) {
>          $self->{sqlgrey}{dbh}->do("SET CHARACTER SET LATIN1")
>          or $self->mylog('dbaccess', 0,
>            "can't set connection character set to LATIN1: $DBI::errstr");
>
>          $self->{sqlgrey}{dbh}->{mysql_auto_reconnect} = 1;
>      }
> ------------------------------------------------------------------------
>
> 	and yesterday I got this:
>
> ------------------------------------------------------------------------
> May 30 14:23:24 hobbes postfix/smtpd[28381]: warning: 190.172.137.78: 
> address not listed for hostname 190-172-137-78.speedy.com.ar
> May 30 14:23:24 hobbes postfix/smtpd[28381]: connect from 
> unknown[190.172.137.78]
> May 30 14:23:26 hobbes postfix/pickup[28070]: 73E491AFEA9: uid=102 
> from=<sqlgrey>
> May 30 14:23:26 hobbes postfix/cleanup[28424]: 73E491AFEA9: 
> message-id=<200...@ho...>
> May 30 14:23:26 hobbes postfix/qmgr[15197]: 73E491AFEA9: 
> from=<sq...@ho...>, size=490, nrcpt=1 (queue active)
>
> May 30 14:23:26 hobbes sqlgrey: warning: Use of uninitialized value in 
> concatenation (.) or string at /usr/sbin/sqlgrey line 1143.
> May 30 14:23:26 hobbes sqlgrey: dbaccess: error: couldn't access 
> from_awl table:
> May 30 14:23:26 hobbes sqlgrey: grey: from awl match: updating 
> 190.172.137.78(190.172.137.78), 
> m<FC>ll...@ly...(m<FC>ll...@ly...)
> ------------------------------------------------------------------------
>
> 	So I got a new warning about a bad concatenation/string when I got a 
> non [A-Za-z] letter (the "<FC>").  The line referenced above is the 
> mylog() line below from is_in_from_awl():
>
> ------------------------------------------------------------------------
>      if (!defined $sth or !$sth->execute($sender_name, $sender_domain, 
> $host)) {
>          $self->db_unavailable();
>          $self->mylog('dbaccess', 0, "error: couldn't access $from_awl 
> table: $DBI::errstr");
>          return 1; # in doubt, accept
>      } else {
> ------------------------------------------------------------------------
>
> 	I went back through my logs trying to see if I could find any other non 
> [A-Za-z] characters in e-mail addresses in sqlgrey lines since April and 
> I couldn't find any.  (Note that does not mean that they're weren't any 
> -- I had 26 megs of sqlgrey lines from that time and I don't quite know 
> what to grep for to find 'em.)
>
> 	So the two questions I have at this point are:
>
> 1.)  Lionel, any suggestions what to do to debug this warning so I can 
> get the real error?
> 2.)  Anybody know a grep regexp for pulling out e-mail addresses with 
> those non [A-Za-z] characters?
>
>
>   

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Philippe C. <sql...@pa...>]

Lionel Bouton wrote:
 > Without patching SQLgrey, you might have luck changing the my.cnf file
 > to change the default client charset (assuming you don't have other
 > clients needing to connect with a different charset to the same
 > system).  If you want to hack SQLgrey, look for the end of the
 > connectdb method, it already has a special case for MySQL
 > (auto-reconnect as MySQL routinely drops conections by default...).
 > You can then send the SQL query setting the character set you need at
 > this point :
 >
 >  $self->{sqlgrey}{dbh}->do("SET CHARACTER SET LATIN1")
 >
 > The MySQL doc isn't clear about what happens when the client
 > auto-reconnects... so your mileage may vary. It would be great if the
 > DBD driver would allow to set the character set at connection-time,
 > but I had no luck browsing it's documentation either.

	So I made this change back on April 3rd:

------------------------------------------------------------------------
     # mysql drops the connection, we have some glue code
     # to reinit the connection, but better use mysql DBD code
     if ($self->MySQL()) {
         $self->{sqlgrey}{dbh}->do("SET CHARACTER SET LATIN1")
         or $self->mylog('dbaccess', 0,
           "can't set connection character set to LATIN1: $DBI::errstr");

         $self->{sqlgrey}{dbh}->{mysql_auto_reconnect} = 1;
     }
------------------------------------------------------------------------

	and yesterday I got this:

------------------------------------------------------------------------
May 30 14:23:24 hobbes postfix/smtpd[28381]: warning: 190.172.137.78: 
address not listed for hostname 190-172-137-78.speedy.com.ar
May 30 14:23:24 hobbes postfix/smtpd[28381]: connect from 
unknown[190.172.137.78]
May 30 14:23:26 hobbes postfix/pickup[28070]: 73E491AFEA9: uid=102 
from=<sqlgrey>
May 30 14:23:26 hobbes postfix/cleanup[28424]: 73E491AFEA9: 
message-id=<200...@ho...>
May 30 14:23:26 hobbes postfix/qmgr[15197]: 73E491AFEA9: 
from=<sq...@ho...>, size=490, nrcpt=1 (queue active)

May 30 14:23:26 hobbes sqlgrey: warning: Use of uninitialized value in 
concatenation (.) or string at /usr/sbin/sqlgrey line 1143.
May 30 14:23:26 hobbes sqlgrey: dbaccess: error: couldn't access 
from_awl table:
May 30 14:23:26 hobbes sqlgrey: grey: from awl match: updating 
190.172.137.78(190.172.137.78), 
m<FC>ll...@ly...(m<FC>ll...@ly...)
------------------------------------------------------------------------

	So I got a new warning about a bad concatenation/string when I got a 
non [A-Za-z] letter (the "<FC>").  The line referenced above is the 
mylog() line below from is_in_from_awl():

------------------------------------------------------------------------
     if (!defined $sth or !$sth->execute($sender_name, $sender_domain, 
$host)) {
         $self->db_unavailable();
         $self->mylog('dbaccess', 0, "error: couldn't access $from_awl 
table: $DBI::errstr");
         return 1; # in doubt, accept
     } else {
------------------------------------------------------------------------

	I went back through my logs trying to see if I could find any other non 
[A-Za-z] characters in e-mail addresses in sqlgrey lines since April and 
I couldn't find any.  (Note that does not mean that they're weren't any 
-- I had 26 megs of sqlgrey lines from that time and I don't quite know 
what to grep for to find 'em.)

	So the two questions I have at this point are:

1.)  Lionel, any suggestions what to do to debug this warning so I can 
get the real error?
2.)  Anybody know a grep regexp for pulling out e-mail addresses with 
those non [A-Za-z] characters?


-- 
    .----------.    ,----------== Peep ==----------,
   /  .-.  .-.  \   `-== (Philippe Chaintreuil) ==-`
  /   | |  | |   \   On The Other Side Of The Wall!
  \   `-'  `-'  _/      FidoNet Netmail Address:
  /\     .--.  / |             1:2613/118
  \ |   /  /  / /          InterNet  Address:
  / |  `--'  /\ \        pe...@pa...
   /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
      "We're just two lost souls swimming in a fish bowl..."

Re: [Sqlgrey-users] postgrey2sqlgrey data conversion tool ?

[Kenneth M. <kt...@ri...>]

If you are using postfix then you can run sqlgrey in
"learn" mode for a couple of weeks to pre-prime the tables.
That worked very well here and resulted in very little user
impact when we actually made it active.

Ken

On Fri, May 23, 2008 at 02:29:32PM +0200, Lionel Bouton wrote:
> Yann Cezard wrote the following on 23.05.2008 14:06 :
> > Hi,
> >
> > I was wondering if there was any tool to migrate postgrey BDB data
> > to an SQLGrey database ?
> >   
> 
> SQLgrey was initially a fork of postgrey, but there's nearly no original 
> code left. The greylisting process uses it's own algorithm and data 
> formats so migrating postgrey data might not be straightforward.
> 
> > I can still try to code one myself, but I don't want to loose time
> > if someone already does.
> >   
> 
> I'll simply switch from postgrey to SQLgrey without migrating data. I 
> recently installed SQLgrey for a customer : the auto-whitelists where 
> ~50% effective in 3 hours and ~80% in 24 hours, with a limit reached at 
> around 85-90%. So if you time it right, installing the greylisting 
> during low-usage periods, nobody even notices the change. This is even 
> more true since the mail reliability went down the sink thanks to 
> aggressive webmail services dropping mails instead of bouncing them 
> (*cough* hotmail *cough*): people slowly get used to low-quality service 
> and often assume there's a problem at one of the two end of the pipe and 
> work around it without bothering admins :-/.
> 
> For the case above, the only end-user reaction was : "What did you do? 
> Most of us didn't receive any SPAM since yesterday!".
> 
> Lionel
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 

Re: [Sqlgrey-users] postgrey2sqlgrey data conversion tool ?

[Lionel B. <lio...@bo...>]

Yann Cezard wrote the following on 23.05.2008 14:06 :
> Hi,
>
> I was wondering if there was any tool to migrate postgrey BDB data
> to an SQLGrey database ?
>   

SQLgrey was initially a fork of postgrey, but there's nearly no original 
code left. The greylisting process uses it's own algorithm and data 
formats so migrating postgrey data might not be straightforward.

> I can still try to code one myself, but I don't want to loose time
> if someone already does.
>   

I'll simply switch from postgrey to SQLgrey without migrating data. I 
recently installed SQLgrey for a customer : the auto-whitelists where 
~50% effective in 3 hours and ~80% in 24 hours, with a limit reached at 
around 85-90%. So if you time it right, installing the greylisting 
during low-usage periods, nobody even notices the change. This is even 
more true since the mail reliability went down the sink thanks to 
aggressive webmail services dropping mails instead of bouncing them 
(*cough* hotmail *cough*): people slowly get used to low-quality service 
and often assume there's a problem at one of the two end of the pipe and 
work around it without bothering admins :-/.

For the case above, the only end-user reaction was : "What did you do? 
Most of us didn't receive any SPAM since yesterday!".

Lionel

[Sqlgrey-users] postgrey2sqlgrey data conversion tool ?

[Yann C. <yan...@un...>]

Hi,

I was wondering if there was any tool to migrate postgrey BDB data
to an SQLGrey database ?

I can still try to code one myself, but I don't want to loose time
if someone already does.

So any clue is welcome.

Thanks !

-- 
Yann Cézard - Administrateur Systèmes Serveurs
Centre de Ressources Informatiques - http://cri.univ-pau.fr
Université de Pau et des Pays de l'Adour - http://www.univ-pau.fr

[Sqlgrey-users] [patch] Re: IPv6 in SQLgrey

[Michal L. <ml...@lo...>]

Hi again,

attached is a patch for SQLgrey 1.7.6 that does what I suggested in my 
previous post. Please apply include in the next SQLgrey release. Thanks.

Michal
-- 
* http://www.logix.cz/michal -- personal homepage

Michal Ludvig wrote:
> Hi Lionel,
> 
> I wonder why SQLgrey 1.7.6 handles IPv6 addresses the way it does. I've 
> performed some tests recently and it looks like the addresses are 
> "shortened" in an overly relaxed way.
> 
> For instance client address  2001:0db8:cafe:b0b0::1  is turned into bare 
> '0db8':
> new: 0db8(2001:0db8:cafe:b0b0::1), a@b.xyz -> y@z.abc
> 
> It doesn't care about the rest of the address:
> 
> reconnect ok: 0db8(2002:0db8:cafe:b0b0::1), a@b.xyz -> y@z.abc
> 	(starts with 2002: instead of 2001: - accepted)
> 
> reconnect ok: 0db8(2001:0db8:cafe:b0b0:210:ff:fe00:1), a@b.xyz ...
> 	(different host in the subnet - accepted)
> 
> reconnect ok: 0db8(2001:0db8:dead:beef:260:ff:fe00:2), ...
> 	(different subnet, in real world likely a different customer -
> 	 again, accepted)
> 
> In some cases though it takes the whole address, e.g. for link local:
> fe80::260:ff:fe00:2(fe80::260:ff:fe00:2)
> fe80::260:ff:fe00:3(fe80::260:ff:fe00:3)
> or, surprisingly, my 6to4:
> 2002:762a:264::204:26ff:fe8b:6a9(2002:762a:264::204:26ff:fe8b:6a9)
> 
> 
> I'm not sure how the subnetting should work but am pretty sure the way 
> it is it's not right.
> 
> IMHO it should take the whole address when dealing with non-EUI64, e.g. 
> 2001:0db8:cafe:b0b0::1 since these are probably fixed addresses of the 
> servers, or some smaller subnets (tunnels, ADSL?), etc. where the /64 
> subnet might cover any number of customers.
> 
> OTOH When dealing with EUI64, e.g. 2001:0db8:dead:beef:260:ff:fe00:2 it 
> should only take the /64 subnet into account, i.e. normalize it to 
> 2001:0db8:dead:beef::/64 since it's likely that if one server on that 
> network segment behaves correctly the others would do as well.
> 
> 
> Anyway thanks a lot for providing IPv6 support in this great piece of 
> software!
> 
> Michal
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft 
> Defy all challenges. Microsoft(R) Visual Studio 2008. 
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 

[Sqlgrey-users] IPv6 in SQLgrey

[Michal L. <ml...@lo...>]

Hi Lionel,

I wonder why SQLgrey 1.7.6 handles IPv6 addresses the way it does. I've 
performed some tests recently and it looks like the addresses are 
"shortened" in an overly relaxed way.

For instance client address  2001:0db8:cafe:b0b0::1  is turned into bare 
'0db8':
new: 0db8(2001:0db8:cafe:b0b0::1), a@b.xyz -> y@z.abc

It doesn't care about the rest of the address:

reconnect ok: 0db8(2002:0db8:cafe:b0b0::1), a@b.xyz -> y@z.abc
	(starts with 2002: instead of 2001: - accepted)

reconnect ok: 0db8(2001:0db8:cafe:b0b0:210:ff:fe00:1), a@b.xyz ...
	(different host in the subnet - accepted)

reconnect ok: 0db8(2001:0db8:dead:beef:260:ff:fe00:2), ...
	(different subnet, in real world likely a different customer -
	 again, accepted)

In some cases though it takes the whole address, e.g. for link local:
fe80::260:ff:fe00:2(fe80::260:ff:fe00:2)
fe80::260:ff:fe00:3(fe80::260:ff:fe00:3)
or, surprisingly, my 6to4:
2002:762a:264::204:26ff:fe8b:6a9(2002:762a:264::204:26ff:fe8b:6a9)


I'm not sure how the subnetting should work but am pretty sure the way 
it is it's not right.

IMHO it should take the whole address when dealing with non-EUI64, e.g. 
2001:0db8:cafe:b0b0::1 since these are probably fixed addresses of the 
servers, or some smaller subnets (tunnels, ADSL?), etc. where the /64 
subnet might cover any number of customers.

OTOH When dealing with EUI64, e.g. 2001:0db8:dead:beef:260:ff:fe00:2 it 
should only take the /64 subnet into account, i.e. normalize it to 
2001:0db8:dead:beef::/64 since it's likely that if one server on that 
network segment behaves correctly the others would do as well.


Anyway thanks a lot for providing IPv6 support in this great piece of 
software!

Michal

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Lionel B. <lio...@bo...>]

Philippe Chaintreuil wrote:
> 	So I have two questions:
>
> 1.)  What's wrong?
>   

MySQL ? I'm wondering why it defaults to a charset different than the 
database one. It seems counterintuitive at best. If you set your 
database charset to a non-default value it's for a damn good reason...


> 2.)  How can I fix it?
>
>   

Without patching SQLgrey, you might have luck changing the my.cnf file 
to change the default client charset (assuming you don't have other 
clients needing to connect with a different charset to the same system).
If you want to hack SQLgrey, look for the end of the connectdb method, 
it already has a special case for MySQL (auto-reconnect as MySQL 
routinely drops conections by default...).
You can then send the SQL query setting the character set you need at 
this point :

 $self->{sqlgrey}{dbh}->do("SET CHARACTER SET LATIN1")

The MySQL doc isn't clear about what happens when the client 
auto-reconnects... so your mileage may vary. It would be great if the 
DBD driver would allow to set the character set at connection-time, but 
I had no luck browsing it's documentation either.

Lionel.

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Philippe C. <sql...@pa...>]

Philippe Chaintreuil wrote:
> sql...@li... wrote:
>  > Philippe Chaintreuil wrote:
>  >> > 	I get errors like the following about once a week:
>  >> >
>  >> > Dec 15 19:51:51 hobbes sqlgrey: dbaccess: warning: couldn't do
>  >> > query: UPDATE from_awl SET last_seen = NOW(), first_seen =
>  >> > first_seen WHERE sender_name = 'schuhb?ckoubut' AND
>  >> > sender_domain = 'rocketmail.com' AND src = '124.106.4.237':
>  >> > Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
>  >> > and (utf8_general_ci,COERCIBLE) for operation '=',
>  >> >  reconnecting to DB

	Okay, here's some more detail.

	Fresh log entry:

-----------------------------------------------------------------------
Apr  2 12:48:42 hobbes sqlgrey: warning: Use of uninitialized value in 
concatenation (.) or string at /usr/sbin/sqlgrey line 1139.
Apr  2 12:48:42 hobbes sqlgrey: dbaccess: error: couldn't access 
from_awl table:

Apr  2 12:48:42 hobbes sqlgrey: grey: from awl match: updating 
212.56.148.57(212.56.148.57), 
il<E4>nde...@fa...(il<E4>nde...@fa...)
Apr  2 12:48:42 hobbes sqlgrey: dbaccess: warning: couldn't do query: 
UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE 
sender_name = 'il<E4>nderkebym' AND sender_domain = 'fastmail.fm' AND 
src = '212.56.148.57': Illegal mix of collations 
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for 
operation '=', reconnecting to DB
-----------------------------------------------------------------------

	MySQL information:

-----------------------------------------------------------------------
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------

	So in general, my system is setup to use UTF-8.  Keep in mind that 
client and connection are referring to the mysql command line client here.

-----------------------------------------------------------------------
mysql> SHOW CHARACTER SET LIKE 'latin1';
+---------+----------------------+-------------------+--------+
| Charset | Description          | Default collation | Maxlen |
+---------+----------------------+-------------------+--------+
| latin1  | cp1252 West European | latin1_swedish_ci |      1 |
+---------+----------------------+-------------------+--------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------

	This shows that latin1 causes the latin1_swedish_ci collation to be set 
as default.

-----------------------------------------------------------------------
mysql> SHOW FULL COLUMNS FROM from_awl ;
+---------------------------------------------+
| Field:      sender_name                     |
| Type:       varchar(64)                     |
| Collation:  latin1_swedish_ci               |
| Null:       NO                              |
| Key:        PRI                             |
| Default:    NULL                            |
| Extra:                                      |
| Privileges: select,insert,update,references |
| Comment:                                    |
+---------------------------------------------+
| Field:      sender_domain                   |
| Type:       varchar(255)                    |
| Collation:  latin1_swedish_ci               |
| Null:       NO                              |
| Key:        PRI                             |
| Default:    NULL                            |
| Extra:                                      |
| Privileges: select,insert,update,references |
| Comment:                                    |
+---------------------------------------------+
| Field:      src                             |
| Type:       varchar(39)                     |
| Collation:  latin1_swedish_ci               |
| Null:       NO                              |
| Key:        PRI                             |
| Default:    NULL                            |
| Extra:                                      |
| Privileges: select,insert,update,references |
| Comment:                                    |
+---------------------------------------------+
| Field:      first_seen                      |
| Type:       timestamp                       |
| Collation:  NULL                            |
| Null:       NO                              |
| Key:                                        |
| Default:    CURRENT_TIMESTAMP               |
| Extra:                                      |
| Privileges: select,insert,update,references |
| Comment:                                    |
+---------------------------------------------+
| Field:      last_seen                       |
| Type:       timestamp                       |
| Collation:  NULL                            |
| Null:       NO                              |
| Key:        MUL                             |
| Default:    0000-00-00 00:00:00             |
| Extra:                                      |
| Privileges: select,insert,update,references |
| Comment:                                    |
+---------------------------------------------+
5 rows in set (0.01 sec)
-----------------------------------------------------------------------
	[I reformatted that output so it wouldn't have to wrap.]

	So the Swedish collation is on all the text fields....

	And here are a few of the relevant lines from an mysqldump of my 
sqlgrey database:
-----------------------------------------------------------------------
--
-- Table structure for table `from_awl`
--

DROP TABLE IF EXISTS `from_awl`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `from_awl` (
   `sender_name` varchar(64) NOT NULL,
   `sender_domain` varchar(255) NOT NULL,
   `src` varchar(39) NOT NULL,
   `first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
   `last_seen` timestamp NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY  (`src`,`sender_domain`,`sender_name`),
   KEY `from_awl_lseen` (`last_seen`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
-----------------------------------------------------------------------

	2nd line from the bottom has "DEFAULT CHARSET=latin1;" which shows that 
I followed the HOWTO's instructions when creating the databases.  (I 
checked another database and it has "DEFAULT CHARSET=utf8" like I'd 
expect if I didn't set something explicitly.)



	So I have two questions:

1.)  What's wrong?
2.)  How can I fix it?


	Right now, my leaning is that the connection from sqlgrey is UTF-8 
since that's my system's default and that's causing the error about utf8 
and swedish fighting.
	Now I know next to nothing about non-ASCII encodings, MySQL's deep dark 
secrets, and Perl.  But I did find this page about setting the 
connection's encoding for mysql:

    http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

-- 
    .----------.    ,----------== Peep ==----------,
   /  .-.  .-.  \   `-== (Philippe Chaintreuil) ==-`
  /   | |  | |   \
  \   `-'  `-'  _/      FidoNet Netmail Address:
  /\     .--.  / |             1:2613/118
  \ |   /  /  / /          InterNet  Address:
  / |  `--'  /\ \        pe...@pa...
   /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
      "We're just two lost souls swimming in a fish bowl..."

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Philippe C. <sql...@pa...>]

Lionel Bouton wrote:
> It was already set. I changed the period to daily instead of monthly. 
> But I suspect a bug...

	Hmmm....  Strange.  Anyway, seems like the list isn't high-traffic, so 
I just dropped the digest mode for myself.  That fixes it for me.

-- 
    .----------.    ,----------== Peep ==----------,
   /  .-.  .-.  \   `-== (Philippe Chaintreuil) ==-`
  /   | |  | |   \
  \   `-'  `-'  _/      FidoNet Netmail Address:
  /\     .--.  / |             1:2613/118
  \ |   /  /  / /          InterNet  Address:
  / |  `--'  /\ \        pe...@pa...
   /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
      "We're just two lost souls swimming in a fish bowl..."

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Lionel B. <lio...@bo...>]

Philippe Chaintreuil wrote:
>  > Date: Tue, 18 Dec 2007 23:36:43 +0100
>
> 	Wow.  Digest subscription to this list is *slow*.  I just got your 
> response today March 28th, 2008!  I had almost forgotten I wrote that 
> original e-mail.
>
> 	List owner: You might want to turn on mailman's "digest_send_periodic" 
> option (on the Admin->Digest Options page of the web interface).
>   

It was already set. I changed the period to daily instead of monthly. 
But I suspect a bug...

Lionel

Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

[Philippe C. <sql...@pa...>]

sql...@li... wrote:
 > Philippe Chaintreuil wrote:
 >> > 	I get errors like the following about once a week:
 >> >
 >> > Dec 15 19:51:51 hobbes sqlgrey: dbaccess: warning: couldn't do
 >> > query: UPDATE from_awl SET last_seen = NOW(), first_seen =
 >> > first_seen WHERE sender_name = 'schuhb?ckoubut' AND
 >> > sender_domain = 'rocketmail.com' AND src = '124.106.4.237':
 >> > Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
 >> > and (utf8_general_ci,COERCIBLE) for operation '=',
 >> >  reconnecting to DB



 > Date: Tue, 18 Dec 2007 23:36:43 +0100

	Wow.  Digest subscription to this list is *slow*.  I just got your 
response today March 28th, 2008!  I had almost forgotten I wrote that 
original e-mail.

	List owner: You might want to turn on mailman's "digest_send_periodic" 
option (on the Admin->Digest Options page of the web interface).

 > Hum, is your database configured to use UTF-8?

	I think it is.  I'm using MySQL, and I'm not that well versed in it's 
deep inner-workings.  Probably about a year ago, I read something that 
said I should get that utf8_general_ci,COERCIBLE in there.  I did that 
and it obviously didn't seem to do much.

 > If so you can get such errors, as email adresses are sent from Postfix
 > as pure ASCII which can (and does regularly) use invalid UTF-8
 > sequences. You'll have to convert it to ASCII (latin-1 will do, it's
 > fine when fed with ASCII data).
 >
 > I should really add an entry related to non-ANSI characters
 > (accentuated mainly) to the FAQ  :-)

	I second the FAQ suggestion on this front -- it should also probably 
have the answer to my next question as well:

	Ummm...  How do I fix this in MySQL?

	It should also probably get added to the INSTALL instructions as well.


	Sorry if this has all been addressed since Lionel replied back in December.

-- 
    .----------.    ,----------== Peep ==----------,
   /  .-.  .-.  \   `-== (Philippe Chaintreuil) ==-`
  /   | |  | |   \
  \   `-'  `-'  _/      FidoNet Netmail Address:
  /\     .--.  / |             1:2613/118
  \ |   /  /  / /          InterNet  Address:
  / |  `--'  /\ \        pe...@pa...
   /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
      "We're just two lost souls swimming in a fish bowl..."

Re: [Sqlgrey-users] False Positives

[Lionel B. <lio...@bo...>]

Wayne Spivak wrote:
> I've been running SQLGREY 1.7.5 for several weeks.
>
> My database contains 65K connects, 266 domain_awl and had 14K from_awl
> entries.
>
>   

These numbers mean that greylisting probably blocks most of the SPAM on 
your configuration. I'd be worried if from_awl entries exceeded connect 
entries.

> Looking thru the from_awl entries, there were way too many false positives.
>
>   

I'm not sure what "false positives" are. SPAM sources in the 
auto-whitelists are obviously expected if they are mail servers and not 
basic spam bots. How is having too many "false positives" a problem for 
you? Usually SQL databases handle millions of entries quite easily...


> Is there a way to tighten the whitelisting criteria.
>   

Not much, you can have reconnect_delay set to a higher value if you 
suspect that many spam bots are always reconnecting only once after a 
known delay (setting reconnect_delay above this known delay will prevent 
them to get through), but it will obviously delay legit mails that don't 
match the whitelists. The awl_age can be set lower to clean up the awl, 
but if the servers in the whitelists could get through the first time, 
there's no reason they won't be able to later.

If the spammers use a real mail server, greylisting alone can't protect 
you. If you don't already you should use a safe blacklist (I'd recommend 
sbl-xbl.spamhaus.org) in combination with greylisting :
- if you use it before greylisting it will avoid too many entries in 
auto whitelists but will add more load on the mail servers (greylisting 
is usually much faster than querying RBLs),
- if you use it after greylisting you'll get the same number of entries 
in auto whitelists than without it but the load on your mail server will 
be lower and the load on your database higher.

You'll get the same level of SPAM protection from both.

Lionel

[Sqlgrey-users] False Positives

[Wayne S. <ws...@sb...>]

I've been running SQLGREY 1.7.5 for several weeks.

My database contains 65K connects, 266 domain_awl and had 14K from_awl
entries.

Looking thru the from_awl entries, there were way too many false positives.

Is there a way to tighten the whitelisting criteria.

Below are some of the pertient conf settings:

reconnect_delay = 5
max_connect_age = 24
awl_age = 32
group_domain_level = 20
db_cleandelay = 1800
clean_method = sync 
greymethod = smart
discrimination = off
discrimination_add_rulenr = off
reject_early_reconnect = immed

Thx for the assistance.

Wayne Spivak
SBA * ConsultingR  & SBA.NET.WEBR
divisions of SBA * Consulting, Ltd.
2711 Bellmore Avenue, Bellmore, New York  11710-4319
Tel: 516-221-3306      Fax: 516-221-7129  mailto:WS...@sb...
http://www.sbanetweb.com   or  http://www.sbaconsulting.com
http://www.sbaconsulting.com/press/ -- Published Articles

Re: [Sqlgrey-users] Accent characters cause query failure.

[Lionel B. <lio...@bo...>]

Philippe Chaintreuil wrote:
> 	I get errors like the following about once a week:
>
> Dec 15 19:51:51 hobbes sqlgrey: dbaccess: warning: couldn't do query:
> UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE
> sender_name = 'schuhböckoubut' AND sender_domain = 'rocketmail.com' AND
> src = '124.106.4.237': Illegal mix of collations
> (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
> operation '=', reconnecting to DB
>
> 	You'll notice the 'ö' in the sender_name.  I used to get a different
> error (couldn't coerce?), and found a suggestion to do something to the
> sqlgrey database.  I think that's why I'm now getting the
> utf8_general_ci in the error now.
>
> 	Anyone have any suggestions as to what I can do to fix this?
>
>   

Hum, is your database configured to use UTF-8?

If so you can get such errors, as email adresses are sent from Postfix 
as pure ASCII which can (and does regularly) use invalid UTF-8 
sequences. You'll have to convert it to ASCII (latin-1 will do, it's 
fine when fed with ASCII data).

I should really add an entry related to non-ANSI characters (accentuated 
mainly) to the FAQ :-)

Lionel

[Sqlgrey-users] Accent characters cause query failure.

[Philippe C. <sql...@pa...>]

	I get errors like the following about once a week:

Dec 15 19:51:51 hobbes sqlgrey: dbaccess: warning: couldn't do query:
UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_name = 'schuhböckoubut' AND sender_domain = 'rocketmail.com' AND
src = '124.106.4.237': Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
operation '=', reconnecting to DB

	You'll notice the 'ö' in the sender_name.  I used to get a different
error (couldn't coerce?), and found a suggestion to do something to the
sqlgrey database.  I think that's why I'm now getting the
utf8_general_ci in the error now.

	Anyone have any suggestions as to what I can do to fix this?

-- 
   .----------.    ,----------== Peep ==----------,
  /  .-.  .-.  \   `-== (Philippe Chaintreuil) ==-`
 /   | |  | |   \   On The Other Side Of The Wall!
 \   `-'  `-'  _/      FidoNet Netmail Address:
 /\     .--.  / |             1:2613/118
 \ |   /  /  / /          InterNet  Address:
 / |  `--'  /\ \        pe...@pa...
  /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
     "We're just two lost souls swimming in a fish bowl..."