{ Hi! I'm Mike }
I'm a core developer with The Horde Project and a founding parter of Horde LLC - the company behind the world's most flexible groupware platform. This is my personal blog full of random thoughts about development and life in general.
May 12, 2012

Shared SQL Authentication with Horde and Dovecot Part 2

In part 1 of this series, we saw how to configure Dovecot to use a simple SQL table as the user and passwd database. We also saw that it was easy to use the existing shadow passwords. This part will focus on setting up Postfix to use Dovecot's user information to determine where to deliver incoming mail, and finally, how to configure Horde to authenticate against the same data. This will also allow Horde to actually manage your mail users.

Basically, it requires just a few changes in main.cf:

# Since we are using ONLY virtual domain accounts, mydestination should be locahost.
# The domain will be handled by the virtual configuration.
mydestination = localhost


# Tell postfix where to find the virtual mailboxes:
virtual_mailbox_base = /var/vmail

# Tell it what domains are virtual (we only have one so no need for a map)
virtual_mailbox_domains = example.com

# Tell Postfix where to find user/mailbox map. In this case, we point to a confguration file
# for a mysql based map.
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf

# The same uid settings as in Dovecot:
virtual_minimum_uid = 150
virtual_uid_maps = static:150
virtual_gid_maps = static:8

# Also, be sure you have this to tell Postfix where to find dovecot's authentication socket.
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth

Now, for the mysql_virtual_mailbox_maps.cf file:

user = vmail
password = dbpassword
hosts = 127.0.0.1
dbname = mail

# Again, since we are only hosting a single domain, we can hard code it
# in the query for simplicity. %u is the user id (username) %s is the incoming email
# address (username@example.com). Ending it in a '/' signifies that it is Maildir format.
query = SELECT 'example.com/%u/' FROM mailbox WHERE uid ='%s'

That's all there is to it. Postfix will now deliver incoming email to the appropriate user's inbox.

At this point, we have a working email server using SQL auth. Now, let's get Horde configured to use it also. For this, you want to head over to Horde's adminstration UI and select the main Horde configuration. Select the "Auth" tab. From here, you want to select the "SQL Authentication with custom queries" driver. You will then be presented with fields to fill out for both connecting to the database containing the data and for entering the various queries. For this example, we are using UNIX sockets to connect to the database. The following is the resulting section of the conf.php file. You can use the array keys to determine what fields they go in on the administrative UI.

$conf['auth']['params']['socket'] = '/var/run/mysqld/mysqld.sock';
$conf['auth']['params']['protocol'] = 'unix';
$conf['auth']['params']['username'] = 'vmail';
$conf['auth']['params']['password'] = 'dbpasswd';
$conf['auth']['params']['database'] = 'mail';
$conf['auth']['params']['query_auth'] = 'SELECT * FROM mailbox WHERE uid = \L AND pwd = \P';
$conf['auth']['params']['query_add'] = 'INSERT INTO mailbox (uid,pwd) VALUES (\L, \P)';
$conf['auth']['params']['query_getpw'] = 'SELECT pwd FROM mailbox WHERE uid = \L';
$conf['auth']['params']['query_update'] = 'UPDATE mailbox SET uid = \L, pwd = \P WHERE uid = \O';
$conf['auth']['params']['query_resetpassword'] = 'UPDATE mailbox SET pwd = \P WHERE uid = \L';
$conf['auth']['params']['query_remove'] = 'DELETE FROM mailbox WHERE uid = \L';
$conf['auth']['params']['query_list'] = 'SELECT uid FROM mailbox';
$conf['auth']['params']['query_exists'] = 'SELECT 1 FROM mailbox WHERE uid = \L';
$conf['auth']['params']['encryption'] = 'crypt-sha512';
$conf['auth']['params']['show_encryption'] = false;

There are a few things to take note of. First, as mentioned in the UI, \P \L and \O are (respectively) the already encrypted password, the username, and the old username. Since we are using existing shadow passwords, the encryption is set to crypt-sha512. This is why we need both an authentication query and a password query - because we need to load the password first to get the salt so we can verify the user provided password. Also, when the expansions are made, they are already quoted, so do not enclose the \P, \L, or \O in quotes when entering the queries.

The final part of this is to change IMP to use horde's authentication data - in imp/config/backends.local.php:

<?php
$servers['imap']['hordeauth'] = true;

That's it. We now have a fully functional mail server, with Horde able to add/remove/edit the mail accounts - while the end users were able to continue to use their existing passwords. As an added bonus, it is now trivial to setup the Horde application, Passwd to allow users to change their passwords.