sqlgrey-users — Users of SQLgrey

Re: AW: [Sqlgrey-users] [RELEASE] 1.6.2

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

Le Mardi 05 Juillet 2005 13:24, Fra...@co... a =E9crit :
>
> im still using version 1.7.0 whithout problems.

Same for me :-)

> This version is now not longer avalible for download.

??? I see it right now at=20
http://sourceforge.net/project/showfiles.php?group_id=3D113566

> Should I go back to 1.6.2 now?=20

The question I would like to ask is rather : Are the latest improvements =
to=20
the "stable" 2.6.x branch as well committed to the 1.7.x branch ?

Cheers.

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

AW: [Sqlgrey-users] [RELEASE] 1.6.2

[<Fra...@co...>]

Hi,

im still using version 1.7.0 whithout problems. This version is now not
longer avalible for download. Should I go back to 1.6.2 now?=20

Frank

> -----Urspr=FCngliche Nachricht-----
> Von: sql...@li...=20
> [mailto:sql...@li...] Im Auftrag=20
> von Lionel Bouton
> Gesendet: Montag, 4. Juli 2005 17:43
> An: sql...@li...
> Betreff: [Sqlgrey-users] [RELEASE] 1.6.2
>=20
> Hi,
>=20
> hre's the commented changelog:
>=20
> - DB handles aren't destroyed by the DBD driver anymore=20
> (attempt to fix the freezes some have witnessed, SQLgrey is=20
> now more in control of the database connections),
> - spec file updated to conform to RPM 4 (Copyright -> License),
> - logstat bugfix (new log format wasn't supported properly),
> - protect Syslog calls from "%?" strings (I don't believe=20
> there was a security problem with this, but some badly=20
> written spambots leave "%_id_" strings behind that perl's=20
> syslog interface try to interpret and this generated ugly=20
> errors in log).
>=20
> http://sourceforge.net/project/showfiles.php?group_id=3D113566&p
> ackage_id=3D155492&release_id=3D339685
>=20
> Lionel.
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration=20
> Strategies from IBM. Find simple to follow Roadmaps,=20
> straightforward articles, informative Webcasts and more! Get=20
> everything you need to get up to speed, fast.=20
> http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclick
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>=20

AW: [Sqlgrey-users] new update_sqlgrey_config script [was: SQLgrey Feature Requests]

[Gianpaolo D. M. <gia...@ge...>]

=20
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello Lionel,

Thank you for your feedback.
I will be testing it later this day.

Regards,

Gianpaolo
=20
- -----Urspr=FCngliche Nachricht-----
Von: Lionel Bouton [mailto:lio...@bo...]=20
Gesendet: Dienstag, 5. Juli 2005 02:07
An: Gianpaolo Del Matto
Cc: sql...@li...
Betreff: Re: [Sqlgrey-users] new update_sqlgrey_config script [was:
SQLgrey Feature Requests]

Hi,

I finally found some time to check your contribution.

Gianpaolo Del Matto wrote the following on 24.06.2005 15:38 :

>=20
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Lionel,
>
>Find a new version of update_sqlgrey_config attached to this
>message. I've ported the whole thing over to Perl as it is more
>portable than a  bash script (eg. FreeBSD misses md5sum and it's own
>md5 has a different  syntax).
> =20
>

I made some changes...

A problem with check_procs:
- - it didn't work properly on Linux (ps -x is not a valid syntax).
- - if for example you do a "vi update_sqlgrey_config" and launch it,
check_procs thinks update_sqlgrey_config is alrealy launched.
As the window for a race condition is now reduced (only move files
around after all downloads, see below), I removed check_procs
completely.

Revert to original update_sqlgrey_config behaviour, specifically :
- - don't download files that already match the checksum,
- - don't replace any file unless all files downloaded match the
checksum,
- - don't print anything unless something had to be done (don't want
cron to send mails each time this script is called, a new
- --verbose|-v flag overrides this).

The code for managing the temp directory is replaced. This one should
be guaranteed collision-free by use of File::Temp::tempdir, the
details for file/directory removal are handled by File::Temp
("CLEANUP =3D> 1" in the tempdir call).

There will be a lot of new dependencies with this script. I think
I'll provide both update_sqlgrey_config files in the future as they
both have their usage. The shell one is small and only needs md5sum,
the perl one is more clean and probably easier to port.

You'll find the result attached.

Lionel.

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.1

iQA/AwUBQsooWy+TvXspauGfEQLaWwCgg/7wLfKKEi39x56CrQbGdEo9C8UAoPsf
v21rIwLKz90nqAdYZSGsoo2T
=3DKWbm
-----END PGP SIGNATURE-----

Re: [Sqlgrey-users] Couple Problems with 1.6.0

[Ed S. (s. list) <sq...@cr...>]

Lionel Bouton wrote:

>You may want to check the number of connections to the database. Do
>something like "netstat -anp | grep <sqlgrey-pid>" to check open TCP
>connections. You should only have one conection to the PostgreSQL server.
>  
>
darkside:~# ps aux |grep sqlgrey
sqlgrey   9082  0.0  0.9  13872  8792 ?        Ss   11:50   0:01
/usr/bin/perl -w /usr/sbin/sqlgrey -d
postgres  9085  0.0  0.9  18064  8948 ?        S    11:50   0:00
postgres: sqlgrey sqlgrey 127.0.0.1 idle
sqlgrey  31292  0.0  0.0      0     0 ?        Z    18:49   0:00
[sqlgrey] <defunct>
root      6135  0.0  0.0   1620   516 pts/1    S+   21:16   0:00 grep
sqlgrey
darkside:~# netstat -anp |grep 9082
tcp        0      0 127.0.0.1:2501          0.0.0.0:*              
LISTEN     9082/perl
tcp        0      0 127.0.0.1:36435         127.0.0.1:5432         
ESTABLISHED9082/perl
tcp        1      0 127.0.0.1:2501          127.0.0.1:34774        
CLOSE_WAIT 9082/perl
unix  3      [ ]         STREAM     CONNECTED     29082    9199/master
unix  3      [ ]         STREAM     CONNECTED     28680    9082/perl


>Hope it can make a difference. If all fails, I'll make the fork
>configurable and inactive by default.
>  
>
While reverting back to self->cleanup() fixed the lockup problem with
1.6.0 and 1.7.0, I have the same lockup problem with 1.6.2.


Cheers,
-- 

Ed

Re: [Sqlgrey-users] new update_sqlgrey_config script [was: SQLgrey Feature Requests]

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

