# HOWTO: install postfixadmin in existing postfix/virtmail

## hanj

Hello

Over the past year, I've been running with the Postfix/Virtual mail setup ( http://www.gentoo.org/doc/en/virt-mail-howto.xml ). This has been running great, but I wanted to include postfixadmin into the mix. I didn't find any good documentation on moving the existing gentoo virtmail database to postfixadmin's postfix database. I'm creating this how-to in case anyone wants to take advantage of postfixadmin and have already set up their virtmail based off of the guide.

My setup may differ from yours ( I use quotas and crypted passwords ). This how-to also assumes that you have a working virtmail set up based off of the guide posted above.

Here is my current set up for virtmail

```

mail-mta/postfix-2.2.5  -hardened -ipv6 -ldap -mailwrapper -mbox +mysql -nis +pam -postgres +sasl (-selinux) +ssl +vda

dev-db/mysql-4.1.14  +berkdb -big-tables -cluster -debug -doc -extraengine -geometry -minimal +perl +readline (-selinux) +ssl -static +tcpd -utf8

net-libs/courier-authlib-0.58  +berkdb +crypt -debug +gdbm -ldap +mysql +pam -postgres

mail-filter/amavisd-new-2.3.3-r2  -ldap -milter +mysql -postgres

app-antivirus/clamav-0.88  +crypt -mailwrapper -milter (-selinux)

mail-filter/spamassassin-3.1.0  +berkdb -doc -minimal +mysql -qmail +ssl -tools

www-apps/postfixadmin-2.1.0  -vhosts

dev-libs/cyrus-sasl-2.1.20 [2.1.20-r2] -authdaemond +berkdb +gdbm -java -kerberos -ldap +mysql +pam -postgres +ssl -static
```

As of this writing, you'll need to add www-apps/postfixadmin to /etc/portage/package.keywords (www-apps/postfixadmin ~x86)

```
emerge -v postfixadmin
```

Now we need to create the database, tables and users.. (make sure you use change the password value 'CREATEGOODPASSWORD' to your desired passwords)

```
USE mysql;

INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('CREATEGOODPASSWORD'));

INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');

INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('CREATEGOODPASSWORD'));

INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');

FLUSH PRIVILEGES;

GRANT USAGE ON postfix.* TO postfix@localhost;

GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost;

GRANT USAGE ON postfix.* TO postfixadmin@localhost;

GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;

CREATE DATABASE postfix;

USE postfix;

CREATE TABLE admin (

  username varchar(255) NOT NULL default '',

  password varchar(255) NOT NULL default '',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  modified datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  PRIMARY KEY  (username),

  KEY username (username)

) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Admins';

#

# Table structure for table alias

#

CREATE TABLE alias (

  address varchar(255) NOT NULL default '',

  goto text NOT NULL,

  domain varchar(255) NOT NULL default '',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  modified datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  PRIMARY KEY  (address),

  KEY address (address)

) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';

#

# Table structure for table domain

#

CREATE TABLE domain (

  domain varchar(255) NOT NULL default '',

  description varchar(255) NOT NULL default '',

  aliases int(10) NOT NULL default '0',

  mailboxes int(10) NOT NULL default '0',

  maxquota int(10) NOT NULL default '0',

  transport varchar(255) default NULL,

  backupmx tinyint(1) NOT NULL default '0',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  modified datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  PRIMARY KEY  (domain),

  KEY domain (domain)

) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';

#

# Table structure for table domain_admins

#

CREATE TABLE domain_admins (

  username varchar(255) NOT NULL default '',

  domain varchar(255) NOT NULL default '',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  KEY username (username)

) TYPE=MyISAM COMMENT='Postfix Admin - Domain Admins';

#

# Table structure for table log

#

CREATE TABLE log (

  timestamp datetime NOT NULL default '0000-00-00 00:00:00',

  username varchar(255) NOT NULL default '',

  domain varchar(255) NOT NULL default '',

  action varchar(255) NOT NULL default '',

  data varchar(255) NOT NULL default '',

  KEY timestamp (timestamp)

) TYPE=MyISAM COMMENT='Postfix Admin - Log';

CREATE TABLE mailbox (

  username varchar(255) NOT NULL default '',

  password varchar(255) NOT NULL default '',

  name varchar(255) NOT NULL default '',

  maildir varchar(255) NOT NULL default '',

  quota int(10) NOT NULL default '0',

  domain varchar(255) NOT NULL default '',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  modified datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  PRIMARY KEY  (username),

  KEY username (username)

) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';

#

# Table structure for table vacation

#

CREATE TABLE vacation (

  email varchar(255) NOT NULL default '',

  subject varchar(255) NOT NULL default '',

  body text NOT NULL,

  cache text NOT NULL,

  domain varchar(255) NOT NULL default '',

  created datetime NOT NULL default '0000-00-00 00:00:00',

  active tinyint(1) NOT NULL default '1',

  PRIMARY KEY  (email),

  KEY email (email)

) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

```

