Patchwork Psychology

It’s supposed to be easier this way, right? Doing virtual mail hosting with mbox files is just asking for it. My manager recommended dbmail over dovecot, and he’s a smart guy, so I’ll go with it. It can’t be that hard. Much to my chagrin, there isn’t a single recent howto on how to combine these pieces in a way that makes sense anymore. I’m having a hard time understanding how something so prolific as this has slipped through the cracks into open-source hell. Oh well…they pay me to find the answers, not to piss and moan about them.

Ok, so here’s the plan. My company’s customer email server is on a desktop-class machine (as is the mail server–and probably DNS servers, too–of any hosting company that got its start in the 90s), and I need to make a new mail server (which is actually a virtual machine, though that’s not really important) so we can shut down that desktop box for good. The new server will use Postfix and DBMail on MySQL. Our clients will be able to authenticate to the SMTP server, or do POP/IMAP before SMTP. The box will run openSuSE 10.3.

Step 1: DBMail.
Not a difficult step–in fact, this may be the easiest part of the whole process. Really, the hardest part of this was making SuSE compatable init.d scripts. I hate doing this, and so they’re pretty minimal.

Setting up DBMail is a cinch: copy the sample dbmail.cf to the config directory and update the DB connection parameters. I also set IMAP_BEFORE_SMTP to yes. Then start the servers and DBMail is up.

Step 2: Postfix.
This proved to be a real wriggling snake. Postfix is rock solid, and it wouldn’t budge. First it wouldn’t deliver mail locally. Then it wouldn’t deliver mail anywhere else. Then it wouldn’t deliver mail anywhere. Everyone was talking about the local_transport_maps setting. As far as I could tell, my local_transport_maps were fine. Then finally I figured it out. If postfix wasn’t going to try to deliver every message locally or every message remotely, it needed to know which domains it delivered locally for, as I used to configure with the vmaildomains (virtual_mailbox_domains directive) file.

Since Postfix was not doing the virtual domains but rather DBMail, the domains need to appear as local domains rather than virtual domains to Postfix. This is set up using the mydestination variable.

#from /etc/postfix/main.cf
mydestination = $myhostname, localhost.$mydomain, mysql:/etc/postfix/local_domains.cf

Creating the proper SQL statement was no walk in the park, either (sure, it’s simple enough, but imagine having little idea of just exactly what the expected output is). Postfix expects one one-column row back from the query based on the value that it puts in, a sort of key=value sort of thing. It’ll submit the domain in and expect to get the domain out if it’s a local destination. DBMail doesn’t keep a table with this, so I have to do some transformation from the aliases.

Since MySQL doesn’t let you use a column alias (i.e. SELECT field AS alias), I have to do the transformation twice: once in select column and once in the where section. Using substring and locate I strip off the username and @ from the email alias and the Postfix query (in %s). It’s a bit messy, but it works.

#/etc/postfix/local_domains.cf
hosts = unix:/var/lib/mysql/mysql.sock
user = dbmail
password = ******
dbname = dbmail
query = SELECT substring(alias FROM locate('@',alias)+1) FROM dbmail_aliases WHERE alias LIKE CONCAT('%%',substring('%s' FROM LOCATE('@','%s')+1)) LIMIT 1

With this in place, Postfix and DBMail are happily married. There is one problem yet: Authentication

Step 3: SASL
Using the usual config for getting this going based on the usual howtos isn’t terribly hard, though it can be trying. For the first two hours while beating on sasl2 I couldn’t figure out why it kept looking for the sasldb password file. Then I figured out that the mysql auxprop plugin hadn’t been installed. The OS had a package for it, at least. After installing it, I still had the same problem, if I remember correctly. I never did figure out what it was doing, but thankfully it stopped eventually and started using my database connection.

#/etc/sasl2/smtpd.conf
pwcheck_method: auxprop
auxprop_plugin: sql
mech_list: plain login cram-md5 digest-md5 ntlm
sql_engine: mysql
sql_hostnames: localhost
sql_user: dbmail
sql_passwd: ******
sql_database: dbmail
sql_select: SELECT passwd FROM dbmail_users WHERE userid = '%u@%r'

Step 4: Migration
I haven’t yet done the migration from the old server to this one–I’m still planning it. There are a few hurdles to jump in the process:

1. Importing the users
2. Importing the aliases
3. Importing the PASSWORDS!
4. Importing the mailboxes

The user and alias imports will be easy enough to do with scripts. The mailboxes can be part of that: DBMail ships with a python script that will copy messages out of a mbox or maildir format mailbox into a DBMail mailbox. But the passwords…

