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.

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.

A Look Back, A Look Ahead

Back in March I wrote about what I planned to focus on once the Horde 4 release process was complete. Well, here we are 9 months later and my personal roadmap has gotten a bit clouded in my head. So I thought with this being the end of the year, it is a good time to take stock and organize what I plan to focus on in the months ahead.

It's always nice to look back and see how well one stuck to plan, and to also take a minute to enjoy one's accomplishments. While there are still things left outstanding on that list, given how little time I have had lately to contribute, I am happy with what I managed to complete.

The work on Ansel that was necessary for a Horde 4 release, including a complete rewrite of the geotagging support, was completed. This has lead to lots of improvements in Horde's mapping library.  I also pushed out an alpha release of iPhoto and Aperture export plugins. These can be downloaded from Ansel's download page. It felt really good to get those things finally off my todo list and out the door. There are lots of enhancement requests

The Hermes Ajax interface, while not feature complete, is functional for day to day time entry. The main missing piece is the search/report functionality, and I hope to complete that in the next few months. Unfortunately, the mobile interface for Hermes has not yet been started. I'll hopefully get to it one of these days, but there seems to always be something else that has more importance for me. It likely won't be finished by the time Hermes for Horde 4 is released.

We had our annual Hackathon this past November in Boston, and LOTS of great work was done by all of our team memebers. Personally - in addition to eating Lucky Burgers and attempting to juggle - I mostly focused on completing the new Service_Weather library, adding basic tag navigation in Trean now that Chuck has migrated Trean away from shares to tags, and a buch of other small bug fixes. It was wonderful to see everybody in person again, a great time was had by all! I'm already itching for our next get together.

The ActiveSync library has also received a considerable amount of work in the last few months; Support for additional devices, improved recurrence series/exception support along with revamped timezone support to name a few. Going forward, I'm looking at implementing the minimum amount of email support that would be required to properly support meeting invitation requests and responses on the device. Once that is figured out, we'll see how much more work it would be for full email support (well, "full" support for what EAS 2.5 allows anyway). There is also talk about implementing a more recent version of the EAS protocol - at least 12.1 - which would give us the ability to not only sync more efficiently, but to sync with Apple's iCal application as well. Stay tuned!

Kronolith has a number of  missing features that haven't been implemented/ported from the traditional view yet. Some years ago, I added support for resource management. At the time, the AJAX view was not released, nor was it even fully functional, so the resource features were only added to the traditional view. These need to be ported to the dynamic view, along with better support for recurrence series editing.

All in all, another busy, but fun, year of Horde development is ahead!

Service_Weather for Developers Part 1

As promised in my last post, here is a basic run down on using Horde_Service_Weather in your own projects.

First, make sure you have the package installed. As of this writing, the latest available packaged release is 1.0.0RC2:

If you have not yet installed any Horde 4 packages, you will need to setup PEAR for Horde 4 (this is not meant to be a HOWTO on installing Horde. See the Horde install docs for more information).

// If you have not yet discovered Horde's PEAR channel:
pear channel-discover pear.horde.org

// Install the package:
pear install horde/Horde_Service_Weather

Next, we need to decide on the actual weather data provider. I recommend using Wunderground, as it is, by far, the most complete of the available choices. It requires registration for at least a free developer's account. Once you have your API key, you can create the weather object:

// Parameters for all driver types
// Note that below we use the global $injector to get the HttpClient and Cache instances.
// If not using the $injector, substitute your own instances in place of the $injector call.
$params = array(
    'http_client' => $injector->createInstance('Horde_Core_Factory_HttpClient')->create(),
    'cache' => $injector->getInstance('Horde_Cache'),
    'cache_lifetime' => 3600
);
$params['apikey'] = 'yourAPIKey';
$weather = new Horde_Service_Weather_WeatherUnderground($params);

Of course, if you choose to use, e.g., Google instead of Wunderground, just create the appropriate object:

// Google returns already localized strings,
// just pass it your language code.
$params['language'] = 'en';
$weather = new Horde_Service_Weather_Google($params);

Now we have our weather object, connected to the desired backend data provider. Let's fetch some weather information:

// Set the desired units
// Defaults to Horde_Service_Weather::UNITS_STANDARD
$weather->units = Horde_Service_Weather::UNITS_METRIC;

// Get current conditions.
// The location identifier can take a wide range of formats.
$conditions = $weather->getCurrentConditions('boston,ma');

// Unit labels
$units = $weather->getUnits();

// Basic condition description:
// e.g., "Sunny" or "Partly Cloudy" etc.
echo $conditions->condition;

// Current temp
echo $conditions->temp . $units['temp'];

Of course, lots of other properties are available. Check the documentation for details. Now, let's get a forecast:

// Get a 5 day forecast.
$forecast = $weather->getForecast('boston,ma', Horde_Service_Weather::FORECAST_5DAY);

// Each forecast result contains a collection of "Period" objects:
foreach ($forecast as $period) {
    echo 'Date: ' . (string)$period->date;  // Horde_Date object
    echo 'Hi: ' $period->high . $units['temp'];
    // Display other properties etc...
}

// If you want just a specific period:
$periodOne = $forecast->getForecastDay(0)
;
// Total snow accumulation for the day:
echo $periodOne->snow_total . $units['snow'];

Again, check the documentation for details on available properties.

In the next installment, we'll look at validating locations, searching locations and using a location autocompleter.

Have fun!