sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] 1.7.4 warnings

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

Lionel Bouton wrote:
> Agreed. I'm leaving for holidays, but as soon as I have some time
> (probably around january the 31th) I'll issue a 1.6.8 with a fix for
> this one.
>   
Ok sounds great.

>> [ 1574884 ] db_clean_hostname and HOSTNAME
.............
>> If I were you, I'd store the last cleanup timestamp in the database.
>>
>>     
Not a bad idea at all.. Except for the race condition you mention and 
having to change the db-schema, which you explicitly told me not to do ;)

>> - it can only marginally slow down the database, not corrupt it in any way
Yeah.. No corruption should be possible when multiple cleanups are made 
at the same time. But it does put heavy load on the sql and sqlgrey on 
our servers during cleanup, Also, AFAIK, sqlgrey is unresponsive during 
cleanup, a problem that gave me a lot of trouble while i was running 
postgrey. And using db_clustering just makes the "connect" table so much 
bigger..

For me, a cronjob would be great, since we are redesigning our 
mailcluster, so that there is a "master" controller box, and a lot of 
slaves (simplified). The controller will run SQL master and LVS 
clustering. The master will (no longer) run postfix and sqlgrey and so 
forth.. Here it would be great to have a cronjob doing the actual 
db-cleanup..

Ive seen some of you on this list actually use the db_cluster features. 
What would suit your needs?

>> You could have race conditions where 2+ SQLgrey wants to update at the
>> very same time. 
>>     
For problems like this, i usually do random(0-x) sleeping , before 
UPDATE'ing the timestamp.. It decreases the likelihood of a collision 
tremendously.
Like this:
SELECT.
Determine if its time to do clean up..
If yes -> sleep random(0-x)
UPDATE (blahblah) WHERE `timestamp`='<timestamp from SELECT>'
If affected_rows > 0 then do_cleanup.

"x" could be 4 seconds or so.

I dont suppose, due to sqlgrey's multithreaded nature, that this will 
cause it to hang for the sleep time?

> - SPF checking would be really cool :). Anyone who knows which spf-lib for perl is the "standard"?
....
> Hum, you do what you want with 1.7.x, but I'm considering SPF as a dying horse begging for someone to put an end to its misery... 
I am SO hoping SPF will be more common (since it is, in theory, a great 
idea). And adding to sqlgrey, so SPF=whitelist, might help the 
adaptation. :) But if me (and the Dmitry122, who posted the idea), are 
the only ones who likes it, i really wont bother :)

> Any DNS query
> is a problem with SQLgrey as the process serializes Postfix requests. MX
> checking would be interesting but you'll have to solve the serialization
> problem.
>   
I dont understand the "serialization problem"? (or maybe i dont 
understand the term).



- Dan Faerch

Re: [Sqlgrey-users] 1.7.4 warnings

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

Dan Faerch wrote the following on 20.01.2007 21:38 :
> Lionel Bouton wrote:
>   
>> I did get these warnings and now they disappeared, so WorksForMe(tm).
>>   
>>     
> Great, thanks for testing. Ill commit it to CVS then.. Since i dont feel 
> such a small change warrents a new release, ill see if i can find time 
> to look at some of the issues/feature requests, reported on the tracker 
> (no garantees though). Especially:
>
> [ 1613916 ] sqlgrey 1.6.7 dies if syslog daemon is down
> - I've confirmed this on our setup. I think its a serious problem.
>
>   

Agreed. I'm leaving for holidays, but as soon as I have some time
(probably around january the 31th) I'll issue a 1.6.8 with a fix for
this one.

> [ 1574884 ] db_clean_hostname and HOSTNAME
> - Non critical problem, but its not doing excacttly what its supposed 
> to. Im thinking about maybe redesigning the way this works so you can 
> disable automatic db-clean entirely, and THEN run it as cronjob from one 
> host. Eg. 'sqlgrey --db-clean'.
>   

If I were you, I'd store the last cleanup timestamp in the database.
SQLgrey would maintain a local cache of the value stored in the DB (to
avoid asking it on each mail). Then, when an instance of SQLgrey would
find that the cleanup is due according to its local cache it would begin
by verifying that its local cache is up to date (if not it replaces the
cache value and tests it again). If the cleanup is indeed due, it would
update the value in DB and then call the cleanup procedure -> zero conf
needed (I like avoiding configuration, software should just work given
the bare minimum information even if it means more work for the developer).

You could have race conditions where 2+ SQLgrey wants to update at the
very same time. This is probably not a problem as:
- it should be very rare,
- it can only marginally slow down the database, not corrupt it in any way,
- each SQLgrey instance won't notice any problem and everybody will
continue happily.

There's even a solution using serializable transactions to make sure
that only one SQLgrey instance will trigger the cleanup if you have a
transactional DBMS (PostgreSQL or MySQL with InnoDB, probably 5.x for
serializable transactions).

As a positive side-effect, SQLgrey can stop cleaning the database each
time it is started...

