Many-Domain conversion

Back

1. Stop services

qmailctl stop && systemctl stop dovecot

2. Install and enable higher priority QMT MD repo, and  Install QMT MD packages.

CentOS 7/QMT only
wget -O /etc/yum.repos.d/qmt-md.repo  https://raw.githubusercontent.com/qmtoaster/mirrorlist/master/qmt-md-centos7.repo
yum-config-manager --enable qmt-md-current
wget -P /etc/yum.repos.d https://raw.githubusercontent.com/qmtoaster/scripts/master/dovecot.repo
yum makecache
yum clean all
yum update dovecot qmail ezmlm ezmlm-cgi vpopmail qmailadmin vqadmin
yum install dovecot-mysql

CentOS 8/QMT
wget -O /etc/yum.repos.d/qmt-md.repo  https://raw.githubusercontent.com/qmtoaster/mirrorlist/master/qmt-md-centos8.repo
yum-config-manager --enable qmt-md-testing
wget -P /etc/yum.repos.d https://raw.githubusercontent.com/qmtoaster/scripts/master/dovecot.repo
yum makecache
yum clean all
yum update dovecot qmail ezmlm ezmlm-cgi vpopmail qmailadmin vqadmin
yum install dovecot-mysql

Make sure packages to be installed are designated '.md'.

3. Create new vpopmail table in vpopmail database (script) Download here

 #!/bin/sh
 # This script converts the vpopmail db traditionally used by QMT with domain
 # tables (domain_tld) to the vpopmail table containing many domains (md).
 # In addition to this vpopmail db conversion appropriate replacement packages
 # must be installed to interact with the converted vpopmail db, specifically,
 # CentOS 7/8 pkgs designated with 'md'. The conversion was necessary not only
 # for Dovecot's dsync utility to utilize commands like `doveadm user '*'` which
 # fails with Dovecot's vpopmail driver but because Dovecot is dropping support
 # for the vpopmail driver.

 passwd=
 read -s -p "Enter your MySQL DB management password: " passwd
 echo ""
 if [ "$passwd" == "" ];
 then
    echo "You must enter the MySQL DB management password"
    exit 0
 fi
 
 MYSQLPW=$passwd
 credfile=~/sql.cnf
 echo -e "[client]\nuser=root\npassword='$MYSQLPW'\nhost=localhost" > $credfile

 mysql --defaults-extra-file=$credfile -e "use vpopmail"
 [ "$?" != "0" ] && echo "Password error, exiting..." && exit 1

 DB=vpopmail

 mysql --defaults-extra-file=$credfile -D $DB -e "CREATE TABLE vpopmail \
 (pw_name char(32) NOT NULL, \
 pw_domain char(96) NOT NULL, \
 pw_passwd char(40) DEFAULT NULL, \
 pw_uid int(11) DEFAULT NULL, \
 pw_gid int(11) DEFAULT NULL, \
 pw_gecos char(48) DEFAULT NULL, \
 pw_dir char(160) DEFAULT NULL, \
 pw_shell char(20) DEFAULT NULL,\
 pw_clear_passwd char(16) DEFAULT NULL, \
 PRIMARY KEY (pw_name,pw_domain)) ENGINE=InnoDB DEFAULT CHARSET=latin1"
 [ "$?" != "0" ] && echo "Error creating vpopmail table" && exit 1
 for i in `echo "show tables" | mysql --defaults-extra-file=$credfile -D $DB|grep -v Tables_in_`;
 do
        if ! [ $i = dir_control ] && ! [ $i = lastauth ] && ! [ $i = vlog ] && ! [ $i = valias ] && ! [ $i = vpopmail ]; then
                # MySQL does not allow table names with a dot (.) so vpopmail replaces the dot (.) with an underscore (_) for
                # domain table names, example: table whitehorsetc.com becomes whitehorsetc_com. Vpopmail utilities will not allow
                # one to create a domain name with an underscore even though RFC allows it. So, we can safely replace all domain
                # table name underscores (_) with periods or dots (.) (below, 'domain_name') in our conversion for entry into the
                # new vpopmail many domains table.
                domain_name=`echo $i | sed -e 's/\(.*\)_/\1./' -e 's/_/-/g'`
                echo $domain_name;
                mysql --defaults-extra-file=$credfile -D $DB -B --skip-column-names -e \
                "INSERT INTO vpopmail SELECT pw_name, '$domain_name', pw_passwd, pw_uid, pw_gid, pw_gecos, pw_dir, pw_shell, pw_clear_passwd \
                FROM $i"
        fi
 done

4. Start services

qmailctl start && systemctl start dovecot

All updated packages should be using the new db and dovecot the sql driver (below).

The file here should now exist in the dovecot directory '/etc/dovecot/dovecot-sql.conf.ext' 
and the new sql driver in file '/etc/dovecot/toaster.conf' (below).

New sql user/password db driver (green)

passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

Replaces

Old vpopmail user/password db driver (red)

passdb {
  args = cache_key=%u%r webmail=127.0.0.1
  driver = vpopmail
}
userdb {
  args = cache_key=%u quota_template=quota_rule=*:backend=%q
  driver = vpopmail
}