sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] PATCH to sqlgrey-logstats.pl

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

Le Jeudi 09 Juin 2005 11:55, Michel Bouissou a =E9crit :
> Attached.
> Adds feature : Analysis / report of throttled connections.

Oops. This one replaces the previous one, and fixes a bug.

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

[Sqlgrey-users] PATCH to sqlgrey-logstats.pl

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

Attached.

Adds feature : Analysis / report of throttled connections.

Sample output:

[root@totor sbin]# sqlgrey-logstats.pl -d < /var/log/mail/info | less

##################
## Global stats ##
##################

Events      : 437
Passed      : 129
Rejected    : 186
New         : 222
Early       : 63
Throttled   : 23

[...]

#######################
## Throttled details ##
#######################

61.31.169.194: 5
            jk...@ms...: 3
            td...@ms...: 1
            sc...@ms...: 1
70.64.2.41: 3
            chu...@sy...: 2
            f_c...@uo...: 1
211.37.104.90: 2
            d.c...@bi...: 2
218.10.171.230: 2
            jk...@at...: 1
            mcp...@bi...: 1
82.159.17.55: 2
            m_c...@ne...: 2
59.115.2.80: 1
            jod...@ea...: 1
218.162.149.30: 1
            ric...@pa...: 1
213.177.150.6: 1
            am...@ne...: 1
80.71.101.58: 1
            jdu...@sp...: 1
202.101.71.62: 1
            a.r...@wa...: 1
80.11.209.13: 1
            jas...@sy...: 1
83.157.26.175: 1
            sel...@ya...: 1
61.31.169.243: 1
            hb...@cs...: 1
61.72.65.78: 1
            ek...@do...: 1


New "unofficial" RPMs including this patch available from 
http://www.bouissou.net/sqlgrey/

Cheers.

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

Re: [Sqlgrey-users] Throttling again ;-)

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

Le Mercredi 08 Juin 2005 19:26, Lionel Bouton a =E9crit :
>
> Unless a new function we are discussing on the mailing-list is proven u=
seful
> to me shortly, I'm planning to release a 1.6.0 stable version based on
> 1.5.9.=20

After some thoughts, I have a couple more things in favor of "throttling"=
 :

1/ The supplementary SELECT count(*) we perform against the connect table=
=20
before deciding if we will accept or not to add a new entry, which is of =
some=20
performance concern to you, is to some extent compensated by the fact tha=
t we=20
save an INSERT each time we refuse an entry -- and that makes also a DELE=
TE=20
that we save at some point in the future for cleanup.

2/ Throttling can to some extent be considered as "self-dynamic-blacklist=
ing",=20
which looks nice : I see some patterns by looking at my logs, showing tha=
t=20
the same spam sources (Zombie machines used as SMTP relays ? Viruses /=20
worms ?) tend to come back again and again randomly in time, with differe=
nt=20
payloads (sender / recipient). If we use throttling, once they've filled =
up=20
their not-retried "quota" in connect, when they come back again, their ne=
w=20
connection is refused without generating any new entry in connect, which =
in=20
turn reduces the chances that they could possibly defeat the greylisting=20
system by trying to resend (at random) a message with a sender/recipient=20
couple already known to the connect table.

By limiting the number of connect entries we allow for a given (new) sour=
ce,=20
we limit its possibility of passing greylisting by chance. This looks ver=
y=20
interesting to me as it can make greylisting yet more efficient.

So this throttling in connect gives a number of interesting things, besid=
es=20
simply saving entries in connect -- and making impossible a connect-flood=
=20
attack, even if the odds that this happens seem remote.

The more I think about it, the more it seems to me that it can only make =
the=20
system more secure, and certainly not less. And well, anyway, "it doesn't=
=20
hurt" ;-)

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

[Sqlgrey-users] Re: bug in SQLGrey 1.4.x?

[Paolo A. <and...@ap...>]

On Wed, 8 Jun 2005, Lionel Bouton wrote:

>> Thanks for replay.
>> The daemon dead. So mail are refused and users ... ;-)
>>
>
> I assume the daemon dies at the time you get the Syslog.pm error. I'm

Yes.

> betting on a pure Perl problem. Which version of Net::Server::Multiplex
> do you have ?

I think it is 0.87.
I have forced reinstall via CPAN.
Also reinstalled DBD::SQLite (1.08)
But for what I know none have updated it in last days (neither last weeks)

>> I have Verbose = 1. Can increase to 2/3/... or is a on/off parameter?
>> No more info from postfix.
>
> You can comment "verbose = 1" and uncomment "debug" in sqlgrey.conf.

