Last updated Februari 10 2013
Full-fledged Postfix using MySQL HOWTO
Postfix, MySQL, IMAP, WebMail, Virus- and spamscanning/checking mail system
by Tom Scholten and authors of likewise documents
v1.0
Special thanks to Remco from WellnessAndConsultancy for testdriving this tutorial
 |
 |
As my PostfixLDAP howto is getting agy and less used with a request from a fellow web-company and one of the main CT customers I’ve decided to write down a comprehensive guide on how to install a mailserver using Postfix based on MySQL – obviously with all the things I think you’ll need with that!
This howto will  – if succesfully deployed – give you an (FreeBSD) Postfix MySQL ViMbAdmin setup with Dovecot, RoundCube and MailScanner. Say what?
- Postfix; main mailserver (as in SMTP, so for server traffic and sending mail)
- MySQL; database
- ViMbAdmin; web-gui to administer (or give someone partial rights) your mail-addresses
- Dovecot; main mailserver (as in POP/IMAP, for fetching, storing your mail) including SIEVE (through pigeonhole) to have customer rule processing
- RoundCube; webmail frontend
- MailScanner + MailWatch; virus and spam-checks, the MailWatch is a front-end to which you can also have people de-quarantine their mail
Rules of engagement
This only describes how to build a mail-host on a secure, trusted, local network. We are not dealing with some of the things you should have in place first
- working and active pf.conf
- ssl-certificates for your web- and mail servers
- (note that you DO need the ‘pem’ format for the mailservers in this document, revert to google and ‘cacert mailserver pem’ for more information
- ensure your apache webserver has the certificates (or test-certificates) working and the production php.ini applied (see /usr/local/etc/php.ini-production)
We do not take any responsibility for your setup – this howto serves as a recipe on how you could do it. Read it and interpret it but create your own secure solution!
Documentation sources
FreeBSD 9.1 base install
install ‘plain’ (no lib32/games/doc) with ports
# freebsd-update fetch
# portsnap fetch
# cd /usr/ports/ports-mgmt/portmaster
# make install
Add to /etc/rc.conf
sendmail_enable=”NO”
sendmail_submit_enable=”NO”
sendmail_outbound_enable=”NO”
sendmail_msp_queue_enable=”NO”
mysql_enable=”YES”
apache22_enable=”YES”
postfix_enable=”YES”
dovecot_enable=”YES”
Edit /etc/make.conf
WITHOUT_X11=yes
Prepare ports
Fetch the below /var/db/ports contents
http://www.compa.nl/tmp/mailserver.ports.tgz
Build the following
# portmaster mail/postfix databases/mysql55-server mail/roundcube mail/dovecot2 mail/dovecot2-pigeonhole www/apache22 lang/php5 lang/php5-extensions devel/git devel/subversion www/smarty3 www/zend-framework databases/pear-Doctrine12 ftp/wget shells/bash mail/mailscanner databases/p5-DBD-mysql
Note; do not build MailScanner with bdc!
Prepare database environment
# mysql -uroot
> use mysql;
> update user SET password=password(‘mastersecret0’) where user=‘root’;
> flush privileges;
> quit;
# mysql -uroot -p
> create database webmail;
> grant ALL ON webmail.* to ‘roundcube’@localhost IDENTIFIED by ‘secret0’;
> create database mailscanner;
> grant ALL ON mailscanner.* to ‘mailwatch’@localhost IDENTIFIED by ‘secrety’;
> grant file on *.* to mailwatch@localhost identified by ‘secrety’;
> create database postfixmail;
> grant ALL ON postfixmail.* to ‘vimbadmin’@localhost IDENTIFIED by ‘secret1’;
> grant SELECT ON postfixmail.* to ‘dovecot’@’localhost’ IDENTIFIED by ‘secret2’;
> grant SELECT ON postfixmail.* to ‘postfix’@’localhost’ IDENTIFIED by ‘secret3’;
> grant SELECT, RELOAD, LOCK TABLES ON *.* to ‘backup’@’localhost’ IDENTIFIED by ‘secret4’;
> flush privileges;
> quit;
# mkdir -p /home/vmail
# chmod 770 /home/vmail
# vi /etc/group
Add a group ‘vmail’ with id 2000
# useradd
> “vmail†as username
> “Virtual Mailbox†as description
> id 2000
> /home/vmail as directory
> group = vmail
> additional groups = mail
> nologin
# chown -R vmail:mail /home/vmail
Dovecot:
# cp -pfr /usr/local/share/doc/dovecot/example-configs/* /usr/local/etc/dovecot/
Adjust according to
http://www.compa.nl/tmp/mailserver.dovecott.diff.txt
Postfix:
Add to /usr/local/etc/postfix/master.cf
# Dovecot LDA
dovecot unix – n n – – pipe
flags=DRhu user=vmail:mail argv=/usr/local/libexec/dovecot/deliver -d ${recipient}
Add the contents of http://www.compa.nl/tmp/mailserver.postfix.main.cf.add.txt to main.cf
Create the mysql_ files as shown in http://www.compa.nl/mailserver.postfix.mysql.files.txt
Add header_checks = regexp:/usr/local/etc/postfix/header_checks to main.cf
Create the file and have it contain
/^Received:/ HOLD
Apache:
In /usr/local/etc/apache22/httpd.conf
- Find the ‘DirectoryIndex’ declaration and add ‘index.php’ behind index.html (with a space separating them)
- ‘untick’ the ‘vhost’ file inclusion near the bottom
- add the following somewhere near there
# PHP settings
Include etc/apache22/extra/httpd-php.conf
Create an ./extra/httpd-php.conf containing
#
# This is the Apache server configuration file providing PHP support.
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
Create a file in ./Includes named ‘yourhost.conf’
<Virtualhost *:80>
ServerName yourhost.example.com
DocumentRoot /usr/local/www/apache22/data/
Alias /vimbadmin /usr/local/vimbadmin/public
<Directory /usr/local/vimbadmin/public>
Options FollowSymLinks
AllowOverride None
Order allow,deny
allow from all
SetEnv APPLICATION_ENV production
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} -s [OR]
RewriteCond %{REQUEST_FILENAME} -l [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^.*$ – [NC,L]
RewriteRule ^.*$ /vimbadmin/index.php [NC,L]
</Directory>
</VirtualHost>
Vimbadmin
# cd /usr/local/
# wget {url to latest version from https://github.com/opensolutions/ViMbAdmin/archives/develop}
# tar xfvz {file you got}
# ln -s {dir it created} vimbadmin
# cd /usr/local/vimbadmin
# cp application/configs/application.ini.dist application/configs/application.ini
# vi application/configs/application.ini
Set resources.frontController.params.displayExceptions = 0 to 1
Adjust ID’s to 2000
Adjust connection to
resources.doctrine.connection_string = “mysql://vimbadmin:supersecret@localhost/postfixmail”
DO NOT FORGET TO RESET displayExceptions back to 0 if vimbadmin GUI works!
Next switch to the /usr/local/vimbadmin/library directory and create symlinks
# ln -s /usr/local/share/pear/Doctrine Doctrine
# ln -s /usr/local/share/smarty3/ Smarty
# ln -s /usr/local/share/ZendFramework/library/Zend Zend
Now initialize the vimbadmin database
# cd /usr/local/vimbadmin
# bin/doctrine-cli.php create-tables
Putting it together
Stage1
Start your webserver
# /usr/local/etc/rc.d/apache22 start
Browse to http://192.168.0.1/vimbadmin
Get the “salt†from this page
Put that in the application.ini file mentioned above + reset the displayExceptions to 0
Reload the page
Put in the salt
Put in a mailaddress en password
Congratulations, you can now login again 🙂
Now create a test-domain (example.org) + mailbox (tester@example.org)
Stage2
Initiate a tail (-f) on /var/log/maillog
# /usr/local/etc/rc.d/dovecot start
Look for any errors and correct them (did you do your certificates!)
Check postfix config (warnings are OK-ish for now)
# postfix check
If all is well start postfix
# /usr/local/etc/rc.d/postfix start
Again, same thing (warnings are OK-ish for now)
Check resolving
# postmap -q testuser@example.org mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
Then start a telnet session to verify delivery
$ telnet localhost 25
you> ehlo host
250-something
you> mail from: johndoe
250 2.1.0 Ok
you> rcpt to: johndoe
THIS ONE SHOULD FAIL
you> rcpt to: johndoe@example.org
THIS ONE SHOULD FAIL
you> rcpt to: testuser@example.org
THIS ONE SHOULD GO OK
250 2.1.5 Ok
you> data
354 End data with .
you> test
you> .
250 2.0.0 Ok: queued as 9729067C17
you> quit
221 2.0.0 Bye
Connection closed by foreign host.
Check your postfix/dovecot output in maillog for delivery
# ls -lR /home/mail/example.org
There should be some mail in the maildir there 🙂
Congratulations you now have a working, basic, setup for your mailserver
Next up
Apache config for PHP
# cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini
# vi /usr/local/etc/php.ini
adjust error_log to /var/log/php.log
adjust date.timezone to ‘CET’
# touch /var/log/php.log
# chown www:www /var/log/php.log
Add php.log to /etc/newsyslog
Restart apache /usr/local/etc/rc.d/apache22 restart
MailScanner (and Postfix)
In /usr/local/etc/postfix add header_checks = regexp:/usr/local/etc/postfix/header_checks to main.cf
Create the file header_checks and have it contain
/^Received:/ HOLD
Next create Mailscanners’ directories
# mkdir -p /var/spool/MailScanner/incoming
# mkdir -p /var/spool/MailScanner/incoming/Locks
# mkdir -p /var/spool/MailScanner/quarantine
# mkdir -p /var/spool/mqueue
# mkdir -p /var/spool/mqueue.in
Getting ‘mailwatch’ from http://sourceforge.net/projects/mailwatch/files/
# mkdir /tmp/src
# cd /tmp/src
# wget {url}
# tar xfvz {file from url}
# cd mailw*
*HINT* if the below command fails, replace “TYPE=MyISAM†with “Engine=MyISAM†through the create file and look for “timestamp(14)†and set this to “timestampâ€
#mysql -umailwatch -p mailscanner < create.sql
#vi MailScanner_perl_scripts/MailWatch.pm
Edit the file for your database connections
# cp MailScanner_perl_scripts/MailWatch.pm /usr/local/lib/MailScanner/MailScanner/CustomFunctions/
Create the admin user
#mysql mailscanner -u mailwatch -p
> INSERT INTO users SET username = ‘youruser’, password = md5(‘secret2much’), fullname = ‘Me Myself and I’, type =’A’
> \q
# mkdir {/wwwroot}/mailwatch
# cp -pfr mailscanner/* {/wwwroot}/mailwatch/
# cp {/wwwroot}/mailwatch/conf.php.example {/wwwroot}/mailwatch/conf.php
# vi {/wwwroot}/mailwatch/conf.php
Set your DB-user and Password & change below lines
define(‘MAILWATCH_HOME’, ‘{/wwwroot}/mailwatch’);
define(‘MS_CONFIG_DIR’, ‘/usr/local/etc/MailScanner/’);
define(‘MS_LIB_DIR’, ‘/usr/local/lib/MailScanner/’);
define(‘SA_RULES_DIR’, ‘/usr/local/share/spamassassin/’);
Edit /usr/local/etc/MailScanner/MailScanner.conf
Change
- Always Looked Up Last = &MailWatchLogging
- Detailed Spam Report = yes
- Quarantine Whole Message = yes
- Quarantine Whole Message As Queue Files = no
- Include Scores In SpamAssassin Report = yes
- Quarantine User = root
- Quarantine Group = apache (this should be the same group as your web server)
- Quarantine Permissions = 0660
- Incoming Work Permissions = 0640
Clean up
# rm -r /tmp/src/mailwatch*
Set groups
# vi /etc/group
Add “clamav” to the postfix group
Now add to your /etc/rc.conf
mailscanner_enable=â€yesâ€
clamav_clamd_enable=”YES”
clamav_freshclam_enable=”YES”
# /usr/local/etc/rc.d/clamav-freshclam start
{wait for a bit}
# /usr/local/etc/rc.d/clamav-clamd start
{make sure it’s succesfull, or wait some more and retry}
{If taking too long, run ‘freshclam’ and wait for it to download}
# /usr/local/etc/rc.d/mailscanner start
Watch for the output and try sending out a testmail to root – see if logging and processing work again
Add the following lines to your apache configuration, we’ve included the rules for vimbadmin just to be sure 🙂
<Virtualhost *:80> ServerName newmail.example.org DocumentRoot /usr/local/www/apache22/data/ Alias /vimbadmin /usr/local/vimbadmin/public <Directory /usr/local/vimbadmin/public> Options FollowSymLinks AllowOverride None Order allow,deny allow from all SetEnv APPLICATION_ENV production RewriteEngine On RewriteCond %{REQUEST_FILENAME} -s [OR] RewriteCond %{REQUEST_FILENAME} -l [OR] RewriteCond %{REQUEST_FILENAME} -d RewriteRule ^.*$ - [NC,L] RewriteRule ^.*$ /vimbadmin/index.php [NC,L] </Directory> Alias /mailscanner /www/example.org/newmail/secure/mailwatch <Directory /www/example.org/newmail/secure/mailwatch> Options FollowSymLinks AllowOverride None Order allow,deny allow from all SetEnv APPLICATION_ENV production </Directory> </VirtualHost>
Todo:
Roundcube config
Before going live
Check firewall (pf.conf), and it’s activation
Install tripwire, monitoring and all that kind of stuff!
Tom Scholten is consultant with Snow B.V., a Dutch Technical Consultancy Company supplying specialists in the fields of Storage, Networking and Unix
 |
 |