Glenn, > Running: > FreeBSD-8.0-RELEASE > amavisd-new-2.6.4_8,1 > squirrelmail-1.4.21 > amavisnewsql-0.8 > > [...] who said that they had a user set in their blacklist for a while, > and it never succeeds in flagging the email as spam. > [...] it made me go and check my own.
> I sent myself an email from that account on gmail.. here are the X-Spam > lines: > > X-Spam-Flag: NO > X-Spam-Score: -101.946 > X-Spam-Level: > X-Spam-Status: No, score=-101.946 tagged_above=-999 required=5 > tests=[AWL=0.245, BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, > DKIM_VALID_AU=-0.1, FREEMAIL_FROM=0.5, RCVD_IN_DNSWL_LOW=-0.7, > SPF_PASS=-0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, USER_IN_WHITELIST=-100] > autolearn=ham > > As you can see, it's saying the user is in the whitelist, The USER_IN_WHITELIST comes from SpamAssassin. It has nothing to do with amavisd black/whitelisting mechanism. > but it's in the blacklist. (c/p from the database:) > rid sid priority email wb > 3 0 9 rainb...@gmail.com B > > So I turned up log_level to 5, put an email address in my user > whitelist, and sent an email from that address, to the user (who owns > the whitelist in question)... Thanks for the log and the database (sent offlist). This explains. > I get the same results whether the sending email address is in the white > or black lists as stored by the amavisnewsql plugin for SM. Your following log entry shows how a table wblist connects a sender id (sid) with mailaddr.id from a table of senders with a recipient id (rid) of a recipient, i.e. the users.id, with a relation between them being the wblist.wb : > (01187-01) lookup_sql select: > SELECT wb FROM wblist JOIN mailaddr ON wblist.sid=mailaddr.id > WHERE wblist.rid=? AND mailaddr.email IN (?,?,?,?,?) > ORDER BY mailaddr.priority DESC But apparently you have a modified database schema, where you keep sender addresses directly in wblist.email (and not indirectly in mailaddr.email joined to wblist via sid). To accomodate such a schema change, you also need to adjust the SQL clause in $sql_select_white_black_list, which you have attempted to do in a later sample: > (02313-01) lookup_sql select: > SELECT wb FROM wblist JOIN users ON wblist.sid=users.id > WHERE wblist.rid=? AND users.email IN (?,?,?,?,?) > ORDER BY users.priority DESC but have done it the other way around: treating users.email as a sending address, ignoring the sending address you have in wblist.email, and insisting on wblist.sid=users.id which is never true because all your records have wblist.sid 0 and there is no record in users with users.id 0. I believe what you need is: $sql_select_white_black_list = 'SELECT wb FROM wblist'. ' WHERE wblist.rid=? AND wblist.email IN (%k)'. ' ORDER BY wblist.priority DESC'; and make sure sender addresses are kept in wblist.email and recipients are kept in table users. Mark ------------------------------------------------------------------------------ Lotusphere 2011 Register now for Lotusphere 2011 and learn how to connect the dots, take your collaborative environment to the next level, and enter the era of Social Business. http://p.sf.net/sfu/lotusphere-d2d _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user Please visit http://www.ijs.si/software/amavisd/ regularly For administrativa requests please send email to rainer at openantivirus dot org