Re: [Sqlgrey-users] Incomplete connect table cleanup

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

Michel Bouissou wrote:

>Hi there,
>
>I've noticed that there are situations where the connect table isn't cleaned 
>up as much as it should at normal processing time, leaving its thorough 
>cleanup to the cleanup task, keeping entries in connect longer than 
>necessary, and resulting in messages being logged as "spam:" where they 
>weren't spam (and were actually successfully retried and accepted by 
>SQLgrey).
>  
>

Nice catch!

I remember being aware of this a long time ago, but obviously I forgot
about it...

>
>First case sample:
>=============
>
>Let's assume our group_domain_level = 3 for the sample
>
>from_awl contains:
>123.231.12	joe	bob.com
>123.231.12	bill	bob.com
>
>connect contains:
>123.231.12 alice	bob.com	mi...@my... (Message #1)
>123.231.12 sue	bob.com	pe...@my... (Message #2)
>
>Now let's suppose Message  #1 comes back :
>
>1/ "123.231.12 bob.com" moves to domain.awl
>2/ from_awl gets cleaned of corresponding entries
>3/ "Message #1" entry gets deleted from connect
>
>4/ "Message #2" entry is NOT deleted from connect, although it matches the new 
>entry in domain_awl
>
>5/ When "Message #2" comes back, it is immediately accepted via domain_awl, 
>and thus is NOT cleaned from connect.
>
>6/ Cleanup of "Message #2" from connect will be done 24 hrs later by the 
>cleanup tasks, and it will log it as "spam", where the message was actually 
>represented, and accepted.
>  
>

SQLgrey could clean connect entries when moving entries from "from_awl"
to "domain_awl". There could be cases where the sender won't retry the
connect entries, but if the src made it to domain_awl, the chances are
rather slim of this happening.

>
>2nd case sample
>=============
>
>(VERP-style, I saw something like this for real...)
>
>Let's suppose connect got a number of messages just after subscribing a 
>VERP-style mailing-list:
>
>#1: gentoo+bounces-1119-me=mydom.net | gentoo.org | 140.105.134 | me...@my...
>#2: gentoo+bounces-1120-me=mydom.net | gentoo.org | 140.105.134 | me...@my...
>#3: gentoo+bounces-1121-me=mydom.net | gentoo.org | 140.105.134 | me...@my...
>#4: gentoo+bounces-1122-me=mydom.net | gentoo.org | 140.105.134 | me...@my...
>
>Now suppose Message #1 comes back :
>
>1/ it gets added to from_awl in its de-VERP'd (here de-plussed) form:
>gentoo | gentoo.org | 140.105.134
>
>2/ Entry #1 gets deleted from connect
>
>3/ Entries #2-4 are NOT deleted from connect (although they match the entry 
>just added to from_awl)
>
>4/ When Messages #2-4 come back, they are immediately accepted via from_awl, 
>and thus are NOT cleaned from connect.
>
>6/ Cleanup of Messages #2-4 from connect will be done 24 hrs later by the 
>cleanup tasks, and it will log them as "spam", where the messages were 
>actually represented, and accepted.
>
>
>Hmmmmm.... Comments ?
>  
>

This one is less obvious. The only way would be to delete entries in
"connect" each time a message is allowed to pass (maybe not only for
AWLs but also for whitelists: if you update them you get the same
beahviour).

Anyway you shouldn't trust "spam:" log entries: there are other cases
where you get bogus log entries (server pools that aren't recognised by
'classc' and 'smart' generate them). I may have chosen a bad header for
the new log format (the old one used "Probable SPAM" IIRC and better
reflected the situation).

The current sqlgrey-logstats.pl is quite dumb (my main goal was to have
top AWL hits to help admins put common MTAs in the *whitelist.local
files and a rough estimate of the AWL performance), but I believe it can
be more clever about these cases, hopefully sorting the bogus entries
from the real SPAM by checking the whole history of the SQLgrey actions.
I'll release 1.5.9 shortly with it and the MySQL timestamp fix.

I've still one problem with the AWL performance report: from what I can
see, most of the delayed mails are in fact SPAM sent by MTAs: they don't
match AWLs simply because they don't match common traffic: the AWL
performance is thus underestimated in the report :-(

Lionel.