TEsting in progress. At the moment no new useful data in logs (or I don't 
see it because I don't know what serach ... ;-))) )


-- 


Regards,
                          Paolo

____________________________________________

[Sqlgrey-users] Re: bug in SQLGrey 1.4.x?

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

Paolo Andretta wrote:

> On Wed, 8 Jun 2005, Lionel Bouton wrote:
>
>>> Thanks for your work.
>>> I am using SQLGrey in a Trustix box (attached rpm -qi report)
>>>
>>> Previous 1.4.x version have problems (in my box) when DB (SQL Lite in
>>> /var/lib/sqlgrey), became about 16MB size.
>>> After upgrade to 1.4.8 I have problems also with smallest DB.
>>> I solve restarting the server and if still having problem, removing
>>> the DB that was recreated on next start.
>>> I have similar problem to 2 different box (but same linux
>>> distro/conf); so I can't understand if it is a sqlgrey's problem or my
>>> perl/conf problem.
>>>
>>> This is what I think is relevant in my log:
>>>
>>> Jun  8 10:56:14 mx7 sqlgrey: fatal: Modification of a read-only value
>>> attempted at
>>> /usr/lib/perl5/5.8.5/i586-linux-thread-multi/Sys/Syslog.pm line 312.
>>
>
>> You may have several problems here. The fact that you get problems when
>> the size of the database grows probably means there is a problem with
>> SQLite or its Perl DBD driver.
>> You didn't tell what problems you had exactly so I don't have any idea
>> of what is happening.
>
>
> Thanks for replay.
> The daemon dead. So mail are refused and users ... ;-)
>

I assume the daemon dies at the time you get the Syslog.pm error. I'm
betting on a pure Perl problem. Which version of Net::Server::Multiplex
do you have ?

>
>> The Syslog.pm errors are puzzling. Did you have them with previous 1.4.x
>> versions or only with 1.4.8?
>
>
> Also with the previous 1.4.x. But very limited compared with current
> problems.
>
>> What was sqlgrey doing at the time (you can use a higher log level
>> and check the postfix logs of the same period)?
>
>
> I have Verbose = 1. Can increase to 2/3/... or is a on/off parameter?
> No more info from postfix.


You can comment "verbose = 1" and uncomment "debug" in sqlgrey.conf.

>
>
> 1.5.x transition can be an option for a production server?


With SQLite, I'm not sure (the point I'm worrying about is the new
asynchronous cleanup, I've made it run a few times on SQLite but not on
an extended period).
1.5.x is well tested with MySQL and PostgreSQL. Unless a new function we
are discussing on the mailing-list is proven useful to me shortly, I'm
planning to release a 1.6.0 stable version based on 1.5.9.

> I can consider using MySQL with 1.4.x or 1.5.x.
>
Then 1.5.9 with MySQL should be fine. If you have Syslog.pm errors
and/or crashes in this configuration, then it's most probably a Perl
problem as many users run MySQL with 1.5.x without trouble.

Lionel.

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Le Mercredi 08 Juin 2005 13:46, Lionel Bouton a =E9crit :
>
> MySQL already changed its behaviour between versions regarding
> timestamps. The =A0fact that "desc from_awl" doesn't output the CREATE
> statement used by SQLgrey but a mangled one shows that MySQL
> deliberately allows itself to change the CREATE statement.

Indeed. By adding its "default behaviour" (which is to consider that the =
first=20
mentioned timestamp column [for which "default 0" is not specified (*)] i=
s=20
auto-update...)

(*) Beginning with MySQL 4.1.2 and on...

Furthermore, "desc" output is incomplete and doesn't always mention _all_=
 the=20
characteristics of a given column ("on update..." isn't displayed anywher=
e by=20
desc).

=3D> If we don't want any auto-update or auto-initialization at all, we s=
hould=20
probably use the "datetime" data type, rather than "timestamp".

> I don't want to rely on the assumptions that the future MySQL versions =
won't
> introduce other on the fly modifications of CREATE statements...

Yep, but "default 0" would be harmless anyway ;-)) Yes, I know,=20
"harmless" ;-)))

...But it would prevent other manual queries that would be done on the ta=
bles=20
to cause the first_seen timestamp to be inadvertently auto-updated.

> I prefer to solve this by forcing the databases to set first_seen to th=
e
> right value on each update. At least no database seems to modify UPDATE
> statements on the fly! This is how it is done in 1.5.9.

Yes, I've checked the diff between 1.5.8 and 1.5.9.

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

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Michel Bouissou wrote:

