Author: Matthew J. Salerno

Updated: 14/07/05

I have a large Qmail/Vpopmail server with many domains and users (stored in mysql) and I wrote a few scripts to handle the migration. None of the scripts do anything more than output sql that you can review before you copy/paste and execute. They are all in Perl and work with my current Postfix/MailScanner/DBMail configuration that includes support for domain aliasing build into DBmail: http://forums.gentoo.org/viewtopic-t-312591-highlight-.html

All users will be created as user@domain

There are 3 steps to the migration, 3 different scripts to run. Before you can move on to the next script, you must execute the sql that gets printed to the screen from the previous script.

The first script will query your vpopmail database and qmail configuration files to get information about the domains that are hosted on the server. Then it will get the user information for the virtual domains. Each new domain gets a different group id. Throughout the process it will output sql that you can use to populate your dbmail tables.

The second script will query your DBMail dbmail_users table. This is required because we cannot populate the dbmail_aliases table until we have the user_idnr and group of the users created by the sql output from the previous script. Once this information is collected, the script will output the sql to populate the dbmail_aliases table.

The third script will scan the filesystem for .qmail aliases and forwards and use this information to output sql to populate the dbmail_aliases table, creating aliases and forwards.

Script 1:


#!/usr/bin/perl -w
use strict;
use DBI;

my %allinfo;
my @sql;

# Setup SQL Connection credentials to the vpopmail server

my $dbhost = "localhost";
my $database = "vpopmail";
my $dbuser = "vpopmail";
my $dbpass = "vpoppasswd";

#Path the the qmail assign file - Contains domains and aliased domains
# find / -name assign | grep qmail

my $assign = "/var/qmail/users/assign";

# Query to get users for domains/sql
my $getusers = "Select pw_name, pw_passwd, pw_shell from ";

#This number will be the first used as the ID for the accounts created
#It will increment by 1 for each new domain.

my $groupid = 5;

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

open (ASSIGN, $assign) || die "Cannot open \"$assign\" $!";;
my @domains = <ASSIGN>;
close(ASSIGN);

foreach (@domains){
        next if $_ !~ /:/g;
        my @indiv = (split(":", $_))[0,1];
        foreach (@indiv){
                s/^\+//;
                s/-$//;
                s/^\s+//;
                s/\s+$//;
                }
        push @{ $allinfo{$indiv[1]} }, $indiv[0];
}

foreach my $maindomain( keys %allinfo ) {
        $groupid++ if $groupid =~ /0$/;
        my $quoteddn = $dbh->quote($maindomain);
        push @sql, "insert into dbmail_vdomains (domain, comment) Values ($quoteddn,\'Primary Domain\');";

        # Get domains and domain aliases
        foreach (@{$allinfo{$maindomain}}){
                next if $_ =~ /$maindomain/i;
                push @sql, "insert into dbmail_vdomains (domain, pridomain, comment) Values (\'$_\', \'$maindomain\', \'Alias for $maindomain\');";
        }
        my $vdomsql = $maindomain;
        for ($vdomsql) {
                s/^\s+//;
                s/\s+$//;
                s/\./_/g;
        }
        my $sth = $dbh->prepare("$getusers $vdomsql");
        $sth->execute();
        while ( my @users = $sth->fetchrow_array ) {
                my $username = "$users[0]\@$maindomain";
                $username = $dbh->quote($username);
                my $password = $dbh->quote($users[1]);
                my $quota = $dbh->quote($users[2]);
                $quota =~ s/NOQUOTA/0/;
                push @sql, "Insert into dbmail_users ( userid, passwd, maxmail_size, client_idnr, encryption_type ) Values ( $username, $password, $quota, $groupid, \'md5\' );";
        }
$groupid++;
}

print "$_\n" foreach @sql;


$dbh->disconnect;

Script 2:


#!/usr/bin/perl -w
use strict;
use DBI;

my %allinfo;
my @sql;

# Setup SQL Connection credentials for the dbmail host

my $dbhost = "192.168.1.xxx";
my $database = "dbmail";
my $dbuser = "dbmail";
my $dbpass = "dbmailpw";

# Query to get users for domains/sql
my $getusers = "Select user_idnr, userid, client_idnr from dbmail_users";

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