> [ 1580029 ] MX and SPF checking
> - SPF checking would be really cool :). Anyone who knows which spf-lib 
> for perl is the "standard"?
>   

Hum, you do what you want with 1.7.x, but I'm considering SPF as a dying
horse begging for someone to put an end to its misery... Any DNS query
is a problem with SQLgrey as the process serializes Postfix requests. MX
checking would be interesting but you'll have to solve the serialization
problem.

Lionel.

Re: [Sqlgrey-users] 1.7.4 warnings

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

Lionel Bouton wrote:
> I did get these warnings and now they disappeared, so WorksForMe(tm).
>   
Great, thanks for testing. Ill commit it to CVS then.. Since i dont feel 
such a small change warrents a new release, ill see if i can find time 
to look at some of the issues/feature requests, reported on the tracker 
(no garantees though). Especially:

[ 1613916 ] sqlgrey 1.6.7 dies if syslog daemon is down
- I've confirmed this on our setup. I think its a serious problem.

[ 1574884 ] db_clean_hostname and HOSTNAME
- Non critical problem, but its not doing excacttly what its supposed 
to. Im thinking about maybe redesigning the way this works so you can 
disable automatic db-clean entirely, and THEN run it as cronjob from one 
host. Eg. 'sqlgrey --db-clean'.

[ 1580029 ] MX and SPF checking
- SPF checking would be really cool :). Anyone who knows which spf-lib 
for perl is the "standard"?

Comments & idea's (and coding help) is welcome. If anyone decides theyll 
look one of these, please let me know so ill postpone looking at that 
specific item.


- Dan

Re: [Sqlgrey-users] 1.7.4 warnings

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

Steve Pellegrin wrote:
> I always see these messages every time I reboot. As I recall, they  
> only occur if you have db clustering turned off. (May be some other  
> option, it's been a while).
>   
Well sorta. It happens when the db clustering module isnt loaded. Since 
the options i set are actually a properties for DBIx::DBCluster, and it 
isnt loaded, it becomes a simple variable that is never used which then 
gives the warning "used only once: possible typo..".
It only happens when using DBI instead of DBx::DBCluster. So even if 
config has db_cluster=off, as long as sqlgrey is installed with make 
db_cluster you wont get the warnings.

The warnings will be gone in 1.7.5. The warnings has NO effect on 
sqlgrey's normal functions.

- Dan

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

[Dave S. <dst...@ma...>]

{slaps head} Of course ! Please label me "the Ignorant American".  ;-)
=20
And yes, that is a steep price, but sure, there are many benefits.=20
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Lionel Bouton <lio...@bo...> 4:56 PM Friday, January 19, =
2007 >>>
Dave Strickler wrote the following on 19.01.2007 22:29 :
> 1,500 Francs for filing paperwork - Ouch! Someone in the government is
> getting rich ;-)

in fact these were euros. Nearly $2000/year. Numerous people get money
through this system and it is probably less than efficient but this is
the price to pay to get life-long medical/unemployement insurances.

Lionel

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share =
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3DDE=
VDEV=20
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...=20
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A

Re: [Sqlgrey-users] 1.7.4 warnings

[Steve P. <st...@co...>]

I always see these messages every time I reboot. As I recall, they  
only occur if you have db clustering turned off. (May be some other  
option, it's been a while).

-Steve


On Jan 20, 2007, at 10:29 AM, Lionel Bouton wrote:

> Dan Faerch wrote the following on 20.01.2007 19:11 :
>> I need a helping hand..
>>>> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at
>>>> /usr/sbin/sqlgrey line 2413.
>>>> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once:  
>>>> possible
>>>> typo at /usr/sbin/sqlgrey line 827.
>>>> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at
>>>> /usr/sbin/sqlgrey line 818.
>> I cant get these warnings on to appear any of my computers (no idea
>> why), so i cant verify that my patch makes them go away.
>>
>> Could someone who actually gets these warnings apply the attached  
>> patch
>> and check that they disappear?
>>
>> $ patch /path/to/your/sqlgrey < sqlgrey-trivial.patch
>
> I did get these warnings and now they disappeared, so WorksForMe(tm).
>
> Lionel.
>
> ---------------------------------------------------------------------- 
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to  
> share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php? 
> page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>

Re: [Sqlgrey-users] 1.7.4 warnings

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

Dan Faerch wrote the following on 20.01.2007 19:11 :
> I need a helping hand..
>>> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at
>>> /usr/sbin/sqlgrey line 2413.
>>> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible
>>> typo at /usr/sbin/sqlgrey line 827.
>>> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at
>>> /usr/sbin/sqlgrey line 818. 
> I cant get these warnings on to appear any of my computers (no idea
> why), so i cant verify that my patch makes them go away.
>
> Could someone who actually gets these warnings apply the attached patch
> and check that they disappear?
>
> $ patch /path/to/your/sqlgrey < sqlgrey-trivial.patch

I did get these warnings and now they disappeared, so WorksForMe(tm).

Lionel.

[Sqlgrey-users] 1.7.4 warnings

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

I need a helping hand..
>> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at
>> /usr/sbin/sqlgrey line 2413.
>> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible
>> typo at /usr/sbin/sqlgrey line 827.
>> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at
>> /usr/sbin/sqlgrey line 818. 
I cant get these warnings on to appear any of my computers (no idea
why), so i cant verify that my patch makes them go away.

Could someone who actually gets these warnings apply the attached patch
and check that they disappear?

$ patch /path/to/your/sqlgrey < sqlgrey-trivial.patch

- Dan

Re: [Sqlgrey-users] Pre Load

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

Micha Silver wrote the following on 20.01.2007 10:27 :
> I set:
> reconnect_delay = 2
> max_connect_age = 24
> Do you recommend something different?
>   

No, it looks good to me.

> Another question I've been trying to work out: Our ISP serves as a 
> backup MX for our mail domains. How should I handle this as far as 
> greylisting?

Don't use a backup MX if you can't control its own anti-SPAM settings.
With greylisting, this means make them access your greylisting database.
In your case, you'll be better of dropping the backup MX (more on this
later).