Now, I figured the best way to deal with our old database is to migrate the data from mailsql to postfix. We don't want to change the code in postfixadmin to look at our old database (mailsql). I put together a simple PHP-CLI script to do this...just name it migration.php. I also wanted to have the ability to switch back to standard mailsql/virtmail in case things break bad. The change went smooth for me.

```
#!/usr/bin/php

<?

$DBserver       = "localhost";

$DBname         = "mailsql";

$DBuser         = "mailsqluser";

$DBpassword     = "mailsqlpassword";

if(!($connect = mysql_connect($DBserver, $DBuser, $DBpassword))){

        echo "Error Connecting to the Database.";

        exit();

} 

$DB             = mysql_select_db($DBname);

$sql            = "SELECT *

                        FROM transport";

if(!$rs = mysql_query($sql,$connect)){

        echo "Database Error\n";

        exit();

}

echo "Getting domains from mailsql\n";

while($row = mysql_fetch_object($rs)){

        $domain         = $row->domain;

        $destination    = rtrim($row->destination,":");

        $sql            = "INSERT INTO domain(domain, description, aliases, mailboxes, maxquota, transport, backupmx, created, modified, active)

                                VALUES('".$domain."',

                                '',

                                10,

                                20,

                                0,

                                '".$destination."',

                                0,

                                NOW(),NOW(),1)";

        if(!mysql_db_query("postfix",$sql,$connect)){

                echo "Database Error - Unable to populate postfix:domains\n";

                echo mysql_error()."\n";

                exit();

        }

        echo "Inserting ".$domain." in postfix database\n";

}

mysql_free_result($rs);

echo "Getting users from mailsql\n";

$sql            = "SELECT *

                        FROM users";

if(!$rs = mysql_db_query("mailsql",$sql,$connect)){

        echo "Database Error - Unable to get users from mailsql\n";

        echo mysql_error()."\n";

        exit();

}

while($row = mysql_fetch_object($rs)){

        $email          = $row->email;

        $crypt          = $row->crypt;

        $name           = $row->name;

        $maildir        = $row->maildir;

        $quota          = $row->quota;

        $emailArr       = explode("@",$email);

        $domain         = $emailArr[1];

        $sql            = "INSERT INTO mailbox(username, password, name, maildir, quota, domain, created, modified, active)

                                VALUES('".$email."',

                                '".addslashes($crypt)."',

                                '".addslashes($name)."',

                                '".$maildir."',

                                '".$quota."',

                                '".$domain."',

                                NOW(), NOW(),1)";

        if(!mysql_db_query("postfix",$sql,$connect)){

                echo "Database Error - Unable to populate postfix:mailbox\n";

                echo mysql_error()."\n";

                exit();

        }

        echo "Inserting mailbox ".$email."\n";

   $sql            = "INSERT INTO alias(address, goto, domain, created, modified, active)

                                VALUES('".$email."',

                                '".$email."',

                                '".$domain."',

                                NOW(),NOW(),1)";

        if(!mysql_db_query("postfix",$sql,$connect)){

                echo "Database Error - Unable to populate postfix:alias\n";

                echo mysql_error()."\n";

                exit();

        }

        echo "Inserting alias ".$email."\n";

}

mysql_free_result($rs);

echo "Getting aliases from mailsql\n";

$sql                    = "SELECT *

                                FROM virtual";

if(!$rs = mysql_db_query("mailsql",$sql,$connect)){

        echo "Database Error - Unable to get alias from mailsql\n";

        echo mysql_error()."\n";

        exit();

}

while($row = mysql_fetch_object($rs)){

        $email          = $row->email;

        $destination    = $row->destination;

        if($email != $destination){     // doing this since 1to1 alias was handled above

                $emailArr       = explode("@",$email);

                $domain         = $emailArr[1];

                $sql            = "INSERT INTO alias(address, goto, domain, created, modified, active)

                                        VALUES('".$email."',

                                        '".$destination."',

                                        '".$domain."',

                                        NOW(),NOW(),1)";

                if(!mysql_db_query("postfix",$sql,$connect)){

                        echo "Database Error - Unable to populate postfix:alias\n";

                        echo $email."\n";

                        // might be duplicate key

                }else{

                        echo "Inserting alias ".$email." -> ".$destination."\n";

                }

        }

}

mysql_free_result($rs);

echo "\n\nDONE";

?>
```

