sqlgrey-users

[Sqlgrey-users] [RELEASE] 1.6.0

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

At last!

1.6.0 is out on sourceforge. Changes from 1.5.9 include cleanups in the
log parser and in the documentation. SQLgrey now logs the actual IP and
the client ID it computes (either IP or class-C) instead of the client
ID alone.

Lionel.

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Ray B. <rj_...@rj...>]

Lionel Bouton wrote:

>At last!
>
>1.6.0 is out on sourceforge. Changes from 1.5.9 include cleanups in the
>log parser and in the documentation. SQLgrey now logs the actual IP and
>the client ID it computes (either IP or class-C) instead of the client
>ID alone.
>
>Lionel.
>
>
>-------------------------------------------------------
>SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
>from IBM. Find simple to follow Roadmaps, straightforward articles,
>informative Webcasts and more! Get everything you need to get up to
>speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
>_______________________________________________
>Sqlgrey-users mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>  
>
Hi Lionel

Thanks for the hard work.  Could you please update the dev ebuild?

Regards
Ray

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Ray B. <rj_...@rj...>]

Lionel Bouton wrote:

>At last!
>
>1.6.0 is out on sourceforge. Changes from 1.5.9 include cleanups in the
>log parser and in the documentation. SQLgrey now logs the actual IP and
>the client ID it computes (either IP or class-C) instead of the client
>ID alone.
>
>Lionel.
>
>
>-------------------------------------------------------
>SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
>from IBM. Find simple to follow Roadmaps, straightforward articles,
>informative Webcasts and more! Get everything you need to get up to
>speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
>_______________________________________________
>Sqlgrey-users mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>  
>
Woops, ignore me :P
Its the stable ebuild link that I need :)

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Michel B. <mi...@bo...>]

Le Vendredi 17 Juin 2005 14:21, Lionel Bouton a =E9crit :
> 1.6.0 is out on sourceforge.

Please find attached the set of patches for adding connect_throttling and=
=20
connect_delete patch to 1.6.0.

I've also produced a set of RPMs of 1.6.0 including these patches, availa=
ble=20
at http://www.bouissou.net/sqlgrey/

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Hi,

I'm currently pushing Michel's patches in sqlgrey for the 1.7.0 release.

First note:

>diff -aurN sqlgrey-1.6.0-Original/sqlgrey sqlgrey-1.6.0-int1/sqlgrey
>--- sqlgrey-1.6.0-Original/sqlgrey	2005-06-16 23:43:07.000000000 +0200
>+++ sqlgrey-1.6.0-int1/sqlgrey	2005-06-19 09:30:49.000000000 +0200
>@@ -1200,6 +1200,13 @@
> 	      ' AND src = ' . $self->quote($host));
> }
> 
>+sub delete_domain_from_connect {
>+    my ($self, $domain, $host) = @_;
>+    $self->do("DELETE FROM $connect " .
>+	      'WHERE sender_domain = ' . $self->quote($domain) .
>+	      ' AND src = ' . $self->quote($host));
>+}
>+
> # Active domain AWL for a domain/IP
> sub move_domain_from_mail_to_domain_awl {
>     my ($self, $domain, $host) = @_;
>@@ -1343,10 +1350,12 @@
> sub delete_mail_ip_from_connect {
>     my ($self, $sender_name, $sender_domain, $addr) = @_;
> 
>+    $sender_name =~ s/^([[:alnum:]]+).*/$1/;
>  
>

This is somehow crude. The goal is to make sure that each connect entry
matching the from we put in from_awl (the result of deverp_user) is
cleaned. The problem is that in the srs' case, this ends like this :
LIKE 'srs0%'
-> all srs connect entries from one domain are cleaned up, but they
won't match the from_awl entry.
Hexadecimal sequences stripping in deverp_user would create the same
problem (but most probably with a lesser real-life impact).

One better way (I think) is to do this :
$sender_name = deverp_user($sender_name);
$sender_name =~ s/#/%/g;

This way the 'LIKE' below will have less chances (there still are some)
of matching entries that wouldn't match the from_awl entry we are creating.

>+    
>     $self->do("DELETE FROM $connect " .
>-	      'WHERE sender_name = ' . $self->quote($sender_name) .
>+	      'WHERE src = ' . $self->quote($addr) .
> 	      ' AND sender_domain = ' . $self->quote($sender_domain) .
>-	      ' AND src = ' . $self->quote($addr));
>+	      ' AND sender_name LIKE ' . $self->quote($sender_name . '%') );
>  
>

(becomes
' AND sender_name LIKE ' . $self->quote($sender_name) );
)

Any thought?

Lionel

Re: [Sqlgrey-users] [RELEASE] 1.6.0

[Michel B. <mi...@bo...>]

Le Lundi 20 Juin 2005 23:23, Lionel Bouton a =E9crit :
>
> >+ =A0 =A0$sender_name =3D~ s/^([[:alnum:]]+).*/$1/;
>
> This is somehow crude.

That's true. I also thought it was "quick and dirty, but efficient".=20
Heuristic, fuzzy-logic programming ;-))

The tradeoff was to assume that there was little chance that several entr=
ies=20
with same "src", same "sender_domain", and "sender_names" with a match at=
 the=20
beginning, AND that wouldn't match when "deverped" to from_awl would be i=
n=20
connect at the same time (remember a legitimate entry is not supposed to =
stay=20
in connect for long...)

