amavis minimal db config for whitelist/blacklist

[Dino Edwards]

This is what I have for the wblist lookup:

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


This is for the policy lookup:

$sql_select_policy = 'SELECT *, users.id FROM users,policy'.
' WHERE (users.policy_id=policy.id) AND (users.email IN (%k))';
' ORDER BY users.priority DESC';

And of course your   @lookup_sql_dsn  you posted below. I'm using MySQL so the queries might be a bit different. 



-----Original Message-----
From: amavis-users [mailto:amavis-users-bounces+dino.edwards=mydirectmail.net at amavis.org] On Behalf Of mabi
Sent: Thursday, April 11, 2019 10:16 AM
To: amavis-users at amavis.org
Subject: amavis minimal db config for whitelist/blacklist

Hello,

I am using amavis with spamassassin and clamav for checking incoming mails. At the moment I am not using any database as I don't use any user specific settings but now I would like to enable the whitelist/blacklist feature of amavis so that the users can have their own whitelist/blacklist.

If I understand amavis sql documentation correctly all I need for that purpose is a lookup database with the mailaddr, users, wblist and policy tables as well as the following config:

  @lookup_sql_dsn =
   ([ 'DBI:Pg:database=mail_prefs', 'username', 'passowrd' ]);

is that it? or am I maybe missing something?

I will be using PostgreSQL as database and would like to keep the config minimal. For example I don't need custom policies just plain default.

Regards,
Mabi