Now just execute this script (make sure you chmod it to 700 first)

```
./migration.php
```

Now aliases seem to be handled a little differently in the postfix database, they are comma seperated in the 'goto' field if there are more than one alias from one email. My script above doesn't handle that, so you'll see 'error' messages when they occur (because of duplicate key error on the email value). I only had a few examples of this in my setup, and adjusting them by hand was faster than getting my brain around the script.. so you may need to adjust to handle that.

This will copy all users, aliases and domain information from mailsql to the new postfix database to their appropriate tables/columns.

At this time, I made sure that the data was correct in the postfixadmin interface. If all looks good, it's time to start configuring postfix and sasl to look at the new table.

I made a few changes to the /postfixadmin/config.inc.php so homedir path would be correct.

```
$CONF['domain_path'] = 'YES';

$CONF['domain_in_mailbox'] = 'NO';

$CONF['transport'] = 'YES';
```

I decided to create new mysql_* files for postfix (in case I needed to rollback quickly). I created a postfixadmin directory in /etc/postfix/ and created the following files (originally referenced here https://forums.gentoo.org/viewtopic-t-294337-highlight-postfixadmin+maildir.html :: Thanks Steve!!):

mysql_virtual_alias_maps.cf

mysql_virtual_domains_maps.cf

mysql_virtual_mailbox_limit_maps.cf

mysql_virtual_mailbox_maps.cf   

/etc/postfix/mysql_virtual_alias_maps.cf

```
# /etc/postfix/mysql_virtual_alias_maps.cf

#

# virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf

user                    = postfix

password                = postfix

dbname                  = postfix

#hosts                  = localhost

hosts                   = unix:/var/run/mysqld/mysqld.sock

table                   = alias

select_field            = goto

where_field             = address     
```

/etc/postfix/mysql_virtual_domains_maps.cf

```
# /etc/postfix/mysql_virtual_domains_maps.cf

#

# virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf

user                    = postfix

password                = postfix

dbname                  = postfix

#hosts                  = localhost

hosts                   = unix:/var/run/mysqld/mysqld.sock

table                   = domain

select_field            = transport

where_field             = domain

additional_conditions   = AND backupmx='0' AND active='1'
```

/etc/postfix/mysql_virtual_mailbox_limit_maps.cf

```
# /etc/postfix/mysql_virtual_mailbox_limit_maps.cf

#

# virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql_virtual_mailbox_limit_maps.cf

user                    = postfix

password                = postfix

dbname                  = postfix

#hosts                  = localhost

hosts                   = unix:/var/run/mysqld/mysqld.sock

table                   = mailbox

select_field            = quota

where_field             = username

additional_conditions   = AND active='1'
```

/etc/postfix/mysql_virtual_mailbox_maps.cf

```
# /etc/postfix/mysql_virtual_mailbox_maps.cf

#

# virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf

user                    = postfix

password                = postfix

dbname                  = postfix

#hosts                  = localhost

hosts                   = unix:/var/run/mysqld/mysqld.sock

table                   = mailbox

select_field            = maildir

where_field             = username

additional_conditions   = AND active='1'
```

Edit /etc/postfix/main.cf

```
virtual_mailbox_domains = mysql:/etc/postfix/postfixadmin/mysql_virtual_domains_maps.cf

virtual_mailbox_maps = mysql:/etc/postfix/postfixadmin/mysql_virtual_mailbox_maps.cf

virtual_alias_maps = mysql:/etc/postfix/postfixadmin/mysql_virtual_alias_maps.cf

virtual_mailbox_limit_maps = mysql:/etc/postfix/postfixadmin/mysql_virtual_mailbox_limit_maps.cf
```

Now it's time to adjust /etc/courier/authlib/authmysqlrc

```
MYSQL_SERVER            localhost

MYSQL_USERNAME          postfixuser

MYSQL_PASSWORD          postfixpassword

MYSQL_SOCKET            /var/run/mysqld/mysqld.sock

MYSQL_PORT              0

MYSQL_OPT               0

MYSQL_DATABASE          postfix

MYSQL_USER_TABLE        mailbox

MYSQL_CLEAR_PWFIELD     password

MYSQL_UID_FIELD         '1003'      # The UID of vmail user

MYSQL_GID_FIELD         '1003'      # THE GID of vmail user

MYSQL_LOGIN_FIELD       username

MYSQL_HOME_FIELD        '/home/vmail'

MYSQL_NAME_FIELD        name

MYSQL_MAILDIR_FIELD     maildir

MYSQL_QUOTA_FIELD       quota

MYSQL_WHERE_CLAUSE      active='1'
```

The final step is to get SASL to lookup the correct crypt/password field in the postfix database

Edit /etc/sasl2/smtpd.conf

```
password_format: crypt

sql_engine: mysql

sql_hostnames: 127.0.0.1

sql_database: postfix

sql_user: postfixuser

sql_passwd: postfixpassword

sql_select: SELECT password FROM mailbox WHERE username='%u@%r'
```

Now we need to reload/restart

```
postfix reload

/etc/init.d/courier-authlib restart

/etc/init.d/saslauthd restart
```

Keep an eye out in /var/log/mail.log and send/receive test messages to ensure that everything is working correctly.

Hope this helps

hanji

----------

## xtimmahx

Just a small correction if you don't mind,

I followed your setup but adapted it to a new installation, so I skipped the migration portion altogether.

In your /etc/sasl2/smtpd.conf you specify encrypted passwords, but in your

/etc/courier/authlib/authmysqlrc you specify the field of

MYSQL_CLEAR_PWFIELD password

When using encrypted passwords, this is incorrect, and took me a while to troubleshoot.  (I'm not terribly experienced with this stuff yet)

I had to instead comment out that line and put in:

MYSQL_CRYPT_PWFIELD password

as well as remove the comments following the UID and GID field lines in order for my pop daemon to respond correctly.

Until I did that I received an input/output error on pop3d.

Now all is well!

Thanks for the nice howto!

----------

## ebnerjoh

Hi!

This Dokumentation worked for me. Now I want to enable the "change_sqlpass"-plugin for squirrelmail but I dont know what kind of encrypted passwords are used in this documentation.

Any tipps?

Regards,

Johannes

----------