>  When sqlgrey sends a 450 response to a new message, the 
> backup MX queues it and does it own retry after a short delay.  Then the 
> message is blocked as spam since the sender addr and IP are the same - I 
> think.

It is blocked by SQLgrey once because the backup MX didn't sent the
message originally (unless there is an awl entry for your backup
already). Then the mail passes on the second try because the backup
should comply with RFCs as a full-fledge MTA. If it is blocked as SPAM,
this is for a reason outside SQLgrey's scope (did you forget to
whitelist your backup MX for some anti-SPAM measure?).

Anyway, just dropping the backup MX should help, its only purpose is to
buffer mails when your server is down (which would happen at the origin
anyway). This (only if configured properly, which means you can trigger
the delivery yourself or your server is constantly monitored by the
backup) can help deliver mails waiting for your server to come back
faster. You are trading a non-measurable and dubious speedup in case of
a crash for more SPAMs in the common case.
This even hides the problem to the sender when your server is down for
an extended period of time (some server are configured to warn the
sender when a mail couldn't reach its destination after a configured
period of time). So the clients/partners/... of your mail user may
discover the problem by phone too late if you are struggling to put
systems back online...

I occasionaly use backup MXs, but only if I'm in control of all of them
and they use a more robust common backend (ie: several cheap pizzaboxes
with good CPUs to handle the computations of clamav and spamassassin,
with a fat system for the storage with big redondant disks, redondant
power and high quality components).

Lionel.

Re: [Sqlgrey-users] Pre Load

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

Paul Barbeau wrote:
> PS I am not running 1.7.4 as I got an error and not sure if it was
> important however it did load. The error is below
>
> Name "DBIx::DBCluster::DEBUG" used only once: possible typo at
> /usr/sbin/sqlgrey line 2413.
> Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible
> typo at /usr/sbin/sqlgrey line 827.
> Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at
> /usr/sbin/sqlgrey line 818.
>
> 1.6.7 works so it became the path of least resistance.  I am not using
> DBCluster I am using DBI and I have tried just running "make install"
> off a clean download as well "make use_dbi && make install" and I still
> get the error. 
>   
Allright allright.. Ill fix this :) Its a harmless warning, but if it 
means ppl wont use this version, ill make a hack to hide these and 
release it as 1.7.5.

- Dan

Re: [Sqlgrey-users] Pre Load

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

Micha Silver wrote:
> Where can I see more details on this 1.7.4 version, and the 
> "discrimination" feature?
In 1.7.4+ theres a README.DISCRIMINATION

It allows you to write regex's for any of the fields passed by postfix 
to sqlgrey. Such as HELO string, client ip, client hostname sender and 
recipient and so forth. If i remember correctly, theres even a bunch of 
examples.

By enabling discrimination, those who do NOT match any expression 
defined, skip greylisting entirely. ill give you two very small 
examples, and for example purpose, we assume theres is only 1 expression 
enabled:

client_name =~ ^unknown$
- Hosts without reverse-dns gets greylisted.

client_name !~ \.il$
- All hosts that do NOT end on ".il" gets greylisted.

We use 14 expressions that we have tweaked over time to catch quite a 
lot of spammersm while bothering only few costumers.

Re: [Sqlgrey-users] Pre Load

[Micha S. <mi...@ar...>]

Michael Storz wrote:

> And the following domains are in my domain_awl, which means they work
> correctly:
>
> bgu.ac.il
> cc.huji.ac.il
> inter.net.il
> mail.biu.ac.il
> netvision.net.il
> technion.ac.il
> zahav.net.il
>
> and severeral other have at least an entry in from_awl.
>
> And several of the entries are entered more than once:
>
> < fcradio.net
> < flowersdirect.co.il
> < ganshmuel.com
> < ganshmuel.com
> < gmail.com
> < iroads.co.il
> < mindspring.com
> < nana.co.il
> < taligrapes.co.il
>
> Michael Storz
> --
>   
Michael:
Thanks for the insight. I'll double check our local list. And I 
appreciate your time.
SOme of the "...ac.il" domains have multiple mail servers and I think 
that certain servers behave with greylisting as expected, while others 
do not.

