This is currently in progress. (Not anymore, everything here has been running fine for me for a long time now) It is based on this howto. Here I detail the problems I had and how I fixed them. The implementation I'm aiming for is it bit more complex than the howto allows for. As well as having MySQL driven virtual users, I want mail to be delivered for shell users like normal, and for the virtual mail to be filtered so that something like spamassassin can be used.
Note that this was written in a sequence-of-discovery type way, so the solutions I find at one point may be superseded by something later on.
Problem: Postfix can't talk to the MySQL socket.
Solution: By default, parts of Postfix run chrooted. This needs to be changed. Solution found here. Other options I'm going to explore at some stage: having postfix talk to MySQL via the network, and having MySQL put a socket inside the postfix chroot.
Solution2: add this in the init.d/postfix script
start)
echo -n "Starting mail transport agent: Postfix"
if [ -e /var/spool/postfix/var/run/mysqld/mysqld.sock ]; then
rm /var/spool/postfix/var/run/mysqld/mysqld.sock
fi
mkdir -p /var/spool/postfix/var/run/mysqld
chown mysql /var/spool/postfix/var/run/mysqld
ln /var/run/mysqld/mysqld.sock /var/spool/postfix/var/run/mysqld/mysqld.sock
Problem: The users in the mailbox table aren't detected by Postfix.
Solution: Turns out you can't have mydomain and myhostname the same as anything in the virtual maps. So the hostname should be set to the actual machine name to prevent this. This has the side effect that you can't deliver to local users in the 'default' postfix way. Also note that setting mydestination to a domain that is the same as in the virtual maps, then it takes precidence over the virtual map, and you only get delivery to local users using the virtual delivery code.
Problem: Can't deliver to local users if they're not listed in the virtual map. Also, if they are added to the virtual maps it means that mail won't be delivered with their permissions.
Solution: In main.cf, put:
local_recipient_maps = proxy:unix:passwd.byname, $alias_maps,mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
to ensure that postfix knows the users exist. Then put:
fallback_transport = virtual
so that non-local users end up with mail being sent to the maildir given in the database.
Problem: Mail for the non-local users needs to be run through procmail, however we somehow need to pass the destination maildir information that is in the database to procmail so that it knows where to put it. (Note: running local mail through procmail is easy, by using
mailbox_command= /usr/bin/procmail -p /etc/procmailrc -a "$EXTENSION"
where /etc/procmailrc is a default procmail setup for local users. Also, it seems that setting virtual_transport = procmail doesn't work when fallback_transport = virtual. The mail doesn't seem to hit procmail. Besides, we'd still need to get the destination maildir information to it somehow.)
Solution: Procmail won't cut it for this. It will need building a custom ruleset to shunt the mail where it needs to go. Instead, I'm using maildrop. Maildrop is like procmail, but better. Most usefully, it reads (in Debian) /etc/courier/userdb.dat (generated from /etc/courier/userdb) to allow mappings of non-existant users to the place where their mail goes. In Debian, the maildrop package won't work, it doesn't know about the userdb system. Instead, you need to use courier-maildrop. Also, it is necessary to change the home directory of the user that will be running maildrop, in my case mail, to be somewhere that isn't world writable. To tell postfix to use maildrop, simply set fallback_transport = maildrop.
Problem: Mail passing through maildrop gets delivered to /home/mail/Maildir rather than the directory specified in the mail= line in userdb.
Solution: Unfortunately this appears to be impossible, or at least, I couldn't figure it out, so I made a compromise: postfix tells maildrop the recipient, and maildrop just uses that value to work out where to put the message, based on it's own rules. This means that the information in the database or userdb as to the destination maildir is ignored, and mail will always go to a location defined by the appropriate maildroprc rule.
In Postfix, maildrop is called by the following in master.cf:
maildrop unix - n n - - pipe
flags=DRhu user=mail argv=/usr/bin/maildrop /etc/maildroprc-virtual ${recipient}
where /etc/maildroprc-virtual is the configuration file that controls where the message ends up, based on the value of ${recipient}. In maildroprc-virtual this is:
DEFAULT="/var/spool/virtualmail/$1/"
additional filtering rules can be placed after this as needed. It may be possible to allow lookups to be done in the filter rules in order to control the destination of the message. This is something to look into later. Another thing is that we need to ensure that the destination maildir exists, otherwise maildrop gets quite unhappy. A nice way of doing this is to have maildrop create non-existing maildirs itself, that way we don't need any external intervention. That can be done with the following addition near the top of the filter rule:
# If the destination maildir doesn't exist, create it. `[ -d $DEFAULT ] || (maildirmake $DEFAULT && maildirmake -f Spam $DEFAULT)`
this also sets up a Spam folder, which will be used below.
Problem: The above may not work sometimes. If you get this error from postfix:
relay=maildrop, delay=1,status=deferred (temporary failure. Command output: /usr/bin/maildrop: Unable to create a dot-lock. )
it can indicate that the destination maildir doesn't exist.
Solution: A possible cause is that the SHELL environment isn't being set, so the backtick evaluation above doesn't happen. To fix, add this line to the top of maildroprc:
SHELL=/bin/bash
(Thanks to Daniele Palumbo for this pointer)
Problem: We want SpamAssassin to be run over all the messages that pass through this.
Solution: This is easily done with maildrop's filtering. Add the following to the maildroprc (or maildroprc-virtual):
if ( $SIZE < 26144 )
{
exception {
xfilter "/usr/bin/spamc"
}
}
if (/^X-Spam-Flag: *YES/)
{
exception {
to "$DEFAULT/.Spam/"
}
}
else
{
exception {
to "/$DEFAULT"
}
}
also ensure that the destination Spam folder does exist, using the maildirmake command. If this isn't the case, the message is delivered to the default maildir.
With this, the incoming mail side of things appears to be working fine. For the interested, my postconf -n output is:
$ postconf -n command_directory = /usr/sbin command_time_limit = 10000 config_directory = /etc/postfix debug_peer_list = 127.0.0.1 fallback_transport = maildrop inet_interfaces = all ipc_timeout = 13600 local_recipient_maps = proxy:unix:passwd.byname, $alias_maps, mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf mail_owner = postfix mail_spool_directory = /var/spool/mail/ mailbox_command = /usr/bin/procmail -p /etc/procmailrc -a "$EXTENSION" mailq_path = /usr/bin/mailq manpage_directory = /usr/man mydestination = kallisti.2y.net, localhost.$mydomain, kallisti.net.nz, kallisti.hopto.org, www.kallisti.net.nz myhostname = agrajag.kallisti.net.nz mynetworks = 192.168.0.0/16,127.0.0.0/8 myorigin = $mydomain newaliases_path = /usr/bin/newaliases queue_directory = /var/spool/postfix relay_domains = $mydestination sample_directory = /etc/postfix sendmail_path = /usr/sbin/sendmail setgid_group = postdrop smtpd_banner = $myhostname ESMTP $mail_name (Commodore Vic-20) soft_bounce = no unknown_local_recipient_reject_code = 550 virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf virtual_gid_maps = static:105 virtual_mailbox_base = /var/spool/virtualmail virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf virtual_mailbox_limit = 51200000 virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf virtual_minimum_uid = 100 virtual_transport = procmail virtual_uid_maps = static:104
Making Courier work with this setup was very smooth. The information in the howto is all that was needed. There are a few minor things that I had to change:
/etc/courier/authmysqlrc are set to 8, that of the mail user.
authmodulelist contains authpam authmysql
~/Maildir/, not /var/spool/mail/$USER/ like I initially intended. You can probably change this, but I couldn't work out how in a short time.
Having all this set up and working is nice, but you also need some way of controlling it. To this end, I wrote up a few PHP scripts that can be used to administer the system. They are fairly primitive, but should be totally functional. They are also likely to undergo a lot of tweaking in the next while, so I'd recommend accessing them via Subversion. The Subversion URL is https://www.kallisti.net.nz/svn/mailadmin (you'll likely want the trunk subdirectory of this). It can also be browsed in a pretty online format. Details on its usage are located in the readme file.