Lionel Bouton wrote the following on 05.07.2005 02:07 :

> [...]
>
>Revert to original update_sqlgrey_config behaviour, specifically :
>- don't download files that already match the checksum,
>- don't replace any file unless all files downloaded match the checksum,
>- don't print anything unless something had to be done (don't want cron
>to send mails each time this script is called, a new --verbose|-v flag
>overrides this).
>  
>

Another thing I missed: the original script shows the differences
between the original and the updated file. It's too late for me to add
it tonight, I'll give it a shot latter.

Lionel.

Re: [Sqlgrey-users] new update_sqlgrey_config script [was: SQLgrey Feature Requests]

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

Hi,

I finally found some time to check your contribution.

Gianpaolo Del Matto wrote the following on 24.06.2005 15:38 :

> 
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Lionel,
>
>Find a new version of update_sqlgrey_config attached to this message.
>I've ported the whole thing over to Perl as it is more portable
>than a bash script (eg. FreeBSD misses md5sum and it's own md5 has
>a different syntax).
>  
>

I made some changes...

A problem with check_procs:
- it didn't work properly on Linux (ps -x is not a valid syntax).
- if for example you do a "vi update_sqlgrey_config" and launch it,
check_procs thinks update_sqlgrey_config is alrealy launched.
As the window for a race condition is now reduced (only move files
around after all downloads, see below), I removed check_procs completely.

Revert to original update_sqlgrey_config behaviour, specifically :
- don't download files that already match the checksum,
- don't replace any file unless all files downloaded match the checksum,
- don't print anything unless something had to be done (don't want cron
to send mails each time this script is called, a new --verbose|-v flag
overrides this).

The code for managing the temp directory is replaced. This one should be
guaranteed collision-free by use of File::Temp::tempdir, the details for
file/directory removal are handled by File::Temp ("CLEANUP => 1" in the
tempdir call).

There will be a lot of new dependencies with this script. I think I'll
provide both update_sqlgrey_config files in the future as they both have
their usage. The shell one is small and only needs md5sum, the perl one
is more clean and probably easier to port.

You'll find the result attached.

Lionel.

Re: [Sqlgrey-users] [RELEASE] 1.6.2

[Ed S. (s. list) <sq...@cr...>]

Lionel Bouton wrote:
> Hi,
> 
> hre's the commented changelog:
> 
> - DB handles aren't destroyed by the DBD driver anymore (attempt to fix
> the freezes some have witnessed, SQLgrey is now more in control of the
> database connections),

Excellent...since reverting back to "$self->cleanup()" seemed to fix the
lockup problem, I'm now trying 1.6.2.

Thanks for your work on this software. :)

[Sqlgrey-users] [RELEASE] 1.6.2

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

Hi,

hre's the commented changelog:

- DB handles aren't destroyed by the DBD driver anymore (attempt to fix
the freezes some have witnessed, SQLgrey is now more in control of the
database connections),
- spec file updated to conform to RPM 4 (Copyright -> License),
- logstat bugfix (new log format wasn't supported properly),
- protect Syslog calls from "%?" strings (I don't believe there was a
security problem with this, but some badly written spambots leave
"%_id_" strings behind that perl's syslog interface try to interpret and
this generated ugly errors in log).

http://sourceforge.net/project/showfiles.php?group_id=113566&package_id=155492&release_id=339685

Lionel.

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michael S. <Mic...@lr...>]

On Thu, 30 Jun 2005, Michel Bouissou wrote:

>
> The major objections that I see to integrating any kind of SPF check in
> sqlgrey is that :
>
> 1/ SPF not having anything to do with greylisting, it isn't pure greylisting
> at all anymore.

This is just a statement without any reasons behind it. Therefore I just
state the opposite: it has to do with greylisting.

>
> 2/ SPF being a complex algorithm, it necessitates a complex implementation in
> SQLgrey, which is both heavy and bug-prone. And as the SPF standard might
> evolve, it would need SQLgrey to follow its evolutions -- we're adding a lot
> of foreseeable trouble there.

Wrong. Here is the relevant code out of my program:

use Mail::SPF::Query;
my %query_options = (   ipv4    => $conn_ip,
                        sender  => $sender_domain,
                        helo    => $sender_domain, # should be helo-domain, but helo-domain not yet avail.
my $query = new Mail::SPF::Query(%query_options);
my ($result, $smtp_comment, $header_comment) = $query->result;
if ($result eq 'pass') {
...

I think these few lines of code cannot be called a complex implementation.

> Furthermore there are other proposed standards competing with SPF, so if you
> consider adding one, then you'll have to consider adding others, and this is
> endless.

As long as the relevant information is outside the evelope, e.g.
information in the header, these standards cannot be used directly by
greylisting. If, however, other standards exist, which operate on the
envelope, we will see, if they could be used by greylisting. But as long
as I do not know them, I cannot say anything about them.

>
> 3/ SPF cannot be done using only the information that Postfix provides to the
> SQLgrey policy server. SPF needs at least one, possibly several, DNS calls,
> which will have a very noticeable negative impact on SQLgrey performance. I
> object to the idea that SQLgrey would need to perform any kind of network
> request to be able to make a decision. One could say that DNS calls are fast,
> but it's not always the case. The remote DNS server you call can sometimes be
> very slow, unreachable or unresponsive...

This is true, the actual implementation of sqlgrey does not allow any
algorithm which uses DNS request which will go out into the Internet. If
we want to use such algorithms, then either sqlgrey must go from multiplex
to prefork mode, or the propagation algorithms must use a sideline
process, similar to the cleanup process. I am aware of this problem since
the beginning.

>
> > The same is true for the rcpt_awl, we need for forwarded emails. We have a
> > lot of employees, which separate their email addresses for business and
> > private use. For their privat emails they use an address with a freemail
> > provider. All emails arriving at the freemail provider are forwarded to
> > their business address, which is allowed in our university environment.
> > This way they have separated their addresses, but still have all emails
> > together in one mailbox, where they can be accessed immediately.
> >
> > The other case is, a lot of employees of the universities come from other
> > universities. When they moved, they set up a forward to an email address
> > here.
> >
> > In both cases we want to accept such forwarded emails immediately without
> > any delay. But with from_awl and domain_awl alone, it is not possible. The
> > sending MTA may have an entry in domain_awl, but normally only for the
> > domains it is responsible. But often the forwarded emails have an
> > originator with a different domain. Therefore every new originator will go
> > through greylisting, which makes no sense, because often we now that the
> > sending MTA is a well behaved MTA and we trust this MTA.
> >
> > With rcpt_awl this is different. In this table we will include the ip
> > address of the MTA and the recipient address, but no information about the
> > originator. That means we need only one entry per forward and all emails
> > coming this way will be accepted immediately. And this means greylisting
> > is strengthened again.
>
> Regarding email forwarded from other universities, I believe the number of
> such universities isn't that high and doesn't grow that fast. You'd probably
> be better adding the know MTAs of those universities to an existing manual
> whitelist, as you know those servers are true MTAs, well behaved.
>
> Same could be done rather easily for the 4 or 5 major "free email providers
> and forwarders" you're talking about. That makes what ? Hotmail, MSN, Yahoo,
> a couple of biggest ISPs in your country, and you'd probably cover more than
> 80% of your forwarding greylisting issues (if one believes the 80/20 law ;-)
>
> For the remaining 20%, it would concern mainly "private forwarded mail", and
> in any case, the from_awl would take care that only the first mail from A to
> B thru "forwarder" would be greylisted. And if "forwarder" forwards a
> noticeable amount of email from any given domain, it would end in domain_awl
> for this domain anyway...
>
> So I'm really not sure that your forwarding issue necessitates the added
> complexity and performance cost that adding new tables to SQLgrey would
> necessitate.
>
> For example, rcpt_awl seems plain useless to me, as if you manually put an
> entry in rcpt_awl for a couple MTA_IP / recipient, then you mean that MTA_IP
> is well behaved. If it's well behaved for "recipient", it's also well behaved
> for any other recipient, and MTA_IP can perfectly be added to the existing
> manual whitelists without the need of adding a supplementary table, isn't
> it ?
>

The most expensive thing at our computer centre ist person power, whereas
computers are cheap. Therefore manual maintenance must be avoided at all
costs. Instead automatic maintenance of whitelists must be done. This is
the reason why we choose sqlgrey, as sqlgrey implements AWLs! rcpt_awl is
an automatic whitelist as the name suggests. The manual whitelist for ip
addresses must only be used as a last resort.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: [Sqlgrey-users] [Semi OT] PostgreSQL vs. MySQL

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

Le Jeudi 30 Juin 2005 10:25, Lionel Bouton a =E9crit :
> Michel Bouissou wrote the following on 30.06.2005 09:08 :
> >Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> >>>BTW, why state that MySQL is a poor choice ?
> >>
> >>It's often buggy and doesn't follow the standard ?
> >
> >I didn't use MySQL until a few weeks ago, so I couldn't tell for older
> >versions, but MySQL 4.1.12 seems to work perfectly for me.
> >
> >And it's 15 times faster (!!!) than PostgreSQL for the very DB-intensi=
ve
> > DSPAM (this nice crowd of bugs ;-)
>
> DSPAM is a special case. I've had a quick look into its backend drivers=
:
> they use proprietary features of MySQL to speed things up. Their
> PostgreSQL driver is known to be less worked on too.

That's true. But in my case, I had to focus on the actual result ;-)

It also seems that the "proprietary features of MySQL" that allows to spe=
ed=20
things up are missing in PostgreSQL.

> For pure *transactional* (MySQL's MyISAM tables aren't in the same
> competitive field) SQL, MySQL and PostgreSQL are roughly at the same
> performance level although PostgreSQL is often reported to scale better
> under concurrent accesses.

I'm using "InnoDB" tables in MySQL, which are transactional (although I=20
configured MySQL not in "completely atomic" mode, as I didn't need full=20
transactional features, and to speed things up). In this configuration, a=
nd=20
on the same machine, DSPAM still is 15 times faster using MySQL than usin=
g=20
PostgreSQL...

> On the stability front, of all the database admins I've had personnal
> contacts with, only the MySQL ones witnessed crashes (and chronic ones
> at that) and data loss not related to hardware failures.

InnoDB tables are reported to be very safe and robust (and that's why I=20
choosed to use them).

> But for 6 years I've personnaly worked with PostgreSQL in various kinds
> of loads and queries, I've *never* had a crash.

I don't have any doubt that PostgreSQL is very good and stable. I've used=
 it=20
for years as well, and have never lost data, nor have I seen any PostgreS=
QL=20
crash.

Only this speed issue with DSPAM made me shift from Pg to MySQL.

> In the quirkiness department, in SQLgrey, setting aside the now()
> function declaration for SQLite, although I originally developped
> SQLgrey with MySQL, all the special cases are for it, from the top of m=
y
> head:
> - the "I update the timestamp because I think it's better for you" bugf=
ix,
> - the need to tell the client to autoreconnect because the server drops
> the connection without being told to,
> - the heavy SQL involved with timestamp differences.
>
> The first two introduced bugs in SQLgrey, the last one made me spent
> quite some time looking for a working SQL syntax...

Well, I don't know for "the server drops the connection without being tol=
d=20
to", but the fact that 2 different SQL servers behave differently on some=
=20
details isn't surprising, and wouldn't make me say that one is better tha=
n=20
the other, as long as this is documented -- which is the case for timesta=
mp=20
fields for example.


> <OT> Yep. In my case, the lack of performance is one reason I consider
> evaluating other statistical filters. Even with MySQL, DSPAM doesn't
> seem to scale well enough for thousands of users. Too bad, DSPAM comes
> with a nice web interface out of the box. I'll have to do some php/perl
> web hacking and I lack time for this :-(</OT>

The things that mostly pisses me off with DSPAM is the number of bugs it=20
contains, the fact that long reported bugs aren't fixed (as the developpe=
r=20
prefers adding new features rather than fixing known bugs, and quite ofte=
n=20
seems not to want to hear that his software may have bugs...), plus the f=
act=20
that half of the times the fixing of a bug introduces another new bug.

I have more than serious doubts about the quality of this software and it=
s=20
development.

OTOH, it "mostly works" (surprisingly enough ;-) and when it works, it us=
ually=20
works very good.

But for example I've noticed than on my system, since I've upgraded to th=
e=20
latest (supposedly stable) DSPAM 3.4.8, retraining missed spams and false=
=20
positives doesn't work anymore :-((

DSPAM says it has done it and doesn't complain, but the tokens in the DB=20
aren't actually reversed :-((
</OT>


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

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Le Jeudi 30 Juin 2005 10:35, Michael Storz a =E9crit :
>
> I suppose you do not want to run a pure greylisting server, because tha=
t
> means to switch off the from_awl und domain_awl. Pure greylisting is do=
ne
> with connect and a simple whitelist for not well behaving MTAs. And thi=
s
> list can be very short, as I have experienced in the meantime.
>
> At the moment you use AWLs, you are not using pure greylisting anymore.
> And I think you ARE using sqlgrey because of its excellent AWLs.

AWLs are just "greylisting with a memory". I don't agree that using AWLs =
isn't=20
"pure greylisting".

> [...] The way I am using SPF is to get entries faster in domain_awl tha=
n
> using group_domain_level alone. SPF is a hint, that the sending IP is
> authorized to send emails with this originator. But it is only a hint. =
The
> MTA has still to prove it is a well behaved MTA [...]

The major objections that I see to integrating any kind of SPF check in=20
sqlgrey is that :

1/ SPF not having anything to do with greylisting, it isn't pure greylist=
ing=20
at all anymore.

2/ SPF being a complex algorithm, it necessitates a complex implementatio=
n in=20
SQLgrey, which is both heavy and bug-prone. And as the SPF standard might=
=20
evolve, it would need SQLgrey to follow its evolutions -- we're adding a =
lot=20
of foreseeable trouble there.
Furthermore there are other proposed standards competing with SPF, so if =
you=20
consider adding one, then you'll have to consider adding others, and this=
 is=20
endless.

3/ SPF cannot be done using only the information that Postfix provides to=
 the=20
SQLgrey policy server. SPF needs at least one, possibly several, DNS call=
s,=20
which will have a very noticeable negative impact on SQLgrey performance.=
 I=20
object to the idea that SQLgrey would need to perform any kind of network=
=20
request to be able to make a decision. One could say that DNS calls are f=
ast,=20
but it's not always the case. The remote DNS server you call can sometime=
s be=20
very slow, unreachable or unresponsive...

> The same is true for the rcpt_awl, we need for forwarded emails. We hav=
e a
> lot of employees, which separate their email addresses for business and
> private use. For their privat emails they use an address with a freemai=
l
> provider. All emails arriving at the freemail provider are forwarded to
> their business address, which is allowed in our university environment.
> This way they have separated their addresses, but still have all emails
> together in one mailbox, where they can be accessed immediately.
>
> The other case is, a lot of employees of the universities come from oth=
er
> universities. When they moved, they set up a forward to an email addres=
s
> here.
>
> In both cases we want to accept such forwarded emails immediately witho=
ut
> any delay. But with from_awl and domain_awl alone, it is not possible. =
The
> sending MTA may have an entry in domain_awl, but normally only for the
> domains it is responsible. But often the forwarded emails have an
> originator with a different domain. Therefore every new originator will=
 go
> through greylisting, which makes no sense, because often we now that th=
e
> sending MTA is a well behaved MTA and we trust this MTA.
>
> With rcpt_awl this is different. In this table we will include the ip
> address of the MTA and the recipient address, but no information about =
the
> originator. That means we need only one entry per forward and all email=
s
> coming this way will be accepted immediately. And this means greylistin=
g
> is strengthened again.

Regarding email forwarded from other universities, I believe the number o=
f=20
such universities isn't that high and doesn't grow that fast. You'd proba=
bly=20
be better adding the know MTAs of those universities to an existing manua=
l=20
whitelist, as you know those servers are true MTAs, well behaved.

Same could be done rather easily for the 4 or 5 major "free email provide=
rs=20
and forwarders" you're talking about. That makes what ? Hotmail, MSN, Yah=
oo,=20
a couple of biggest ISPs in your country, and you'd probably cover more t=
han=20
80% of your forwarding greylisting issues (if one believes the 80/20 law =
;-)

For the remaining 20%, it would concern mainly "private forwarded mail", =
and=20
in any case, the from_awl would take care that only the first mail from A=
 to=20
B thru "forwarder" would be greylisted. And if "forwarder" forwards a=20
noticeable amount of email from any given domain, it would end in domain_=
awl=20
for this domain anyway...

So I'm really not sure that your forwarding issue necessitates the added=20
complexity and performance cost that adding new tables to SQLgrey would=20
necessitate.

For example, rcpt_awl seems plain useless to me, as if you manually put a=
n=20
entry in rcpt_awl for a couple MTA_IP / recipient, then you mean that MTA=
_IP=20
is well behaved. If it's well behaved for "recipient", it's also well beh=
aved=20
for any other recipient, and MTA_IP can perfectly be added to the existin=
g=20
manual whitelists without the need of adding a supplementary table, isn't=
=20
it ?

Cheers.

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

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michael S. <Mic...@lr...>]