Warm regards,
Micha

Re: [Sqlgrey-users] Pre Load

[Micha S. <mi...@ar...>]

Lionel Bouton wrote:

> I've seen many entries that should work without whitelisting them.
> microsoft.com, yahoo.com, wanadoo.fr, gmail.com at least work on my
> servers and they are others so big that I doubt other SQLgrey users
> never had mail from them.
> Did you setup SQLgrey with a low max_connect_age, the default is 24 (24
> hours)? It could explain so many false positives.
>
> Lionel
>
>   
I set:
reconnect_delay = 2
max_connect_age = 24
Do you recommend something different?

Another question I've been trying to work out: Our ISP serves as a 
backup MX for our mail domains. How should I handle this as far as 
greylisting? When sqlgrey sends a 450 response to a new message, the 
backup MX queues it and does it own retry after a short delay.  Then the 
message is blocked as spam since the sender addr and IP are the same - I 
think. (I looked thru the logs today, but couldn't find a good example, 
but cases like this did come up) .

Thanks,
Micha

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

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

Dave Strickler wrote the following on 19.01.2007 22:29 :
> 1,500 Francs for filing paperwork - Ouch! Someone in the government is
> getting rich ;-)

in fact these were euros. Nearly $2000/year. Numerous people get money
through this system and it is probably less than efficient but this is
the price to pay to get life-long medical/unemployement insurances.

Lionel

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

[Dave S. <dst...@ma...>]

1,500 Francs for filing paperwork - Ouch! Someone in the government is
getting rich ;-)
=20
Well, when you're out on your own, make sure let the list know. I'll be
watching...
=20
In the mean time, anyone interested in helping out? I'm happy to pay a
reasonable consulting fee. I really just need some Perl expertise, and
someone familiar with SQLGrey would be great...
=20
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Lionel Bouton <lio...@bo...> 3:16 PM Friday, January 19,
2007 >>>
Dave Strickler wrote the following on 19.01.2007 20:41 :
> Lionel,
> =20
> Thanks for the *excellent* advice. I will certainly be watching for
> all these things.
> =20
> As far as speeding up SQLGrey, I'm not sure memcache will either,
but
> I know there's only one way to know for sure, and I'm working on
> seeing if it does. I think caching the connect table seem silly, but
> the AWL table seems like a good choice.
> =20
> I'm not sure if this is the right place to ask, but do you do any
> customizing of SQLGrey on an hourly-rate basis? If so, we'd like to
> hire someone to write and test a patch for us with the understanding
> that we would share the results and the code with the Community.

I'm afraid I can't (yet). To do that in France you must set up a
business which is not the quickest and easiest thing to do (as soon as
you earn money you must pay taxes and insurances and you need a
structure to do that, I just learned that the bare minimum of taxes is
around 1500=E2=82=AC/year). I'm currently leaving my employer (I work for=
 them
until April) and I'm looking for a new job while setting a small
business of my own. Then I'll be able to work as a gun for hire part
of
the time.

But if the patch is not to big, you could ask here for it. Someone
else
might be interested or even willing to do it for free :-)

Lionel

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D=
DEVDEV

_______________________________________________
Sqlgrey-users mailing list
Sql...@li...=20
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-=
time, intelligent, e-mail firewall used to scan inbound and outbound mess=
ages for SPAM, Viruses and Content.=20

  For more information, please visit: http://www.mailwise.com

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

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

Dave Strickler wrote the following on 19.01.2007 20:41 :
> Lionel,
> =20
> Thanks for the *excellent* advice. I will certainly be watching for
> all these things.
> =20
> As far as speeding up SQLGrey, I'm not sure memcache will either, but
> I know there's only one way to know for sure, and I'm working on
> seeing if it does. I think caching the connect table seem silly, but
> the AWL table seems like a good choice.
> =20
> I'm not sure if this is the right place to ask, but do you do any
> customizing of SQLGrey on an hourly-rate basis? If so, we'd like to
> hire someone to write and test a patch for us with the understanding
> that we would share the results and the code with the Community.

I'm afraid I can't (yet). To do that in France you must set up a
business which is not the quickest and easiest thing to do (as soon as
you earn money you must pay taxes and insurances and you need a
structure to do that, I just learned that the bare minimum of taxes is
around 1500=E2=82=AC/year). I'm currently leaving my employer (I work for=
 them
until April) and I'm looking for a new job while setting a small
business of my own. Then I'll be able to work as a gun for hire part of
the time.

But if the patch is not to big, you could ask here for it. Someone else
might be interested or even willing to do it for free :-)

Lionel

Re: [Sqlgrey-users] Pre Load

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