>Le Mercredi 08 Juin 2005 11:05, Lionel Bouton a =E9crit :
> =20
>
>>"It doesn't hurt anyway" isn't enough. It must solve real world
>>problems. I'm aware that theoriticaly this is good to have less entries
>>in the connect table but as I said earlier the practical benefits aren'=
t
>>clear to me yet.
>>   =20
>>
>
>I strongly believe there are benefits, otherwise I wouldn't have asked f=
or it=20
>in the first time then coded it in the end ;-)
>
>Well, I know, I can be mistaken ;-)
>
> =20
>
>>Michel, could you give us a ratio between the results of:
>>grep "sqlgrey: grey: throttling: " | wc -l " (on a log spanning the las=
t
>>max_connect_age period)
>>and
>>select count(*) from connect
>>
>>on your configuration ? This would help measure the benefits of tarpitt=
ing.
>>   =20
>>
>
>I'm not sure my server is a good real-life example, as its traffic is re=
ally=20
>moderate.
>
>OTOH, I've already seen some tapitting in action since I installed it=20
>yesterday afternoon, and I recall my "connect" table size had been mutip=
lied=20
>by a factor 10 when the latest M$ worm came out... Hence the idea I had =
about=20
>tarpitting for fighting this kind of event.
>Guess we need another new M$ worm to figure out the benefits it gives wh=
en=20
>such an event occurs...
>
> =20
>
>>If other users could fetch Michel's build and test it in the same manne=
r
>>too that would be great.
>>   =20
>>
>
>Yep. I'd love to get some feedback.
>
> =20
>
>># connect cleanup
>>
>>I'm worrying about the LIKE. There are 2 problems with it:
>>- may hurt performance (I've no experience with it, I'm currently
>>guessing performance is OK),
>>   =20
>>
>
>It probably won't hurt, as the query still use the main index for IP and=
=20
>sender_domain, leaving the LIKE select a very small subset of entries in=
=20
>connect...
>
> =20
>
>>- I'll have to check SQLite to see if it supports this.
>>   =20
>>
>
>LIKE is a very standard SQL statement... I would be surprised if a decen=
t SQL=20
>system didn't implement it.
> =20
>

SQLite supports it (at least SQLite2 and SQLite3 do, which is enough for
me).
I'll take the cleanup code then. But don't start trusting the spam log
entries: there are cases where it will have false positives.

>BTW, have you considered creating the tables with "default 0" for timest=
amp=20
>columns ? "default 0" should be OK with any SQL, isn't it ? And it would=
=20
>prevent MySQL from performing auto-updates...
> =20
>

MySQL already changed its behaviour between versions regarding
timestamps. The  fact that "desc from_awl" doesn't output the CREATE
statement used by SQLgrey but a mangled one shows that MySQL
deliberately allows itself to change the CREATE statement. I don't want
to rely on the assumptions that the future MySQL versions won't
introduce other on the fly modifications of CREATE statements...

I prefer to solve this by forcing the databases to set first_seen to the
right value on each update. At least no database seems to modify UPDATE
statements on the fly! This is how it is done in 1.5.9.

Lionel

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Le Mercredi 08 Juin 2005 11:05, Lionel Bouton a =E9crit :
>
> Michel, could you give us a ratio [...]

> If other users could fetch Michel's build and test it in the same manne=
r
> too that would be great.

Everybody can easily figure out if it could save many entries in their co=
nnect=20
table by performing manually a simple sql query such as :

select src, count(*) as cpt from connect group by src having cpt >=3D 3 o=
rder by=20
cpt desc, src;

(replace >=3D 3 with any value you would consider for setting the tarpitt=
ing=20
threshold)

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

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Le Mercredi 08 Juin 2005 13:06, Michel Bouissou a =E9crit :
>
> > Michel, could you give us a ratio between the results of:
> > grep "sqlgrey: grey: throttling: " | wc -l " (on a log spanning the l=
ast
> > max_connect_age period)
> > and
> > select count(*) from connect
> >
> > on your configuration ? This would help measure the benefits of
> > tarpitting.
>
> I'm not sure my server is a good real-life example, as its traffic is
> really moderate.
>
> OTOH, I've already seen some tapitting in action since I installed it
> yesterday afternoon, and I recall my "connect" table size had been
> mutiplied by a factor 10 when the latest M$ worm came out... Hence the =
idea
> I had about tarpitting for fighting this kind of event.
> Guess we need another new M$ worm to figure out the benefits it gives w=
hen
> such an event occurs...

Anyhow, for now I have:

mysql> select count(*) from connect;
+----------+
| count(*) |
+----------+
|      198 |
+----------+

[root@totor etc]# grep -c "totor sqlgrey: grey:=20
throttling:" /var/log/mail/info
29


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

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Le Mercredi 08 Juin 2005 11:05, Lionel Bouton a =E9crit :
>
> "It doesn't hurt anyway" isn't enough. It must solve real world
> problems. I'm aware that theoriticaly this is good to have less entries
> in the connect table but as I said earlier the practical benefits aren'=
t
> clear to me yet.

I strongly believe there are benefits, otherwise I wouldn't have asked fo=
r it=20
in the first time then coded it in the end ;-)