The passwords are currently stored in a htpasswd-generated passwd file, which is simply formatted username:crypt_pass\n. I’m in luck, right? DBMail supports plain, crypt, and md5 passwords. Oh but wait! The sasl2 sql auxprop plugin only supports plain passwords. I didn’t find that out until I put a crypt password in and enjoyed great IMAP service and no SMTP service. Apparently if you want to use a crypt password, you’ll be limited to the PLAIN mechanism (because the shared secret mechanisms require the unencrypted password) and you’ll have to do it with saslauthd through PAM.

So install the pam-devel package, download pam-mysql and compile it up. Then edit the PAM config file for smtp:

#/etc/pam.d/smtp
#%PAM-1.0
auth optional pam_mysql.so user=dbmail host=localhost passwd=****** db=dbmail table=dbmail_users usercolumn=userid passwdcolumn=passwd crypt=1
account required pam_mysql.so user=dbmail host=localhost passwd=****** db=dbmail table=dbmail_users usercolumn=userid passwdcolumn=passwd crypt=1

Oh yeah, the sasl config as well:

/etc/sasl2/smtpd.conf
pwcheck_method: saslauthd
mech_list: plain login

You have to make sure to start the saslauthd with the “-a pam” option. If you are authenticating with the full email address as the username, you’ll also need to specify the “-r” option which processes the full user@realm as the username. I learned that the hard way.

Anyway, at this point I’m up and running with pam-mysql. The only catch is that now I’m sending passwords in clear text over the wire. SSL is probably a good idea before this box is production.

So yeah, not as easy as some other solutions, but the payoff is good enough. DBMail makes maintenance easier (especially with DBMail Administrator, a CGI frontend) and performance better. I have no regrets in choosing it.

Here are my configuration files for the benefit of the world.

/etc/postfix/main.cf
biff = no
broken_sasl_auth_clients = yes
command_directory = /usr/sbin
config_directory = /etc/postfix
daemon_directory = /usr/lib/postfix
debug_peer_level = 9
debug_peer_list = 10.10.10.90
defer_transports =
disable_dns_lookups = no
disable_mime_output_conversion = no
html_directory = /usr/share/doc/packages/postfix/html
inet_interfaces = all
inet_protocols = all
local_recipient_maps = mysql:/etc/postfix/local_recipients.cf
mail_owner = postfix
mail_spool_directory = /var/mail
mailbox_size_limit = 0
mailbox_transport = dbmail-lmtp:[127.0.0.1]:24
mailq_path = /usr/bin/mailq
manpage_directory = /usr/share/man
masquerade_classes = envelope_sender, header_sender, header_recipient
masquerade_exceptions = root
message_size_limit = 10240000
mydestination = $myhostname, localhost.$mydomain, mysql:/etc/postfix/local_domains.cf
myhostname = mta-l-001.thissillydomain.com
mynetworks = 127.0.0.0/8, 10.0.0.0/8 192.168.0.0/16, mysql:/etc/postfix/relay.cf
mynetworks_style = subnet
newaliases_path = /usr/bin/newaliases
queue_directory = /var/spool/postfix
readme_directory = /usr/share/doc/packages/postfix/README_FILES
sample_directory = /usr/share/doc/packages/postfix/samples
sendmail_path = /usr/sbin/sendmail
setgid_group = maildrop
smtp_sasl_auth_enable = no
smtp_use_tls = yes
smtpd_client_restrictions =
smtpd_helo_required = no
smtpd_helo_restrictions =
smtpd_recipient_restrictions = permit_sasl_authenticated,permit_mynetworks,check_relay_domains,reject_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = $myhostname
smtpd_sender_restrictions = hash:/etc/postfix/access
smtpd_use_tls = no
strict_8bitmime = no
strict_rfc821_envelopes = no
unknown_local_recipient_reject_code = 550

/etc/postfix/local_recipients.cf (for local_transport_maps)
user = dbmail
password = ******
hosts = localhost
dbname = dbmail
table = dbmail_aliases
select_field = alias
where_field = alias

/etc/postfix/local_domains.cf (for mydestination)
hosts = unix:/var/lib/mysql/mysql.sock
user = dbmail
password = ******
dbname = dbmail
query = SELECT substring(alias FROM locate('@',alias)+1) FROM dbmail_aliases WHERE alias LIKE CONCAT('%%',substring('%s' FROM LOCATE('@','%s')+1)) LIMIT 1

