Mailing List Archive: SpamAssassin: users

Spamassassin rules from sql

Index | Next | Previous | View Threaded

maf at eurotux

Feb 1, 2012, 8:37 AM

Post #1 of 8 (314 views)
Permalink

Spamassassin rules from sql

Hi!

I'm just wondering, is there a limitation on the type of rules that can
be added to the SpamassAssin rules table?
Because adding something like (subject scoring):

header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations
of fields: preference & value, with no success)
score TEST_SUBJECT 30 (this one is possible)

Does not seem straightforward...

My goal is to be able to score subjects through sql rules.

Thanks!

--
Cumprimentos,
Miguel Fernandes

kdeugau at vianet

Feb 1, 2012, 8:52 AM

Post #2 of 8 (302 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

Miguel Fernandes wrote:
> Hi!
>
> I'm just wondering, is there a limitation on the type of rules that can
> be added to the SpamassAssin rules table?
> Because adding something like (subject scoring):
>
> header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations
> of fields: preference & value, with no success)
> score TEST_SUBJECT 30 (this one is possible)
>
> Does not seem straightforward...
>
> My goal is to be able to score subjects through sql rules.

It should be possible with something like (untested):

INSERT INTO userpref (username, preference, value) VALUES
('@GLOBAL','header TEST_SUBJECT','Subject =~ /test/i');

I don't know if @GLOBAL entries like that will be preloaded like the
stock rules or any of the other file-based config, or if that's just a
shortcut for something to be retrieved for all users on a userprefs
lookup. If it's the latter (I'm pretty sure it is), you'll have to
enable allow_user_rules.

FWIW, this is probably the wrong way to solve whatever problem you're
seeing. If you can describe why you want to do this, someone can
probably give you a better way to do it.

-kgd

axb.lists at gmail

Feb 1, 2012, 9:01 AM

Post #3 of 8 (307 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

On 02/01/2012 05:52 PM, Kris Deugau wrote:
> Miguel Fernandes wrote:
>> Hi!
>>
>> I'm just wondering, is there a limitation on the type of rules that can
>> be added to the SpamassAssin rules table?
>> Because adding something like (subject scoring):
>>
>> header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations
>> of fields: preference & value, with no success)
>> score TEST_SUBJECT 30 (this one is possible)
>>
>> Does not seem straightforward...
>>
>> My goal is to be able to score subjects through sql rules.
>
> It should be possible with something like (untested):
>
> INSERT INTO userpref (username, preference, value) VALUES
> ('@GLOBAL','header TEST_SUBJECT','Subject =~ /test/i');
>
> I don't know if @GLOBAL entries like that will be preloaded like the
> stock rules or any of the other file-based config, or if that's just a
> shortcut for something to be retrieved for all users on a userprefs
> lookup. If it's the latter (I'm pretty sure it is), you'll have to
> enable allow_user_rules.
>
> FWIW, this is probably the wrong way to solve whatever problem you're
> seeing. If you can describe why you want to do this, someone can
> probably give you a better way to do it.

Check:

http://wiki.apache.org/spamassassin/UsingSQL

"Global, Per-Domain, and Per-User Preferences via SQL"

This setup works. (thanks Dallas)

maf at eurotux

Feb 1, 2012, 10:23 AM

Post #4 of 8 (298 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

Just tested with this:

+--------------+---------------------+--------------------+
| username | preference | value |
+--------------+---------------------+--------------------+
| @fatias.pt | header TEST_SUBJECT | Subject =~ /test/i |
| @fatias.pt | score TEST_SUBJECT | 30 |
| @fatias.pt | score GTUBE | 10 |
+--------------+---------------------+--------------------+

Catched Mysql doing this:

SELECT preference, value FROM userpref WHERE username = 'manel [at] fatias' OR username = '$GLOBAL' OR username = CONCAT('@','fatias.pt') ORDER BY username ASC
(I've changed the '%' for a '@', as I need exact domain matches, hence the username entries with '@'domain)

result is:
+---------------------+--------------------+
| preference | value |
+---------------------+--------------------+
| header TEST_SUBJECT | Subject =~ /test/i |
| score TEST_SUBJECT | 30 |
| score GTUBE | 10 |
+---------------------+--------------------+

GTUBE works, TEST_SUBJECT does not

Thanks for the replies!

On 02/01/2012 05:01 PM, Axb wrote:
> On 02/01/2012 05:52 PM, Kris Deugau wrote:
>> Miguel Fernandes wrote:
>>> Hi!
>>>
>>> I'm just wondering, is there a limitation on the type of rules that can
>>> be added to the SpamassAssin rules table?
>>> Because adding something like (subject scoring):
>>>
>>> header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations
>>> of fields: preference & value, with no success)
>>> score TEST_SUBJECT 30 (this one is possible)
>>>
>>> Does not seem straightforward...
>>>
>>> My goal is to be able to score subjects through sql rules.
>>
>> It should be possible with something like (untested):
>>
>> INSERT INTO userpref (username, preference, value) VALUES
>> ('@GLOBAL','header TEST_SUBJECT','Subject =~ /test/i');
>>
>> I don't know if @GLOBAL entries like that will be preloaded like the
>> stock rules or any of the other file-based config, or if that's just a
>> shortcut for something to be retrieved for all users on a userprefs
>> lookup. If it's the latter (I'm pretty sure it is), you'll have to
>> enable allow_user_rules.
>>
>> FWIW, this is probably the wrong way to solve whatever problem you're
>> seeing. If you can describe why you want to do this, someone can
>> probably give you a better way to do it.
>
>
> Check:
>
> http://wiki.apache.org/spamassassin/UsingSQL
>
> "Global, Per-Domain, and Per-User Preferences via SQL"
>
> This setup works. (thanks Dallas)
>
>

