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

##############################################################

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

##############################################################

##############################################################

database :  mail_server

accout : usermail

password : test@test

host : localhost 

##############################################################

##############################################################

$ sudo mysql -u root -p

### Generate Database for mail

> create database mail_server;

### 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;

###  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;

###  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;

###  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;

### 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');

### 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');

### 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');

### Check Virtual Table

> SELECT * FROM mail_server.virtual_domains;

> SELECT * FROM mail_server.virtual_users;

> SELECT * FROM mail_server.virtual_aliases;

> quit;

$ sudo systemctl restart mariadb

3. Setup Postfix

### 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
=>
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_scache
=>
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

### Generate mysql-virtual-mailbox-domains.cf

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

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

### Generate mysql-virtual-mailbox-maps.cf

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

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@test.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

### 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

$ 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

 

### 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 service postfix restart

$ sudo netstat -lnp

check port 25 & 587

5. Setup Dovecot

$ 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

### Setup 10-master.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 

### 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

### 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

$ 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

$ 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
} 

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

# 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'; 

### Change File Owner & Permissions

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

$ sudo chmod -R 771 /etc/dovecot 

### Setup 10-master.conf

$ sudo -i

# 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
}

### 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.service

 How to Install WordPress with Mariadb(or MySQL) on Ubuntu-20.04

##############################################################

Environment

  Device :  Odroid-HC2

  OS : Ubuntu-20.04

  IP : 192.168.101.100

  Pre Insall App : Mariadb(or MySQL), NGINX, PHP

##############################################################

1. Install WordPress

$ sudo apt install wordpress -y

2. Generate database for WordPress

##############################################################
WordPress 
  SQL database : wordpress
  SQL userid : wordpress
  SQL password : test@test
##############################################################
  
$ sudo mysql -u root
> create database wordpress;
> grant all on wordpress.* to wordpress@localhost identified by 'test@test';
> flush privileges;
> quit;

3. Setup WordPress

$ sudo ln -s /usr/share/wordpress /var/www/html/wp

$ sudo ls /var/www/html/ -l

$ sudo chown -R www-data:www-data /var/www/html/wp

$ sudo chown -R www-data:www-data /var/www/html/wp/

$ sudo ls /var/www/html/ -l

$ sudo ls /var/www/html/wp/ -l

$ sudo cp /var/www/html/wp/wp-config-sample.php /var/www/html/wp/wp-config.php

$ sudo nano /var/www/html/wp/wp-config.php

Change Configuration

define( 'DB_NAME', 'database_name_here' );
=>
define( 'DB_NAME', 'wordpress' );
define( 'DB_USER', 'username_here' );
=>
define( 'DB_USER', 'wordpress' );
define( 'DB_PASSWORD', 'password_here' );
=>
define( 'DB_PASSWORD', 'test@test' );

Insert Configuration

/** Enable Direct Update **/
define('FS_METHOD', 'direct');

$ sudo systemctl restart nginx

4. Connect Web

test@test.com

 How to Upgrade MariaDB from Ubuntu-18.04 to Ubuntu-20.04

======================================

Environment

  u1804 : Ubuntu-18.04

  u2004 : Ubuntu-20.04

======================================

If you convert MariaDB from Ubuntu-18.04 to Ubuntu-20.04

you can see error message

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline

1. Change Row type from compact to dynamic

2. convert MariaDB from Ubuntu-18.04 to Ubuntu-20.04

How To Install MriaDB on Ubunut-20.04

---

    Environment
      Device :  Odroid-HC2 (or XU4)
      OS : Ubuntu-20.04
      Host : test (192.168.101.10)

---

1. Install MariaDB

$ sudo apt install mariadb-server mariadb-comm mycli python3-mysqldb -y

2. Setup intial mariadb configuration

MariaDB Initial Setup

$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

MariaDB Server IP Change

$ sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.orig
$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Change Configuration

bind-address            = 127.0.0.1

==>

bind-address            = 192.168.101.100

3. Generate Management Account

MriadDB Login

$ sudo mysql -u root -p

Create MariaDB Account

> create user 'test'@'%' identified by 'test@test';
> grant all privileges on *.* to  'test'@'%' with grant option;
> flush privileges;

4. Verify MariaDB

$ mycli -h 192.168.101.100 -u test