Postfix MySQL howto v1.0

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

Postfix logo_250x80

 

 

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

Snow B.V. FreeBSD