Installing a mailserver on Debian 8/9 – Part 2: Preparations: Apache, Let’s Encrypt, MySQL and phpMyAdmin

How to install a complete mailserver on Debian 8/9, featuring Postfix, Dovecot, MySQL, Spamassassin, ClamAV, Roundcube and Fail2ban.

~ the howto that actually works ~

Part 1: Introduction
Part 2: Preparations: Apache, Let’s Encrypt, MySQL and phpMyAdmin
Part 3: MTA: Postfix
Part 4: IMAP server: Dovecot
Part 5: Web interface: Roundcube
Part 6: Spam filtering: SpamAsasssin
Part 7: Antivirus: ClamAV and ClamSMTP
Part 8: Quota and other Roundcube settings
Part 9: Using mail with a remote IMAP client (i.e. Thunderbird)
Part 10: Counter brute-force attacks with Fail2ban
Part 11: Sources, config files, colouring and comments

Setting up Apache

If you haven’t installed Apache and PHP yet do so now by running

# tasksel

and selecting Web server. If you want to do it manually install Apache2 and PHP.

Creating a website
Open /etc/apache2/sites-available/000-default.conf and change

#ServerName www.example.com

ServerName example.com

Reload Apache:

# service apache2 reload

Verify your site is reachable from the internet on http://example.com. You should see the Apache2 Debian Default Page. If not, check /var/log/apache/error.log.

Let’s Encrypt

You can purchase commercial certificates. If you run a business you should at least consider it. Alternatively use self-signed certificates – those would be most appropriate if you’re running everything on an intranet and/or have your own corporate CA.

Thanks to the Let’s Encrypt initiative we can now have free of charge certificates signed by a trusted third party and that’s what I’ll describe here.

Your mail domain vs. your website’s domain
It is important to understand that your website’s domain does not need to equal your mail domain. It is perfectly valid to host mail for aaa.com, bbb.com with a web interface on ccc.com. The SSL certificates you would be using would be those for ccc.com and in configuration files (e.g. /etc/dovecot/conf.d/10-ssl.conf) you would need to enter

ssl_cert = </etc/letsencrypt/live/ccc.com/fullchain.pem
ssl_key = </etc/letsencrypt/live/ccc.com/privkey.pem

The certificates you are receiving from Let’s Encrypt need to reflect your website’s domain, which may be identical to your mail domain but it certainly is not necessary.

Letsencrypt
This part is for Debian 9. If you’re running Debian 8, follow the backports instructions.
Install Letsencrypt:

# aptitude install python-certbot-apache

Run Letsencrypt for Apache:

# certbot --apache

Note – 2018-02-13: Let’s Encrypt have changed their system somewhat, you now have to do:

# certbot --authenticator webroot --installer apache --webroot-path /var/www/html -d your.domain.name

Create an autorenew script. In /root/renew-letsencrypt.sh:

#! /bin/bash
certbot renew -q

Make the script executable:

# chmod +x /root/renew-letsencrypt.sh

Use cron to schedule the script to run twice a day.

Backports – only for Debian 8
For Debian 8 you need to install from the backports. Aptitude will give you a bunch of options for resolving dependencies. Choose the one that does not leave any dependency unmet – for me that was the third option but that is likely to change per system and over time. I needed to let it install 8 packages.

** NOTE 2016-06-07: Jessie’s backports currently holds broken dependencies for Letsencrypt. If you are unable to install Letsencrypt using the method described below, do:

# aptitude install certbot python-certbot-apache

END OF NOTE ***

Install Let’s Encrypt:

# aptitude install letsencrypt python-letsencrypt-apache

The following actions will resolve these dependencies:

     Keep the following packages at their current version:
1)     letsencrypt [Not Installed]
2)     python-acme [Not Installed]
3)     python-letsencrypt [Not Installed]
4)     python-letsencrypt-apache [Not Installed]



Accept this solution? [Y/n/q/?] n

The following actions will resolve these dependencies:

     Install the following packages:
1)     python-cffi-backend [1.4.2-2~bpo8+1 (jessie-backports)]
2)     python-cryptography [1.1.1-1~bpo8+1 (jessie-backports)]
3)     python-enum34 [1.0.3-1 (stable)]
4)     python-idna [2.0-3~bpo8+1 (jessie-backports)]
5)     python-ipaddress [1.0.16-1~bpo8+1 (jessie-backports)]
6)     python-openssl [0.15.1-2~bpo8+1 (jessie-backports)]
7)     python-pyasn1 [0.1.9-1~bpo8+1 (jessie-backports)]

     Keep the following packages at their current version:
8)     python-cffi [Not Installed]



Accept this solution? [Y/n/q/?] n

The following actions will resolve these dependencies:

     Install the following packages:
1)     python-cffi [1.4.2-2~bpo8+1 (jessie-backports)]
2)     python-cffi-backend [1.4.2-2~bpo8+1 (jessie-backports)]
3)     python-cryptography [1.1.1-1~bpo8+1 (jessie-backports)]
4)     python-enum34 [1.0.3-1 (stable)]
5)     python-idna [2.0-3~bpo8+1 (jessie-backports)]
6)     python-ipaddress [1.0.16-1~bpo8+1 (jessie-backports)]
7)     python-openssl [0.15.1-2~bpo8+1 (jessie-backports)]
8)     python-pyasn1 [0.1.9-1~bpo8+1 (jessie-backports)]



Accept this solution? [Y/n/q/?] y

Enable encryption on your site:

# letsencrypt run

The installer asks for your e-mail address and wether you would like to redirect all HTTP requests to HTTPS. Since we’ll be using the site as a web interface for a mailserver I suggest you do.

Verify your site is now available at https://example.com.

Schedule the Let’s Encrypt update to run every week:

# crontab -e

Enter this line:

@monthly /usr/bin/letsencrypt renew –-agree-tos

I recommend you read up on Let’s Encrypt renewals here: https://letsencrypt.org/getting-started/ and in the letsencrypt manpage because Let’s Encrypt is still in development.

Install MySQL

In the original text of this tutorial I used MySQL. With Debian 9 I have switched to MariaDB. MariaDB is for the most part a drop-in replacement for MySQL: you can swap them with a almost no side effects. One thing both database systems do since some time is they perform socket logins by default; what this means in practice is that if you log on as the Linux root user (or with sudo) you don’t get asked for the password, even if you set one. Another difference is that they don’t allow logging in a non-root user. That’s easy to change but a) unnecessary and b) less secure.

Both MySQL and MariaDB work. I let the MySQL part remain here for archeological purposes.

# aptitude install mysql-client mysql-server

# aptitude install mariadb-client mariadb-server

For Debian 8: Remember the root password you enter.

Optional: setting up PhpMyAdmin for database administration

PhpMyAdmin is a webbased database administration tool. Its installation is optional but I like it because it gives a good overview of your databasian situation.

As discussed, the newer versions don’t allow non-root logins so we’ll create a dedicated PhpMyAdmin user:

# mysql
> GRANT ALL PRIVILEGES ON *.* TO 'somename'@'localhost' IDENTIFIED BY 'P@ssw0rd';
> FLUSH PRIVILEGES;
> QUIT

By including the ‘identified by’ clause MariaDB assumes we’re either creating and setting a password for the user or changing the existing user’s password, while granting the privileges. Replace somename with a name of your choice. I would suggest not choosing ‘phpmyadmin’ as the username as that would be easy to guess. Use that username and password to login to PhpMyAdmin.

# aptitude install phpmyadmin

Choose the Apache webserver when asked, unless you know you’re running a different webserver.

Have the installer create a database for you.

Use the root password you entered when installing mysql-server. (Not necessary on newer systems.)

Enter MySQL's root password — Enter MySQL’s root password

Enter an application password for phpMyAdmin when the installer asks for it. You do not need this password afterwards so a random password is ok. Just leave the field empty.