/etc/postfix/relay.cf (for mynetworks/pop-before-smtp)
hosts = unix:/var/lib/mysql/mysql.sock
user = dbmail
password = ******
dbname = dbmail
table = dbmail_pbsp
select_field = ipnumber
where_field = ipnumber

/etc/postfix/master.cf
smtp inet n - n - - smtpd
pickup fifo n - n 60 1 pickup
cleanup unix n - n - 0 cleanup
qmgr fifo n - n 300 1 qmgr
rewrite unix - - n - - trivial-rewrite
bounce unix - - n - 0 bounce
defer unix - - n - 0 bounce
trace unix - - n - 0 bounce
verify unix - - n - 1 verify
flush unix n - n 1000? 0 flush
proxymap unix - - n - - proxymap
smtp unix - - n - - smtp
relay unix - - n - - smtp
-o fallback_relay=
showq unix n - n - - showq
error unix - - n - - error
discard unix - - n - - discard
local unix - n n - - local
virtual unix - n n - - virtual
lmtp unix - - n - - lmtp
anvil unix - - n - 1 anvil
scache unix - - n - 1 scache
maildrop unix - n n - - pipe
flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}
cyrus unix - n n - - pipe
user=cyrus argv=/usr/lib/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user}
uucp unix - n n - - pipe
flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)
ifmail unix - n n - - pipe
flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)
bsmtp unix - n n - - pipe
flags=Fq. user=foo argv=/usr/local/sbin/bsmtp -f $sender $nexthop $recipient
procmail unix - n n - - pipe
flags=R user=nobody argv=/usr/bin/procmail -t -m /etc/procmailrc ${sender} ${recipient}
retry unix - - n - - error
dbmail-lmtp unix - - n - - lmtp

/etc/sasl2/smtpd.conf
pwcheck_method: saslauthd
mech_list: plain login

/etc/pam.d/smtp
#%PAM-1.0
auth optional pam_mysql.so user=dbmail host=localhost passwd=****** db=dbmail table=dbmail_users usercolumn=userid passwdcolumn=passwd crypt=1
account required pam_mysql.so user=dbmail host=localhost passwd=****** db=dbmail table=dbmail_users usercolumn=userid passwdcolumn=passwd crypt=1

/etc/dbmail/dbmail.conf
[DBMAIL]
driver = mysql
authdriver = sql
host = localhost
sqlport =
sqlsocket = /var/lib/mysql/mysql.sock
user = dbmail
pass = ******
db = dbmail
table_prefix = dbmail_
encoding = utf8
default_msg_encoding = utf8
sendmail = /usr/sbin/sendmail
TRACE_SYSLOG = 3
TRACE_STDERR = 1
EFFECTIVE_USER = dbmail
EFFECTIVE_GROUP = dbmail
BINDIP = *
NCHILDREN = 2
MAXCHILDREN = 10
MINSPARECHILDREN = 2
MAXSPARECHILDREN = 4
MAXCONNECTS = 10000
MAX_ERRORS = 500
TIMEOUT = 300
login_timeout = 60
RESOLVE_IP = no
logfile = /var/log/dbmail.log
errorlog = /var/log/dbmail.err
pid_directory = /var/run
state_directory = /var/run
[SMTP]
[LMTP]
PORT = 24
[POP]
PORT = 110
POP_BEFORE_SMTP = yes
[IMAP]
PORT = 143
TIMEOUT = 4000
IMAP_BEFORE_SMTP = yes
[SIEVE]
PORT = 2000
[LDAP]
PORT = 389
VERSION = 3
HOSTNAME = ldap
BASE_DN = ou=People,dc=mydomain,dc=com
BIND_DN =
BIND_PW =
SCOPE = SubTree
USER_OBJECTCLASS = top,account,dbmailUser
FORW_OBJECTCLASS = top,account,dbmailForwardingAddress
CN_STRING = uid
FIELD_PASSWD = userPassword
FIELD_UID = uid
FIELD_NID = uidNumber
MIN_NID = 10000
MAX_NID = 15000
FIELD_CID = gidNumber
MIN_CID = 10000
MAX_CID = 15000
FIELD_MAIL = mail
FIELD_QUOTA = mailQuota
FIELD_FWDTARGET = mailForwardingAddress
[DELIVERY]
SIEVE = yes
SUBADDRESS = yes
SIEVE_VACATION = yes
SIEVE_NOTIFY = yes
SIEVE_DEBUG = no
AUTO_NOTIFY = no
AUTO_REPLY = no
suppress_duplicates = no

Good luck!