mafflog

Update: This post is quite old and does not work out of the box with the current ISPMail tutorials. I posted an update here.

When it comes to mail servers, I really like the setup Christoph Haas describes in his Document Howto: ISP-style Email Server with Debian-Etch and Postfix 2.3. One thing I was missing on a server was the ability to automatically generate config files for fetchmail and sieve from the database in order to get mail from other servers and being able to apply server side filters on incoming mail. This howto is based on the mentioned tutorial.

The additional setup is quite simple: 2 more database tables hold the data for fetchmail and sieve rules and a set of PHP scripts called by cron every few minutes fetches the data and writes it into the appropriate config files. For fetchmail, a script creates a .fetchmailrc file in /home/vmail/. For sieve, another script creates a .dovecot.sieve config file for every user who got sieve rules in the database.

Edit dovecot configuration

To enable per-user sieve scripts, a virtual mail user has to have a home directory where dovecot/sieve can find the user-specific config file. This is given by the existing setup, but I didn’t want to write directly to the Maildir structure. So I moved the user’s mail in a subdirectory called – guess what – mail.

So the original setup looks like this: user home and mail location are the same directory

/home/vmail/%d/%n/

After editing I got this directory structure

home: /home/vmail/domains/%d/%n/
mail: /home/vmail/domains/%d/%n/mail/

You may notice, that I put all the mail structure into a subdirectory domains. That’s just an optional step I did for personal preference, so you can safely ignore it. Just take care when changing paths on your setup.

Edit /etc/dovecot/dovecot.conf and change the following entry:

mail_location = maildir:/home/vmail/domains/%d/%n/mail

Remember that if you already got existing mailboxes, you have to move them into the new subdirectories.

After a reload, dovecot does now look for a file named .dovecot.sieve in a user’s virtual home directory. You can try creating a sieve config file and sending a mail to that user to see if sieve works. Dovecot should also create a file named .dovecot.sievec and eventually .dovecot.sieve.err. If it doesn’t work, please make sure you loaded the cmusieve plugin in dovecot’s config. You can learn more on dovecot’s sieve implementation on the dovecot wiki.

Alter the database

To hold fetchmail and sieve records, it’s necessary to add two more tables to the mailserver database. I will also add two views which will make it easier to query the record sets.

First, let’s add the fetchmail table. The fields should be pretty straightforward.

CREATE TABLE `virtual_fetchmail` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
`options` varchar(50) NOT NULL,
`remoteserver` varchar(50) NOT NULL,
`remoteuser` varchar(50) NOT NULL,
`remotepass` varchar(50) NOT NULL,
PRIMARY KEY  (`id`),
FOREIGN KEY (user_id) REFERENCES virtual_users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

Please be aware that login credentials for external servers are stored in plain text in the database. This is OK for me, but might not be for you. So take care about passwords and access rights and think about a way of encrypting passwords stored in the database.

Same for the sieve table

CREATE TABLE `virtual_sieve` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
`rules` text NOT NULL,
PRIMARY KEY  (`id`),
FOREIGN KEY (user_id) REFERENCES virtual_users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

The mentioned views:

CREATE VIEW view_fetchmail AS
SELECT
virtual_fetchmail.id,
virtual_fetchmail.active,
virtual_fetchmail.remoteserver,
virtual_fetchmail.remoteuser,
virtual_fetchmail.remotepass,
virtual_fetchmail.options,
CONCAT(virtual_users.user, '@', virtual_domains.name) AS destination
FROM virtual_fetchmail
LEFT JOIN virtual_users ON(virtual_fetchmail.user_id = virtual_users.id)
LEFT JOIN virtual_domains ON(virtual_users.domain_id = virtual_domains.id);

CREATE VIEW view_sieve AS
SELECT
virtual_sieve.id,
virtual_sieve.active,
virtual_users.user,
virtual_domains.name AS domain,
virtual_sieve.rules
FROM virtual_sieve
LEFT JOIN virtual_users ON(virtual_sieve.user_id = virtual_users.id)
LEFT JOIN virtual_domains ON(virtual_users.domain_id = virtual_domains.id);

Install fetchmail

Easy step, thanks to apt :)

aptitude install fetchmail

Install scripts

As I mentioned in the introduction, I wrote a set of small PHP scripts which fetch the data from the database and write it into the config files. The scripts rely on PEAR::DB, so you need to have PHP and PEAR installed. If you don’t have done so yet, install them with apt:

aptitude install php5-cli php5-mysql php-pear php-db

Next install the scripts – I put them into a /home/vmail/mailserverscripts/. You can download them from my SVN repository or simply check them out with svn:

svn co http://svn.ailoo.net/dev/public/mailserverscripts/

Then, copy config.example.inc.php to config.inc.php and edit it according to your needs. There shouldn’t bee too much to change, just the database credentials, some default options and possibly the paths to config files.

It’s important to change file permissions as all scripts should run under the user vmail and nobody should be able to read the config file.

chown vmail.vmail /home/vmail/mailserverscripts -R
chmod 0600 /home/vmail/mailserverscripts/config.inc.php

Test the setup

Now that all is in place, try adding some records to the database and executing the scripts as user vmail:

su vmail
php /home/vmail/mailserverscripts/fetchmail.php
php /home/vmail/mailserverscripts/sieve.php

Then check if the config files .fetchmailrc in vmail’s home and .dovecot.sieve in the virtual mail user’s home were generated.

Running scripts as cronjob

To generate the config files automatically every few minutes, set up cronjobs for the user vmail. Edit the vmail’s crontab with crontab -e. My crontab looks like this:

*/5 * * * * php /home/vmail/mailserverscripts/fetchmail.php
*/5 * * * * php /home/vmail/mailserverscripts/sieve.php
*/2 * * * * /usr/bin/fetchmail > /dev/null 2>&1

Fetchmail and sieve config files are regenerated every 5 minutes, fetchmail looks for new mail on foreign servers every 2 minutes. This values depend on the amount of mail accounts and sieve rules you have to configure.

Conclusion

It’s a quite simple setup which does its work. However, this may not be useful for bigger installations, as the scripts regenerate the config files without checking for modifications. So if you got many rules to fetch, it would make sense to extend the scripts by some sort of caching. Another aspect is security: fetchmail passwords are stored in plain text in the database and in the .fetchmailrc file, so take care of setting the right file permissions. The sieve script loads the three extensions ["fileinto", "reject", "vacation"] by default, which may not be very efficient and could be replaced by some sort of mechanism which checks for the needed extensions.

Another thing you may notice: if you got sieve rules set for a user and delete all of them, on the next run the script will not operate on the user’s config and therefore will not clean the config file. This results in old rules remaining set until you create new rules for that user. A simple workaround for this bug is creating an empty rule and waiting until the script ran at least once, which will create an empty config file. This and other bugs/errors may be corrected in future versions.

Any ideas and suggestions are welcome :)

License for the scripts: GNU General Public License (GPL)