only white_black_list in mysql

[Toni Schornböck via amavis-users]

Hi.

I have an amavis-new installation on CentOS 6.4 with postfix 2.10.

my amavis version:
$ amavisd -V
amavisd-new-2.8.0 (20120630)


I want to use a mysql database for storing the softscoring information for
sender email adresses. I don't need a per user based scoring, because this
is just a spamfilter-gateway that does not know what users I have and
such. It is just there to filter spam that I get and I want to be able to
soft whitelist a few email addresses.
What I want to do is basically putting @score_sender_maps into mysql.
Currently I have all softscoring done in @score_sender_maps using
read_hash("/etc/amavisd/whitelist") and in "whitelist" I have lots of
email addresses and their softscore. I want to move this into a database
for easier maintenance.

I tried doing this using in amavisd.conf:

@lookup_sql_dsn = (
        ['DBI:mysql:database=amavis;host=127.0.0.1', 'amavis', '<passwd>'],
);
$sql_select_white_black_list = 'SELECT sum(`score`) from
`white_black_list` where `email` in (%k)';
$sql_select_policy = 'SELECT 0';

I have set sql_select_policy to this meaningless query because I don't
have a users table and I don't want one (I have no data to put there).
In mysqld.log I can see the queries:

204 Connect	amavis at localhost on amavis
204 Query	set autocommit=1
204 Query	SET NAMES 'utf8'
204 Query	SELECT 0

and then nothing more. The mail gets delivered but my white_black_list
data doesn't get applied.

My maillog looks clean.

Sep 12 14:00:44 mailtest amavis[8662]: (08662-01) Passed CLEAN
{RelayedInbound}, [x.x.x.x]:64565 [x.x.x.x] <x at x.com> -> <x at x.at>,
Queue-ID: 01EEEA072C, Message-ID: <x>, mail_id: C08g6rZWffx4, Hits:
-0.799, size: 1793, queued_as: 250 7E4E0283 Message accepted, transient
identifier was 185, dkim_sd=x, 10118 ms

I don't really understand why my query doesn't get called - so I must have
missed something. Maybe someone can give me a pointer in the right
direction? Is it even possible to just put the white_black_list into mysql
and let the rest be handled the default way? Is there another or better
way to achieve moving the score_sender_maps into a database?
Everything else is running fine, amavis filters spam correctly and mails
get delivered correctly.

Thanks in advance.
Toni Schornböck