my $sth = $dbh->prepare($getusers);
        $sth->execute();
        while ( my @users = $sth->fetchrow_array ) {
                push @sql, "Insert into dbmail_aliases ( alias, deliver_to, client_idnr ) Values (\'$users[1]\', \'$users[0]\', \'$users[2]\')";
        }

print "$_;\n" foreach @sql;
$dbh->disconnect;

Script 3:


#!/usr/bin/perl -w
use strict;
use File::Find;
use DBI;

my @sql;
my %aliasalias;
# Setup SQL Connection credentials for the dbmail host

my $dbhost = "192.168.10.131";
my $database = "dbmail";
my $dbuser = "dbmail";
my $dbpass = "password";

# Query to get users for domains/sql
my $getusers = "Select user_idnr, userid, client_idnr from dbmail_users";

# Query to get aliases for domains/sql
my $getaliases = "Select alias_idnr, alias, deliver_to, client_idnr from dbmail_aliases";

my $getdomains = qq |
Select REPLACE(dv1.domain, '\@', '') as domain, da.client_idnr from dbmail_vdomains dv1 left join dbmail_aliases da on
REPLACE(dv1.domain, '\@', '') = substring( da.alias, LOCATE( '\@', da.alias ) +1 ) or
REPLACE(dv1.pridomain, '\@', '') = substring( da.alias, LOCATE( '\@', da.alias ) +1 )
Group by dv1.domain|;


my $vpopmailroot = '/var/vpopmail/domains/';

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

my $dbmailusers = $dbh->selectall_hashref($getusers, 'userid');
my $dbmailaliases = $dbh->selectall_hashref($getaliases, 'alias');
my $domainhref = $dbh->selectcol_arrayref($getdomains, { Columns=>[1,2] });
my %domains = @$domainhref;
$dbh->disconnect;

find({wanted =>\&wanted}, "$vpopmailroot");

while (my($key, $value) = each %aliasalias) {
        my @aliasedal = split(/:/, $key);
        process($aliasedal[0],$aliasedal[1]);
}

sub wanted {
        my $i = 0;
        if (-f && /^\.qmail/) {
                my ($alias, $aliasguts);
                my @paths = split("/", "$File::Find::dir/$_");
                if ($_ =~ /\.qmail-/){
                        $alias = $_;
                        $alias =~ s/\.qmail-//;
                }
                else {
                        $alias = "$paths[5]";
                }
                        {
                        local(*QMAILALIAS, $/);
                        open (QMAILALIAS, "$File::Find::dir/$_") || die "can't open $File::Find::dir/$_: $!";
                        $aliasguts = <QMAILALIAS>;
                        $aliasguts =~ s/\n/\\/g;
                        my @aliasdest = split(/\\/, $aliasguts);
                        foreach (@aliasdest){
                                s/&//g;
                                next if $_ =~ /^\//;
                                next if $_ =~ /^\|/;
                                next if length($_) < 2;
                                my $dbdest;
                                my $alias = "$alias\@$paths[4]";
                                if ($_ =~ /@/g){
                                        $dbdest = "$_";
                                }
                                else {
                                        $dbdest = "$_\@$paths[4]";
                                }
                                process($dbdest,$alias);
                        }
                }
        }
}

sub process {
        my $dbdest = $_[0];
        my $alias = $_[1];
        my $key = "$dbdest:$alias";
        my $domain = (split(/@/, $dbdest))[1];
        my $destdom = (split(/@/, $alias))[1];
        my $user_idnr = $dbmailusers->{$dbdest}{user_idnr};
        if (!exists($domains{$domain}) and !exists($domains{"\@$domain"})){
                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$dbdest', '$domains{$destdom}');";
        }
        if ( $dbmailusers->{$dbdest}{userid} ){
                my $client_idnr = $dbmailusers->{$dbdest}{client_idnr};
                my $user_idnr = $dbmailusers->{$dbdest}{user_idnr};
                #push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$user_idnr', '$client_idnr');";

                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$dbdest', '$client_idnr');";
        }
        if ( !$dbmailusers->{$dbdest}{userid} ){
                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$dbdest', '$domains{$destdom}');";
        }
        return;
}

print "$_\n" foreach @sql;

 
migrating_from_qmail_vpopmail_sql.txt · Last modified: 2012/02/27 21:37 by bas
 
DBMail is developed by Paul J Stevens together with developers world-wide