On Wed, 29 Jun 2005, Who Knows wrote:

> Okay, here is my $0.02. They DO NOT strengthen the greylisting process
> as they have
> nothing to do with greylisting. They are simply other methods for
> combatting SPAM.
>
> Any utility that attempts to be the omnicient "do all" ultimately fails.
> Therefore
> my vote is that SQLgrey should do what is does best... greylisting.
>
> Jim
>

I do not think there was a request to "enhance" sqlgrey to a "do all"
daemon. I agree with you every new feature must be related to greylisting.
I suppose you do not want to run a pure greylisting server, because that
means to switch off the from_awl und domain_awl. Pure greylisting is done
with connect and a simple whitelist for not well behaving MTAs. And this
list can be very short, as I have experienced in the meantime.

At the moment you use AWLs, you are not using pure greylisting anymore.
And I think you ARE using sqlgrey because of its excellent AWLs.

The goal of all future enhancements are twofold:

- they should put as many entries as possible in AWLs to reduce the delay
  of regular messages
- they should reduce the possibility that spam mails make use of entries
  in the whitelists

You must always see these two goals together, they cannot be separated.

Coming to SPF. If you want to use SPF to combat spam directly, then you
should use a program/policy server outside of sqlgrey. The way I am using
SPF is to get entries faster in domain_awl than using group_domain_level
alone. SPF is a hint, that the sending IP is authorized to send emails
with this originator. But it is only a hint. The MTA has still to prove it
is a well behaved MTA (at the moment we use 3 retries = 3 entries in
from_awl, whereas group_domain_level is set to 10). This kind of usage of
SPF is a way to strengthen greylisting/sqlgrey.

The same is true for the rcpt_awl, we need for forwarded emails. We have a
lot of employees, which separate their email addresses for business and
private use. For their privat emails they use an address with a freemail
provider. All emails arriving at the freemail provider are forwarded to
their business address, which is allowed in our university environment.
This way they have separated their addresses, but still have all emails
together in one mailbox, where they can be accessed immediately.

The other case is, a lot of employees of the universities come from other
universities. When they moved, they set up a forward to an email address
here.

