====== Ubuntu - Email - Install a full mail server ======

===== Requirements =====

  * Multiple domains using this for email (e.g. @company.com, @othercompany.com, @company-other-spelling.org).
  * Webmail on your server (for anyone in the org to access email).
  * Aliases / redirects for some email addresses (e.g. so you can redirect "support@" to a particular person).
  * DO NOT create "linux users" for every email user – it’s a huge security hole, and a massive pain in the ass for the sysadmin.
  * DO NOT do mail-relaying.

----

===== What is needed =====

  * Web server [Nginx]
  * Database server (MySQL)
  * Email server (MTA) (Exim4)
  * IMAP server (Dovecot)
  * Webmail server (Roundcube)


The database server will be used to manage ALL logins and usernames/passwords.

----

===== Installation =====

You need to install ALL of:

  * apt-get install apache2-mpm-prefork\\ (Some of these email servers require PHP; PHP is crappy and requires mpm-prefork (the 'slow' version of Apache))
  * apt-get install mysql-client\\ (should auto-install something like: mysql-common + mysql-client-5.5)
  * apt-get install mysql-server\\ (should auto-install something like: mysql-server-5.5 + mysql-server-core-5.5)
  * apt-get install exim4
  * apt-get install exim4-base
  * apt-get install exim4-config
  * apt-get install exim4-daemon-heavy\\ (there's an "exim4-mysql" that might be sufficient to replace this, but I gave up: there are way too many exim4 packages, and no help for installing the "correct" set, so … just pick this and get the lot!)
  * apt-get install dovecot-core
  * apt-get install dovecot-imapd
  * apt-get install dovecot-mysql
  * apt-get install roundcube
  * apt-get install roundcube-core
  * apt-get install roundcube-mysql


----

===== Setup: DNS =====

You need an "MX" record on your DNS server, and it needs to point to your main server where you’ll run your email, web, etc.

----

===== Setup: Web server =====

Roundcube sets up an over-the-top config: it creates an email server on every single website hosted on your server, and makes them all available at once.

Following the idea of http://www.cpierce.org/2012/04/roundcube-for-your-debian-squeeze-mail-server/, I used a much simpler, easier-to-maintain, and easier-to-secure setup. This is documented in the Debian package docs too.


----

==== Create a web address for your webmail ====

If you have multiple websites hosted on your server, you SHOULD have a separate file for each inside /etc/apache2/sites-available. e.g.:

  * /etc/apache2/sites-available/domain1.com
  * /etc/apache2/sites-available/other-domain.com
  * /etc/apache2/sites-available/my-friends-domain.org

For each domain that you want to give webmail to, edit the file and ADD the following:


<file apache>
<VirtualHost *:80>
  ServerName webmail.[the domain name]
  DocumentRoot /var/lib/roundcube
</VirtualHost>
</file>

Note: replace “[the domain name]” with the domain name, e.g. "domain1.com"

----

===== Setup: create databases =====

Create your databases. From the command-line, you can do something like:

<code bash>
mysql -u root -p
</code>

…or use your preferred softare (e.g. phpMyAdmin).

----

==== Create the database ====

<code mysql>
CREATE DATABASE email_accounts;
</code>

==== Create the tables for email-accounts and config ====

<code mysql>
USE email_accounts;

CREATE TABLE mailboxes (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(10) NOT NULL,
    local_part VARCHAR(250) NOT NULL,
    password VARCHAR(100) NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP NOT NULL DEFAULT NOW(),
    modified TIMESTAMP NULL
);
CREATE TABLE aliases (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(10) NOT NULL,
    local_part VARCHAR(250) NOT NULL,
    goto VARCHAR(250) NOT NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP NOT NULL DEFAULT NOW(),
    modified TIMESTAMP NULL
);
CREATE TABLE vacations (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mailbox_id INT(10) NOT NULL,
    subject VARCHAR(250) NOT NULL,
    body TEXT NOT NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP NOT NULL DEFAULT NOW(),
    modified TIMESTAMP NULL
);

CREATE TABLE domains (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fqdn VARCHAR(250) NOT NULL,
    type ENUM('local','relay') NOT NULL DEFAULT 'local',
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP NOT NULL DEFAULT NOW(),
    modified TIMESTAMP NULL
);
</code>

----

==== Create a database-account to access the database ====

<code mysql>
grant ALL on email_accounts.* to 'email'@'localhost' identified by 'password';
flush privileges;
</code>

Note: that is not an email address, it’s a MySQL user account.
Note: this account will ONLY be accessible by our software running on the server; you cannot access this account remotely (over the internet).

----

==== Create your first email account and domain ====


<code mysql>
INSERT INTO domains VALUES(NULL,'mydomain.com','local','My nice domain for local delivery',1,NOW(),NOW());
INSERT INTO mailboxes VALUES(NULL,1,'joe',MD5('password - choose a good one'),'My account for joe@mydomain.com',1,NOW(),NOW());
</code>

Note: this password is used over the internet when you login to webmail – so pick a good one! This has to be secure!

----

==== Create a redirector for an email address ====

<code mysql>
INSERT INTO aliases VALUES (NULL, 1, 'support', 'ceo@mydomain.com', 'Redirecting support@ to the CEO. It will be a good experience', 1, NOW(), NOW() );
</code>

Note: only set this up if you actually want a redirect.

----

===== Setup: Configure Exim4 =====

When you install Exim4, make sure you chose the “split” packages. If not, you can fix that now by running:

<code bash>
dpkg-reconfigure exim4-config
</code>

----

==== Debian: set the global / initial Exim config ====

NB: these are the settings filled out by “dpkg-reconfigure exim4-config”. Here’s what your file should look like:

Edit: /etc/exim4/update-exim4.conf.conf

<file bash /etc/exim4/update-exim4.conf.conf>
# /etc/exim4/update-exim4.conf.conf
#
# Edit this file and /etc/mailname by hand and execute update-exim4.conf
# yourself or use 'dpkg-reconfigure exim4-config'
#
# Please note that this is _not_ a dpkg-conffile and that automatic changes
# to this file might happen. The code handling this will honor your local
# changes, so this is usually fine, but will break local schemes that mess
# around with multiple versions of the file.
#
# update-exim4.conf uses this file to determine variable values to generate
# exim configuration macros for the configuration file.
#
# Most settings found in here do have corresponding questions in the
# Debconf configuration, but not all of them.
#
# This is a Debian specific file

dc_eximconfig_configtype='internet'
dc_other_hostnames='[YOUR DOMAIN 1];[YOUR DOMAIN 2]'
dc_local_interfaces='127.0.0.1;[PUT YOUR SERVER's IP ADDRESS HERE]'
dc_readhost=''
dc_relay_domains=''
dc_minimaldns='false'
dc_relay_nets=''
dc_smarthost=''
CFILEMODE='644'
dc_use_split_config='false'
dc_hide_mailname=''
dc_mailname_in_oh='true'
dc_localdelivery='maildir_home'
</file>