Well, I know, I can be mistaken ;-)

> Michel, could you give us a ratio between the results of:
> grep "sqlgrey: grey: throttling: " | wc -l " (on a log spanning the las=
t
> max_connect_age period)
> and
> select count(*) from connect
>
> on your configuration ? This would help measure the benefits of tarpitt=
ing.

I'm not sure my server is a good real-life example, as its traffic is rea=
lly=20
moderate.

OTOH, I've already seen some tapitting in action since I installed it=20
yesterday afternoon, and I recall my "connect" table size had been mutipl=
ied=20
by a factor 10 when the latest M$ worm came out... Hence the idea I had a=
bout=20
tarpitting for fighting this kind of event.
Guess we need another new M$ worm to figure out the benefits it gives whe=
n=20
such an event occurs...

> If other users could fetch Michel's build and test it in the same manne=
r
> too that would be great.

Yep. I'd love to get some feedback.

> # connect cleanup
>
> I'm worrying about the LIKE. There are 2 problems with it:
> - may hurt performance (I've no experience with it, I'm currently
> guessing performance is OK),

It probably won't hurt, as the query still use the main index for IP and=20
sender_domain, leaving the LIKE select a very small subset of entries in=20
connect...

> - I'll have to check SQLite to see if it supports this.

LIKE is a very standard SQL statement... I would be surprised if a decent=
 SQL=20
system didn't implement it.

BTW, have you considered creating the tables with "default 0" for timesta=
mp=20
columns ? "default 0" should be OK with any SQL, isn't it ? And it would=20
prevent MySQL from performing auto-updates...

Cheers.

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

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Michel Bouissou wrote:

>Lionel, would you consider integrating this into the mainstream SQLgrey ? As 
>throttling is completely optional, "it doesn't hurt anyway", and somebody who 
>doesn't want the feature can just ignore it.
>  
>

# Throttling

"It doesn't hurt anyway" isn't enough. It must solve real world
problems. I'm aware that theoriticaly this is good to have less entries
in the connect table but as I said earlier the practical benefits aren't
clear to me yet.

Michel, could you give us a ratio between the results of:
grep "sqlgrey: grey: throttling: " | wc -l " (on a log spanning the last
max_connect_age period)
and
select count(*) from connect

on your configuration ? This would help measure the benefits of tarpitting.

If other users could fetch Michel's build and test it in the same manner
too that would be great.

# connect cleanup

I'm worrying about the LIKE. There are 2 problems with it:
- may hurt performance (I've no experience with it, I'm currently
guessing performance is OK),
- I'll have to check SQLite to see if it supports this.

Once I've a better understanding of the two points above, I'll make a
decision on the connect cleanup (should be shortly).

Lionel.

Re: [Sqlgrey-users] nproxy.gmail.com

[Klaus A. S. <kse...@gm...>]

Lionel Bouton wrote:

>>> 64.233.183 in clients_ip_whitelist.local (google registered 64.233.160.=
0/19
>>> anyway)
>>=20
>> Rather 64.233.182.  64.233.183 is covered by the
>> /^[mnrwz]proxy\.gmail\com$/ regexp, at least as of now, but I get
>> your point.
> =20
> Unless the DNS is answering differently to me (could be),
> nproxy.gmail.com does only cover 64.233.182.x addresses.
>=20
> The fqdn Postfix hands to SQLgrey is the result of a double query: first
> a PTR query to get the fqdn (both 62.233.182.... and 64.233.183....
> points to nproxy.gmail.com) then it queries the fqdn to check that the
> fqdn covers the original IP to be sure that it isn't a simple trick :
> this is were 64.233.183 isn't found and Postfix considers the address
> unknown.
>=20
> So /^[mnrwz]proxy\.gmail\com$/ can only match the systems on 62.233.182.x
>=20
> Am I mistaken?

No, not anymore. ;-)  When I sent my original email DNS listed nproxy
as 64.233.183.192-207, but hosts HELOing as nproxy came from address
in 62.233.182.x.  Now it seems DNS has been corrected so that nproxy
has the IP addresses 64.233.182.192-207.  Case solved, I guess.  It
must have been a typo in the Gmail zone file.

Cheers,

--=20
Klaus Alexander Seistrup
Magnetic Ink =B7 Copenhagen =B7 Denmark
http://magnetic-ink.dk/

Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Le Mardi 07 Juin 2005 18:47, Lionel Bouton a =E9crit :
>
> SQLgrey 1.5.9 tarball is on sourceforge [...]

Hi there,

My "connect throttling" and "connect cleanup" patches have been tested he=
re=20
and seem to be working very fine. Please find attached the complete patch=
=20
against 1.5.9.

I've produced 1.5.9 RPMs including this patch, available from=20
http://www.bouissou.net/sqlgrey/