In both cases we want to accept such forwarded emails immediately without
any delay. But with from_awl and domain_awl alone, it is not possible. The
sending MTA may have an entry in domain_awl, but normally only for the
domains it is responsible. But often the forwarded emails have an
originator with a different domain. Therefore every new originator will go
through greylisting, which makes no sense, because often we now that the
sending MTA is a well behaved MTA and we trust this MTA.

With rcpt_awl this is different. In this table we will include the ip
address of the MTA and the recipient address, but no information about the
originator. That means we need only one entry per forward and all emails
coming this way will be accepted immediately. And this means greylisting
is strengthened again.

I hope it is now clear what I meant with:

"If these additions will strengthen the greylisting process, then they
should be implemented."

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Michel Bouissou wrote the following on 30.06.2005 09:08 :

>Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> =20
>
>>>BTW, why state that MySQL is a poor choice ?
>>>     =20
>>>
>>It's often buggy and doesn't follow the standard ?
>>   =20
>>
>
>I didn't use MySQL until a few weeks ago, so I couldn't tell for older=20
>versions, but MySQL 4.1.12 seems to work perfectly for me.
>
>And it's 15 times faster (!!!) than PostgreSQL for the very DB-intensive=
 DSPAM=20
>(this nice crowd of bugs ;-)
> =20
>

DSPAM is a special case. I've had a quick look into its backend drivers:
they use proprietary features of MySQL to speed things up. Their
PostgreSQL driver is known to be less worked on too.

For pure *transactional* (MySQL's MyISAM tables aren't in the same
competitive field) SQL, MySQL and PostgreSQL are roughly at the same
performance level although PostgreSQL is often reported to scale better
under concurrent accesses.

On the stability front, of all the database admins I've had personnal
contacts with, only the MySQL ones witnessed crashes (and chronic ones
at that) and data loss not related to hardware failures. It was each
time on heavily loaded, very big databases (tables in the GB or tens of
GB range), running on multiple CPUs...
The stability seems better since 6 months ago in the 4.1.x branch tough.
But for 6 years I've personnaly worked with PostgreSQL in various kinds
of loads and queries, I've *never* had a crash. In fact I've a
PostgreSQL 7.2 server in production for 3 years now with around 500 days
of uptime, which is the time since the last kernel upgrade (I don't know
the exact uptime as it wrapped at around 450 days and now shows 42 days
:-) ) hosting 10 databases with a total constant load around 10
queries/second.

In the quirkiness department, in SQLgrey, setting aside the now()
function declaration for SQLite, although I originally developped
SQLgrey with MySQL, all the special cases are for it, from the top of my
head:
- the "I update the timestamp because I think it's better for you" bugfix=
,
- the need to tell the client to autoreconnect because the server drops
the connection without being told to,
- the heavy SQL involved with timestamp differences.

The first two introduced bugs in SQLgrey, the last one made me spent
quite some time looking for a working SQL syntax...

>15 times faster, that was a good reason for me to migrate...
> =20
>

