Geek Culture
Published in

Geek Culture

How to Host a Personal Email Server on Google Cloud (for Free!): Part IV

Managing Virtual Mailboxes with MariaDB & Postfixadmin

Articles in this series

  1. Introduction & GCP Setup
  2. Configuring Postfix, Mailgun, & DNS Records
  3. Configuring Dovecot & Encryption
  4. Managing Virtual Mailboxes with MariaDB & Postfixadmin
  5. Hosting Webmail with Roundcube
  6. Filtering Spam with Rspamd & Sieve

If you have not read the previous articles in this series, please follow the links above to catch up. At this point, we have our server running on GCP configured to send and receive email via the SMTP protocol and allow IMAP connections, encrypting traffic with TLS. Plus our DNS records are configured properly to ensure email is delivered to & from us. The next step is to setup virtual mailboxes. For that, we will install MariaDB database software and Postfixadmin, software for managing virtual email domains, users, & aliases. Let’s dive in!

Installing MariaDB

We need a place to store our virtual domains, mailboxes/users, and aliases. For that we will setup a database. I have chosen MariaDB because it has a small memory footprint, and on our GCP free tier VM, RAM is a valuable commodity. You could choose another database solution if you prefer and adjust the following steps accordingly. Now let’s install MariaDB.

sudo apt install mariadb-server -y

Then, run the provided security script to secure our MariaDB installation.

sudo mysql_secure_installation

When prompted for a password for root, just press Enter as one is not set. You will be told that your root account is already protected, so you can safely answer ’n’ to the next two questions. In MariaDB 10.4.3 and later, unix_socket authentication (a password-less authentication mechanism) is enabled for root by default, so answering ’n’ to these questions doesn’t change anything. Answer ‘y’ to the remaining four questions to remove anonymous users, disallow remote root login, remove test database and access to it, and reload privilege tables. This will tighten our security and remove the pre-installed ‘test’ database.

Installing Postfixadmin

Postfixadmin is a web portal written in PHP, therefore we need to install web server software to host it as well as PHP. I will be using Nginx, but if you wish to use Apache, you can adjust the instructions as you follow along. Let’s install the necessary software.

sudo apt install nginx php-fpm -y

Now we can enter our mail subdomain into a browser and be greeted with the Nginx welcome page!

Nginx created the route /var/www/html/. Any files you host via Nginx should reside in the /var/www/ directory. Let’s go ahead and navigate to /var/www/ and rename the html directory to mail, as we will be serving only email related files from here. We can leave the index.nginx-debian.html file for now.

cd /var/www/
sudo mv html/ mail/

Next, we will download and install Postfixadmin from the Github repo. In order to download the files, we need to install wget.

sudo apt install wget

Then, navigate to the /srv/ directory and run the following commands to download, unzip, and unarchive the Postfixadmin files.

cd /srv/
sudo wget -O postfixadmin.tgz https://github.com/postfixadmin/postfixadmin/archive/postfixadmin-3.3.10.tar.gz
sudo tar -zxvf postfixadmin.tgz

NOTE: At the time of writing, the latest version of Postfixadmin is 3.3.10. You may wish to update the previous command to download the latest version available to you.

Delete the zip file and rename the folder to postfixadmin. Then, create a symlink to the /srv/postfixadmin/public/ folder in our /var/www/mail/ directory in order to host our admin portal at mail.example.com/admin.

sudo rm postfixadmin.tgz
sudo mv postfixadmin-postfixadmin-3.3.10/ postfixadmin/
sudo ln -s /srv/postfixadmin/public/ /var/www/mail/admin

Setting Up the Database

Now we need to create a database and user for all of our mail software to use. Run mysql.

sudo mysql

Then, run the following commands to create our database and user with proper permissions. Replace password with your chosen password.

CREATE DATABASE postfix;
CREATE USER postfix@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON postfix.* TO postfix@localhost;
flush privileges;
exit;

Configuring Postfixadmin

Next, we need to configure Postfixadmin to use the database. Create the file /srv/postfixadmin/config.local.php containing our local configuration.

<?php
$CONF['database_type'] = 'mysqli';
$CONF['database_host'] = 'localhost';
$CONF['database_user'] = 'postfix';
$CONF['database_password'] = 'password';
$CONF['database_name'] = 'postfix';
$CONF['encrypt'] = 'dovecot:SHA512';
$CONF['configured'] = true;
?>

