ISPmail (3): Chuẩn bị CSDL

Trong phần này chúng ta chuẩn bĩ CSDL MariaDB để dùng với PostFix và Dovecot.

Các mail server có thể khác nhau một ít về cấu trúc CSDL nên các ứng dụng quản trị giao diện web khó có thể dùng chung, tuy rằng các tác giả vẫn cố giữ chúng tương thích với các webmail phổ biến.

Bài giảng tương ứng với phần này là Prepare the database

Ở giai đoạn cài đặt, user root của MariaDB chưa có mật khẩu. Chúng ta sẽ tạo thêm 2 user có quyền trên CSDL mailserver với mật khẩu ngẫu nhiên. Các mật khẩu này lưu lại trên file ~/readme.txt để sử dụng về sau.

User mailadmin quản trị mailserver qua giao diện web của Adminer (tương tự PHPmyadmin nhưng chiếm dụng ít tài nguyên hơn)

Giả sử chúng ta đang SSH vào RPi bằng quyền root.

Tạo website Adminer

sed '/<\/VirtualHost>/ i\
  Alias /adminer /usr/share/adminer/adminer' -i /etc/apache2/sites-available/${hostname}-https.conf
systemctl restart apache2

Tạo CSDL mailserver

## Tạo mật khẩu ngẫu nhiên ##
read -r adm_pwd svr_pwd <<< $(pwgen -s1 -C 30 2)
## Tạo CSDL mailserver và các user mailadmin, mailserver ##
mysql -h localhost \
-e "DROP DATABASE IF EXISTS mailserver;" \
-e "CREATE DATABASE mailserver;" \
-e "GRANT ALL ON mailserver.* TO 'mailadmin'@'localhost' IDENTIFIED BY '$adm_pwd';" \
-e "GRANT SELECT ON mailserver.* TO 'mailserver'@'127.0.0.1' IDENTIFIED BY '$svr_pwd';"
cat > ~/readme.txt <<EOT
DB user của CSDL mailserver
  - user: mailadmin
    password: $adm_pwd
  - user: mailserver
    password: $svr_pwd

Quản lý CSDL
  https://$hostname/adminer
  user: mailadmin
  password: $adm_pwd
EOT

Đăng nhập vào Adminer qua đường link https://mx.example.org/adminer với mailadmin/$adm_pwd

Tạo các bảng dữ liệu

• virtual_domains (id, name)
• virtual_users (id, domain_id, email, password, quota)
• virtual_aliases (id, domain_id, source, destination)

## Tạo bảng virtual_domains ##
mysql -h localhost -D mailserver <<EOQ
CREATE TABLE IF NOT EXISTS virtual_domains ( id int(11) NOT NULL auto_increment, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
## Tạo bảng virtual_users ##
CREATE TABLE IF NOT EXISTS virtual_users ( id int(11) NOT NULL auto_increment, domain_id int(11)  NOT NULL,  email varchar(100) NOT NULL, password varchar(150) NOT NULL, quota bigint(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY email (email), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
## Tạo bảng virtual_aliases ##
CREATE TABLE IF NOT EXISTS 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;
EOQ

Chúng ta tạo 3 bảng domains, users, aliases có tên bắt đầu từ tiền tố virtual. Bởi vì tất cả đều là ảo. Mail domain ở đây không cần liên quan đến mail server, mail user không cần là system user như pi hay root, còn alias không phải là một user có thật. Tuy nhiên chính sự ảo hóa này làm tăng khả năng ứng dụng của mail server.

Tiếp theo là tạo dữ liệu để test, có thể bỏ qua sau này tạo khi cần.

Script

#!/bin/bash
# Tên script: 02.db.sh
# CSDL mailserver
# © 2020 LNT <lnt@ly-le.info>
# version 20200801
#
echo 'Cài đặt CSDL...'
HOSTNAME=$(hostname)
DOMAIN=${HOSTNAME#*.}
## Tạo mật khẩu ngẫu nhiên ##
{ read ADM_PWD; read SEL_PWD; read PM_PWD; } <<< $(pwgen -s -1 30 3)
pm_pwd=`/usr/bin/doveadm pw -s BLF-CRYPT -p ${PM_PWD}`
## Tạo CSDL mailserver và các user mailadmin, mailselect ##
mysql -h localhost \
  -e "DROP DATABASE IF EXISTS mailserver;" \
  -e "CREATE DATABASE mailserver;" \
  -e "GRANT ALL ON mailserver.* TO 'mailadmin'@'localhost' IDENTIFIED BY '${ADM_PWD}';" \
  -e "GRANT SELECT ON mailserver.* TO 'mailselect'@'127.0.0.1' IDENTIFIED BY '${SEL_PWD}';"
cat > ~/${DOMAIN}.txt <<EOT
User của CSDL mailserver
  1. user: mailadmin
     password: ${ADM_PWD}
  2. user: mailselect
     password: ${SEL_PWD}

Quản lý CSDL
  https://${HOSTNAME}/adminer
  user: mailadmin
  password: ${ADM_PWD}

Email đầu tiên
  user: postmaster@${DOMAIN}
  password: $PM_PWD

Catch All:
  user: postmaster@${DOMAIN}
EOT

## Tạo bảng virtual_domains ##
mysql -h localhost -D mailserver <<EOQ
CREATE TABLE IF NOT EXISTS virtual_domains ( id int(11) NOT NULL auto_increment, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
## Tạo bảng virtual_users ##
CREATE TABLE IF NOT EXISTS virtual_users ( id int(11) NOT NULL auto_increment, domain_id int(11)  NOT NULL,  email varchar(100) NOT NULL, password varchar(150) NOT NULL, quota bigint(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY email (email), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
## Tạo bảng virtual_aliases ##
CREATE TABLE IF NOT EXISTS 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;
REPLACE INTO mailserver.virtual_domains (id,name) VALUES ('1','${DOMAIN}');
REPLACE INTO mailserver.virtual_users (id,domain_id,password,email) VALUES ('1', '1', '$pm_pwd', 'postmaster@${DOMAIN}');
REPLACE INTO mailserver.virtual_aliases (id,domain_id,source,destination) VALUES ('1', '1', '@${DOMAIN}', 'postmaster@${DOMAIN}');
EOQ