qmail/MySQL - overview

What it does

These patches allow you to set up virtual POP3 mailboxes (or indeed maildirs) for your customers. All the relevant information needed to create the accounts, ie username, password and home directory, is stored in a MySQL database, thus eliminating the need to create real system users and profiting from the speed that MySQL gives you.

MySQL authentication is used in precedence over system files (/etc/passwd for username/password pairs and ~qmail/control/xxx for delivery instructions) to determine:

In all these cases, qmail will signal a temporary error (thus allowing for further attempts in the future) if it cannot establish a connection with the MySQL server and fall back to system files if no matches were found in the database. This allows you, for example, to have a separate POP3 and login passwords.

How it does it

There follows a discussion of how the various components of the qmail system function under the patched configuration. Consider as an example an incoming SMTP connection which will deliver mail to example@iain.cx. It will be helpful to know that envnoathost for iain.cx is babasse.com. By the way, if you don't understand all of this, don't worry about it. I would recommend you find out though...

qmail-smtpd

Is the connecting client allowed to relay? assume no
What is the domain name it wishes to deliver to? iain.cx
Is iain.cx in the rcpthosts table? yes
Then accept delivery.

If iain.cx were not in the rcpthosts table, ~qmail/control/rcpthosts would be checked as usual.

qmail-send

qmail-send needs to determine if an address is local or remote. It first checks its ~qmail/control/locals file to see if iain.cx is a local domain. Assume so. In fact iain.cx is not in my locals file...

Does the user example exist in the mailbox table?
  if so, mark the mail as local and exit
  if not, check if example@iain.cx is in the virtual table.
    if so, mark the mail as local and exit
    if not, rewrite the address by replacing the given domain with envnoathost and continue.

Because qmail-send recognised iain.cx as local, it has now rewritten the recipient address as example@babasse.com (which is my envnoathost).

Does the user example@babasse.com exist in the virtual table? yes
Then mark the mail as local and deliver to the owner of the virtual domain (iain@babasse.com).

If no record could be found in the virtual table, qmail-send would try the ~qmail/control/virtualdomains file before giving up on rewriting the recipient. It would still mark the mail as local: it's just likely that qmail-local will bounce it later on.

You will recall I stated that iain.cx is not really in my locals file. What would actually happen is qmail-send would skip the check for the user called example and go straight to the example@iain.cx virtual domain query. If it didn't get any matches either there or in the ~qmail/control/virtualdomains file it would class the mail as remote. Note that if no match is returned for virtual_user@virtual_host the virtual query will try ""@virtual_host, thus allowing you to set up a catchall virtual domain owner.

qmail-getpw

Now our example email has been given a Delivered-To: iain@babasse.com header. qmail-getpw will now look for the user iain in the mailbox table and get my UID, GID and home directory. If I'm not in the mailbox it will look for me in /etc/passwd before returning alias-iain. Note that qmail-getpw will die if its MySQL server has gone away, rather than risk incorrectly pronouncing that a user doesn't exist.

qmail-local

The modified qmail-local simulates reading a .qmail file by querying all rows from the alias table which match the given username and extension. If it finds no matches it will check for a blank extension, allowing you to set up a catchall alias. You can put anything you like into the alias table, including mailbox, maildir or program delivery instructions. See the configuration section for more details.

checkpassword

checkpassword checks the mailbox table before reading /etc/passwd to authenticate users. You can choose between using MySQL passwords (the default), UNIX crypt passwords and plaintext passwords. Optionally, checkpassword can record all attempts to log in through splogger. In this case (on by default) it will tell you who logged in and who didn't - and the reason for their failed login (unknown username, incorrect password, system error). Like the other patched files, this checkpassword can be asked to record the SQL queries it sends in its logs. However it will not record the password it is verifying...