Setting up per-recipient whitelists for multiple domains?

[forkie]

Hi all,

I have a LAMP mail server running Amavisd/Spamassisin/Postfix/Dovecot.

I manage email hosting for a few small businesses, but we are running into issues with Amavisd and/or SpamAssassin blocking legitimate email (false positives).

I want to give my users a web front end (via PHP) where they can simply add an email address from a trusted sender, and my PHP app can update the necessary config files to white-list this email address for the recipient.

I have been into the amavisd.conf file and looked at the documentation and file under the "ENVELOPE SENDER SOFT-WHITELISTING / SOFT-BLACKLISTING" section.

However, if I manually add every single white-listed email to this file it could end up quite large.

Is there anyway I can store per-recipient white/black lists in a separate(s) file that Amavisd and/or SpamAssassin reads?

Is this the best approach?

Do I need to restart Amavisd/SpamAssassin/Postfix after each change?

I'm just concerned about writting a PHP script that directly modifies these main Amavisd/SpamAssassin configuration files on the fly.

Many thanks for any assistance.

Cheers, Jason.

[mr88talent]

Any static files that you make require that amavisd-new is reloaded to see changes. You can set up hard black/white lists that read external files by using something something like:

Code: Select all

$per_recip_blacklist_sender_lookup_tables = {
  '.example.com'    => read_hash("$MYHOME/example_blacklist"),
  '.example2.com'    => read_hash("$MYHOME/example2_blacklist"),
};

$per_recip_whitelist_sender_lookup_tables = {
  'fred@example.com => read_hash("$MYHOME/fred_example_whitelist"),
  '.example.com'    => read_hash("$MYHOME/example_whitelist"),
  '.example2.com'    => read_hash("$MYHOME/example2_whitelist"),
};

you would simply put email addresses or domains in the files,e.g:
mike@example.net
.chrysler.com

In the blacklist sample above I show entire domains. If you wanted to be more granular than an entire domain matching (in other words each recipient is listed in the table) then you would still have to edit amavisd.conf each time you wanted to add someone new. If a file was missing (for example fred_example_whitelist) then amavisd-new will fail to start.

Really the best way is to use SQL so that updates to the database are read dynamically by amavisd-new. To do this however requires an understanding of the amavisd-new SQL schema and you need some method of entering changes into the SQL database. In a Howto I wrote where Dovecot is also used, I set up Squirrellmail, a patched version of amavisnewsql and a customized amavisd-new SQL database schema (which uses a mix of the amavisnewsql schema and the amavisd-new schema). Individuals can log into squirrellmail and modify not only their white/black lists but also their spam thresholds. They also have to optional ability to manage their quarantines using MailZu. It is not a simple task to set it up.

You can see my Howto here http://www200.pair.com/mecham/spam/virtual2.html , but it is designed to be used as a whole and taking pieces of it out of context may result in problems.

[forkie]

Really the best way is to use SQL so that updates to the database are read dynamically by amavisd-new......you need some method of entering changes into the SQL database.

This is the most ideal solution for me.

I'm a LAMP developer, so developing a quick admin front end in PHP that manipulates an MySQL database is a walk in the park for me.

To do this however requires an understanding of the amavisd-new SQL schema...

Any idea of where that is??

Thanks, Jason.

[mr88talent]

http://www.ijs.si/software/amavisd/README.sql.txt
http://www.ijs.si/software/amavisd/README.sql-mysql

There are two parts to the schema, basically a read only part and a read/write part. For w/b you would only need to configure the read only part. The amavisd-new developer has normalized the w/b table:

Code: Select all

CREATE TABLE wblist (
  rid        integer unsigned NOT NULL,  -- recipient: users.id
  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
  PRIMARY KEY (rid,sid)
);

but I don't, and instead use:

Code: Select all

CREATE TABLE wblist (
  rid        integer unsigned NOT NULL,  -- recipient: users.id
  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
  priority   integer      NOT NULL DEFAULT '7',
  email      varbinary(255) NOT NULL default '',
  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
  PRIMARY KEY (rid,email) -- amavisnewsql specific, normally it's (rid,sid)
);

with this change in amavisd.conf:

Code: Select all

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

whereas the amavisd-new developer would have used:

Code: Select all

#   $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';

You need to have some understanding that each user is assigned a policy, so at least one policy needs to be created. If you don't want entries in the policy table to affect your current static settings, then setting particular fields to NULL will allow the query to "drop down through" to the static settings. See README.lookups (notably SQL LOOKUPS)
http://www.ijs.si/software/amavisd/#doc and you will discover that amavisd-new queries the SQL table before it queries static entries and "first match wins". It's also important to understand how users.priority affects the query. This is the default select clause:

# $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';

Read my brief explanation of Amavisd-new lookups http://www200.pair.com/mecham/spam/virtual2p2.html