We need to create the /srv/postfixadmin/templates_c/ directory to be used by PostfixAdmin. It should be owned by the web server user created by Nginx (www-data).

sudo mkdir /srv/postfixadmin/templates_c/ && sudo chown -R www-data /srv/postfixadmin/templates_c/

Configuring Nginx

Now we need to configure Nginx to serve from our renamed mail directory and to serve the PHP files, as this is not active by default. We will also configure it to use our TLS certificate to enforce encrypted traffic as we never want to send login credentials over non-encrypted HTTP. Let’s edit /etc/nginx/sites-available/default.

First, comment out the following lines:

listen 80 default_server;
listen [::]:80 default_server;

Then, uncomment the following lines;

listen 443 ssl default_server;
listen [::]:443 ssl default_server;

We need to add the location of our TLS certificate and key.

ssl_certificate /etc/letsencrypt/live/mail.example.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/mail.example.com/privkey.pem;

Then, update the root folder location to our renamed mail directory and add index.php to the list of index files to serve.

root /var/www/mail;# Add index.php to the list if you are using PHP
index index.php index.html index.htm index.nginx-debian.html;

Uncomment the following lines to enable php-fpm.

location ~ \.php$ {
include snippets/fastcgi-php.conf;
fastcgi_pass unix:/run/php/php7.4-fpm.sock;
}

Finally, we will create a server configuration to listen to unencrypted HTTP request on port 80 and redirect the request to port 443 to force only encrypted connections. Add the following lines to the file, then save and exit.

server {
listen 80;
listen [::]:80;
server_name _; return 301 https://$host$request_uri;
}

Reload Nginx to use our new configuration.

sudo service nginx reload