Note: replace “[YOUR DOMAIN 1]” with e.g. “my-company.com”, or “mail.company.com” – you must have one of these for EACH of your domains which has email accounts.
Note: replace “[PUT YOUR SERVER’s IP ADDRESS HERE]” with e.g. “10.0.0.1” (whatever your public internet address is)

----

==== Setup Exim: Macros ====

ADD the following to /etc/exim4/conf.d/main/000_localmacros:

<file bash>
MAIN_LOCAL_DOMAINS = @:localhost:dsearch;/etc/exim4/virtual:${lookup mysql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_mysql:$domain}' AND type='local' AND active=1}}
</file>

ADD the following to /etc/exim4/conf.d/main/01_exim4-config_listmacrosdefs:

<file bash>
# List of domains considered local for exim. Domains not listed here
# need to be deliverable remotely.
domainlist local_domains = MAIN_LOCAL_DOMAINS

# MySQL because exim4 on Debian doesn't always add this:

MYSQL_SERVER=127.0.0.1
MYSQL_DB=email_accounts
MYSQL_USER=email
MYSQL_PASSWORD=password
hide mysql_servers = MYSQL_SERVER/MYSQL_DB/MYSQL_USER/MYSQL_PASSWORD
</file>

Note: “hide mysql_servers” isn’t “hiding” anything – it’s an ESSENTIAL step! It actually means “use this database server”. Terrible config name :(.

----

==== Setup Exim: Routers ====

CREATE the file /etc/exim4/conf.d/router/360_exim4-config_mysqlusers:

<file bash>
dovecot_user:
      driver = accept
        condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) AS goto FROM domains,mailboxes WHERE \
                   mailboxes.local_part='${quote_mysql:$local_part}' AND \
                   mailboxes.active=1 AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}{yes}{no}}
     transport = dovecot_delivery
