Best way to start amavisd-snmp-subagent?

[Deeztek Support via amavis-users]

On 8/23/2013 10:01 AM, Benedict White wrote:
> -----Original Message-----
> From: Deeztek Support [mailto:support at deeztek.com]
> Sent: 23 August 2013 14:43
> To: Benedict White; amavis-users
> Subject: Re: Best way to start amavisd-snmp-subagent?
>> I certainly understand that. I can certainly try that route but I just need to make sure that the format I'm using in the white.lst and the black.lst files is correct. Namely, I read somewhere that a carriage return must be present at >the end of the files the .lst files. Also is it required for domain to be prefixed with "." like .domain.tld? :
>>
>> 50-user config:
>>
>> @whitelist_sender_maps=(read_hash("/etc/amavis/white.lst"));
>> @blacklist_sender_maps=(read_hash("/etc/amavis/black.lst"));
>>
>> white.lst file:
>>
>> domain.tld
>> whitelisted at somedomain.tld
>>
>> black.lst file:
>>
>> domain.tld
>> someoone at someotherdomain.tld
> I only use SpamAssassin white and blacklists (though as I am looking to develop MailZu I may also do sql ones) so can't comment on the syntax of the file.
>
> What I can say is that when I had a problem I couldn't find a fix for,  an extended log file led me to the problem
>
> Kind regards
>
> Benedict White
>
>
So I decided to give up on the static white.lst and black.lst files and 
I'm trying to get per-recipient black and white lists to work using 
mysql. So I commented out the following files in my 50-users file:

#@whitelist_sender_maps=(read_hash("/etc/amavis/white.lst"));
#@blacklist_sender_maps=(read_hash("/etc/amavis/black.lst"));

and I put the following entry in my 50-users file:

$sql_select_white_black_list = 'SELECT wb FROM wblist,mailaddr WHERE 
(wblist.rid=?) AND (wblist.sid=mailaddr.id) AND (mailaddr.email IN (%k)) 
ORDER BY mailaddr.priority DESC';

in the mailaddr table I put the following values:

id       priority      email
2        7                 recipient at mydomain.tld
1        7                 sender at outsidedomain.tld

in the wblist table I put the following values:

rid        sid        wb
2          1            W


I turned the log file to 5 in amavisd and send a message with the GTUBE 
string in it and it got blocked. I was expecting to pass. Here's the 
pertinent log:

-------------------------   START LOG ----------------------
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) lookup_sql " sender at outsidedomain.tld", query args: "7", 
"ARRAY(0xbf385e8)", "ARRAY(0xbf23950)", "ARRAY(0xbf37ef8)", 
"ARRAY(0xbf23740)", "ARRAY(0xbf34ed0)"
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) lookup_sql select: SELECT wb FROM wblist,mailaddr WHERE 
(wblist.rid=?) AND (wblist.sid=mailaddr.id) AND (mailaddr.email IN 
(?,?,?,?,?)) ORDER BY mailaddr.priority DESC
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) sql begin, nontransaction
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) sql: preparing and executing: SELECT wb FROM wblist,mailaddr 
WHERE (wblist.rid=?) AND (wblist.sid=mailaddr.id) AND (mailaddr.email IN 
(?,?,?,?,?)) ORDER BY mailaddr.priority DESC
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) lookup_sql, " sender at outsidedomain.tld" no match
Aug 23 14:33:58.803 smtp.deeztek.com /usr/sbin/amavisd-new[8482]: 
(08482-01) lookup_sql_field(wb), " sender at outsidedomain.tld" no matching 
records

------------------  END LOG ------------------------

Now the weird thing is that if I run the following query on my database 
it works fine:

SELECT wb FROM wblist,mailaddr WHERE (wblist.rid=2) AND 
(wblist.sid=mailaddr.id) AND (mailaddr.email IN 
("sender at outsidedomain.tld")) ORDER BY mailaddr.priority DESC

I get back wb=W

Maybe related or not, my query to select recipient policies from the 
recipients table works fine in amavis. Is there a bug maybe? I just 
don't understand why this is not working.