<OT> Yep. In my case, the lack of performance is one reason I consider
evaluating other statistical filters. Even with MySQL, DSPAM doesn't
seem to scale well enough for thousands of users. Too bad, DSPAM comes
with a nice web interface out of the box. I'll have to do some php/perl
web hacking and I lack time for this :-(</OT>

Lionel.

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> >BTW, why state that MySQL is a poor choice ?
>
> It's often buggy and doesn't follow the standard ?

I didn't use MySQL until a few weeks ago, so I couldn't tell for older=20
versions, but MySQL 4.1.12 seems to work perfectly for me.

And it's 15 times faster (!!!) than PostgreSQL for the very DB-intensive =
DSPAM=20
(this nice crowd of bugs ;-)

15 times faster, that was a good reason for me to migrate...

Of course for sqlgrey and less intensive DB applications, PostgreSQL was=20
satisfactory to me, but I didn't want to have several DB servers running =
on=20
my system, so I migrated all my DBs from PostgreSQL to MySQL.

Cheers.

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

Re: [Sqlgrey-users] Couple Problems with 1.6.0

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

Edward Shornock (sqlgrey list) wrote the following on 27.06.2005 05:35 :

>Edward Shornock (sqlgrey list) wrote:
>
>  
>
>>I'm experiencing the the problem now as well, and this just started
>>Friday. I thought it might have had something to do with the upgrade to
>>1.7, but after reverting back to 1.6 (which I had no problems with), the
>>problem persists. I've also turned up the logging level trying to track
>>this down. I'm using Debian Sid/unstable and PostgreSQL (7.4.8) as the
>>DB system.
>>
>>Hopefully we can track this problem down fairly easily...
>> 
>>
>>    
>>
>More information (I hope it helps), just before the process hangs:
>
>Jun 26 20:01:47 darkside postfix/smtpd[18921]: connect from
>russian-caravan.cloud9.net[168.100.1.4]
>Jun 26 20:01:48 darkside sqlgrey: 2005/06/26-20:01:48 CONNECT TCP Peer:
>"127.0.0.1:56048" Local: "127.0.0.1:2501"
>Jun 26 20:01:48 darkside sqlgrey: optin: greylisting active for
>pos...@cr...
>Jun 26 20:01:48 darkside sqlgrey: grey: unknown pattern:
>russian-caravan.cloud9.net, 168.100.1.4: using C-class (168.100.1).
>Jun 26 20:01:48 darkside sqlgrey: system: forked cleanup child (18925)
>Jun 26 20:01:48 darkside sqlgrey: warning: ERROR:  prepared statement
>"dbdpg_1" does not exist
>Jun 26 20:01:48 darkside sqlgrey: warning: message type 0x43 arrived
>from server while idle
>Jun 26 20:01:48 darkside sqlgrey: warning: message type 0x5a arrived
>from server while idle
>Jun 26 20:01:48 darkside sqlgrey: grey: domain awl match: updating
>168.100.1(168.100.1.4), postfix.org
>
>...and at this point, sqlgrey has given up.
>Jun 26 20:03:20 darkside sqlgrey: optin: greylisting active for
>pos...@cr...
>Jun 26 20:03:20 darkside sqlgrey: grey: unknown pattern:
>camomile.cloud9.net, 168.100.1.3: using C-class (168.100.1).
>Jun 26 20:03:20 darkside sqlgrey: perf: spent 0s cleaning: from_awl (0)
>domain_awl (0) connect (0)
>Jun 26 20:03:20 darkside sqlgrey: system: cleanup child exit (18925)
>Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max
>connection rate 1/60s for (smtp:146.82.138.6) at Jun 26 19:54:46
>Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max
>connection count 1 for (smtp:146.82.138.6) at Jun 26 19:54:46
>Jun 26 20:04:46 darkside postfix/anvil[18364]: statistics: max cache
>size 1 at Jun 26 19:54:46
>Jun 26 20:05:00 darkside postfix/smtpd[18921]: warning: timeout on
>127.0.0.1:2501 while reading input attribute name
>Jun 26 20:05:00 darkside postfix/smtpd[18921]: warning: problem talking
>to server 127.0.0.1:2501: Connection timed out
>Jun 26 20:06:41 darkside postfix/smtpd[18921]: warning: timeout on
>127.0.0.1:2501 while reading input attribute name
>Jun 26 20:06:41 darkside postfix/smtpd[18921]: warning: problem talking
>to server 127.0.0.1:2501: Connection timed out
>Jun 26 20:06:41 darkside postfix/smtpd[18921]: NOQUEUE: reject: RCPT
>from camomile.cloud9.net[168.100.1.3]: 450 Server configuration problem;
>from=<own...@po...>
>to=<pos...@cr...> proto=ESMTP
>helo=<camomile.cloud9.net>
>Jun 26 20:06:41 darkside postfix/cleanup[19161]: 8BD20D6B082:
>message-id=<200...@da...>
>Jun 26 20:06:41 darkside postfix/qmgr[3099]: 8BD20D6B082:
>from=<dou...@da...>, size=1086,
>nrcpt=1 (queue active)
>Jun 26 20:06:41 darkside postfix/smtpd[18921]: disconnect from
>camomile.cloud9.net[168.100.1.3]
>
>
>
># ps aux |grep sqlgrey
>sqlgrey   3754  0.0  0.9  13660  8684 ?        Ss   15:43   0:00
>/usr/bin/perl -w /usr/sbin/sqlgrey -d
>postgres  3755  0.0  1.0  18076  9096 ?        S    15:43   0:00
>postgres: sqlgrey sqlgrey 127.0.0.1
>idle                                                      
>sqlgrey  18925  0.0  0.0      0     0 ?        Z    20:01   0:00
>[sqlgrey] <defunct>
>root     26635  0.0  0.0   1624   452 pts/2    S+   22:42   0:00 grep
>sqlgrey
>
>
>strace on PID 3754 just sat there.  I restarted PostgreSQL, and then the
>strace finally showed activity.
>
>It seems that even though sqlgrey wasn't accepting connections The logs
>at this time show:
>Jun 26 22:56:51 darkside postfix/pickup[27380]: 08C7AD6B082: uid=132
>from=<sqlgrey>
>Jun 26 22:56:51 darkside postfix/qmgr[26777]: 08C7AD6B082:
>from=<sq...@da...>, size=485, nrcpt=1
>(queue active)
>Jun 26 22:56:51 darkside amavis[15461]: (15461-06-3) LMTP::10024
>/var/lib/amavis/tmp/amavis-20050626T190955-15461:
><sq...@da...> ->
><pos...@cr...> Received: SIZE=485 from
>darkside.crazeecanuck.homelinux.net ([127.0.0.1]) by localhost (darkside
>[127.0.0.1]) (amavisd-new, port 10024) with LMTP id 15461-06-3 for
><pos...@cr...>; Sun, 26 Jun 2005 22:56:51 -0400
>(EDT)
>Jun 26 22:56:51 darkside sqlgrey: dbaccess: error: couldn't get now()
>from DB: FATAL:  terminating connection due to administrator command
>server closed the connection unexpectedly     This probably means the
>server terminated abnormally     before or while processing the request.
>Jun 26 22:56:51 darkside sqlgrey: system: forked cleanup child (27706)
>Jun 26 22:56:51 darkside amavis[15461]: (15461-06-3) Checking:
><sq...@da...> ->
><pos...@cr...>
>Jun 26 22:56:51 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:51 darkside sqlgrey: dbaccess: error: couldn't access
>domain_awl table: no connection to the server
>Jun 26 22:56:51 darkside sqlgrey: grey: domain awl match: updating
>168.100.1(168.100.1.3), postfix.org
>Jun 26 22:56:51 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:51 darkside sqlgrey: dbaccess: warning: couldn't do query:
>UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
>sender_domain = NULL AND src = NULL: FATAL:  the database system is
>shutting down , reconnecting to DB
>JJun 26 22:56:53 darkside sqlgrey: 2005/06/26-22:56:53 CONNECT TCP Peer:
>"127.0.0.1:52685" Local: "127.0.0.1:2501"
>Jun 26 22:56:53 darkside sqlgrey: optin: greylisting active for
>deb...@cr...
>Jun 26 22:56:53 darkside sqlgrey: grey: unknown pattern:
>murphy.debian.org, 146.82.138.6: using C-class (146.82.138).
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't get now()
>from DB: FATAL:  the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't access
>domain_awl table: FATAL:  the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: grey: domain awl match: updating
>146.82.138(146.82.138.6), lists.debian.org
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: warning: couldn't do query:
>UPDATE domain_awl SET last_seen = NOW(), first_seen = first_seen WHERE
>sender_domain = NULL AND src = NULL: FATAL:  the database system is
>shutting down , reconnecting to DB
>Jun 26 22:56:53 darkside sqlgrey: 2005/06/26-22:56:53 CONNECT TCP Peer:
>"127.0.0.1:52693" Local: "127.0.0.1:2501"
>Jun 26 22:56:53 darkside sqlgrey: optin: greylisting active for
>sp...@cr...
>Jun 26 22:56:53 darkside sqlgrey: grey: unknown pattern:
>cherry.ease.lsoft.com, 209.119.0.109: using C-class (209.119.0).
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: FATAL: 
>the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't get now()
>from DB: FATAL:  the database system is shutting down
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: can't connect to DB: could
>not connect to server: Connection refused     Is the server running on
>host "localhost" and accepting     TCP/IP connections on port 5432?
>Jun 26 22:56:53 darkside sqlgrey: dbaccess: error: couldn't access
>domain_awl table: could not connect to server: Connection refused     Is
>the server running on host "localhost" and accepting     TCP/IP
>connections on port 5432?
>Jun 26 22:56:53 darkside sqlgrey: grey: domain awl match: updating
>209.119.0(209.119.0.109), peach.ease.lsoft.com
>
>the PostgreSQL logs showed:
>2005-06-26 20:01:48 [18926] LOG:  connection authorized: user=sqlgrey
>database=sqlgrey
>2005-06-26 20:01:48 [3755] ERROR:  prepared statement "dbdpg_1" does not
>exist
>
>After restarting PGSQL, sqlgrey is working again. Note that I could
>connect to the sqlgrey database just fine.  I'm now strace'ing the
>sqlgrey process, and dumping the output to a log file, hopefully that
>log will give a glue as to what's happening. at the time of hanging--I
>have no idea at this point.
>
>If there's anything else that I can do to help track down this bug,
>please let me know...
>  
>

You may want to check the number of connections to the database. Do
something like "netstat -anp | grep <sqlgrey-pid>" to check open TCP
connections. You should only have one conection to the PostgreSQL server.

I'm currently testing a 1.6.2 release (tar.bz2 at
http://sqlgrey.bouton.name/sqlgrey-1.6.2.tar.bz2 until I consider stable
enough to put it on sourceforge) with a safenet for the database
connection handling: SQLgrey will now explicitly call disconnect on a
buggy connection before reconnecting. All database connections are set
up to be explicitly disconnected in this version too: SQLgrey doesn't
rely on the DBD driver for reaping inactive connections anymore.