</file>

Either DELETE this file, or comment-out all lines /etc/exim4/conf.d/router/400_exim4-config_system_aliases:

<file bash>
### router/400_exim4-config_system_aliases
#################################

# This router handles aliasing using a traditional /etc/aliases file.
#
##### NB  You must ensure that /etc/aliases exists. It used to be the case
##### NB  that every Unix had that file, because it was the Sendmail default.
##### NB  These days, there are systems that don't have it. Your aliases
##### NB  file should at least contain an alias for "postmaster".
#
# This router handles the local part in a case-insensitive way which
# satisfies the RFCs requirement that postmaster be reachable regardless
# of case. If you decide to handle /etc/aliases in a caseful way, you
# need to make arrangements for a caseless postmaster.
#
# Delivery to arbitrary directories, files, and piping to programs in
# /etc/aliases is disabled per default.
# If that is a problem for you, see
#   /usr/share/doc/exim4-base/README.Debian.gz
# for explanation and some workarounds.

#system_aliases:
#  debug_print = "R: system_aliases for $local_part@$domain"
#  driver = redirect
#  domains = +local_domains
#  allow_fail
#  allow_defer
#  data = ${lookup{$local_part}lsearch{/etc/aliases}}
#  .ifdef SYSTEM_ALIASES_USER
#  user = SYSTEM_ALIASES_USER
#  .endif
#  .ifdef SYSTEM_ALIASES_GROUP
#  group = SYSTEM_ALIASES_GROUP
#  .endif
#  .ifdef SYSTEM_ALIASES_FILE_TRANSPORT
#  file_transport = SYSTEM_ALIASES_FILE_TRANSPORT
#  .endif
#  .ifdef SYSTEM_ALIASES_PIPE_TRANSPORT
#  pipe_transport = SYSTEM_ALIASES_PIPE_TRANSPORT
#  .endif
#  .ifdef SYSTEM_ALIASES_DIRECTORY_TRANSPORT
#  directory_transport = SYSTEM_ALIASES_DIRECTORY_TRANSPORT
#  .endif
</file>

CREATE this file /etc/exim4/conf.d/router/401_exim4-config_mysql_aliases:

<file bash>
### router/401_exim4-config_mysql_aliases
#################################

# ADAM: This router handles aliasing using the proprietary mysql setup
#
# c.f. http://alex.mamchenkov.net/2010/06/24/exim-dovecot-and-mysql/
#

system_aliases:
     driver = redirect
     allow_fail 
     allow_defer
     data = ${lookup mysql{SELECT aliases.goto AS goto FROM domains,aliases WHERE \
                   (aliases.local_part='${quote_mysql:$local_part}' OR aliases.local_part='@') AND \
                   aliases.active=1 AND \
                   aliases.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}}
</file>

---- 

==== Setup exim: Transports ====

CREATE / OVERWRITE the file /etc/exim4/conf.d/transport/30_exim4-config_dovecot:

<file bash>
### transport/30_exim4-config_dovecot
#################################

#

dovecot_delivery:
     driver = appendfile
     maildir_format = true
     directory = /var/spool/mail/$domain/$local_part
     create_directory = true
     directory_mode = 0770
     mode_fail_narrower = false
     message_prefix =
     message_suffix =
     delivery_date_add
     envelope_to_add
     return_path_add
     user = mail
     group = mail
     mode = 0660
</file>

----

==== Setup exim: Auth ====

CREATE the file /etc/exim4/conf.d/auth/20_exim4-config_mysql-authenticator:

<file bash>
### AUTHENTICATIOR SECTION

