All negative I'm afraid. The file is said to be read in, but no traces of SQL lookups.
Now I really don't know what I can still do. > 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 >>>>> >>>> >>> >> >
