Migrating vpopmail database to Postfix + mysql

Submitted by admin on Mon, 28/11/2011 - 11:55pm

Recently I've had the (pleasure?) of beginning a migration from a 9 year old qmailrocks vpopmail installation to a more modern Dovecot/postfix installation. There are a number of little things that exist in this world to create headaches, but this quick blog post is really just to share a script I think other people will find useful.

As a quick overview, our migration is taking a few steps:

  1. Set up postfix to accept and route mail to old addresses using transport map
  2. Set up postfix to accept SMTP authentication on existing user tables
  3. Set up dovecot to provide POP3 and IMAP access to mailboxes
  4. Move Maildir mailboxes to new server
  5. Update DNS records so that everyone starts using the new box/system

Postfix, when coupled with auxprop and the SQL module provides a very extensible authentication mechanism. But we simply wanted to be able to continue to do password lookups, as we had been doing since time immemorial.

So - without further ado, here is a love bit of code to help with the migration process.

<?php

$query = "Select domain from dir_control where cur_users >0";

$dbh = new PDO('mysql:host=localhost;dbname=vpopmail', 'root', 'secret');
$dbhp = new PDO('mysql:host=localhost;dbname=postfix', 'root', 'secret');

$sth = $dbh->prepare($query);
$sth->execute();

$insert = "Insert into users (username, domain, password, directory) values (:username, :domain, :password, :directory)";
$pfs = $dbhp->prepare($insert);

while ($row = $sth->fetchObject()) {
$domain = $row->domain;
$domain = preg_replace('/\./', "_", $domain);
$usersQuery = "Select pw_name, pw_clear_password, pw_dir from ".$domain;
$usersStatement = $dbh->prepare($usersQuery);
$usersStatement->execute();
echo $usersQuery."\n";
while ($row=$usersStatement->fetchObject()) {
if ($row->pw_clear_password=="") continue;
$pfs->bindParam('username', $row->pw_name, PDO::PARAM_STR);
$pfs->bindParam('domain', $domain, PDO::PARAM_STR);
$pfs->bindParam('password', $row->pw_clear_password, PDO::PARAM_STR);
$pfs->bindParam('directory', $row->pw_dir, PDO::PARAM_STR);
$pfs->execute();
}
}

And a word for those who read this and are in the know:
It is worth knowing that for historical reasons passwords were stored in clear text in the old system. By default, Cyrus SASL expects this to be the case. We are evaluating what it would mean to re-encrypt these passwords in a format that Cyrus understands so as to provide "better" security - but keep in mind that unless you use SSL on your IMAP or POP connections, your passwords most likely travel in plain text anyway.

It's also worth noting that we haven't factored how a solution like Postfixadmin might play into this for user management, and we do intend to do this. If and when we do we will post a modified version of this script that creates the data in a suitable format for it.