auth_plain:
     driver = plaintext
     public_name = PLAIN
     server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',1) AND \
                       mailboxes.password=MD5('${quote_mysql:$auth3}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',-1) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = :
     server_set_id = $auth2

auth_login:
     driver = plaintext
     public_name = LOGIN
     server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',1) AND \
                       mailboxes.password=MD5('${quote_mysql:$auth2}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',-1) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = Username:: : Password::
     server_set_id = $auth1
</file>

----

===== Setup: Configure Dovecot =====

When installing the dovecot apts, make sure you chose the “split files” option (exactly as with Exim4). It makes life easier. If you got this wrong, run:

<code bash>
dpkg-reconfigure dovecot-core
</code>

Note: Dovecot installs with almost everything "Commented out". Many of these options exist commented-out, you should find them in the config file, and put your "new" values on the line below, so it’s easy in future to find them and see which "defaults" you changed.

----

==== Dovecot: find your "mail" linux user ====

For security, you want a "mail" user account that runs your server-software, and has restricted access to your server. Debian auto-creates this, but you need to find out what uid and gid it has.

To find these out do:

<code bash>
cat /etc/passwd
</code>

…and find the line something like:

<file bash /etc/passwd>
mail:x:8:8:mail:/var/mail:/bin/sh
</file>

the first 8 is your uid, the second 8 is your gid (could be different numbers on your server)

----

==== Dovecot: all config files ====

ADD to the file /etc/dovecot/dovecot.conf:

<file bash>
protocols = imap
listen = *, ::
</file>

Add to the file /etc/dovecot/conf.d/10-mail.conf:

<file bash>
mail_location = maildir:~
</file>

ADD to the file /etc/dovecot/conf.d/10-auth.conf:

<file bash>
!include auth-sql.conf.ext
</file>


ADD to the file /etc/dovecot/dovecot-sql.conf.ext:

<file bash>
connect = host=127.0.0.1 dbname=email_accounts user=email password=password
default_pass_scheme = MD5

password_query = SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) as `user`, mailboxes.password AS `password`,'/var/spool/mail/%d/%n' AS `userdb_home`, [YOUR UID] AS `userdb_uid`, [YOUR GID] AS `userdb_gid` FROM `mailboxes`, `domains` WHERE mailboxes.local_part = '%n' AND mailboxes.active = 1 AND mailboxes.domain_id = domains.id AND domains.fqdn = '%d' AND domains.active = 1

user_query = SELECT '/var/spool/mail/%d/%n' AS `home`, [YOUR UID] AS `uid`, [YOUR GID] AS `gid`
</file>

Note: replace [YOUR UID] and [YOUR GID] with correct numbers (that you found out using cat /etc/passwd)

----

===== Setup: Configure Roundcube =====

EDIT the file /etc/roundcube/main.inc.php:

<code bash>
$rcmail_config['default_host'] = '[YOUR MX RECORD]';
</code>

Note: replace “[YOUR MX RECORD]” with the MX address you put on your DNS server at the very start. e.g. "mail.my-domain.com".

In that file, there are instructions on how to make it automatically calculate the address using %n, %d, etc. If your MX records for your different domains follow the same pattern (e.g. they are all “mail.my-domain.com”), and your webmail login addresses all follow the same pattern (e.g. "wemail.my-domain.com"), you can put one string here and it will automatically log people into the right server in every case, based on the URL they visited.

----

===== Restart EVERYTHING =====

Now you’ve set it up, you MUST restart the web and email servers.

You must ALSO do this everytime you change any config files!

<code bash>
/etc/init.d/apache2 restart
/etc/init.d/exim4 restart
/etc/init.d/dovecot restart
</code>


Exim may output a "paniclog". If so, read it, fix it – and then manually delete the paniclog file, or else you’ll keep getting fake warnings every time you restart exim.

----

===== Debugging – making it work! =====

You’ve got a lot to test here!

==== Test exim ====

=== receiving emails ===

Pick an email address that you added to the "email_accounts" database, and try sending email to it while logged-in to server command-line:

<code bash>
exim -d -bt testname@yourdomain.com
</code>

…this will give a COMPLETE list of what exim is doing, and it will tell you every decision it made along the way. It should eventually decide the address is “routeable” and OK it.