On Fri, 19 Jan 2007, Lionel Bouton wrote:

> Micha Silver wrote the following on 19.01.2007 19:58 :
> > Lionel Bouton wrote:
> >
> >
> >> Micha Silver wrote the following on 19.01.2007 19:25 :
> >>
> >>
> >>> Where can I see more details on this 1.7.4 version, and the
> >>> "discrimination" feature?
> >>> We've been using greylisting for several months, and while spam has
> >>> indeed decreased dramatically, we have to spend time every day or two
> >>> adding new entries to the client_fqdn_addr.local file because of false
> >>> postivies. I think the problem is mostly mail servers that resend almost
> >>> immediately and get marked as spam.
> >>>
> >>>
> >>>
> >> What do you mean by "marked as spam"? mails should either be rejected or
> >> received in the end never "marked as spam" (at least by SQLgrey).
> >>
> >>
> > Sorry. I meant rejected.
> >
> >> If you need to add entries to client_fqdn_addr.local in order to receive
> >> mail from them, I'd like to know them.
> >>
> >>
> >>
> > Here's the current list:
> > http://www.arava.co.il/~micha/clients_fqdn_whitelist.local
> > They're mostly domains hosted here in Israel.
> >
>
> I've seen many entries that should work without whitelisting them.
> microsoft.com, yahoo.com, wanadoo.fr, gmail.com at least work on my
> servers and they are others so big that I doubt other SQLgrey users
> never had mail from them.
> Did you setup SQLgrey with a low max_connect_age, the default is 24 (24
> hours)? It could explain so many false positives.
>
> Lionel
>

And the following domains are in my domain_awl, which means they work
correctly:

bgu.ac.il
cc.huji.ac.il
inter.net.il
mail.biu.ac.il
netvision.net.il
technion.ac.il
zahav.net.il

and severeral other have at least an entry in from_awl.

And several of the entries are entered more than once:

< fcradio.net
< flowersdirect.co.il
< ganshmuel.com
< ganshmuel.com
< gmail.com
< iroads.co.il
< mindspring.com
< nana.co.il
< taligrapes.co.il

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

[Dave S. <dst...@ma...>]

Lionel,
=20
Thanks for the *excellent* advice. I will certainly be watching for all =
these things.
=20
As far as speeding up SQLGrey, I'm not sure memcache will either, but I =
know there's only one way to know for sure, and I'm working on seeing if =
it does. I think caching the connect table seem silly, but the AWL table =
seems like a good choice.=20
=20
I'm not sure if this is the right place to ask, but do you do any =
customizing of SQLGrey on an hourly-rate basis? If so, we'd like to hire =
someone to write and test a patch for us with the understanding that we =
would share the results and the code with the Community.
=20
Thanks,
=20
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
dst...@ma...=20
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Lionel Bouton <lio...@bo...> 2:20 PM Friday, January 19, =
2007 >>>
Dave Strickler wrote the following on 19.01.2007 19:41 :
> We are experimenting here with it, with good results on other
> SQL-based PHP software.
> =20
> I would like to try it with Perl, and have the API, but am having
> trouble setting variables into memcached, and could use some help.
> =20
> I am using code that takes a SQL command string, turns it into an MD5
> hash, then sees it if can get the data from memcached. If not, it
> looks it up in SQL, and then stores it in memcached.
> =20
> Anyone try this? I have *NO* idea what I am doing with Perl since I am
> just a PHP guy.

I've used memcached, but with Ruby, not Perl. I've 2 code-related comments =
:
- you shouldn't use MD5 to cache statements it's inefficient and
theoritically you could have a collision (sometime in this century...).
The good practice is to wrap the code that fetches and sets data in the
DB to access memcache with a unique key (you can reuse the primary key
used by SQLgrey)

For example in the "is_in_from_awl" method you could check for the
presence of the key "from_awl|<sender_name>|sender_domain|src" which
would be expected to store the "last_seen" value stored in DB, if not
found, ask the DB.
For the key I use '|' as a separator and the table and the primary key
column names as element to make sure the key is unique in memcache.

Then in "put_in_from_awl" you'd build the key the same way and put the
last_seen value with an expiration of
"$self->past_tstamp($self->{sqlgrey}{awl_age}" days.

Don't forget to put and check the timestamp values or changing your
delays in configuration would have no effect on memcache-stored entries.

- you *must* handle the cache expiration (by either making entries
expire, explicitely delete them or checking their value for validity),
by only wrapping the statements like you do in your code you don't
handle DELETEs properly, the method described above should be ok. Note:
when you'll want to handle the connect table, you'll definitely have to
delete entries in memcache when they are moved to the auto-whitelist.