Some sample of working throttling, taken from my logs:

Jun  8 02:30:29 totor sqlgrey: grey: new: 24.208.114.197,=20
fzj...@bu... -> da...@bo...

Jun  8 02:30:30 totor sqlgrey: grey: new: 24.208.114.197,=20
fzj...@bu... -> cl...@bo...

Jun  8 02:30:31 totor sqlgrey: grey: new: 24.208.114.197,=20
fzj...@bu... -> fi...@bo...

Jun  8 02:30:31 totor sqlgrey: grey: new: 24.208.114.197,=20
fzj...@bu... -> ad...@bo...

Jun  8 02:30:31 totor sqlgrey: grey: new: 24.208.114.197,=20
fzj...@bu... -> ope...@bo...

Jun  8 02:30:34 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> ch...@bo...

Jun  8 02:30:39 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> al...@bo...

Jun  8 02:30:44 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> ad...@bo...

Jun  8 02:30:55 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> ad...@bo...

Jun  8 02:31:02 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> ic...@bo...

Jun  8 02:31:07 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> ca...@bo...

Jun  8 02:31:13 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> aa...@bo...

Jun  8 02:31:19 totor sqlgrey: grey: throttling: 24.208.114.197,=20
fzj...@bu... -> de...@bo...


...and I have several of the kind.

I think that throttling may not only save space in connect, but also help=
=20
prevent some zombies (that tries random addresses from a dictionary or=20
infected machine's address book) from being able to pass thru greylisting=
 in=20
the end : By limiting the number of waiting entries for a given source in=
=20
connect, we reduce the chances that a random new try from the same source=
=20
matches a previous attempt, thus effectively improving the system's=20
efficiency.

Lionel, would you consider integrating this into the mainstream SQLgrey ?=
 As=20
throttling is completely optional, "it doesn't hurt anyway", and somebody=
 who=20
doesn't want the feature can just ignore it.

Cheers.

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

Re: [Sqlgrey-users] nproxy.gmail.com

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

Klaus Alexander Seistrup wrote:

>Lionel Bouton wrote:
>
>  
>
>>>Perhaps Google's BOfHs has made a typo somewhere, because
>>>nproxy.gmail.com has the IP addresses 64.233.183.192-207, perhaps not.
>>>      
>>>
>>They probably made a typo.
>>    
>>
>
>I hope so.
>
>  
>
>>64.233.183 in clients_ip_whitelist.local (google registered 64.233.160.0/19
>>anyway)
>>    
>>
>
>Rather 64.233.182.  64.233.183 is covered by the
>/^[mnrwz]proxy\.gmail\com$/ regexp, at least as of now, but I get your
>point.
>  
>

Unless the DNS is answering differently to me (could be),
nproxy.gmail.com does only cover 64.233.182.x addresses.

The fqdn Postfix hands to SQLgrey is the result of a double query: first
a PTR query to get the fqdn (both 62.233.182.... and 64.233.183....
points to nproxy.gmail.com) then it queries the fqdn to check that the
fqdn covers the original IP to be sure that it isn't a simple trick :
this is were 64.233.183 isn't found and Postfix considers the address
unknown.

So /^[mnrwz]proxy\.gmail\com$/ can only match the systems on 62.233.182.x

Am I mistaken?

Lionel

Re: [Sqlgrey-users] nproxy.gmail.com

[Klaus A. S. <kse...@gm...>]

Lionel Bouton wrote:

>> Perhaps Google's BOfHs has made a typo somewhere, because
>> nproxy.gmail.com has the IP addresses 64.233.183.192-207, perhaps not.
>=20
> They probably made a typo.

I hope so.

> 64.233.183 in clients_ip_whitelist.local (google registered 64.233.160.0/=
19
> anyway)

Rather 64.233.182.  64.233.183 is covered by the
/^[mnrwz]proxy\.gmail\com$/ regexp, at least as of now, but I get your
point.

Cheers,

--=20
Klaus Alexander Seistrup
Copenhagen =B7 Denmark
http://seistrup.dk/

Re: [Sqlgrey-users] RELEASE: 1.5.9

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

Lionel Bouton wrote:

>Hi,
>
>SQLgrey 1.5.9 tarball is on sourceforge (RPMs should come shortly after,
>I'm fighting with failing hardware on the RH8 host I use for building RPMs).
>
>  
>

RPMS are on sourceforge.

Re: [Sqlgrey-users] Incomplete connect table cleanup

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

Le Mardi 07 Juin 2005 17:44, Michel Bouissou a =E9crit :
> Le Mardi 07 Juin 2005 17:26, Lionel Bouton a =E9crit :
> > SQLgrey could clean connect entries when moving entries from "from_aw=
l"
> > to "domain_awl".
>
> I think this is good, and wouldn't be a performance penalty as movement=
s to
> domain_awl are quite rare...
[...]
> But by first calculating a "sender_truncated" that would be the sender_=
name
> truncated to only its alphanumeric beginning /^[a-zA-Z0-9]+/ and using =
an
> SQL "like" to delete everything that begins with this (for same domain =
and
> IP of course).

I propose the attached patch to fix this (supposed to be applied after my=
=20
previous "throttling" patch, but should work as well without it).

Not much tested yet ;-)

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

[Sqlgrey-users] RELEASE: 1.5.9

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

Hi,

SQLgrey 1.5.9 tarball is on sourceforge (RPMs should come shortly after,
I'm fighting with failing hardware on the RH8 host I use for building RPMs).

Changelog :
- MySQL timestamp bugfix,
- improved log parser.

Happy greylisting,

Lionel.

Re: [Sqlgrey-users] Incomplete connect table cleanup

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

Le Mardi 07 Juin 2005 17:26, Lionel Bouton a =E9crit :
>
> SQLgrey could clean connect entries when moving entries from "from_awl"
> to "domain_awl".

I think this is good, and wouldn't be a performance penalty as movements =
to=20
domain_awl are quite rare...

> There could be cases where the sender won't retry the=20
> connect entries, but if the src made it to domain_awl, the chances are
> rather slim of this happening.

And anyway it wouldn't matter if the connect entry has been deleted, and =
is=20
not retried...

> >2nd case sample
> >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> >(VERP-style, I saw something like this for real...)
[...]
> 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).

For whitelists, it's less of a problem (IMHO) if entries remain in connec=
t or=20
AWLs for a while before being purged by the cleanup task.

I think deleting from connect each time a message is allowed to pass woul=
d be=20
too much of a performance penalty.

But I suggest instead that each time an address is moved from connect to=20
from_awl, connect should be purged not with :

$self->delete_mail_ip_from_connect($sender_name, $sender_domain,
					   $cltid);

But by first calculating a "sender_truncated" that would be the sender_na=
me=20
truncated to only its alphanumeric beginning /^[a-zA-Z0-9]+/ and using an=
 SQL=20
"like" to delete everything that begins with this (for same domain and IP=
 of=20
course).

There would be a very little risk to delete a "corresponding" longer addr=
ess=20
from the same IP and domain waiting in connect, but I think this would be=
=20
very rare, and would only result in the delayed message being delayed a=20
little longer, but I'm sure it will happen once in years ;-)

It would surely be much more efficient on the performance standpoint than=
=20
trying to delete from connect each and everytime we accept a message...

Cheers.

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

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.

[Sqlgrey-users] Incomplete connect table cleanup

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

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).


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.


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 ?

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