After installation you can find phpMyAdmin at https://example.com/phpmyadmin. You can log in with user root and the MySQL root password.

For extra security, deny access to phpMyAdmin from outside your own network by editing /etc/phpmyadmin/apache.conf. Below <Directory /usr/share/phpmyadmin> insert these lines:

Order deny,allow
# Allow from 192.168.1.1/24
# Allow from 192.168.1.1/255.255.255.0
# Allow from 12.34.56.78
# Allow from 12.34.56.78 87.65.43.21
Deny from all

Uncomment and edit any appropriate Allow lines. Lines 1 and 2 are equal, only the notation differs. Afterwards reload Apache to effectuate the settings:

# service apache2 reload

Always verify your settings take: go to https://example.com/phpmyadmin from an address not listed as allowed and make sure you can’t get in.

Creating the user database

There is a near infinite number of ways to set up your database. I’ll be working with two tables: one for virtual users and one for virtual aliases. (More on virtual users later.)

A couple of notes on your database design.

Choose UTF8 as the password collation. This will allow weird ascii characters to be used.
Don’t worry if phpMyAdmin says your collation is Swedish – this is a phpMyAdmin quirk that occurs because it adds up all the collations and then finds the one that corresponds. Just ignore it.
Email address length can be bigger but you’ve got to draw the line somewhere and I think fifty characters is enough.
varchar(106) for the password field allows it to be encrypted with SHA512.
“password” is a protected word in MySQL so it’s best not to confuse things unnecessarily and avoid it. That’s why I named the table ‘pwd’.
In principle the e-mail address is unique so it could serve as a primary key. However e-mail addresses may change and besides it is best practice to have a dedicated primary key.
If you’re going to use this for a production server think very carefully about your database setup. A small choice now can have a huge effect later.

Option 1: from the command line
Log into MySQL:

# mysql -u root -p

For the next lines be careful to use backticks ( ` ) where needed.

Creating the databases from the command line

mysql> CREATE DATABASE postfix;
mysql> USE postfix;

Create the user table:

mysql> CREATE TABLE `addresses` (
`id` int NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `email` varchar(50) NOT NULL,
  `pwd` varchar(106) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
quit

Create the aliases table:

CREATE TABLE `aliases` (
`id` int NOT NULL AUTO_INCREMENT,
  `source` varchar(50) NOT NULL,
  `target` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> quit

Creating the databases from phpMyAdmin

Select the Databases tab and create a database called postfix. Set the collation to utf8_general_ci.

Select the new table.

Create a table called addresses with 4 columns.

Create these colums:
name: id; type: INT; Index: PRIMARY, A_I (=auto-increment)
name: active; type: TINYINT; length: 1
name: email; type: VARCHAR; length: 50; index: UNIQUE
name: pwd; type: VARCHAR; length: 106

Verify the settings by viewing the new table’s structure.

View the table's structure — View the table’s structure

Note the Primary and Unique properties.

Create a second table.

Create these columns:

Table name: aliases, 3 columns
Name: id; type: INT; Index: PRIMARY
Name: source; type: VARCHAR; length: 50
Name: target; type: VARCHAR; length: 50

View the new table’s structure.

View the aliases table's structure — View the aliases table’s structure

Verify all settings are correct.

Setting permissions from the command line

CREATE USER 'mailman'@'127.0.0.1' IDENTIFIED BY '***';
GRANT SELECT ON `postfix`.`addresses` TO 'mailman'@'127.0.0.1';
GRANT SELECT ON `postfix`.`aliases` TO 'mailman'@'127.0.0.1';
quit

Setting permissions from phpMyAdmin

Click on the home icon in the top left corner of phpMyAdmin to return to the start page.

Select the Users tab.

Click Add user.

Create a user called mailman with host 127.0.0.1. For all values select ‘Use text field’. Remember the password (in these articles I’m using P@ssw0rd as an example). Then scroll down and press Go.