How to use @sa_userconf_maps for white-/blacklisting in Amavis 2.7.0

[Matt Goodman]

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
>