Re: [Sqlgrey-users] [Patch] Tarpitting / Throttling

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

Le Lundi 06 Juin 2005 21:44, Lionel Bouton a =E9crit :
>
> Thousands of entries shouldn't be a problem (there are configurations
> out there with 10s of thousands of entries in the connect table). In
> fact hundreds of thousands of entries should be common on big
> configurations.

Thousands of entries shouldn't be a problem =3D> On "fast machine with bi=
g disk=20
at big ISP".

I think SQLgrey is not made only for "big ISP with big server", but can a=
lso=20
be used at "small regional office with small email gateway with slow=20
processor, little RAM and little disk" (i.e. OpenBrick with VIA processor=
),=20
isn't it ?

> Today an attack on the connect table is mostly theoritical. To do such
> an attack, the attacker must :
> - know at least one valid rcpt address (on configs that don't use
> "reject_unlisted_recipient" before greylisting, they should do so),

Some configs may prefer to use greylisting _before_ recipient check to pr=
event=20
possible dictionary attacks to be able to determine which email addresses=
=20
exist or not at a given domain, before the mail comes back a 2nd time...

The nice thing with greylisting is that it can be used in a number of=20
different ways which provides different "features" ;-)

> - make hundreds of thousands of SMTP transactions to Postfix with
> different "FROM" headers. On most configurations you probably can't mak=
e
> more than 10s of such transactions/s.

NOT ONLY FROM, "RCPT TO:" is enough. ONE message with ONE "MAIL FROM:" an=
d 50=20
different "RCPT TO:" makes 50 new entries in connect... With this diction=
ary=20
attacks or random recipient attacks can fill a connect table with junk qu=
ite=20
fast (and I have experienced it for real).

> >and B3/ the connect table wouldn't grow too big on disk
>
> True, but mostly connected to B1 (disk space is cheap).

Assertion #1: Disk space is cheap (true)

Assertion #2: One given disk doesn't grow by itself (true)

Assertion #3: The bigger disk you have, the faster you tend to fill it up=
 with=20
junk (and the less housekeeping you do) (true)