Now ensure that we can access Postfixadmin over HTTPS by navigating to the setup page that we will use in the next step (i.e. https://mail.example.com/admin/setup.php).

Configuring Postfixadmin some more

Generate a setup password on the setup page, and add the resulting line to our config.local.php file. Then, refresh the page and login with the setup password.

This page is very handy. It shows us what is working and what is not.

You can ignore two of the Warnings as they relate to missing database servers and extensions that we are not using. However, the final warning should be remedied as we want to automate the creation of our default folders for new mailboxes. We also have to remedy all issues listed under Errors.

Please note that in the Information section, we get checkmark for having a MySQL database running, but we are missing the required extension. We will have to install it while resolving the errors as well.

Run the following command to install the missing PHP modules.

sudo apt install php-mysql php-imap php-mbstring -y

If you refresh the page now, you will see that the only problem remaining is a Password Hashing error. This is because Postfixadmin does not have access to our TLS certificate nor Dovecot’s stats-writer. To remedy this will need to first update our Access Control Lists to provide our www-data user with the proper permissions. This will allow us to have finer control over file permissions. We will need the acl package to do this.

sudo apt install acl

Now, rather than making www-data the owner of the certificate directories, we can simply provide read & execute permissions to the www-data user. We must do so for both the live/ and archive/ directories.

sudo setfacl -R -m u:www-data:rx /etc/letsencrypt/live/ /etc/letsencrypt/archive/

Then, we need to edit /etc/dovecot/conf.d/10-master.conf, adding the following to the end of the file.

service stats {
unix_listener stats-reader {
user = www-data
mode = 0660
}
unix_listener stats-writer {
user = www-data
mode = 0660
}
}

Finally, add the www-data user to the dovecot group and restart Dovecot.

sudo usermod -aG dovecot www-data
sudo service dovecot restart

Refresh the page once again, and Voila! We are error free! 😃 Now we need to add an admin account that we will use to login to Postfixadmin. I like to use an admin address that I then setup as an alias to my email address, but you may choose to simply use your email addess.

Afterwards, we can navigate to our admin web portal (i.e. https://mail.example.com/admin) and login with our newly created admin account.

Creating Virtual Domains & Mailboxes

Now we can use Postfixadmin’s wonderful web interface to manage our domain, mailboxes/users, & aliases.

Select Overview and then Add Domain. Type in your domain and adjust the settings according to your needs.

Then, navigate to Virtual List and scroll down to the Mailboxes section, and select Add Mailbox. (We will update our default mail aliases momentarily.) Fill out the information and select Add Mailbox. Username will be the part of your email address that proceeds your domain (i.e. some.user@example.com).

Next, navigate back to Virtual List and update each alias to forward mail to your newly created address. We now have a working virtual mailbox! You can add/remove/edit domains, mailboxes, and aliases as needed at anytime using your new admin web portal.

We are so close now! The final step is to configure Postfix and Dovecot to use virtual mailboxes via our MariaDB database.

Configuring Postfix to use MariaDB

First, we need to install the postfix-mysql package so Postfix can communicate with our database.

sudo apt install postfix-mysql

Create a directory to store our SQL configuration files.

sudo mkdir /etc/postfix/sql/

Then we need to edit /etc/postfix/main.cf, adding/editing the following lines:

#mailbox_transport = lmtp:unix:private/dovecot-lmtp#mydestination = $mydomain, localhostvirtual_transport = lmtp:unix:private/dovecot-lmtpvirtual_mailbox_domains =
proxy:mysql:/etc/postfix/sql/virtual_domains_maps.cf
virtual_mailbox_maps =
proxy:mysql:/etc/postfix/sql/virtual_mailbox_maps.cf,
proxy:mysql:/etc/postfix/sql/virtual_alias_domain_mailbox_
maps.cf
virtual_alias_maps =
proxy:mysql:/etc/postfix/sql/virtual_alias_maps.cf,
proxy:mysql:/etc/postfix/sql/virtual_alias_domain_maps.cf,
proxy:mysql:/etc/postfix/sql/virtual_alias_domain_catchall_
maps.cf

Let’s break down these variables.

  • virtual_mailbox_domains tells Postfix how to look up the virtual domains in the database
  • virtual_mailbox_maps tells Postfix how to look up virtual mailboxes in the database
  • virtual_alias_maps tells Postfix how to look up the aliases in the database
  • virtual_transport tells Postfix to use Dovecot to deliver mail to our virtual mailboxes

NOTE: You may comment out or remove the lines setting mailbox_transport and mydestination as they are no longer necessary since we are using virtual domains & mailboxes, but it is not necessary and will have no impact on behavior.

Now we need to create the config files that we have referenced. Create /etc/postfix/sql/virtual_domains_maps.cf, adding the following lines (replacing password with the password that you set up when creating the postfix database user):

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s' AND active = '1'

Create /etc/postfix/sql/virtual_mailbox_maps.cf and write:

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username='%s' AND active = '1'

Create /etc/postfix/sql/virtual_alias_domain_mailbox_maps.cf and write:

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT maildir FROM mailbox,alias_domain WHERE alias_domain.alias_domain = '%d' and mailbox.username = CONCAT('%u', '@', alias_domain.target_domain) AND mailbox.active = 1 AND alias_domain.active='1'

Create /etc/postfix/sql/virtual_alias_maps.cf and write:

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

Create /etc/postfix/sql/virtual_alias_domain_maps.cf and write:

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

Create /etc/postfix/sql/virtual_alias_domain_catchall_maps.cf and add the following:

user = postfix
password = password
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

Because these files contain the database password in plain text, we should restrict access permissions to only our root and postfix users.

sudo chmod 640 /etc/postfix/sql/*
sudo setfacl -R -m u:postfix:r /etc/postfix/sql/*

Now edit /etc/postfix/main.cf one last time. Add the following lines:

virtual_mailbox_base = /var/vmail
virtual_minimum_uid = 5000
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000

Here, we are telling Postfix where to store our mail for our virtual mailboxes and what system user will own them. Now we need to create the vmail user & group and /var/vmail location, giving ownership to the user. We will disable login and prevent a home folder from being located.

sudo adduser vmail --system --group --uid 5000 --disabled-login --no-create-homesudo mkdir /var/vmail/ && sudo chown -R vmail:vmail /var/vmail/

Restart Postfix.

sudo service postfix restart

Configuring Dovecot to use MariaDB

We have reached the final step before being able to send and receive mail from our server through an email client! We must configure Dovecot to use our virtual mailboxes via MariaDB. First, we must install MySQL support for Dovecot.

sudo apt install dovecot-mysql

Then, edit /etc/dovecot/conf.d/10-mail.conf. Since our virtual mailboxes are not attached to an actual local user, we need to add the following line:

mail_home = /var/vmail/%d/%n

This will create directories under /var/vmail/ in domain/username/ hierarchy.

Edit the following lines in /etc/dovecot/conf.d/10-auth.conf to use user information from our database and prevent local unix users from sending email.

#!include auth-system.conf.ext
!include auth-sql.conf.ext

Now edit /etc/dovecot/dovecot-sql.conf.ext. Uncomment/edit the following lines (or just copy & paste):

driver = mysqlconnect = host=localhost dbname=postfix user=postfix password=passworddefault_pass_scheme = SHA512password_query = SELECT username AS user,password FROM mailbox WHERE username = '%u' AND active='1'user_query = SELECT maildir, 2000 AS uid, 2000 AS gid FROM mailbox WHERE username = '%u' AND active='1'iterate_query = SELECT username AS user FROM mailbox

Restart Dovecot.

sudo service dovecot restart

Connecting via an Email Client

Great job! We can now connect to our server and send/receive email via desktop and mobile email clients. The process is essentially the same across clients, but may vary slightly. These are the general steps:

  1. Select Add new account.
  2. If selecting Account type, choose Other or IMAP.
  3. Enter your email address (setup in Postfixadmin).
  4. Enter your password.
  5. Set the Incoming (IMAP) & Outgoing (SMTP) servers to your subdomain (i.e. mail.example.com).
  6. If required, set the Incoming port to 993 and the Outgoing port to 587 (STARTTLS) or 465 (SSL/TLS).

If you have trouble signing into either one, you can add the following lines to /etc/dovecot/conf.d/10-auth.conf. This will log authentication issues to /var/log/mail.log. Be sure to comment out or remove the lines when you are done diagnosing the problem.

auth_debug = yes
auth_debug_passwords = yes

Now send yourself an email and bask in your accomplishment!

PRO TIP: You can check that your email will avoid the spam folder by testing your configuration with mail tester!

Auto-Renew TLS Certificate

Let’s Encrypt certificates expire after 90 days. If ours expires, we will not be able to access our mailbox from an email client. Let’s Encrypt is kind enough to include an auto-renewal service with installation. However, it is configured according to the command used to request and install the certificate. Since we installed our certificate before installing Nginx, we need to reconfigure the service to use the Nginx plugin when renewing. First, install the Nginx plugin. (If you chose to use Apache, then install python3-certbot-apache instead.)

sudo apt install python3-certbot-nginx -y

Then, simply renew the certificate using the plugin. Because the certificate is not actually due for renewal yet, we must use the --force-renewal option.

sudo certbot renew --nginx --force-renewal

Now you’re TLS certificate will always be up-to-date! 😃

Conclusion

Congratulations! You are officially the proud owner of a private email server hosted in the cloud, and at absolutely no cost! This is no small feat, so take a moment to appreciate what you have accomplished. Then, let’s review what we covered in this article.

  • We setup MariaDB as our database to store our virtual mailboxes.
  • We setup Nginx and PHP to serve Postfixadmin over the web.
  • We setup Postfixadmin to manage our virtual domains, mailboxes & aliases.
  • We configured Postfix & Dovecot to use our virtual mailboxes via MariaDB.
  • We connected our email client to our server and confirmed that we can send & receive emails.
  • We configured certbot to automatically renew our TLS certificate.

Our series is coming to a close. There are only two articles left! In the next article, we will install Roundcube to provide a webmail client. Then, in the final article we will install Rspamd & Sieve in order to detect spam and reject or send it to the Junk folder.

Thank you for reading! If you found this article helpful and are interested in following the rest of the series, please clap and follow to be updated when the upcoming pieces are published.

--

--

--

A new tech publication by Start it up (https://medium.com/swlh).

Recommended from Medium

Say Hello to Big-data Performance Testing

🔰 Create High Availability Architecture with AWS CLI 🔰

Modeling Data in Applications using Oracle DataModeler

Sign your git commits using your Yubikey + GPG (OS X)

Optimize transportation combination for orders in Excel

Rails Threading Gains

How Flutter Can Reduce App Development Cost, Time & Efforts

Common Guidelines of Software Teams

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lee Phillips

Lee Phillips

Software developer. Flutter fanatic. Other interests include photography, sports, coffee, and food.

More from Medium

How to Host a Personal Email Server on Google Cloud (for Free!): Part I

WordPress attacks & Cloud Armor protection on GCP— True story.

Working with Terraform as a Team using terraform_remote_state

Building Sustainable Software and IoT Products