It works! This is the solution:
http://de.postfix.org/pipermail/amavis-users/2011-August/000708.html So it seems to be a bug in amavisd-new 2.7.0. Please note that with a .* setting in sa_userconf_maps, it doesn't seem to work. Give explicit domain names, with a dot in front. The sa_username_maps setting can be left as it is. Cheers, S. > Oh, now I think I might have found something. Amavis (not > spamassassin!?) complains that "no lookup tables [are] found". Then it > maps the sender and receiver to "undef", and it says that the white- and > blacklist entries do not match because of the "undef". > > But I don't know if that is actually the Spamassassin whitelist, or the > (non-existing) amavis whitelisting. > > Thanks. > > >> Check your init scripts. I'm sure one of those will have a startup command >> line. I believe you can override it by launching spamd with the -C >> /path/to/file.cf option. >> >> -----Original Message----- >> From: SB [mailto:kfsw...@yahoo.de] >> Sent: Sunday, October 16, 2011 1:05 AM >> To: Matt Goodman >> Subject: Re: How to use @sa_userconf_maps for white-/blacklisting in Amavis >> 2.7.0 >> >> Thanks for your time. >> Sorry, probably I was not clear in enough in the last email: The DB >> connection and all the entries (black-/whitelist) DO work whenever I call >> spamc manually -- so there should be no error with either (a) the DB >> connection parameters or (b) the DB entries themselves. It just doesn't work >> whenever Amavis is in control of spamassassin. >> >> So I suppose amavis is reading from the wrong spamassassin config file. >> Where can I figure out what *spamassassin* config file amavis is using? >> >> Thanks, >> S. >> >>> Can you print out the database record that has your blacklist entry? >>> >>> -----Original Message----- >>> From: SB [mailto:kfsw...@yahoo.de] >>> Sent: Saturday, October 15, 2011 12:02 PM >>> To: amavis-users@amavis.org; Matt Goodman >>> Subject: Re: How to use @sa_userconf_maps for white-/blacklisting in >>> Amavis 2.7.0 >>> >>> ?? I did – see below. >>> However, in Ubuntu, there is no single amavisd.conf, but it's splitted up, >>> so the relevant bits are somewhere in /etc/amavis/conf.d/... But that >>> shouldn't make a big difference (the sa_username_maps settings are >>> interpreted after all). >>> >>> Cheers, >>> S. >>> >>> >>> >>>> Show me your @sa_userconf_maps and your @sa_username_maps from >>>> /etc/amavisd.conf >>>> >>>> -----Original Message----- >>>> From: SB [mailto:kfsw...@yahoo.de] >>>> Sent: Saturday, October 15, 2011 10:27 AM >>>> To: amavis-users@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-emailaddr...@example.org" does not >>>> match Oct 15 22:08:38 myservername amavis[23564]: (23564-01) >>>> lookup_re("recipient-emailaddr...@example.org") matches key ".*", >>>> result="$GLOBAL" >>>> Oct 15 22:08:38 myservername amavis[23564]: (23564-01) lookup >>>> [sa_username] => true, "recipient-emailaddr...@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,REMOV >>>> E _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:kfsw...@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 m...@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'^([^@]+@.*)'i => '${1}' ] >>>>>> >>>>>> ); >>>>>> >>>>>> >>>>>> >>>>>> Again, only use the above regular expression if you want the SA >>>>>> username field in the database to be the ‘u...@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 >>>>>> >>>>> >>>> >>> >>