Assertion #4: Although regrettable, quite a number of servers out there r=
un=20
with little left free disk space (true)

Assertion # 5: Any given disk has a natural tendancy to get full at some =
point=20
in time (true)

So taking care of not wasting precious disk space still makes sense ;-)

> I'm not yet convinced this would be really useful, probably harmless
> though.

Well, rather than discussing for ages whether the idea would be good or n=
ot,=20
I'm for the experimental method ;-)

So I've written a patch to include tarpitting / throttling in SQLgrey, wi=
th=20
the already discussed "refinements".

Please find it attached, and feel free to test for yourself if this bring=
s an=20
improvement or makes things worse...

My first tests tend to make me think that it works nice, but I'm definite=
ly no=20
Perl expert, so feel free to double-check the code.

I suggest adding the following paragraph to sqlgrey.conf :

## Throttling too many new entries from new host
# Setting this optional parameter will refuse an excessive number of
# new entries in the connect table from the same host, in the following
# manner:
# - If there are already "connect_src_throttle" entries in the connect
#   table from the same host (e-mails which have not been retried yet)
# - And there is NO entry for this host in domain_awl
# - And there are LESS than "connect_src_throttle" entries in the
#   from_awl table for this host
# THEN further incoming connections from this host will be (temporarily)
# refused without new entries being created in the connect table (until
# some already waiting entries have been successfully retried).
# This feature may prevent the connect table from growing too big and
# being polluted by spambots, viruses, zombie machines and the like.
# If set to "0" (default), this feature won't be used.
connect_src_throttle =3D 0

# connect_src_throttle =3D 5


Cheers.

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

Re: [Sqlgrey-users] nproxy.gmail.com

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

Klaus Alexander Seistrup wrote:

>Hi SQLgrey Users,
>
>SPF for Gmail lists /^[mnrwz]proxy\.gmail\com$/ as outgoing MTAs for
>gmail.com, and I have listed those hosts in
>clients_fqdn_whitelist.local -- no problem.
>
>However, lately I have seen a bunch of hosts with IP addresses in one
>of Google's IP blocks that HELOs as nproxy.gmail.com.  Their reverse
>DNS reolves to nproxy.gmail.com, but the IP addresses are not listed
>for nproxy.gmail.com so Postfix skips the name entirely.
>
>My SQLgrey table now has 9 IP addresses (min 64.233.182.192, max
>64.233.182.207) associated with the same sender and recipient, and
>Gmail keeps trying delivering, but gets greylisted each time because
>it tries from another IP address each time, which causes a
>considerable delay.
>  
>

Annoying, to say the least.

>Perhaps Google's BOfHs has made a typo somewhere, because
>nproxy.gmail.com has the IP addresses 64.233.183.192-207, perhaps not.
>  
>

They probably made a typo.

> If the current hosts that HELO as nproxy.gmail.com all lie within the
>range 64.233.182.192-207 (i.e., 182 instead of 183), then eventually
>mail will get through but the delay is annoying.
>
>Do I have a better choice than putting e.g. 64.233.182.192-207 in my
>clients_ip_whitelist.local file?
>  
>

From the look of the whois requests I did, you could probably put

64.233.183 in clients_ip_whitelist.local (google registered 64.233.160.0/19 anyway)

Cheers,

Lionel

[Sqlgrey-users] nproxy.gmail.com

[Klaus A. S. <kse...@gm...>]

Hi SQLgrey Users,

SPF for Gmail lists /^[mnrwz]proxy\.gmail\com$/ as outgoing MTAs for
gmail.com, and I have listed those hosts in
clients_fqdn_whitelist.local -- no problem.

However, lately I have seen a bunch of hosts with IP addresses in one
of Google's IP blocks that HELOs as nproxy.gmail.com.  Their reverse
DNS reolves to nproxy.gmail.com, but the IP addresses are not listed
for nproxy.gmail.com so Postfix skips the name entirely.

My SQLgrey table now has 9 IP addresses (min 64.233.182.192, max
64.233.182.207) associated with the same sender and recipient, and
Gmail keeps trying delivering, but gets greylisted each time because
it tries from another IP address each time, which causes a
considerable delay.

Perhaps Google's BOfHs has made a typo somewhere, because
nproxy.gmail.com has the IP addresses 64.233.183.192-207, perhaps not.
 If the current hosts that HELO as nproxy.gmail.com all lie within the
range 64.233.182.192-207 (i.e., 182 instead of 183), then eventually
mail will get through but the delay is annoying.

Do I have a better choice than putting e.g. 64.233.182.192-207 in my
clients_ip_whitelist.local file?

Has anyone else on the list experienced this?

Cheers,

--=20
Klaus Alexander Seistrup
Copenhagen =B7 Denmark
http://seistrup.dk/

Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

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

