How to install DBMail with MySQL backend on Ubuntu Server 14.04 LTE

In this article I will show you step-by-step how to install and configure DBMail server on Ubuntu Server 14.04 LTE.

DBMail in my opinion is a great solution for those who want robust and scalable mail server. If you also care about things like easy management and high recoverability or high scalability DBMail is just tailor made for you.

Unfortunately it’s not a very popular solution. Perhaps it’s because of lack of good documentation especially for less experienced administrators. When I started my adventure with DBMail I really struggled to pull all information together which is scattered among many incomplete how-to’s and posts. This is really painstaking way to success and a great number of interested folks must have been thrown away because of it. It’s a shame and I decided to write my own step-by-step guide for Ubuntu Server I use.

Let’s waste no more time then and get straight to work.

Install MySQL server

Because I need also web server (webmail) the fastest solution for me was to install LAMP instead if all the servers separately. To install LAMP which is Apache web server + MySQL + PHP after logging in to the server’s console type:

tasksel install lamp-server

I assume you know who to follow the installation steps so I won’t describe them here. After the procedure you have MySQL 5.5.x server installed (and of course Apache server and PHP what is not important for now).

Change MySQL server configuration

edit /etc/mysql/my.cnf file:

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=4G 
innodb_log_file_size=1G

Save and restart MySQL server.

innodb_buffer_pool_size – should be 50-80% of available memory
innodb_log_file_size – should be 25% of innodb_buffer_pool_size

In this example I have 8GB RAM.

For some reason my MySQL server was crashing when I set innodb_log_file_size=1G so I had to comment it out. In my case it didn’t change anything (my mail server is not very heavily loaded). Anyway if you can guess possible reason and the solution to that please put it in comments below.

Create dbmail user and database and import data

mysqladmin create dbmail -u root -p

This creates a database with the name “dbmail”. Now you have to give a non-root user access to this database.

Start the MySQL command-line client as root:

 mysql -u root -p

and enter the following command:

GRANT ALL ON dbmail.* to dbmail@localhost identified by '<pass>';

Where <pass> should be replaced with the password you want for the dbmail user. After this step, the database is ready to be used by the dbmail user.

Install DBMail

Open up /etc/apt/sourses.list with your favourite editor and add a line:

deb http://debian.nfgd.net/debian/ wheezy main

then after saving from command line do:

apt-get update && apt-get install dbmail

Install also all suggested extra packages.

Fill the database with data

The next step is to create the database tables used by DBMail. Log out from the MySQL client and run the following command from the command line.

zcat /usr/share/doc/dbmail-mysql/examples/create_tables.mysql.gz  | mysql -u dbmail dbmail -p

Check if innodb_file_per_table is set. Technically you can use DBMail with the all tables stored in one file, but in real life when your mail server is under any bigger load it will cause some serious problems. So if you want to make a production installation every table should be stored in a separate innodb file.

Since you are using MySQL 5.5 you can use following SQL statement to check your database:

SELECT DISTINCT TABLE_NAME, SPACE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE 'SYS%';

The SPACE will be 0 for the global tablespace (ibdata1) and some greater number for file-per-table tablespaces.

In my case it shows:

TABLE_NAME SPACE
dbmail/dbmail_acl 1
dbmail/dbmail_messages 2
dbmail/dbmail_header 3
dbmail/dbmail_headername 4
dbmail/dbmail_headervalue 5
dbmail/dbmail_aliases 6
dbmail/dbmail_authlog 7
dbmail/dbmail_auto_notifications 8
dbmail/dbmail_auto_replies 9
dbmail/dbmail_envelope 10
dbmail/dbmail_filters 11
dbmail/dbmail_keywords 12
dbmail/dbmail_mailboxes 13
dbmail/dbmail_mimeparts 14
dbmail/dbmail_partlists 15
dbmail/dbmail_pbsp 16
dbmail/dbmail_physmessage 17
dbmail/dbmail_referencesfield 18
dbmail/dbmail_replycache 19
dbmail/dbmail_sievescripts 20
dbmail/dbmail_subscription 21
dbmail/dbmail_usermap 22
dbmail/dbmail_users 23

If you have your dbmail database installed before you did change the configuration and you have all the tables in one innodb file you can still change it to use file per table by executing MySQL query:

alter table dbmail.dbmail_messages engine=innodb;

Of course you have to do the same for every single table in dbmail database
This will extract the InnoDB table into a separate file.

Configure which of DBMail services should start

Edit the /etc/default/dbmail file.
You should uncomment lmtp and imap and if you need also pop.

# comment out to disable the pop3 server
#START_POP3D=true

# comment out to disable the imapd server
START_IMAPD=true

# uncomment to enable the lmtpd server
START_LMTPD=true

# uncomment to enable the timsieved server
#START_SIEVE=true

# comment out to enable the stunnel SSL wrapper
#START_SSL=true

# specify the filename for the pem file as
# it resides in /etc/ssl/certs
#PEMFILE="dbmail.pem"

Configure DBMail to use MySQL

You can configure DBMail to not only use MySQL server but also SQLite and PostgreSQL. In my case the DB server of the choice is MySQL.

Edit /etc/dbmail/dbmail.conf file seting mysql connection parameters like this:

dburi = mysql://localhost:3306/dbmail?charset=utf8&user=dbmail&password=secret
authdriver = sql

Obviously your password should be a bit more complicated than “secret”.

Install and configure Postfix

apt-get install postfix postfix-mysql

Edit /etc/postfix/main.cf

After installation the file should look like this:

# See /usr/share/postfix/main.cf.dist for a commented, more complete version
# Debian specific:  Specifying a file name will cause the first
 # line of that file to be used as the name.  The Debian default
 # is /etc/mailname.
 #myorigin = /etc/mailname
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
 biff = no
# appending .domain is the MUA's job.
 append_dot_mydomain = no
# Uncomment the next line to generate "delayed mail" warnings
 #delay_warning_time = 4h
readme_directory = no
# TLS parameters
 smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
 smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
 smtpd_use_tls=yes
 smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
 smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
 # information on enabling SSL in the smtp client.
smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination
 myhostname = yourdomain.com
 alias_maps = hash:/etc/aliases
 alias_database = hash:/etc/aliases
 myorigin = /etc/mailname
 mydestination = yourdomain.com, localhost, localhost.localdomain, localhost
 relayhost =
 mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
 mailbox_size_limit = 0
 recipient_delimiter = +
 inet_interfaces = all
 inet_protocols = all

Change it to look like this (do not forget to remove domain names except localhost, localhost.localdomain, localhost from mydestination):

# See /usr/share/postfix/main.cf.dist for a commented, more complete version
# Debian specific:  Specifying a file name will cause the first
 # line of that file to be used as the name.  The Debian default
 # is /etc/mailname.
 #myorigin = /etc/mailname
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
 biff = no
# appending .domain is the MUA's job.
 append_dot_mydomain = no
# Uncomment the next line to generate "delayed mail" warnings
 #delay_warning_time = 4h
readme_directory = no
# TLS parameters
 smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
 smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
 smtpd_use_tls=yes
 smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
 smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options                             = noanonymous, noplaintext
 smtpd_sasl_tls_security_options                         = noanonymous
 smtpd_tls_auth_only                                     = yes
 broken_sasl_auth_clients                                = yes
 smtpd_tls_security_level                                = may
#smtpd_data_restrictions                                 = reject_unauth_pipelining
 smtpd_helo_required                                     = yes
# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
 # information on enabling SSL in the smtp client.
smtpd_relay_restrictions =
 permit_mynetworks,
 permit_sasl_authenticated,
 defer_unauth_destination,
 reject_invalid_hostname,
 reject_non_fqdn_sender,
 reject_non_fqdn_recipient,
 reject_unknown_recipient_domain,
 reject_unauth_pipelining,
 permit
myhostname = yourdomain.com
 #alias_maps = hash:/etc/aliases
 #alias_database = hash:/etc/aliases
 myorigin =  yourdomain.com
 mydestination = localhost, localhost.localdomain, localhost
 relayhost =
 mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
 mailbox_size_limit = 0
 recipient_delimiter = +
 inet_interfaces = all
 inet_protocols = all
virtual_transport = dbmail-lmtp:127.0.0.1:24
 virtual_mailbox_domains = mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
 virtual_mailbox_maps = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf

What we have done is:

  • removed domain name from mydestinanion because it will be in mysql database
  • added virtual transport virtual_transport = dbmail-lmtp:127.0.0.1:24
  • told postfix where should it check what domain it can relay and what mailboxes exist
    virtual_mailbox_domains = mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
    virtual_mailbox_maps = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf

Let’s create necessary files.

Create the virtual mailbox file  /etc/postfix/sql-virtual_mailbox_domains.cf
and add the following:

user     = <SQL-username>
password = <SQL-password>
hosts    = <SQL-host>
dbname   = <SQL-database>
query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING_INDEX(alias, '@', -1) = '%s';

After that create the virtual mailbox file  /etc/postfix/sql-virtual_mailbox_maps.cf and add the following:

user     = <SQL-username>
 password = <SQL-password>
 hosts    = <SQL-host>
 dbname   = <SQL-database>
 query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';

Now we have to edit /etc/postfix/master.cf and add this line

dbmail-lmtp     unix    -       -       n       -       -       lmtp

at the end of the file (but you can add this statement elsewhere in the file if you find it more convenient  – Postfix won’t mind)
You can add -v flag at the end of the line if you want LMTP be very verbose – it’ll put a lot in logs:

dbmail-lmtp     unix    -       -       n       -       -       lmtp -v

We can also enable smtps:

smtps     inet  n       -       -       -       -       smtpd
 -o syslog_name=postfix/smtps
 -o smtpd_tls_wrappermode=yes
 -o smtpd_sasl_auth_enable=yes
 -o smtpd_client_restrictions=permit_sasl_authenticated,reject
 submission inet n      -       -       -       -       smtpd
 -o syslog_name=postfix/submission
 -o smtpd_tls_security_level=encrypt
 -o smtpd_etrn_restrictions=reject
 -o smtpd_enforce_tls=yes
 -o smtpd_sasl_auth_enable=yes

Configuring authentication and encryption

apt-get install sasl2-bin libsasl2-modules

Create/edit file /etc/postfix/sasl/smtpd.conf

mech_list: PLAIN LOGIN
 pwcheck_method: saslauthd

Now we need to prepare sasladthd to work with chrooted Postfix.

Create a copy of saslauthd’s config file

cp /etc/default/saslauthd /etc/default/saslauthd-postfix

and edit it:

START=yes
 DESC="SASL Auth. Daemon for Postfix"
 NAME="saslauthd-postf"      # max. 15 char.
 # Option -m sets working dir for saslauthd (contains socket)
 OPTIONS="-c -r -m /var/spool/postfix/var/run/saslauthd"        # postfix/smtp in chroot()

The -r option is required if you’re going to use usernames like user@yourdomain.com instead of user.

Create required subdirectories in postfix chroot directory:

dpkg-statoverride --add root sasl 710 /var/spool/postfix/var/run/saslauthd

Add the user “postfix” to the group “sasl”:

adduser postfix sasl

Restart saslauthd and Postfix.

Configure secure IMAP and POP3 connection

On this point we have working mail system. The thing is the transmission between the client and server in not secure. Let’s solve this problem.

Edit file /etc/default/dbmail and uncomment:

# comment out to enable the stunnel SSL wrapper
 START_SSL=true

Then edit /etc/dbmail/dbmail.conf file. Find the SSL/TLS section and set it as follows:

#
 # SSL/TLS certificates
 #
 # A file containing a list of CAs in PEM format
 tls_cafile            = /etc/ssl/certs/ssl-cert-snakeoil.pem
# A file containing a PEM format certificate
 tls_cert              = /etc/ssl/certs/ssl-cert-snakeoil.pem
# A file containing a PEM format RSA or DSA key
 tls_key               = /etc/ssl/private/ssl-cert-snakeoil.key
# A cipher list string in the format given in ciphers(1)
 #tls_ciphers           =
 hash_algorithm = SHA1

This is self generated certificate and for production environment you should rather use a certificate generated by a company like GeoTrust or whatever you like.

Restart DBMail.

If everything went good at this point you have fully functional mail server with secure connections by SMTP, POP3 and IMAP and with authentication.

Comments are welcome.

,,,,,

Comments

Leave a comment

Your email address will not be published. Required fields are marked *