Hope it can make a difference. If all fails, I'll make the fork
configurable and inactive by default.

Lionel.

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Please don't take this as the start of a troll-war Lionel, I just want to=
=20
answer your points...

Le Mardi 28 Juin 2005 23:00, Lionel Bouton a =E9crit :
>
> SQLgrey already does more than pure greylisting. It uses a specific
> whitelist and AWLs, 1.7.x even does throttling.

All this _is_ pure greylisting. It is intelligent greylisting, but it is =
only=20
based upon whether or not hosts are able to reconnect for resending=20
temporarily rejected messages, with AWLs "memorizing" it automatically, a=
nd=20
throttling avoiding that a machine that does not retry fills up a connect=
=20
table with hundreds or thousands of dummy "first tries".

And the manual whitelists are just here to be able to let pass some rare=20
"known good" hosts which couldn't pass greylisting otherwise, because the=
y=20
don't behave as they should (they are "good" hosts, but they violate RFCs=
).

This is only greylisting. Good and intelligent one.

> Whatever will be added=20
> to SQLgrey will try to help fine-tune the greylisting process more or
> less in the same way:
> - either try to avoid greylisting "know good" clients as is done today,
> - maybe discriminate among senders, enforcing different reconnect delay
> or reconnect tries before they enter AWLs and/or are allowed to pass.

Then what you use for this "discrimination" clearly makes you step out of=
=20
greylisting, because you will take into account information of different=20
kinds, that have nothing to do with greylisting itself.

> The keyword here is *combination*: if we can combine other informations
> about the connection to help fine-tune the greylisting process

I don't believe that those kinds of "combinations" will make the greylist=
ing=20
any better. Only heavier, more complicated, more bug-prone...

> *and* it is impossible to do with Postfix alone or really difficult (Mi=
chel,
> I don't want to awaken another thread but you are the one asking for "M=
AIL
> FROM:" whitelists in SQLgrey

That's true. When you build a filter of any kind, making provisions for=20
(manual) exceptions that should be allowed thru seems to me to stay withi=
n=20
the scope of the given filter.

> although it is rather easy to use them with Postfix,

Nope. Because if you have a series of 10 Postfix restrictions, and let's =
says=20
SQLgrey comes #5, but you want to skip greylisting for a given sender, if=
 you=20
use to Postfix table to say "OK" for the given sender, then you don't onl=
y=20
skip greylisting, but also skip the 4 following other rules, which you mi=
ght=20
want to enforce. That's why it's impractical.

Yes, I know, you can create specific chains of "smtpd_restriction_classes=
" as=20
well, but the number of possible combinations grows exponentially as the=20
number of rules or filters you use grows.

That's why I think that it makes sense that each filters makes provisions=
 for=20
its exceptions...

> your position here surprises me...)

I hope I made it clearer...

> then adding code to SQLgrey makes sense (especially when SQLgrey will b=
e
> modular).=20

Now your position suprises me ;-) I've always seen you very reluctant abo=
ut=20
adding new features or algos to SQLgrey, and very hard to convice that a=20
suggestion may be useful. It takes time and effort before convicing you t=
hat=20
a given idea could be good and worth implementing. I think this is good,=20
because this way of working allows you to keep the software simple and=20
efficient, and avoid it becoming bloated with tons of more or less=20
useful(-less) features...

So well, I admit this easily, even if you refuse to integrate some featur=
es I=20
would like, as a sender regexp whitelist.

And now I see you considering adding tons of new features that have stric=
tly=20
nothing to do with greylisting, and for which the usefulness of combinati=
on=20
with greylisting still needs to be precisely demonstrated (which I doubt =
very=20
much).

So now I am suprised by your own positions ;-)

Mine is clear and simple : Let's SQLgrey be the best and more complete=20
greylisting tool out there. Let it do well everyting that relates to=20
greylisting. And don't do anything that doesn't directly relate to=20
greylisting...

But of course, this is your "child", so you're the boss ;-)

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

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Michel Bouissou wrote:

>Le Mercredi 29 Juin 2005 16:27, Who Knows a =E9crit :
> =20
>
>>>If these additions will strengthen the greylisting process, then they
>>>should be implemented.
>>>     =20
>>>
>>Okay, here is my $0.02. They DO NOT strengthen the greylisting process
>>as they have nothing to do with greylisting. They are simply other meth=
ods
>>for combatting SPAM.
>>   =20
>>
>
>Absolutely.
>
> =20
>
>>Any utility that attempts to be the omnicient "do all" ultimately fails=
.
>>Therefore my vote is that SQLgrey should do what is does best...
>>greylisting.=20
>>   =20
>>
>
><AOL>
>Me too !
></AOL>
>
> =20
>
<ENRON>
Me too !
</ENRON>

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Le Mercredi 29 Juin 2005 16:27, Who Knows a =E9crit :
>
> >If these additions will strengthen the greylisting process, then they
> >should be implemented.
>
> Okay, here is my $0.02. They DO NOT strengthen the greylisting process
> as they have nothing to do with greylisting. They are simply other meth=
ods
> for combatting SPAM.

Absolutely.

> Any utility that attempts to be the omnicient "do all" ultimately fails=
.
> Therefore my vote is that SQLgrey should do what is does best...
> greylisting.=20

<AOL>
Me too !
</AOL>

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

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Who K. <qui...@me...>]

Michael Storz wrote:

>On Tue, 28 Jun 2005, Michel Bouissou wrote:
>
> =20
>
>>Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
>>   =20
>>
>>>This is why I added a reference to SPF in my TODO
>>>     =20
>>>
>>And if you add SPF, then tomorrow you'll have to consider adding M$
>>CallerID/SenderID as well, and then Yahoo! DomainKeys, then SRS validit=
y
>>check, then the same for SES, then for BATV, then for the new algorithm=
 of
>>the day ;-)
>>
>>
>>   =20
>>
>
>If these additions will strengthen the greylisting process, then they
>should be implemented.
>
> =20
>
Okay, here is my $0.02. They DO NOT strengthen the greylisting process=20
as they have
nothing to do with greylisting. They are simply other methods for=20
combatting SPAM.

Any utility that attempts to be the omnicient "do all" ultimately fails.=20
Therefore
my vote is that SQLgrey should do what is does best... greylisting.

Jim

Re: [Sqlgrey-users] Couple Problems with 1.6.0

[Edward J S. <ed...@cr...>]

Edward J Shornock wrote:
> David Rees wrote:
> 
>>On 6/28/05, Lionel Bouton <lio...@bo...> wrote:
>>
>>
>>>One possible culprit could be the way SQLgrey handles the database
>>>cleanups since 1.6.0 (the zombie process points to a forked child problem).
>>
>>
>>FWIW, Since FC4 released perl-Net-Server 0.88 I haven't had any
>>problems with sqlgrey locking up...
>>
> 
> 
> Hmm...I'm still on 0.87 yet. I've tried the "workaround" that Mr. Bouton
> suggested, I'll post a follow-up if changing the "$self->fork_cleanup()"
> calls to "$self->cleanup()" resolves it.  If it does, I'll try upgrading
> to Net::Server 0.88 as well.
> 

Update:  Reverting back to $self->cleanup() seemed to work, there
weren't any lockups after 15 hours or so.  I upgraded Net::Server to
0.88 and changed it back to $self->fork_cleanup(). I restarted sqlgrey
in within a few hours the lockup happened.

I've since changed back to $self->cleanup() in the code, maybe I got
lucky before...

I'll post another update if this *really* stops the lockups, after
giving it a few days of operation.

Re: [Sqlgrey-users] warn_if_reject to populate the db

[Michael S. <Mic...@lr...>]

On Tue, 28 Jun 2005, Michel Bouissou wrote:

> Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> > This is why I added a reference to SPF in my TODO
>
> And if you add SPF, then tomorrow you'll have to consider adding M$
> CallerID/SenderID as well, and then Yahoo! DomainKeys, then SRS validity
> check, then the same for SES, then for BATV, then for the new algorithm o=
f
> the day ;-)
>
>

If these additions will strengthen the greylisting process, then they
should be implemented.


BTW, deverping BATV-adresses will be in the patch I am testing at the
moment. We need this, because abuse.net uses BATV and we do not want to
delay their emails.

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Michel Bouissou wrote the following on 28.06.2005 17:25 :

>Le Mardi 28 Juin 2005 17:09, Michael Storz a =E9crit :
> =20
>
>>These algorithms run for about 4 months at our site and they have prove=
n
>>to be very successful.
>>   =20
>>
>
>I have no doubt about it. But to me the question is about the vocation o=
f=20
>SQLgrey. Is SQLgrey supposed to be a (very efficient) greylisting system=
, or=20
>is it evoluating to become an exhaustive MTA anti-spam policy server=20
>implementing each and every possible way of filtering spam (before queue=
)..
>
> =20
>

SQLgrey already does more than pure greylisting. It uses a specific
whitelist and AWLs, 1.7.x even does throttling. Whatever will be added
to SQLgrey will try to help fine-tune the greylisting process more or
less in the same way:
- either try to avoid greylisting "know good" clients as is done today,
- maybe discriminate among senders, enforcing different reconnect delay
or reconnect tries before they enter AWLs and/or are allowed to pass.

The keyword here is *combination*: if we can combine other informations
about the connection to help fine-tune the greylisting process *and* it
is impossible to do with Postfix alone or really difficult (Michel, I
don't want to awaken another thread but you are the one asking for "MAIL
FROM:" whitelists in SQLgrey although it is rather easy to use them with
Postfix, your position here surprises me...) then adding code to SQLgrey
makes sense (especially when SQLgrey will be modular).

Lionel.

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> This is why I added a reference to SPF in my TODO

And if you add SPF, then tomorrow you'll have to consider adding M$=20
CallerID/SenderID as well, and then Yahoo! DomainKeys, then SRS validity=20
check, then the same for SES, then for BATV, then for the new algorithm o=
f=20
the day ;-)

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

Re: [Sqlgrey-users] Couple Problems with 1.6.0

[Edward J S. <ed...@cr...>]

David Rees wrote:
> On 6/28/05, Lionel Bouton <lio...@bo...> wrote:
> 
>>One possible culprit could be the way SQLgrey handles the database
>>cleanups since 1.6.0 (the zombie process points to a forked child problem).
> 
> 
> FWIW, Since FC4 released perl-Net-Server 0.88 I haven't had any
> problems with sqlgrey locking up...
> 

Hmm...I'm still on 0.87 yet. I've tried the "workaround" that Mr. Bouton
suggested, I'll post a follow-up if changing the "$self->fork_cleanup()"
calls to "$self->cleanup()" resolves it.  If it does, I'll try upgrading
to Net::Server 0.88 as well.


Thanks guys,

--Ed

Re: [Sqlgrey-users] warn_if_reject to populate the db

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

Le Mardi 28 Juin 2005 17:09, Michael Storz a =E9crit :
>
> These algorithms run for about 4 months at our site and they have prove=
n
> to be very successful.

I have no doubt about it. But to me the question is about the vocation of=
=20
SQLgrey. Is SQLgrey supposed to be a (very efficient) greylisting system,=
 or=20
is it evoluating to become an exhaustive MTA anti-spam policy server=20
implementing each and every possible way of filtering spam (before queue)=
.

I am personally interested in the 1st option, and not in the 2nd, as may =
good=20
solutions already exists for the "non-greylisting" methods, and because I=
MHO=20
it doesn't make much sense to mix all this together in a single system,=20
unless you want to end up with something as heavy and complex as=20
swiss-army-knife tools such as amavisd-new (for example).

I'm much more in favour of the Unix philosophy : Build with bricks, one b=
rick=20
does one thing and does it well. In such a vision, IMHO, the role of SQLg=
rey=20
is to be one of the bricks of an antispam solution, no to try to be the=20
complete wall on its own.

Let the MTA be the place where to integrate the bricks and build the wall=
=20
according to its admin's choices.

But that's just my 2c...

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