Michel Bouissou wrote:

>Le Dimanche 05 Juin 2005 20:15, Lionel Bouton a =E9crit :
> =20
>
>>>Other subject: Lionel, have you had time to think again about the
>>>tarpitting / throttling feature that I had suggested ? I still would l=
ike
>>>it ;-)
>>>     =20
>>>
>>I'm not yet convinced it's a good idea. I'll sum up what I understand
>>about tarpitting below for you to point mistakes or missing points.
>>
>>Tarpitting (refusing to create new connect entries if there are already
>><n> existing entries with the same source, with refinements to disable
>>tarpitting when domain_awl holds entries for the source or enough
>>entries exist in from_awl) could help preventing pollutions of the
>>connect table from one single src.
>>   =20
>>
>
>Yes, and it might by the way prevent a possible attack against a greylis=
ting=20
>mailserver, as otherwise it would be easy for an evil system to flood a=20
>connect table with thousands of entries or more...
>
> =20
>

Thousands of entries shouldn't be a problem (there are configurations
out there with 10s of thousands of entries in the connect table). In
fact hundreds of thousands of entries should be common on big
configurations.
You can already consider that ISPs are under DDOS from Windows Zombies...

Today an attack on the connect table is mostly theoritical. To do such
an attack, the attacker must :
- know at least one valid rcpt address (on configs that don't use
"reject_unlisted_recipient" before greylisting, they should do so),
- make hundreds of thousands of SMTP transactions to Postfix with
different "FROM" headers. On most configurations you probably can't make
more than 10s of such transactions/s. So it should take around 3 hours
of constant hammering to start making a difference on the DB.

If I were an attacker I would try to flood with TCP connections only. I
just checked : Postfix doesn't put a limit on connnections/host, you can
easily tie up all connections from a single IP and DoS it in a second in
the default configuration at least. So why bother with a slower way that
requires both to know valid rcpts and that the target runs SQLgrey ?

>>This would have two main benefits :
>>B1/ faster DB access to the connect table (which probably is the most
>>used on common config under heavy Zombie pressure...) from SQLgrey.
>>B2/ easier analysis of the connect table by a curious sysadmin.
>>   =20
>>
>
>and B3/ the connect table wouldn't grow too big on disk
>
> =20
>

True, but mostly connected to B1 (disk space is cheap).

>>Here are the risks I'm worried about :
>>R1/ using tarpitting will also interfere with legit mails that don't
>>match AWLs (more retries).
>>   =20
>>
>
>I have already stated that I believe it probably wouldn't be an issue,=20
>especially if using some refinements which you citate above.
>
> =20
>
>>R2/ SQLgrey will have to do another query on the connect table which
>>would most probably kill the performance advantage we get from a smalle=
r
>>connect table
>>   =20
>>
>
>Yes, that's one more query on "connect", but this will only affect=20
>"newcomers", and not sources that are already AWL'd.
>
>Some time ago, you were considering adding supplementary tables (such as=
 a=20
>"connect_awl" and even a "src_awl") and the fact that this needed=20
>supplementary queries for most of the messages didn't seem to be a=20
>show-stopper ;-)
>
> =20
>

The benefit of these two awls was clear to me: better AWL performance
(in fact there was a third awl considered: rcpt_awl). This was backed by
real-life cases (forward services that would be handled by rcpt_awl,
spam that use a weakness of from_awl removed by connect_awl, mail relays
of big ISPs that would be handled by src_awl).

>>and for the refinements, one query involving domain_awl=20
>>and if needed another involving from_awl.
>>   =20
>>
>
>Yes, but those "refinements" will be used only for sources which already=
 have=20
>more than "n" records in connect, basically zombies. If we're under zomb=
ie=20
>attack, the corresponding DB pages will probably be in-cache and the que=
ries=20
>will be fast.
>
>And when the "refinements" are called for non-zombies (big sites), their=
 very=20
>purpose it to make sure the messages will be accepted fast, so the=20
>refinements won't be repeatedly used for long...
>
> =20
>

Makes sense.

>>My main problem for me was with R1 until the number of queries piled up
>>to avoid the fact that in some cases (big ISPs consolidating email
>>infrastructure, new smtp-out adresses, ...) you might very well
>>introduce huge delays or even bounce mails (which was a show stopper fo=
r
>>me). Now I think R2 will make the whole thing pointless.
>>   =20
>>
>
>I still believe that this idea should be tried out to experiment how it=20
>behaves in "real life".
>
>Avoiding zombie of virussed machines to pollute connect to a great deal,=
 and=20
>making sure that a "connect flooding attack" cannot be done seems to me =
good=20
>enough reasons to make it interesting...
>
> =20
>

I'm not yet convinced this would be really useful, probably harmless thou=
gh.

Lionel.