Hello,

I've patched maildrop to read filtering rules out of a MySQL database.

The patch is against maildrop-1.5.3 because that's the version that's in Debian unstable currently. I think that it would be fairly easy to port to a newer version; i brought this from 1.3.x to 1.5.3 fairly easily.

It assumes a vpopmail+qmail environment, but is applicable anywhere if the offending code is removed. The mdmysql code is used to look up users.

It does a lot of things (which is probably unsuitable for a single patch, but if I were to take the time to separate them out I might never get done):

*) Read the mysql database for mail filtering purposes. This replaces .mailfilter files.

*) Deliver to vpopmail virtual users. (replaces vdelivermail)

*) Read the /var/qmail/users/cdb file to figure out aliased domains. (us...@a.com should have us...@b.com's filtering rules if b.com is an alias for a.com).

*) Create maildirs on delivery.

*) Deliver to multiple sources based on SQL. Change a user's maildir simply by updating the database.

*) Deliver mail on our million+/day system :)

The goods (my changes are also released under GPLv2):

wget http://tacos.sus.mcgill.ca/~erik/maildrop-1.5.3-sql.patch

It needs the following to be untarred into the maildrop-1.5.3 directory: wget http://tacos.sus.mcgill.ca/~erik/cdb.tar.gz

You need to re-run aclocal; automake; autoconf in maildrop-1.5.3/ and maildrop-1.5.3/maildrop after patching.

A full tarball, already patched: wget http://tacos.sus.mcgill.ca/~erik/maildrop-1.5.3-sql.tar.gz

After patching, maildrop-1.5.3/mailfilter.sql contains the schema that I've used.

To compile:

./configure --enable-maildropmysql --enable-mysqlmailfilter make

Feel free to e-mail me <er...@midmaine.com> with any questions, metapatches, suggestions, optimizations, etc.

And now for how it works:

A rule is composed of several conditions and several actions. The conditions are ANDed together; if they are all satisfied then the actions will happen in order. At the moment, a condition can only be a regex-match. Many rules per user can be specified, and will happen in decreasing order of 'priority'.

I think it's easiest to demonstrate with an example:

### first rule: deliver to Maildir. lowest priority = happens last # a condition number of -1 means that the rule is always applied insert into mailfilter (user, domain, condition, action, priority) values ('erik', 'midmaine.com', -1, 0, 0);

# 'deliver' is like dotmailfilter's cc. You can also say 'forward' # to mean | $SENDMAIL -f '$SENDER' $(value_column) insert into actions (id, position, type, value) values (0, 0, 'deliver', './Maildir/');

# a stop action is necessary to stop control from spilling to more rules insert into actions (id, position, type, value) values (0, 1, 'stop', NULL);

### second rule: deliver to a Quarantine folder on regex match insert into mailfilter (user, domain, condition, action, priority) values ('erik', 'midmaine.com', 1, 1, 5);

# two conditions with the same ID will be ANDed together insert into conditions (id, position, negate, header, value) values (1, 0, 'false', 'From', 'buys...@junkmailer.com');

insert into conditions (id, position, negate, header, value) values (1, 1, 'false', 'Subject', 'FREE MONEY');

insert into actions (id, position, type, value) values (1, 0, 'deliver', './Maildir/.Quarantine/');

insert into actions (id, position, type, value) values (1, 1, 'stop', NULL);

Additionally, the 'users' table requires a 'nas' column. This is mapped in the 'nasgroups' table (as column 'name') to several 'nasid's. maildrop-sql does "select directory from mailstores where nasid='$nasid'" to see where it should deliver to. It will deliver to all such directories for the name in the user's 'nas' column. We use this to do hot-backups; all mails are delivered twice and if one of the NFS boxes were to die the mail is saved on the second.

Hope it's useful (it has been for me!);

Erik