Hosted Exchange Replacement with DBMail, Postfix, Roundcube and Z-Push – DBMail Setup

Our first setup is to get Postfix and DBMail working. Postfix will handle receiving our email then hand it off to DBMail using LMTP (Local Mail Transfer Protocol) to store it in a Mysql Database. In this instance we already have a database server that we are going to use to store email so we skip that step however there are plenty of walk through out there if you need help.

  1. The first step is to add the EPEL repository then install DBMail
    [root@mail ~]# rpm -Uvh http://mirror.iprimus.com.au/epel/6/i386/epel-release-6-8.noarch.rpm
    [root@mail ~]# yum install dbmail -y
    
  2. Next we need to create a database to store the email. Connect to the database server, create the user then set the privileges
    [root@mail ~]# yum install mysql -y
    [root@mail ~]# mysql -u root -p -h 192.168.100.21
    mysql> create database dbmail;
    mysql> CREATE USER 'dbmail'@'%' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
    mysql> CREATE USER 'dbmail'@'localhost' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
    mysql> grant all privileges on dbmail.* to 'dbmail'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    mysql> grant all privileges on dbmail.* to 'dbmail'@'%';
    Query OK, 0 rows affected (0.00 sec)
    mysql> FLUSH PRIVILEGES;
    
  3. Next import the default database into mysql
    [root@mail ~]# mysql -u dbmail dbmail -p -h 192.168.100.21 < /usr/share/doc/dbmail-3.0.2/sql/mysql/create_tables.mysql
    
  4. Postfix needs to support Mysql. The default version in Centos 6 does however to double check use postconf. Mysql should be listed after running the command below
    [root@mail ~]# postconf -c /etc/postfix -m
    mysql
    
  5. Now append the following line into the postfix master.cf file
    [root@mail ~]# vim /etc/postfix/master.cf
    dbmail-lmtp     unix    -       -       n       -       -       lmtp
    
  6. Now the following lines in the postfix main.cf need to be updated/created
    [root@mail ~]# vim /etc/postfix/main.cf
    inet_interfaces = all
    virtual_transport = dbmail-lmtp:127.0.0.1:24
    smtpd_sender_login_maps=mysql:/etc/postfix/dbmail_recipients.cf
    virtual_mailbox_domains=mysql:/etc/postfix/dbmail_domains.cf
    virtual_mailbox_maps=mysql:/etc/postfix/dbmail_mailboxes.cf
    
  7. The following files now need to be created. Update your Mysql username/password/hostname for your environment
    vim /etc/postfix/dbmail_recipients.cf
    hosts = 192.168.100.21
    dbname = dbmail
    user = dbmail
    password = password
    query = SELECT alias FROM dbmail_aliases WHERE alias='%s' LIMIT 1
    
    [root@mail ~]# vim /etc/postfix/dbmail_domains.cf
    hosts = 192.168.100.21
    dbname = dbmail
    user = dbmail
    password = password
    query = SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING_INDEX(alias, '@', -1) = '%s'
    
    [root@mail ~]# vim /etc/postfix/dbmail_mailboxes.cf
    hosts = 192.168.100.21
    dbname = dbmail
    user = dbmail
    password = password
    query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s' LIMIT 1
    
  8. Next update the DBMail settings. Again update the settings with your Mysql settings
    [root@mail ~]# vim /etc/dbmail.conf
    [DBMAIL]
    driver               = mysql
    authdriver           = sql
    host                 = 192.168.100.21
    user                 = dbmail
    pass                 = password
    db                   = dbmail
    effective_user        = dbmail
    effective_group       = dbmail
    pid_directory         = /var/run/dbmail
    
  9. Next create a directory for DBMail to put its pid file in and change it to the owner
    [root@mail ~]# mkdir /var/run/dbmail
    [root@mail ~]# chown dbmail:dbmail /var/run/dbmail/
    
  10. Reload postfix for the settings to take place and also start the dbmail-lmtp and dbmail-imapi services
    [root@mail ~]# /etc/init.d/postfix reload
    Reloading postfix:                                         [  OK  ]
    [root@mail ~]# /etc/init.d/dbmail-lmtpd start
    Starting dbmail-lmtpd:                                     [  OK  ]
    [root@mail ~]# /etc/init.d/dbmail-imapd start
    Starting dbmail-imapd:                                     [  OK  ]
    
  11. Enable the required DBMail services to start on boot
    [root@mail ~]# chkconfig dbmail-lmtpd on
    [root@mail ~]# chkconfig dbmail-imapd on
    
  12. Now we create a user
    [root@mail ~]# dbmail-users -p md5 -a peter@mytechtradie.com.au -w password -s peter@mytechtradie.com.au
    Adding INBOX for new user... ok.
    [peter@mytechtradie.com.au]
    Done
    peter@mytechtradie.com.au:x:5:0:0.00:0.00:peter@mytechtradie.com.au
    
  13. Now we have a user we can use the postmap command to check that postfix is talking to the database correctly
    [root@mail ~]# postmap -q 'peter@mytechtradie.com.au' mysql:/etc/postfix/dbmail_recipients.cf
    peter@mytechtradie.com.au
    [root@mail ~]# postmap -q 'mytechtradie.com.au' mysql:/etc/postfix/dbmail_domains.cf
    1
    [root@mail ~]# postmap -q 'peter@mytechtradie.com.au' mysql:/etc/postfix/dbmail_mailboxes.cf
    1
    
  14. Now to do a full test. Start by using telnet to send an email to the server
    [root@mx1 ~]# telnet 192.168.100.16 25
    Trying 192.168.100.16...
    Connected to 192.168.100.16.
    Escape character is '^]'.
    220 mail.onemetric.local ESMTP Postfix
    ehlo test
    250-mail.onemetric.local
    250-PIPELINING
    250-SIZE 10240000
    250-VRFY
    250-ETRN
    250-ENHANCEDSTATUSCODES
    250-8BITMIME
    250 DSN
    mail from: peter@onemetric.com
    250 2.1.0 Ok
    rcpt to: peter@mytechtradie.com.au
    250 2.1.5 Ok
    data
    354 End data with .
    test email
    .
    250 2.0.0 Ok: queued as CDC1EA04EB
    

    Now test that the email is received by using telnet and imap

    [root@mail ~]# telnet 127.0.0.1 143
    Trying 127.0.0.1...
    Connected to 127.0.0.1.
    Escape character is '^]'.
    * OK [CAPABILITY IMAP4rev1 AUTH=LOGIN AUTH=CRAM-MD5 STARTTLS ID] dbmail 3.0.2 ready.
    . LOGIN peter@mytechtradie.com.au password
    . OK [CAPABILITY IMAP4rev1 ACL RIGHTS=texk NAMESPACE CHILDREN SORT QUOTA THREAD=ORDEREDSUBJECT UNSELECT IDLE ID] User peter@mytechtradie.com.au authenticated
    . List '' '*'
    . OK LIST completed
    ? LIST "" "*"
    * LIST (\hasnochildren) "/" "INBOX"
    ? OK LIST completed
    ? Select "INBOX"
    * 2 EXISTS
    * 2 RECENT
    * FLAGS (\Seen \Answered \Deleted \Flagged \Draft)
    * OK [PERMANENTFLAGS (\Seen \Answered \Deleted \Flagged \Draft \*)] Flags allowed.
    * OK [UIDNEXT 5] Predicted next UID
    * OK [UIDVALIDITY 2] UID value
    * OK [UNSEEN 1] first unseen message
    ? OK [READ-WRITE] SELECT completed
    ? FETCH 1> All
    * 1 FETCH (INTERNALDATE "08-Jul-2013 00:02:48 +0000" RFC822.SIZE 230 FLAGS () ENVELOPE ("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL NIL NIL NIL NIL NIL))
    ? OK FETCH completed
    
  15. DBMail is now setup, next step is to get Roundcube setup.

Posts in Series