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