Hello (reposted to vchkpw list because it's very useful in the environment of
vpopmail);

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. ([EMAIL PROTECTED] should have [EMAIL PROTECTED]'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 <[EMAIL PROTECTED]> 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', '[EMAIL PROTECTED]');

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

-- 
Erik Bourget
Mid-Maine Communications


Reply via email to