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,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: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 >>> >> >
