{ 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 =
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:

$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.

May 12, 2012

Shared SQL Authentication with Horde and Dovecot Part 1

I recently had the opportunity to reconfigure a mail server for a client. This client wanted an existing Dovecot/Postfix setup to be moved to use Virtual Mailbox Domains and SQL authentication. The existing setup was a typical out of the box install utilizing system accounts as the mail user base. The driving factor in this was to be able to use Horde to not only authenticate against the mail server, but to also have Horde be able to manage mailbox users.

The requirements for this were pretty simple, so these steps are fairly simplistic. For starters, this server is only hosting a single domain, so there is no need to track different domains in Dovecot's virtual setup. Also, since these servers were already setup and functional, this article will skip the steps for things like setting up TLS and the like. I have done setups like this before, but not since the Horde 3 days, so it might be helpful for others to see what needed to be done.

This article will show how to setup the Dovecot portion of things. The next article will show Postfix, followed by Horde.

The first thing I did, since this was an existing mail system was to install Horde 4 to be sure that any requirements for Horde were already met on server. Specifically, that Horde would have no problems communicating with the IMAP server. Next, it was time to configure Dovecot to use SQL maps for the mailboxes. There are a lot of HOWTOs out there about setting up Dovecot from scratch to do this, and most of those are overly complex for what was needed in this case. First, I created a mail database. Unlike all the other tutorials out there, I only had to create a single table in the database. Since we are only hosting a single domain, and the location of the user mailboxes will be easily calculatable, this table only holds usernames and passwords:

 CREATE TABLE `mailbox` (
  `uid` varchar(255) NOT NULL DEFAULT '',
  `pwd` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`)

Next, it's time to configure Dovecot. The things that needed to be changed in /etc/dovecot.conf:

# Put mailboxes in /var/vmail/{domain}/{username}
mail_location = maildir:/var/vmail/%d/%u

# Limit the uid/gid that can login
first_valid_uid = 150
last_valid_uid = 150

auth default {
# .
# .
  passdb sql {
    # Location of the SQL configuration
    args = /etc/dovecot/dovecot-sql.conf
  userdb sql {
    args = /etc/dovecot/dovecot-sql.conf

  # It's possible to export the authentication interface to other programs:
  socket listen {
    master {
      # Master socket provides access to userdb information. It's typically
      # used to give Dovecot's local delivery agent access to userdb so it
      # can find mailbox locations.
      path = /var/run/dovecot/auth-master
      mode = 0600
      # Default user/group is the one who started dovecot-auth (root)
      user = vmail
      group = mail
    client {
      # The client socket is generally safe to export to everyone. Typical use
      # is to export it to your SMTP server so it can do SMTP AUTH lookups
      # using it.
      #path = /var/run/dovecot/auth-client
      path = /var/spool/postfix/private/auth
      mode = 0660
      user = postfix
      group = postfix

These changes tell Dovecot where to find the configuration to use SQL for the user and passwd database, and to export an authentication socket that Postfix can use. We will take care of both of these things later. First, we need to create the vmail user that we told the authentication socket to use and give it the required uid that we specifed. While we are at it, lets also create the directory to hold the virtual mailboxes.

useradd -r -u 150 -g mail -d /var/vmail -s /sbin/nologin -c “Virtual mailbox” vmail
mkdir /var/vmail
chmod 770 /var/vmail/
chown vmail:mail /var/vmail/

Now for /etc/dovecot-sql.conf On some distros, this file will already exist. We just need to tweak it for our situation.

#Database driver
driver = mysql

# Connect string for the database containing the mailbox table.
connect = host=localhost dbname=mail user=vmail password=thedbpasswd

# Since we want to migrate the existing users over from system accounts
# using shadow passwords, we use the CRYPT function.
default_pass_scheme = CRYPT

# The query needed to get the user/password %n contains only the user part of user@example.com
password_query = SELECT uid as user, pwd as password FROM mailbox WHERE uid = '%n'

# The user query. Since we are only hosting a single domain, it can be hardcoded here.
# This simplifies the DB table and queries. Notice we also always return a static uid and gid
# that match the vmail user we created. This causes the vmail system user to be the user 
# used to read the mailbox data.
user_query = SELECT '/var/vmail/example.com/%n' as home, 'maildir:/var/vmail/example.com/%n' as mail, 150 as uid, 8 as gid FROM mailbox WHERE uid = '%n'

Now, remember that we are moving existing Maildir accounts that currently use shadow passwords. This client's system only had a few existing accounts, so I just manually added the entries into the mailbox table we created above. The passwords were just copy/pasted from the system's shadow file:

INSERT INTO mailbox (uid, pwd) VALUES('userone', '$6$xxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxx');

Take note of the password. It's a SHA-512 encrypted password (as indicated by the $6$). The salt is contained in the string itself. This will be important to know when configuring Horde later.

Next, now that the user accounts are setup in the table, we can copy any existing mailboxes over to the new location. In this case, we have Maildir files located in ~/Maildir. This is fairly simple, copy the directory and change ownership:

cp -r ~/userone/Maildir /var/vmail/example.com/userone
cd /var/vmail/example.com
chown -R vmail:mail userone

At this point, the users can now access their mailboxes just as before. Nothing will look different from the user's point of view. In the next part, we will configure Postfix to know where to deliver incoming mail.