--
Cumprimentos,
Miguel Fernandes

axb.lists at gmail

Feb 1, 2012, 10:26 AM

Post #5 of 8 (300 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

kdeugau at vianet

Feb 1, 2012, 10:37 AM

Post #6 of 8 (298 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

Miguel Fernandes wrote:
> Just tested with this:
>
> +--------------+---------------------+--------------------+
> | username | preference | value |
> +--------------+---------------------+--------------------+
> | @fatias.pt | header TEST_SUBJECT | Subject =~ /test/i |
> | @fatias.pt | score TEST_SUBJECT | 30 |
> | @fatias.pt | score GTUBE | 10 |
> +--------------+---------------------+--------------------+
>
> Catched Mysql doing this:
>
> SELECT preference, value FROM userpref WHERE username =
> 'manel [at] fatias' OR username = '$GLOBAL' OR username =
> CONCAT('@','fatias.pt') ORDER BY username ASC
> (I've changed the '%' for a '@', as I need exact domain matches, hence
> the username entries with '@'domain)
>
> result is:
> +---------------------+--------------------+
> | preference | value |
> +---------------------+--------------------+
> | header TEST_SUBJECT | Subject =~ /test/i |
> | score TEST_SUBJECT | 30 |
> | score GTUBE | 10 |
> +---------------------+--------------------+

Right, but does SA actually trigger than rule? I'm pretty sure it
won't, because regex rules are not allowed in userprefs unless you have
allow_user_rules enabled.

What I've done where I really want/need a rule for a subset of users is
to define the rule as usual in my local configuration, but set a score
of 0. That way it won't fire by default. Then, add the "score
TEST_SUBJECT 30" entry to the userprefs as you've got above, and the
rule *will* trigger.

-kgd

parkerm at pobox

Feb 1, 2012, 1:09 PM

Post #7 of 8 (304 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

On Feb 1, 2012, at 10:37 AM, Miguel Fernandes wrote:

> Hi!
>
> I'm just wondering, is there a limitation on the type of rules that can be added to the SpamassAssin rules table?
> Because adding something like (subject scoring):
>
> header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations of fields: preference & value, with no success)
> score TEST_SUBJECT 30 (this one is possible)
>
> Does not seem straightforward...
>
> My goal is to be able to score subjects through sql rules.
>

Perhaps you can make use of

loadplugin Mail::SpamAssassin::Plugin::WhiteListSubject

blacklist_subject test
blacklist_subject This is a subject line I want to blacklist

That will cause the msg to get an extra 100 score.

You can of course also whitelist subjects as well, feel free to look over the documentation.

whitelist_subject and blacklist_subject are not listed as admin commands so they should work just fine in SQL. You won't be able regexes but you can use glob type params.

Michael

maf at eurotux

Feb 2, 2012, 1:40 AM

Post #8 of 8 (294 views)
Permalink

Re: Spamassassin rules from sql [In reply to]

Thanks!

That worked perfectly:

blacklist_subject test

On 02/01/2012 09:09 PM, Michael Parker wrote:
> On Feb 1, 2012, at 10:37 AM, Miguel Fernandes wrote:
>
>> Hi!
>>
>> I'm just wondering, is there a limitation on the type of rules that can be added to the SpamassAssin rules table?
>> Because adding something like (subject scoring):
>>
>> header TEST_SUBJECT Subject =~ /test/i (I've tried several combinations of fields: preference& value, with no success)
>> score TEST_SUBJECT 30 (this one is possible)
>>
>> Does not seem straightforward...
>>
>> My goal is to be able to score subjects through sql rules.
>>
> Perhaps you can make use of
>
> loadplugin Mail::SpamAssassin::Plugin::WhiteListSubject
>
> blacklist_subject test
> blacklist_subject This is a subject line I want to blacklist
>
> That will cause the msg to get an extra 100 score.
>
> You can of course also whitelist subjects as well, feel free to look over the documentation.
>
> whitelist_subject and blacklist_subject are not listed as admin commands so they should work just fine in SQL. You won't be able regexes but you can use glob type params.
>
> Michael
>
>

--
Cumprimentos,
Miguel Fernandes

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads