Purpose

To create a personal email service

How to

Creating MySQL user

dnf install postfix dovecot dovecot-mysql postfix-mysql mutt
sudo mysqladmin -u root -p create mailserver
sudo mysql -u root -p

Creating the database and tables

/*! Create the MySQL user and grant the new user permissions over the database. */
GRANT SELECT ON mailserver.* TO 'mailuser'@'localhost' IDENTIFIED BY 'mailuserpass';
FLUSH PRIVILEGES;
USE mailserver;

/*! Create a table for the domains that will receive mail */
CREATE TABLE `virtual_domains` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*! Create a table for all of the email addresses and passwords */
CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(106) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*! Create a table for the email aliases */
CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `source` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*! Add the domains */
INSERT INTO `mailserver`.`virtual_domains`
  (`id` ,`name`)
VALUES
  ('1', 'mkmark.net');
  
/*! Add the users */
INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'info@mkmark.net'),
  ('2', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'noreply@mkmark.net'),
  ('101', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'mark@mkmark.net');
  
/*! Add the alias */
INSERT INTO `mailserver`.`virtual_aliases`
  (`id`, `domain_id`, `source`, `destination`)
VALUES
  ('1', '1', 'markma@mkmark.net', 'mark@mkmark.net'),
  ('2', '1', 'mark.ma@mkmark.net', 'mark@mkmark.net');

note the password can also be generated from python

import crypt
import hashlib
import random
salt = hashlib.sha1(str(random.random()).encode()).hexdigest()[0:16]
print(crypt.crypt("password", "$6$"+salt+"$"))

Postfix

modified and added files: (see reference for detail)

/etc/postfix/main.cf
/etc/postfix/mysql-virtual-mailbox-domains.cf
/etc/postfix/mysql-virtual-mailbox-maps.cf
/etc/postfix/mysql-virtual-alias-maps.cf
/etc/postfix/mysql-virtual-email2email.cf
/etc/postfix/master.cf

Dovecot

modified and added files: (see reference for detail)

/etc/dovecot/dovecot.conf
/etc/dovecot/conf.d/10-mail.conf
/etc/dovecot/conf.d/10-auth.conf
/etc/dovecot/conf.d/auth-sql.conf.ext
/etc/dovecot/conf.d/10-master.conf
/etc/dovecot/conf.d/10-ssl.conf
/etc/dovecot/dovecot-sql.conf.ext

Post processing

sudo chown -R vmail:dovecot /etc/dovecot
chown -R vmail:dovecot /etc/dovecot
chmod -R o-rwx /etc/dovecot
systemctl restart dovecot
chmod -R o-rwx /etc/postfix
systemctl restart postfix
systemctl enable dovecot
systemctl enable postfix

Test

dnf install mailx
mail email1@example.com
tail /var/log/maillog

Client config

465 smtp TLS
587 smtp STARTTLS
993 imap ssl
995 pop3 ssl

Reference

1. Email with Postfix, Dovecot and MariaDB on CentOS 7
2. Virtual user mail system with Postfix, Dovecot and Roundcube