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

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

https://github.com/opensolutions/ViMbAdmin/wiki/

http://wiki.dovecot.org/HowTo/DovecotLDAPostfixAdminMySQL

http://mailscanner.info/postfix.html

http://trac.roundcube.net/wiki/Howto_Install

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

Recentste berichten

Most Used Categories

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

Geef een reactie