On the principle, I'm not sure you would earn much from using memcache.
When you don't find an information in memcache, you suppose it isn't
there and check in the DB instead. So a large subset of the queries made
by SQLgrey will still hit the DB. You could alleviate the problem by
storing negative hits in memcache, but then you'll have to expire them
properly too (and there should be so many of them, nearly never reused
that you could end up ejecting more useful memcache content when adding
them).
If it works for you could you please bench the results with and without
your modification (the average load on a mail server with a local DB
with and without your patch). I don't think we have any performance
problem even with large mail systems but if we get a good performance
boost, I'll definitely consider adding your patch.

Lionel.


-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share =
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3DDE=
VDEV=20
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...=20
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A

Re: [Sqlgrey-users] Pre Load

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

Micha Silver wrote the following on 19.01.2007 19:58 :
> Lionel Bouton wrote:
>
>   
>> Micha Silver wrote the following on 19.01.2007 19:25 :
>>   
>>     
>>> Where can I see more details on this 1.7.4 version, and the 
>>> "discrimination" feature?
>>> We've been using greylisting for several months, and while spam has 
>>> indeed decreased dramatically, we have to spend time every day or two 
>>> adding new entries to the client_fqdn_addr.local file because of false 
>>> postivies. I think the problem is mostly mail servers that resend almost 
>>> immediately and get marked as spam.
>>>   
>>>     
>>>       
>> What do you mean by "marked as spam"? mails should either be rejected or
>> received in the end never "marked as spam" (at least by SQLgrey).
>>   
>>     
> Sorry. I meant rejected.
>   
>> If you need to add entries to client_fqdn_addr.local in order to receive
>> mail from them, I'd like to know them.
>>
>>   
>>     
> Here's the current list:
> http://www.arava.co.il/~micha/clients_fqdn_whitelist.local
> They're mostly domains hosted here in Israel.
>   

I've seen many entries that should work without whitelisting them.
microsoft.com, yahoo.com, wanadoo.fr, gmail.com at least work on my
servers and they are others so big that I doubt other SQLgrey users
never had mail from them.
Did you setup SQLgrey with a low max_connect_age, the default is 24 (24
hours)? It could explain so many false positives.

Lionel

Re: [Sqlgrey-users] Anyone use memcached with SQLGrey ?

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

Dave Strickler wrote the following on 19.01.2007 19:41 :
> We are experimenting here with it, with good results on other
> SQL-based PHP software.
>  
> I would like to try it with Perl, and have the API, but am having
> trouble setting variables into memcached, and could use some help.
>  
> I am using code that takes a SQL command string, turns it into an MD5
> hash, then sees it if can get the data from memcached. If not, it
> looks it up in SQL, and then stores it in memcached.
>  
> Anyone try this? I have *NO* idea what I am doing with Perl since I am
> just a PHP guy.

I've used memcached, but with Ruby, not Perl. I've 2 code-related comments :
- you shouldn't use MD5 to cache statements it's inefficient and
theoritically you could have a collision (sometime in this century...).
The good practice is to wrap the code that fetches and sets data in the
DB to access memcache with a unique key (you can reuse the primary key
used by SQLgrey)

For example in the "is_in_from_awl" method you could check for the
presence of the key "from_awl|<sender_name>|sender_domain|src" which
would be expected to store the "last_seen" value stored in DB, if not
found, ask the DB.
For the key I use '|' as a separator and the table and the primary key
column names as element to make sure the key is unique in memcache.

Then in "put_in_from_awl" you'd build the key the same way and put the
last_seen value with an expiration of
"$self->past_tstamp($self->{sqlgrey}{awl_age}" days.

Don't forget to put and check the timestamp values or changing your
delays in configuration would have no effect on memcache-stored entries.

- you *must* handle the cache expiration (by either making entries
expire, explicitely delete them or checking their value for validity),
by only wrapping the statements like you do in your code you don't
handle DELETEs properly, the method described above should be ok. Note:
when you'll want to handle the connect table, you'll definitely have to
delete entries in memcache when they are moved to the auto-whitelist.

On the principle, I'm not sure you would earn much from using memcache.
When you don't find an information in memcache, you suppose it isn't
there and check in the DB instead. So a large subset of the queries made
by SQLgrey will still hit the DB. You could alleviate the problem by
storing negative hits in memcache, but then you'll have to expire them
properly too (and there should be so many of them, nearly never reused
that you could end up ejecting more useful memcache content when adding
them).
If it works for you could you please bench the results with and without
your modification (the average load on a mail server with a local DB
with and without your patch). I don't think we have any performance
problem even with large mail systems but if we get a good performance
boost, I'll definitely consider adding your patch.

Lionel.

Re: [Sqlgrey-users] Pre Load

[Micha S. <mi...@ar...>]

Lionel Bouton wrote:

> Micha Silver wrote the following on 19.01.2007 19:25 :
>   
>> Where can I see more details on this 1.7.4 version, and the 
>> "discrimination" feature?
>> We've been using greylisting for several months, and while spam has 
>> indeed decreased dramatically, we have to spend time every day or two 
>> adding new entries to the client_fqdn_addr.local file because of false 
>> postivies. I think the problem is mostly mail servers that resend almost 
>> immediately and get marked as spam.
>>   
>>     
>
> What do you mean by "marked as spam"? mails should either be rejected or
> received in the end never "marked as spam" (at least by SQLgrey).
>   
Sorry. I meant rejected.
> If you need to add entries to client_fqdn_addr.local in order to receive
> mail from them, I'd like to know them.
>
>   
Here's the current list:
http://www.arava.co.il/~micha/clients_fqdn_whitelist.local
They're mostly domains hosted here in Israel.

Regards,
Micha
> Lionel.
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>   

[Sqlgrey-users] Anyone use memcached with SQLGrey ?

[Dave S. <dst...@ma...>]

We are experimenting here with it, with good results on other SQL-based =
PHP software.=20
=20
I would like to try it with Perl, and have the API, but am having trouble =
setting variables into memcached, and could use some help.
=20
I am using code that takes a SQL command string, turns it into an MD5 =
hash, then sees it if can get the data from memcached. If not, it looks it =
up in SQL, and then stores it in memcached.
=20
Anyone try this? I have *NO* idea what I am doing with Perl since I am =
just a PHP guy.
=20
Thanks!
=20
    sub get_memcached_object {
    my ($sql) =3D @_;
    use Digest::MD5;
    my $md5 ;
    $md5 =3D Digest::MD5->new;
    $md5->add($sql) ;
    my $md5_id =3D $md5->hexdigest ;
    print "SQL hashed to $md5_id \n" ;
=20
    use Cache::Memcached;
    my $obj =3D Cache::Memcached->get("$md5_id");
    if ($obj)
    {
    # return $obj if $obj;
    print "$md5_id found in memcached \n" ;
    return $obj;
    }
=20
    # $obj =3D $::db->selectrow_hashref("$sql") ;
    my $dbh ;
    $dbh =3D DBI->connect('DBI:Pg:dbname=3Dv3_mw_filter_rules; host=3Dour_h=
ostname, our_username, ourpassword,
                   { PrintError =3D> 0,
                       AutoCommit =3D> 1,
                       InactiveDestroy =3D> 1
                   }
                 ) ;
    print "Requesting from SQL server: $sql \n" ;
    $obj =3D $dbh->selectrow_hashref("$sql") ;
    print "Settting object in memcached: $obj \n" ;
    Cache::Memcached->set("$md5_id", $obj);
=20
    my $obj ;
    $obj =3D Cache::Memcached->get("$md5_id");
    if ($obj)
    {
    # return $obj if $obj;
    print "$md5_id found in memcached \n" ;
    return $obj;
    }
}

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A

Re: [Sqlgrey-users] Pre Load

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

Micha Silver wrote the following on 19.01.2007 19:25 :
> Dan Faerch wrote:
>
>   
>> I agree with Lionel.
>> Its best letting sqlgrey do all this since you really cant tell who is 
>> valid senders if you dont temporally reject first, and then sees who 
>> comes back. (which is what greylisting does)
>>
>> If you are worried that delaying all mail for 5 minutes at the same day 
>> you enable sqlgrey, consider using the "discrimination" feature from 
>> 1.7.4+. That will let you, via regular expressions, select what to 
>> greylist and what to just let through . Good way of slowly introducing 
>> greylisting by making the discrimination rules more and more restrictive.
>>
>>   
>>     
> Where can I see more details on this 1.7.4 version, and the 
> "discrimination" feature?
> We've been using greylisting for several months, and while spam has 
> indeed decreased dramatically, we have to spend time every day or two 
> adding new entries to the client_fqdn_addr.local file because of false 
> postivies. I think the problem is mostly mail servers that resend almost 
> immediately and get marked as spam.
>   

What do you mean by "marked as spam"? mails should either be rejected or
received in the end never "marked as spam" (at least by SQLgrey).

If you need to add entries to client_fqdn_addr.local in order to receive
mail from them, I'd like to know them.

Lionel.

Re: [Sqlgrey-users] Pre Load

[Micha S. <mi...@ar...>]

Dan Faerch wrote:

> I agree with Lionel.
> Its best letting sqlgrey do all this since you really cant tell who is 
> valid senders if you dont temporally reject first, and then sees who 
> comes back. (which is what greylisting does)
>
> If you are worried that delaying all mail for 5 minutes at the same day 
> you enable sqlgrey, consider using the "discrimination" feature from 
> 1.7.4+. That will let you, via regular expressions, select what to 
> greylist and what to just let through . Good way of slowly introducing 
> greylisting by making the discrimination rules more and more restrictive.
>
>   
Where can I see more details on this 1.7.4 version, and the 
"discrimination" feature?
We've been using greylisting for several months, and while spam has 
indeed decreased dramatically, we have to spend time every day or two 
adding new entries to the client_fqdn_addr.local file because of false 
postivies. I think the problem is mostly mail servers that resend almost 
immediately and get marked as spam.

Thanks,
Micha

Re: [Sqlgrey-users] Pre Load

[Paul B. <pa...@hy...>]

Thanks for the feed back.  We have already for the last week with one of
our domains been running it and have notice some servers (Bell Canada)
retry in 3 hours not 20 mins so that delay is getting a bit up there.
Also about 6 months ago we tried with postgrey and had to pull it
because of the delays so I am a bit gun shy.

With the sql interface we can keep a better eye on what is in the
connect database so I think I will enter the ones I know are good via
SQL INSERT and wing it for the rest. =20

Thanks again for the input everyone.

Paul

PS I am not running 1.7.4 as I got an error and not sure if it was
important however it did load. The error is below

Name "DBIx::DBCluster::DEBUG" used only once: possible typo at
/usr/sbin/sqlgrey line 2413.
Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible
typo at /usr/sbin/sqlgrey line 827.
Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at
/usr/sbin/sqlgrey line 818.

1.6.7 works so it became the path of least resistance.  I am not using
DBCluster I am using DBI and I have tried just running "make install"
off a clean download as well "make use_dbi && make install" and I still
get the error.=20


-----Original Message-----
From: sql...@li...
[mailto:sql...@li...] On Behalf Of Dan
Faerch
Sent: Friday, January 19, 2007 12:28 PM
To: SQLgrey users mailing-list
Subject: Re: [Sqlgrey-users] Pre Load

I agree with Lionel.
Its best letting sqlgrey do all this since you really cant tell who is=20
valid senders if you dont temporally reject first, and then sees who=20
comes back. (which is what greylisting does)

If you are worried that delaying all mail for 5 minutes at the same day=20
you enable sqlgrey, consider using the "discrimination" feature from=20
1.7.4+. That will let you, via regular expressions, select what to=20
greylist and what to just let through . Good way of slowly introducing=20
greylisting by making the discrimination rules more and more
restrictive.


Also i got this wild idea of the top of my head (note: my wild idea's=20
arent always recommended :)):

You could let sqlgrey learn over a month or two. Havent tried excactly=20
what you need, but i imagine something like this would work:

By using version 1.7.4+ you can set the "reject_code". By setting
reject_code =3D dunno
sqlgrey performs as usual, except nothing gets rejected.

Then by changing  "reconnect_delay" to something really low (dont know=20
if you kan use "0" here) and
"max_connect_age" to something really high, then everyone who sends more

than 1 mail withing the time specified by "max_connect_age", will be=20
treated as a valid sender and added to "from_awl".

after a while, simply set reject_code, max_connect_age and=20
reconnect_delay back to default.

A word of warning.
This will result in a large "connect" table. Also, you will likely will=20
get a stack of spammers added to your from_awl as well whom will have to

expire.

As i said, its a wild idea ;)

- Dan Faerch


Paul Barbeau wrote:
> I was not using grey listing before at all so this is first time data.
> What I am pulling out of the logs is from the postfix lines and they
are
> sender, recipient, server what normally would have been passed to the
> server if I had been using it.  I know they will go into the "connect"
> first however based on volume I can move them over as I know what
should
> have been added.
>
> Hope this clears up what i meant.
>
> Paul
>
> -----Original Message-----
> From: sql...@li...
> [mailto:sql...@li...] On Behalf Of
Kasey
> Speakman
> Sent: Friday, January 19, 2007 11:04 AM
> To: SQLgrey users mailing-list
> Subject: Re: [Sqlgrey-users] Pre Load
>
> If you're using the same MySQL database as before, it should already =20
> be there.  The logs only record what is inserted into the database, =20
> so losing the logs will not affect your domain_awl, as long as the =20
> database still exists.  If you're setting up a new MySQL server too, =20
> then just mysql-dump the old database and restore it into the new =20
> server.
>
> On Jan 19, 2007, at 9:43 AM, Paul Barbeau wrote:
>
>  =20
>> I am about to start up SQLGREY and I would like to "pre" load the
>> "domain_awl" and "from_awl" from my old postfix logs and usage.  I =20
>> know
>> I can and server/ip to my whitelist files however I would rather =20
>> them go
>> into the AWL so they can expire if need be.  Other then just adding
to
>> the database is there a better way to do this?  Some how submit =20
>> into the
>> daemon?
>>
>> Paul
>>
>>
>>
----------------------------------------------------------------------
>>    =20
>
>  =20
>> ---
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to =20
>> share your
>> opinions on IT & business topics through brief surveys - and earn
cash
>> http://www.techsay.com/default.php?=20
>> page=3Djoin.php&p=3Dsourceforge&CID=3DDEVDEV
>> _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>
>> --=20
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>>    =20
>
>
>
------------------------------------------------------------------------
> -
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to
share
> your
> opinions on IT & business topics through brief surveys - and earn cash
>
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D=
DEVDE
> V
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>
------------------------------------------------------------------------
-
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to
share your
> opinions on IT & business topics through brief surveys - and earn cash
>
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D=
DEVDE
V
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>  =20


------------------------------------------------------------------------
-
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D=
DEVDE
V
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users