qmail/MySQL - configuration

Quick links: Patching qmail - Building a patched qmail - Configuring the database

Patching qmail

Follow the instructions relevant to your qmail package: regular qmail or the newer netqmail. If your patch program doesn't like the unified diff format I used, download GNU patch.

Patching netqmail

First extract a clean qmail source tree:

sh:# tar zxf netqmail-1.05.tar.gz

Apply the netqmail patches:

sh:# cd netqmail-1.05
./collate.sh

Next apply the MySQL patch:

sh:# patch -p0 < netqmail-mysql-1.1.9.patch

Patching qmail

First extract a clean qmail source tree:

sh:# tar zxf qmail-1.03.tar.gz

Next apply the patch:

sh:# cd qmail-1.03
sh:# patch -p0 < qmail-mysql-1.1.9.patch

Patching checkpassword

First extract a clean password source tree:

sh:# tar zxf checkpassword-0.90.tar.gz

Next apply the patch:

sh:# cd checkpassword-0.90
sh:# patch -p0 < checkpassword-mysql-2.0.1.patch

Building qmail

There are various options inside the patched Makefile which you can configure. The most important is the paths to your MySQL installations include and lib directories. By default, they're set to /opt/mysql (simply because that's where MySQL is on my machine). You'll have to change these locations if your MySQL doesn't live there.

You may also need to add a few linker options to get qmail to compile. Most FreeBSD users will need to add "-lm" to the $(MYSQL_LIBS) line and Solaris people will want "-lsocket -lnsl" if they get compilation errors related to sockets.

Uncomment "-lz" if you get errors about compress.

Uncomment any sections under OS_SPECIFIC if they apply to you. At time of writing, the only OS specific stuff anyone will need is the -DSOLARIS_STUPIDITY #define. Solaris users must uncomment this!

Finally you can tweak the logging options:

-DO_NOT_LOG_CONNECTS: suppresses logging of successful connections to the MySQL server (you probably don't want to see them)
-DO_NOT_LOG: suppresses all MySQL logging (you probably do want to see it)
-DEBUG_DEBUG_DEBUG: prints out a whole load of debugging messages (you probably don't want to see them)

Other sections of the Makefile are tweakable but unsupported.

Now you can build and install qmail as usual:

sh:# make setup check

checkpassword configuration is very similar. One thing to note is that you must make sure that conf-qmail (in the checkpassword source tree) contains the correct path to your qmail installation. If you install qmail in /opt/qmail, for instance, you should:

sh:# echo /opt/qmail > conf-qmail

Do this PRIOR to building checkpassword.

Configuring the database

The first thing to do is set up the database. You can use whatever schema you like, as long as you create the tables qmail will expect to find and include at least the (correctly named) fields it expects to be present. You can even edit mysql_queries.h and tweak the SQL queries if you don't like the names I used.

To reiterate: you can add your own fields, reorder fields or do whatever you like as long as you retain the ones qmail expects to find.

Download the default schema

Grant a user access to the database. For example:

mysql> grant select on qmail.* to user@localhost identified by 'password'

Finally, put a file called sqlserver in ~qmail/control. The file should include the following lines:

server localhost
login user
password password
db qmail

Change the parameters to suit your setup.

Two optional, mutually exclusive lines may be specified:

port 3309
socket /tmp/mysql.sock

Adding users

You must set up at least one system account to map the database's virtual users. Most of the documentation assumes that you will create the user popuser (with uid 800) in the group users (with gid 100).

Inside this account's home directory, you will create homes and maildirs for each of your virtual POP users. Use maildirmake to create the maildirs as you usually would.

Make sure popuser's home directory is world executable so qmail-local can stat the virtual users' homes. Virtual user directories (and their maildirs!) must be 0700 and owned by popuser:users.

The example below shows the setup for a virtual user dmp, assuming popuser's home directory is /mail:

drwx--x--x 5 popuser users    512 Jan 7 14:08 /mail
drwx------ 5 popuser users    512 Jan 7 14:08 /mail/dmp
drwx------ 5 popuser users    512 Jan 7 14:08 /mail/dmp/Maildir

Virtual users go into the mailbox table in MySQL.

mailbox table
username	uid	gid	home	password	password_type	suspended
dmp	800	100	/mail/dmp	flibble	Password	N

The example above shows the correct configuration for our imaginary user dmp. The uid (800) and gid (100) match the ownership of the home directory /mail/dmp.

Passwords and suspended users

The password, password_type and suspended fields are only significant for users logging in via POP or IMAP to read their mail. The password_type field specifies what type of data is stored under password.

If password_type is Password, the user's plaintext password is stored in the password field.

If password_type is Crypt-Password, the user's password is stored in crypt format.

If password_type is MySQL-Password, the user's password is stored in MySQL hashed password format. This is the recommended format.

A user whose suspended field is set to Y will always be refused login, although mail will still be delivered to the mailbox.

Real users

You are not restricted to handling virtual users in the database. As long as the uid and gid in the mailbox table match those of the user's home directory and maildir, qmail can handle existing system accounts too. One possible reason for doing this would be to protect a user's login password by setting up a different password for mail retrieval.

mailbox table
username	uid	gid	home	password	password_type	suspended
dmp	800	100	/mail/dmp	flibble	Password	N
iain	101	31337	/home/iain	5d2e19393cc5ef67	MySQL-Password	N

Here we see an entry for the real system user iain (uid 101, gid 31337), whose home directory is /home/iain.

drwx------ 5 iain    admin    512 Mar 9 21:36 /home/iain
drwx------ 5 iain    admin    512 Mar 9 18:52 /home/iain/Maildir

Home directories, Maildirs and mailboxes

mailbox was perhaps an unfortunate choice of name for this table. The all-important field is home, which specifies user home directories, not their mailboxes. Where the mail is eventually delivered is dependent on the contents of ~qmail/rc.

Virtual addresses

The virtual table controls virtual host addressing.

In a nutshell, qmail delivers mail for virtual_username@virtual_host to username (or possibly username-ext).

The most common configuration options are like this:

virtual table
username	ext	virtual_username	virtual_host
dmp		diane.p	tterson.net
iain		iain.p	tterson.net
iain	legacy	geocities	babasse.com
iain	@		iain.cx
hancocaj			ncock.net

In the example above, mail for diane.p@tterson.net is delivered to dmp. Mail for iain.p@tterson.net is delivered to iain. Mail for geocities@babasse.com is delivered to iain-legacy. Mail for xxx@iain.cx is delivered to iain-xxx for all values of xxx, and mail for xxx@ncock.net is delivered to hancocaj for all values of xxx.

When virtual_username is left blank, it functions as a catchall and matches anything in front of the @ sign that isn't explicitly matched by another rule. Setting ext to @ tells qmail to take whatever the value of virtual_username was and add it to the end of the destination username. Most people won't want to do this. The last line in the example table above is what you'll probably use most often.

Note that explicit matches override catchalls. In other words you could add an entry to the table viz:

virtual table
username	ext	virtual_username	virtual_host
hancocah		miss.h	ncock.net

Doing so would deliver mail for miss.h@ncock.net to the user hancocah while mail for xxx@ncock.net would still go to hancocaj for all other values of xxx.

Furthermore, if no catchall entry exists, mail will be bounced if there is no explicit match. In other words, with the table below, mail for bogus.p@tterson.net would bounce.

Forwarding virtual addresses off-site

Many people have asked how to configure qmail so that mail for a virtual address is forwarded somewhere else (for example to a user's Hotmail account) without passing through the system at all.

It IS possible. There are two ways of doing it. One is intuitively easier to understand but the code is not perfect and can cause mail loops. The other way takes a little more work but is more reliable.

Please read this FAQ entry for more details.

The rcpthosts table

Adding entries to virtual does not instruct qmail-smtpd to accept delivery for a domain. You still need entries in the rcpthosts list.

Rather than create a huge ~qmail/control/rcpthosts file with the names of all your virtual domains, you can add them to the rcpthosts table. This table and the flat file can co-exist peacefully.

rcpthosts table
host
tterson.net
babasse.com
iain.cx
ncock.net

Do not add domains to the ~qmail/control/locals file if you want to set them up as pure virtual domains.

Aliases and lists

The alias table allows you to specify aliases and mailing lists in a similar way to .qmail files. In general, mail for username is forwarded to alias_username or alias_username@alias_host if alias_host is not blank.

You can also "forward" to programs or explicitly named files or directories, just like you can with .qmail files. To do this, you must put either |, . or / in alias_username and the remainder of the delivery instruction in alias_host.

Sample alias table:

alias table
username	alias	alias_username	alias_host
dmp		dmp	nigelj.force9.co.uk
iain	test	/	home/iain/test/
iain		\|	preline procmail
njp	spam	.	/Spam/
alias	bogus	iain

Mail for dmp is forwarded to dmp@nigelj.force9.co.uk. Mail for iain is piped through preline. Mail for iain-test is delivered directly to the /home/iain/test/ maildir. Mail for njp-spam is delivered to the Spam maildir in njp's home directory.

Just as with standard qmail, you can use rules involving the alias user to catch mail for nonexistent accounts. In the example above we send mail for the unknown user bogus to iain.

This is the equivalent of adding iain to .qmail-bogus in the alias user's home directory.

Please note: You still need to set up usernames and maildirs for users you use in alias table entries, even if your alias rules send mail off-site.

They can be system users (listed in the passwd file) or virtual users (in the mailbox table) but they must exist somewhere.

Mailing lists

Creating mailing lists is as simple as adding multiple entries into the alias table:

alias table
username	alias	alias_username	alias_host
alias	customers	customer1	company1.com
alias	customers	customer2	company2.com.hk
alias	customers	customer3	company3.co.uk
alias	customers	customer4	company4.fr

Here, mail addressed to customers is forwarded to each of the for example destinations.

Delivering directly to a remote destination

If you don't want mail for a certain user to be delivered to his mailbox at all but rather be delivered to some remote destination, an entry in the alias table is NOT sufficient! You must still set up the user (either in the mailbox table or as a system user) and create his home directory with the correct ownership and permissions as well!

A note on dots and slashes

The examples above show how you can write mail to mbox files or to maildirs by splitting delivery instructions across the alias_username and alias_host fields. It follows that the entry below is effectively a no-op when the default destination is ./Maildir/

alias table
username	alias	alias_username	alias_host
username		.	/Maildir/

Problems?

Refer to the FAQ or troubleshooting pages if you are having trouble configuring the database.

Quick links: Patching qmail - Building a patched qmail - Configuring the database

Adding users - Virtual hosts - Aliases