If that looks OK, try sending an email from your normal email account (e.g. your Hotmail / Gmail / Yahoo.com address). Wait a minute, then check the server to see if it crashed trying to receive the email, by checking the logfiles.


=== Check exim’s logfiles ===

Exim will put its logfiles in /var/log/exim4. Check for errors using:

<code bash>
tail /var/log/exim4/mainlog
</code>

(if there’s a lot of errors, you’ll have to cat the whole thing)

If it rejected the email, it will send a bounce-back to your email provider (yahoo/gmail/etc), and it will ALSO put some info into:

<code bash>
tail /var/log/exim4/rejectlog
</code>


=== sending emails ===

…I waited until I had webmail (Roundcube) working before trying this…


=== Any other Exim problems? ===

If exim is working, but its blocking/rejecting/losing emails, it will "freeze" them after the first failure. You need to "unfreeze" (i.e. retry) each email to see if you’ve fixed the problem.

How?

Here is a list of commands to help: http://bradthemad.org/tech/notes/exim_cheatsheet.php

=== Test Dovecot ===

Dovecot’s maintainers have written [[http://wiki2.dovecot.org/TestInstallation|an excellent step-by-step guide to testing it, with copy/pasteable command-lines]].

Note: to make this work, I had to install telnet: “apt-get install telnet-client”


=== Test Roundcube ===

Go to the web-address you configured at the very start (e.g. "webmail.your-domain.com"). It should give you a login page for Roundcube.

Login using the user-account you crated in MySQL at the start, using the FULL email address, e.g.:

Username: "joe@mydomain.com"
Password: "password – choose a good one"

If you set things up correctly, following my steps above, it should NOT ask you for an IMAP server. If it does … go back and read this post more carefully.

You should find yourself in webmail, able to send emails, and receive them.


==== If it all works … speed it up! ====

Out of the box, Roundcube runs very, very, very slowly … because it checks lots of different passwords before asking MySQL to check the password.

Fortunately there’s a very quick fix here: http://jrs-s.net/2013/07/14/slow-performance-with-dovecot-mysql-roundcube/.

After doing that, I found webmail go from “takes 5 seconds per click” to “most clicks have immediate effect” (on my fast broadband).

----

===== What you should do next… =====

This setup gets you decent, working, webmail. This is the hardest bit!

But it’s missing some core features you’ll want to add next:

  - Reduce incoming spam: install SpamAssassin or similar
  - Secure the webmail connection: buy an SSL certificate, install it in Apache, force webmail to use SSL/TLS.
  - Secure the IMAP connection: the setup above allows anyone to IMAP to the server from public internet. This allows you to use Outlook etc as a mail client. But if you *only* want to allow Webmail, you can edit your Dovecot configs and change the “listen” setting to only listen on 127.0.0.1 / localhost. This will allow Roundcube to connect (it’s on the same server) but will block internet clients.

…those should be easy to find separate guides for. Good luck.

----

===== 2016 Update =====

Changes needed for Ubuntu 15.10 (may be needed for some other Debian's, but I didn’t need them with stock Debian):

Only two things I might add:

  - In the file /etc/dovecot/conf.d/auth-sql-conf.ext uncomment driver and set it to mysql
  - /etc/dovecot/conf.d/10-mail.conf uncomment first_valid_uid and set it to [your_uid] (ie. 8). If you need to do the same for first_valid_gid

----

===== Comments =====

  - In the file /etc/dovecot/conf.d/auth-sql-conf.ext uncomment driver and set it to mysql
  - /etc/dovecot/conf.d/10-mail.conf uncomment first_valid_uid and set it to [your_uid] (ie. 8). If you need to do the same for first_valid_gid

Option “CONCAT” unknown. Usually due to incorrect version of exim4.

----

===== References =====


http://t-machine.org/index.php/2014/06/27/webmail-on-your-debian-server-exim4-dovecot-roundcube/

https://weijl.org/virtual-domains-with-exim4-dovecot-dspam-and-mysql/

http://www1.alx.pl/w/linux/exim-sql.conf

http://alex.mamchenkov.net/2010/06/24/exim-dovecot-and-mysql/