For if a given src/domain couple generates much mail, it will quickly go =
to=20
domain_awl, so such problems can occur only as long as it isn't there. If=
 it=20
isn't yet in domain_awl and throttling is in action, there won't be many=20
simultaneous entries in connect, so the chances of collisions are small.

If a given src/domain couple generates few simultaneous entries (moderate=
=20
traffic), then there are little chances that collisions might happen in=20
connect.

Now I had considered the question with the following angle : What if a "L=
IKE%=20
collision in connect" happens anyway ? The answer is that when moving one=
=20
entry in from_awl, we will delete from connect another entry that we=20
shouldn't have deleted.

What are the consequences ? The entry that got deleted will be "greyliste=
d=20
again" instead of accepted immediately when it comes back. The final=20
consequence is that in the rare case where we "delete the wrong entry", i=
t=20
will delay the "deleted entry message" by one more server retry. This wil=
l=20
happen only once for the given sender, of course.

Given the fact that I assume that such collisions will be rather rare, I =
had=20
decided for myself that it wasn't a problem ;-)

> The goal is to make sure that each connect entry=20
> matching the from we put in from_awl (the result of deverp_user) is
> cleaned. The problem is that in the srs' case, this ends like this :
> LIKE 'srs0%'
> -> all srs connect entries from one domain are cleaned up, but they
> won't match the from_awl entry.

True. But the SRS origin domain will very soon go to domain_awl if it for=
wards=20
a noticeable amount of mail to our domain, and if it isn't the case,=20
collisions will be rather rare...

> Hexadecimal sequences stripping in deverp_user would create the same
> problem (but most probably with a lesser real-life impact).
>
> One better way (I think) is to do this :
> $sender_name =3D deverp_user($sender_name);
> $sender_name =3D~ s/#/%/g;
>
> This way the 'LIKE' below will have less chances (there still are some)

I'm not sure that a LIKE with several "%" would be legal and would work w=
ell.=20
I have never used it and would need to check the precise syntax...

> Any thought?

If you're sure that it works, that's good for me. It can solve the proble=
m you=20
mention, however I doubt this problem is of any real-world practical=20
incidence...

Cheers.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] [RELEASE] 1.6.0

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

Michel Bouissou wrote the following on 20.06.2005 23:45 :

>Le Lundi 20 Juin 2005 23:23, Lionel Bouton a =E9crit :
> =20
>
>>>+    $sender_name =3D~ s/^([[:alnum:]]+).*/$1/;
>>>     =20
>>>
>>This is somehow crude.
>>   =20
>>
>
>That's true. I also thought it was "quick and dirty, but efficient".=20
>Heuristic, fuzzy-logic programming ;-))
>
>The tradeoff was to assume that there was little chance that several ent=
ries=20
>with same "src", same "sender_domain", and "sender_names" with a match a=
t the=20
>beginning, AND that wouldn't match when "deverped" to from_awl would be =
in=20
>connect at the same time (remember a legitimate entry is not supposed to=
 stay=20
>in connect for long...)
>
>For if a given src/domain couple generates much mail, it will quickly go=
 to=20
>domain_awl, so such problems can occur only as long as it isn't there. I=
f it=20
>isn't yet in domain_awl and throttling is in action, there won't be many=
=20
>simultaneous entries in connect, so the chances of collisions are small.
>
>If a given src/domain couple generates few simultaneous entries (moderat=
e=20
>traffic), then there are little chances that collisions might happen in=20
>connect.
>
>Now I had considered the question with the following angle : What if a "=
LIKE%=20
>collision in connect" happens anyway ? The answer is that when moving on=
e=20
>entry in from_awl, we will delete from connect another entry that we=20
>shouldn't have deleted.
>
>What are the consequences ? The entry that got deleted will be "greylist=
ed=20
>again" instead of accepted immediately when it comes back. The final=20
>consequence is that in the rare case where we "delete the wrong entry", =
it=20
>will delay the "deleted entry message" by one more server retry. This wi=
ll=20
>happen only once for the given sender, of course.
>
>Given the fact that I assume that such collisions will be rather rare, I=
 had=20
>decided for myself that it wasn't a problem ;-)
>
> =20
>
>>The goal is to make sure that each connect entry=20
>>matching the from we put in from_awl (the result of deverp_user) is
>>cleaned. The problem is that in the srs' case, this ends like this :
>>LIKE 'srs0%'
>>-> all srs connect entries from one domain are cleaned up, but they
>>won't match the from_awl entry.
>>   =20
>>
>
>True. But the SRS origin domain will very soon go to domain_awl if it fo=
rwards=20
>a noticeable amount of mail to our domain, and if it isn't the case,=20
>collisions will be rather rare...
>
> =20
>
>>Hexadecimal sequences stripping in deverp_user would create the same
>>problem (but most probably with a lesser real-life impact).
>>
>>One better way (I think) is to do this :
>>$sender_name =3D deverp_user($sender_name);
>>$sender_name =3D~ s/#/%/g;
>>
>>This way the 'LIKE' below will have less chances (there still are some)
>>   =20
>>
>
>I'm not sure that a LIKE with several "%" would be legal and would work =
well.
>

Standard SQL and perf will be good (we already use indexes to select the
few connect entries that may be relevant).

patch 2 and 3 are already in with minor modifications (I realised some
error messages weren't clear enough from the start so I took the time to
clarify them and some of yours fall into the pack).

Lionel.