| View previous topic :: View next topic |
| Author |
Message |
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1500
|
 Posted: Mon Mar 06, 2006 4:39 am Post subject: HOWTO: install postfixadmin in existing postfix/virtmail |
 |
|
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 | Code: |
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)
| Code: | | 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)
| Code: | 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.
| Code: | #!/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)
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.
| Code: | $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
| Code: | # /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
| Code: | # /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
| Code: | # /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
| Code: | # /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
| Code: | 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
| Code: | 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
| Code: | 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
| Code: | 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 |
|
| Back to top |
|
 |
xtimmahx
n00b
Joined: 23 Jan 2007
Posts: 3
|
| Posted: Fri Mar 30, 2007 8:52 pm Post subject: |
|
|
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! |
|
| Back to top |
|
|
ebnerjoh
Tux's lil' helper
Joined: 27 Oct 2006
Posts: 83
|
| Posted: Wed Apr 25, 2007 6:54 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Networking & Security
