How to Install Postfix & Dovecot with MariaDB (or MySQL) on Ubuntu-20.04

Beyond Here

  Environment
    Device :  Odroid-HC2
    OS : Ubuntu-20.04
    Pre Insalled App : MariaDB(or MySQL)

1. Install Postfix & Dovecot

$ sudo apt-get install postfix postfix-mysql dovecot-core dovecot-imapd dovecot-lmtpd  dovecot-pop3d dovecot-mysql
$ sudo systemctl restart dovecot
$ sudo netstat -lnp

2. Setup mariadb

    Environment
    database :  mail_server
    accout : usermail
    password : test@test
    host : localhost

1) Generate Database for mail

$ sudo mysql -u root -p

> create database mail_server;

2) Generate Acccout for mail

> GRANT SELECT ON mail_server.* TO 'usermail'@'127.0.0.1' IDENTIFIED BY 'test@test';
> flush privileges;
> GRANT SELECT ON mail_server.* TO 'usermail'@'localhost' IDENTIFIED BY 'test@test';
> flush privileges;

3) Generate Virtual Domain Table

> USE mail_server;
> CREATE TABLE `virtual_domains` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4) Generate Virtual User Table

> CREATE TABLE `virtual_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `domain_id` INT NOT NULL,
  `password` VARCHAR(106) NOT NULL,
  `email` VARCHAR(120) 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;

4)Generate Virtual Alias Table

> CREATE TABLE `virtual_aliases` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `domain_id` INT 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;

5) Insert Virtual Domain

> INSERT INTO mail_server.virtual_domains
  (`id` ,`name`)
VALUES
  ('1', 'test.com'),
  ('2', 'test.test.com'),
  ('3', 'test'),
  ('4', 'mail.test.com'),
  ('5', 'localhost.test.com'),
  ('6', 'localhost');

6) Insert Virtual Mail User

> INSERT INTO `mail_server`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('test@test', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'test@test.com'),
  ('2', '1', ENCRYPT('test@test', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'net@test.com'),
  ('3', '1', ENCRYPT('test@test', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'net1@test.com'),
  ('4', '1', ENCRYPT('test@test', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'net2@test.com');

7) Insert Virtual Alias

> INSERT INTO `mail_server`.`virtual_aliases`
  (`id`, `domain_id`, `source`, `destination`)
VALUES
  ('1', '1', 'admin@test.com', 'test@test.com'),
  ('2', '1', 'root@test.com', 'test@test.com');

7) Check Virtual Table

> SELECT * FROM mail_server.virtual_domains;
> SELECT * FROM mail_server.virtual_users;
> SELECT * FROM mail_server.virtual_aliases;
> quit;

3. Setup Postfix

1) Setup main.cf

$ sudo cp /etc/postfix/main.cf /etc/postfix/main.cf.original
$ sudo nano /etc/postfix/main.cf

Change Configuration

# 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_tls_security_level=may

==>

# TLS parameters
smtpd_use_tls = yes
smtpd_tls_cert_file=/etc/dovecot/private/dovecot.pem
smtpd_tls_key_file=/etc/dovecot/private/dovecot.key
smtpd_tls_security_level=may

smtp_tls_CApath=/etc/ssl/certs
smtp_tls_security_level=may
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scach

==>

smtp_use_tls = yes
#smtp_tls_CApath=/etc/ssl/certs
smtp_tls_security_level=may
#smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination

==>

smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_relay_restrictions = permit_mynetworks, permit_sasl_authenticated,  reject_unauth_destination

mydestination = $myhostname, test.com, ubt64.test.com, localhost.test.com, localhost

==>

mydestination = localhost

Insert Configuration

#Handing off local delivery to Dovecot's LMTP, and telling it where to store mail
virtual_transport = lmtp:unix:private/dovecot-lmtp
#Virtual domains, users, and aliases
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf

2) Generate mysql-virtual-mailbox-domains.cf

$ sudo nano /etc/postfix/mysql-virtual-mailbox-domains.cf

Insert Configuration

user = usermail
password = test@test
hosts = 127.0.0.1
dbname = mail_server
query = SELECT 1 FROM virtual_domains WHERE name='%s'

$ sudo service postfix restart
$ sudo postmap -q test.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

3) Generate mysql-virtual-mailbox-maps.cf

sudo nano /etc/postfix/mysql-virtual-mailbox-maps.cf

Insert Configuration

user = usermail
password = test@test
hosts = 127.0.0.1
dbname = mail_server
query = SELECT 1 FROM virtual_users WHERE email='%s'

$ sudo service postfix restart
$ sudo postmap -q test.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

4) Generate mysql-virtual-alias-maps.cf

$ sudo nano /etc/postfix/mysql-virtual-alias-maps.cf

user = usermail
password = test@test
hosts = 127.0.0.1
dbname = mail_server
query = SELECT destination FROM virtual_aliases WHERE source='%s'

$ sudo service postfix restart
$ sudo postmap -q admin@test.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

5) Setup master.cf

$ sudo cp /etc/postfix/master.cf /etc/postfix/master.cf.orig
$ sudo nano /etc/postfix/master.cf

Change Configuration

#submission inet n       -       y       -       -       smtpd
#  -o syslog_name=postfix/submission
#  -o smtpd_tls_security_level=encrypt
#  -o smtpd_sasl_auth_enable=yes
#  -o smtpd_tls_auth_only=yes
#  -o smtpd_reject_unlisted_recipient=no
#  -o smtpd_client_restrictions=$mua_client_restrictions
#  -o smtpd_helo_restrictions=$mua_helo_restrictions

==>

submission inet n       -       y       -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
#  -o smtpd_tls_auth_only=yes
#  -o smtpd_reject_unlisted_recipient=no
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
#  -o smtpd_helo_restrictions=$mua_helo_restrictions

$ sudo systemctl restart postfix
$ sudo netstat -lnp
check port 25 & 587

4. Setup Dovecot

1) Backup Dovecot Configuration

$ sudo cp /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.orig
$ sudo cp /etc/dovecot/conf.d/10-mail.conf /etc/dovecot/conf.d/10-mail.conf.orig
$ sudo cp /etc/dovecot/conf.d/10-auth.conf /etc/dovecot/conf.d/10-auth.conf.orig
$ sudo cp /etc/dovecot/dovecot-sql.conf.ext /etc/dovecot/dovecot-sql.conf.ext.orig
$ sudo cp /etc/dovecot/conf.d/10-master.conf /etc/dovecot/conf.d/10-master.conf.orig
$ sudo cp /etc/dovecot/conf.d/10-ssl.conf /etc/dovecot/conf.d/10-ssl.conf.orig

2) Setup dovecot.conf

$ sudo nano /etc/dovecot/dovecot.conf

Change Configuration

!include_try /usr/share/dovecot/protocols.d/*.protocol

==>

!include_try /usr/share/dovecot/protocols.d/*.protocol
protocols = imap pop3 lmtp

3) Setup 10-mail.conf

$ sudo nano /etc/dovecot/conf.d/10-mail.conf

Change Configuration

mail_location = mbox:~/mail:INBOX=/var/mail/%u

==>

#mail_location = mbox:~/mail:INBOX=/var/mail/%u
mail_location = maildir:/var/mail/vhosts/%d/%n

mail_privileged_group = mail

==>

mail_privileged_group = mail

4) Generate domain per Virtual Host

$ sudo ls -ld /var/mail
$ sudo mkdir -p /var/mail/vhosts/test.com

$ sudo groupadd -g 5000 vmail
$ sudo useradd -g vmail -u 5000 vmail -d /var/mail
$ sudo chown -R vmail:vmail /var/mail

5) Setup 10-auth.conf

$ sudo nano /etc/dovecot/conf.d/10-auth.conf

Change Configuration

#disable_plaintext_auth = yes

==>

disable_plaintext_auth = yes

auth_mechanisms = plain

==>

auth_mechanisms = plain login

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

==>

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

6) Setup auth-sql.conf.ext

$ sudo cp /etc/dovecot/conf.d/auth-sql.conf.ext /etc/dovecot/conf.d/auth-sql.conf.ext.orig
$ sudo nano /etc/dovecot/conf.d/auth-sql.conf.ext

Change Configuration

passdb {
  driver = sql
  # Path for SQL configuration file, see example-config/dovecot-sql.conf.ext
  args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

==>

passdb {
  driver = sql
  # Path for SQL configuration file, see example-config/dovecot-sql.conf.ext
  args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
  driver = static
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
}

7) Setup dovecot-sql.conf.ext

$ sudo nano /etc/dovecot/dovecot-sql.conf.ext

Change Configuration

# Database driver: mysql, pgsql, sqlite
#driver =

==>

# Database driver: mysql, pgsql, sqlite
driver = mysql

#connect

==>

connect = host=127.0.0.1 dbname=mail_server user=usermail password=test@test

#default_pass_scheme = MD5

==>

default_pass_scheme = SHA512-CRYPT

#password_query = \
#  SELECT username, domain, password \
#  FROM users WHERE username = '%n' AND domain = '%d'

==>

password_query = \
  SELECT email as user, password \
  FROM virtual_users WHERE email='%u';

8) Change File Owner & Permissions

$ sudo chown -R vmail:dovecot /etc/dovecot
$ sudo chmod -R 771 /etc/dovecot

9) Setup 10-master.conf

$ sudo nano /etc/dovecot/conf.d/10-master.conf

Change Configuration

service imap-login {
  inet_listener imap {
    #port = 143
  }
  inet_listener imaps {
    #port = 993
    #ssl = yes
  }
}

==>

service imap-login {
  inet_listener imap {
    port = 143
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}

service pop3-login {
  inet_listener pop3 {
    #port = 110
  }
  inet_listener pop3s {
    #port = 995
    #ssl = yes
  }
}

==>

service pop3-login {
  inet_listener pop3 {
    port = 110
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}

service lmtp {
  unix_listener lmtp {
    #mode = 0666
  }

==>

service lmtp {
   unix_listener /var/spool/postfix/private/dovecot-lmtp {
    mode = 0600
    user = postfix
    group = postfix
  }

Change service auth parameter

  unix_listener auth-userdb {
    #mode = 0666
    #user =
    #group =
  }

==>

  unix_listener auth-userdb {
    mode = 0600
    user = vmail
    #group =
  }

  # Postfix smtp-auth
  #unix_listener /var/spool/postfix/private/auth {
  #  mode = 0666
  #}

==>

  # Postfix smtp-auth
  unix_listener /var/spool/postfix/private/auth {
    mode = 0666
    user = postfix
    group = postfix
  }

  # Auth process is run as this user.
  #user = $default_internal_user
}

==>

  # Auth process is run as this user.
  #user = $default_internal_user
  user = dovecot
}

Change service auth-worker parametet

service auth-worker {
  # Auth worker process is run as root by default, so that it can access
  # /etc/shadow. If this isn't necessary, the user should be changed to
  # $default_internal_user.
  #user = root
}

==>

service auth-worker {
  # Auth worker process is run as root by default, so that it can access
  # /etc/shadow. If this isn't necessary, the user should be changed to
  # $default_internal_user.
  user = vmail
}

10) Setup 10-ssl.conf

$ sudo nano /etc/dovecot/conf.d/10-ssl.conf

Change Configuration

ssl = yes

==>

ssl = required

ssl_cert = </etc/dovecot/private/dovecot.pem
ssl_key = </etc/dovecot/private/dovecot.key

==>

ssl_cert = </etc/dovecot/private/dovecot.pem
ssl_key = </etc/dovecot/private/dovecot.key

$ sudo systemctl restart dovecot

Additional Resources

How to Install MariaDB on Ubuntu-20.04

MariaDB (or MYSQL) Commands