How to use @sa_userconf_maps for white-/blacklisting in Amavis 2.7.0
Matt Goodman
matt at dgisoda.com
Sun Oct 16 00:13:46 CEST 2011
I'd love to help but I don't really see a problem at this very moment. It looks like everything is mapping properly - so at this point you should define your rules and test them.
Keep in mind also you're not going to see the SpamAssassin/SQL debug simply because amavis is not the one doing that - SpamAssassin is. Now whether you have debug turned on there or not is another matter entirely. From all I can see there - you should be good to go.
Decide which userpref you want to test against and create a rule for it in the table. Make sure the username is $GLOBAL and then fill out the other fields accordingly.
-----Original Message-----
From: SB [mailto:kfswido at yahoo.de]
Sent: Saturday, October 15, 2011 10:27 AM
To: amavis-users at amavis.org; Matt Goodman
Subject: Re: How to use @sa_userconf_maps for white-/blacklisting in Amavis 2.7.0
Hi,
Thanks for your help. I think we're getting closer to it:
I get some log entries now, but no further processing is done (see below, the blacklist entry is not retrieved). I also don't observe any call to the database in the mysql log.
These are the entries I get (slightly anonymised):
-----
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) lookup [sa_userconf] => undef, "recipient-emailaddress at example.org" does not match Oct 15 22:08:38 myservername amavis[23564]: (23564-01)
lookup_re("recipient-emailaddress at example.org") matches key ".*", result="$GLOBAL"
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) lookup [sa_username] => true, "recipient-emailaddress at example.org" matches, result="$GLOBAL", matching_key=".*"
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) SA user config:
"", username: "$GLOBAL", 0
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) switching SA (0) username "amavis" -> "$GLOBAL"
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) calling SA parse (0), SA vers 3.3.1, 3.003001, data as GLOB, recips_ind [0], user: "$GLOBAL"
Oct 15 22:08:38 myservername amavis[23564]: (23564-01) get_deadline SA check - deadline in 479.9 s, set to 475.000 s Oct 15 22:08:38 myservername amavis[23564]: (23564-01) CALLING SA check (0) Oct 15 22:08:38 myservername named[1183]: error (unexpected RCODE
REFUSED) resolving 'somedomain.com/A/IN': 8.8.8.8#53 Oct 15 22:08:38 myservername named[1183]: error (unexpected RCODE
REFUSED) resolving 'someotherdomain.com/A/IN': 8.8.8.8#53 Oct 15 22:08:43 myservername amavis[23564]: (23564-01) DONE SA check (0) Oct 15 22:08:43 myservername amavis[23564]: (23564-01) get_deadline spam_scan_sa - deadline in 474.1 s, set to 332.000 s Oct 15 22:08:43 myservername amavis[23564]: (23564-01) prolong_timer
spam_scan_sa: timer 332, was 471, deadline in 474.1 s Oct 15 22:08:43 myservername amavis[23564]: (23564-01) spam_scan:
score=-1.794 autolearn=ham
tests=[BAYES_50=0.8,DKIM_SIGNED=0.1,DKIM_VALID=-0.1,DKIM_VALID_AU=-0.1,DKIM_VERIFIED=-5.7,HTML_IMAGE_RATIO_06=0.001,HTML_MESSAGE=0.001,MIME_QP_LONG_LINE=0.001,PYZOR_CHECK=1.392,RCVD_IN_DNSWL_NONE=-0.0001,REMOVE_BEFORE_LINK=1.8,SPF_FAIL=0.001,T_FRT_PROFIT1=0.01]
recips=0
----
("$GLOBAL" is the name under which I have defined my rules in the spamassassin db, and I've simply mapped all email addresses to this global identifier in the config file of amavis, as follows:
@sa_userconf_maps = (
{
'.*' => 'sql:',
}
);
@sa_username_maps = new_RE (
[ '.*' => '$GLOBAL' ]
);
)
So I suppose the reason is that spamassassin's user_scores_dsn ruleset in /etc/spamassassin/local.cf is not considered. Might it be reading from the wrong file, or not have the permission etc.?
(Does anyone know how to figure out which spamassassin config file amavis uses?)
I think this is a very probable cause since everything works (also the
blacklisting) when sending my email via spamc on the command line.
I'd be more than happy if anyone could point me in the right direction.
I've spent hours fixing this, without success.
Thanks a lot,
S.
>
> I would say that if there is even one entry in the database that you did not place yourself manually, then it is likely that the SQL is functional. You could increase the amavis debug log to maximum and you should look for @sa_username_maps to determine if the amavis log is correctly determining the SA username, be prepared for a lot of debug information but I do know that amavisd refers to the @sa*.* config options in the debug, so you could always look for those.
>
> Did you add the @sa_username_maps option into your amavisd.conf?
>
> Lastly with respect to the database design you should probably dump it and start over using the SpamAssassin SQL schema that is provided in the source tarball. It's in there somewhere, definitely use it.
>
>
> -----Original Message-----
> From: SB [mailto:kfswido at yahoo.de]
> Sent: Friday, October 14, 2011 12:00 PM
> To: Matt Goodman
> Subject: Re: How to use @sa_userconf_maps for white-/blacklisting in
> Amavis 2.7.0
>
> Hi,
>
> Thanks a lot. All your comments make sense; I've double-checked everything, but still no effect. This makes me ask two more things:
>
> - Are there any log file entries that I could track (or miss)?
> - Let's check the database design. I have spamassassin db with just one table named "userpref".
>
> The content of this table is as follows:
>
> username preference value prefid
> amavis blacklist_from mail at example.org 1
>
> or whitelist_from, whitelist_auth... Is that correct?
>
> It pretty much seems to me the database isn't considered at all. May I have forgotten to install any SQL module etc.?
>
>
> Thanks a lot for your help,
>
> S.
>
>
>> I have had some experience using these new config parameters. Let me
>> try to answer these as best I can:
>>
>>
>>
>> Hi,
>>
>> I'd like to use the new sa_userconf_maps feature in Amavis 2.7.0 (Ubuntu
>> x64), but unfortunately I'm not able to figure out the correct
>> configuration from what I can find online.
>>
>> My setting is as follows:
>>
>> I have a MYSQL database called 'spamassassin_db' on my server with a
>> single table 'userpref' in it, containing white- and blacklist entries.
>> These are filled by some other application, so I don't want to change
>> anything here (e.g., to amavis SQL rules which seem to be even less well
>> documented) if possible.
>>
>> I have configured the SQL database in my /etc/spamassassin/local.cf as
>> follows:
>>
>> user_scores_dsn DBI:mysql:spamassassin_db:localhost
>> user_scores_sql_username (...)
>> user_scores_sql_password (...)
>> user_scores_sql_custom_query SELECT preference, value FROM userpref
>> WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username =
>> CONCAT('%',_DOMAIN_) ORDER BY username ASC
>>
>>
>>
>> What I would like amavisd to do is to have spamassassin respect the
>> whitelist_auth/blacklist_from rules in this database table globally,
>> i.e. regardless of the recipient's email address.
>> However, I don't seem to know the appropriate commands, and where to put
>> them.
>>
>> If I just put
>>
>> @sa_userconf_maps = (
>> {
>> '.*' => 'sql:',
>> }
>> );
>>
>> Ø This is how it is configured on my end as well.
>>
>>
>>
>>
>> ...how does amavis know which SQL database is referred to? Or that it
>> should use "local.cf"?
>>
>>
>>
>> Ø SpamAssassin knows which SQL database to use based on what is
>> configured in your site’s /etc/spamassassin/local.cf file
>> (user_scores_dsn, user_awl_dsn, bayes_sql_dsn) as long as those are
>> configured – they will be referenced in the Amavis lookup routine.
>> As for which table – as long as you imported the “standard”
>> SpamAssassin SQL schema, the lookups will work properly.
>>
>>
>>
>> I have also tried something like
>>
>> @sa_userconf_maps = (
>> "/etc/spamassassin/local.cf"
>> );
>>
>> Ø Not valid – please use the parameter you specified above which
>> is known good on my machine as well.
>>
>>
>>
>>
>> It's not surprising to me that I haven't got it to work so far, given
>> that I simply can't find any clear instructions anywhere on the Web.
>>
>> Ø I hear ya, thanks to people on this list I was able to figure
>> it out – but it did take some time due to the lack of published
>> examples.
>>
>>
>> Any help would be very much appreciated! :)
>>
>> Ø One more configuration option you may need, is the
>> @sa_username_maps parameter. When SpamAssassin stores config
>> options, Bayesian tokens, and white/black list records – it does so
>> with the ‘username’ field. If you do not specify a username –
>> SpamAssassin will store all config options under the _/same/_ user
>> that invokes amavisd. So if you want each user to be able to store
>> individual preferences, awl/blacklist, and bayes – you will need to
>> define the @sa_username_maps. Using the following regular expression
>> – you will effectively map the RECIPIENT address (of the incoming
>> email to be scanned/parsed) as the SA ‘username’. Any
>> awl/autolearn/Bayesian/config options that are either retrieved from
>> the database, or written to it – will do so by the recipient address
>> of the email.
>>
>>
>>
>> (originally provided by Renato Botelho on 09/2/2011 in
>> amavis-users)
>>
>>
>>
>> @sa_username_maps = new_RE (
>>
>> [ qr'^([^@]+ at .*)'i => '${1}' ]
>>
>> );
>>
>>
>>
>> Again, only use the above regular expression if you want the SA
>> username field in the database to be the ‘user at domain.com’ in the
>> RCPT of the email message (which should be your mail user, anyways).
>> This would be useful in a mail server that houses more than one domain.
>>
>>
>> Thanks,
>> S.
>>
>>
>>
>> I hope that helps.
>>
>>
>>
>> Matt Goodman
>>
>
More information about the amavis-users
mailing list