mysql & spamassassin

[mr...@sbgit.com]

Hi there, 

it's me again.
Now i got a question about mysql and Spamassassin.

I wan't to store all rules inside an mysql table,
but the only think i can find is to store user prefs.

I did'nt use user prefs, because i use 
postfix/amavisd-new/SA as an Anti Spam gateway wich
is connected between internet and the internal mailserver.

Thanx peter

Re: mysql & spamassassin

[mouss <us...@free.fr>]

Keith Dunnett a écrit :
> As long as spamassassin is invoked from 
> something which behaves properly (i.e. delivers the mail) in the event 
> that spamd fails - which amavis does, and so does maildrop - then I 
> can't see that the restart causes a significant problem.

well, it may cause the mta to consider the "destination" as having 
problems and thus delay all mail for sometime.

Re: mysql & spamassassin

[Keith Dunnett <ke...@dunnett.org>]

Mike Jackson wrote:

>> IIWY I would not try to reinvent the wheel. SQL support is useful for 
>> userprefs, Bayes and AWL because it stores data for users who don't 
>> have a home directory on the system. That's why it's been developed 
>> for those functions. For global information which happily lives in 
>> /etc, I don't see why you *want* this in MySQL other than 'because 
>> SQL is cool' (which is not a good basis for running your mail system).
>
>
> There is the little issue that changes to SQL-based rules don't 
> require a spamd restart. That would be one reason to store rules in a 
> database rather than flat files. It would work best on smaller systems 
> and/or ones without lots of extra rulesets, because you'd lose any 
> performance benefit if it had to poll the database for every rule for 
> every single message, but it's still a reason.

Valid point, which I hadn't considered. However, you'd have to have a 
busy system to mind a ten-second restart every two to three days (such 
as caused by rules_du_jour), and if using it on a busy system would lose 
much of the benefit, then the point is moot. In this particular case, 
the question is somewhat academic given that per-user configuration is 
disabled.

>
> (BTW, I haven't tested this, but with any rule that could be set in a 
> user preference file, you should be able to set '@GLOBAL' as the 
> username in the SQL preferences to make the rule apply to anyone.)

Apparently not. From the relevant sql/README file:

'Note that this will NOT look for test rules, only local scores, 
whitelist_from(s), required_score, and auto_report_threshold.'

Quite why that is, I can't be certain... but I can see why this has not 
been developed as a matter of priority, and don't think that it is a 
priority. I retract the view that the use of SQL-based rulesets is 
undesirable, but remain of the opinion that it is not practical or 
worthwhile at the moment. As long as spamassassin is invoked from 
something which behaves properly (i.e. delivers the mail) in the event 
that spamd fails - which amavis does, and so does maildrop - then I 
can't see that the restart causes a significant problem.

Regards,

Keith

Re: mysql & spamassassin

[Mike Jackson <mj...@barking-dog.net>]

> With due respect... if you're not using per-user preferences then you're 
> not using per-user rules either, so what is the point of storing them in 
> MySQL?
> How often do they need to be edited, and by whom?
>
> Good reasons not to: there's no support for it that I know of, so you'll 
> have to write it yourself, and if it is possible it would break 
> compatibility with existing tools like sa-update and rules_du_jour (which 
> you need to filter the HTML spam you asked about earlier). Get those 
> working first, and URIBLs / SURBLs as suggested in a later reply, and then 
> decide whether you really need to break it all...
>
> IIWY I would not try to reinvent the wheel. SQL support is useful for 
> userprefs, Bayes and AWL because it stores data for users who don't have a 
> home directory on the system. That's why it's been developed for those 
> functions. For global information which happily lives in /etc, I don't see 
> why you *want* this in MySQL other than 'because SQL is cool' (which is 
> not a good basis for running your mail system).

There is the little issue that changes to SQL-based rules don't require a 
spamd restart. That would be one reason to store rules in a database rather 
than flat files. It would work best on smaller systems and/or ones without 
lots of extra rulesets, because you'd lose any performance benefit if it had 
to poll the database for every rule for every single message, but it's still 
a reason.

(BTW, I haven't tested this, but with any rule that could be set in a user 
preference file, you should be able to set '@GLOBAL' as the username in the 
SQL preferences to make the rule apply to anyone.) 

Re: mysql & spamassassin

[Keith Dunnett <ke...@dunnett.org>]

mrtg@sbgit.com wrote:

>Hi there, 
>
>it's me again.
>Now i got a question about mysql and Spamassassin.
>
>I wan't to store all rules inside an mysql table,
>but the only think i can find is to store user prefs.
>
>I did'nt use user prefs, because i use 
>postfix/amavisd-new/SA as an Anti Spam gateway wich
>is connected between internet and the internal mailserver.
>
>  
>
With due respect... if you're not using per-user preferences then you're 
not using per-user rules either, so what is the point of storing them in 
MySQL?
How often do they need to be edited, and by whom?

Good reasons not to: there's no support for it that I know of, so you'll 
have to write it yourself, and if it is possible it would break 
compatibility with existing tools like sa-update and rules_du_jour 
(which you need to filter the HTML spam you asked about earlier). Get 
those working first, and URIBLs / SURBLs as suggested in a later reply, 
and then decide whether you really need to break it all...

IIWY I would not try to reinvent the wheel. SQL support is useful for 
userprefs, Bayes and AWL because it stores data for users who don't have 
a home directory on the system. That's why it's been developed for those 
functions. For global information which happily lives in /etc, I don't 
see why you *want* this in MySQL other than 'because SQL is cool' (which 
is not a good basis for running your mail system).

Regards,

Keith.