CentOS 7.x Postfix + Dovecot + MySQL + RoundCube Webmail

CentOS 7.x Postfix + Dovecot + MySQL + RoundCube Webmail

# yum install postfix dovecot dovecot-mysql roundcubemail

# useradd -u 5000 -d /home/vmail -s /sbin/nologin -m vmail


CREATE DATABASE webmail;
GRANT ALL PRIVILEGES ON webmail.* TO webmail@localhost IDENTIFIED BY 'dbpassword';


CREATE TABLE `webmail`.`virtual_domains` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
 ) DEFAULT CHARSET=utf8;

CREATE TABLE `webmail`.`virtual_users` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `password` VARCHAR(106) NOT NULL,
 `email` VARCHAR(80) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`)
 ) DEFAULT CHARSET=utf8;

CREATE TABLE `webmail`.`virtual_aliases` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `source` varchar(100) NOT NULL,
 `destination` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
 ) DEFAULT CHARSET=utf8;

INSERT INTO `webmail`.`virtual_domains` (`id` ,`name`) VALUES
 ('1', 'aaa.com'),
 ('2', 'bbb.com'),
 ('3', 'example.com');

INSERT INTO `webmail`.`virtual_users` (`id`, `password`, `email`) VALUES
 ('1', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@aaa.com'),
 ('2', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@aaa.com'),
 ('3', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@bbb.com'),
 ('4', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@bbb.com'),
 ('5', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@example.com'),
 ('6', ENCRYPT('userpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@example.com');

INSERT INTO `webmail`.`virtual_aliases` (`id`, `source`, `destination`) VALUES
 ('1', 'alias@aaa.com', 'user1@aaa.com'),
 ('2', 'user2@example.com', 'user1@example.com');


# vi /etc/postfix/mysql-virtual-alias-maps.cf
hosts = 127.0.0.1
dbname = webmail
user = webmail
password = dbpassword
query = SELECT destination FROM virtual_aliases WHERE source='%s'

# vi /etc/postfix/mysql-virtual-users.cf
hosts = 127.0.0.1
dbname = webmail
user = webmail
password = dbpassword
query = SELECT email FROM virtual_users WHERE email='%s'

# vi /etc/postfix/mysql-virtual-mailbox-domains.cf
hosts = 127.0.0.1
dbname = webmail
user = webmail
password = dbpassword
query = SELECT name FROM virtual_domains WHERE name='%s'

# vi /etc/postfix/mysql-virtual-mailbox-maps.cf
hosts = 127.0.0.1
dbname = webmail
user = webmail
password = dbpassword
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM virtual_users WHERE email='%s'

# chgrp postfix /etc/postfix/mysql-virtual-*.cf
# chmod o= /etc/postfix/mysql-virtual-*.cf


# vi /etc/postfix/main.cf
myhostname = mail.example.com
mydestination = localhost
mynetworks = 127.0.0.0/8
inet_interfaces = all
message_size_limit = 30720000
virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual-alias-maps.cf, mysql:/etc/postfix/mysql-virtual-users.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_mailbox_base = /home/vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
broken_sasl_auth_clients = yes
smtpd_sasl_authenticated_header = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_use_tls = yes
smtpd_tls_cert_file = /etc/pki/dovecot/certs/dovecot.pem
smtpd_tls_key_file = /etc/pki/dovecot/private/dovecot.pem
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1


# vi /etc/postfix/master.cf
dovecot   unix  -       n       n       -       -       pipe
    flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient}

submission inet n       -       n       -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_reject_unlisted_recipient=no
  #-o smtpd_client_restrictions=$mua_client_restrictions
  #-o smtpd_helo_restrictions=$mua_helo_restrictions
  #-o smtpd_sender_restrictions=$mua_sender_restrictions
  -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING
smtps     inet  n       -       n       -       -       smtpd
  -o syslog_name=postfix/smtps
  -o smtpd_tls_wrappermode=yes
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_reject_unlisted_recipient=no
  #-o smtpd_client_restrictions=$mua_client_restrictions
  #-o smtpd_helo_restrictions=$mua_helo_restrictions
  #-o smtpd_sender_restrictions=$mua_sender_restrictions
  -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING



# systemctl restart postfix
# systemctl status postfix
# postmap -q alias@aaa.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf
# postmap -q user1@aaa.com mysql:/etc/postfix/mysql-virtual-users.cf
# postmap -q aaa.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
# postmap -q user1@aaa.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf


# mv /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.ori
# vi /etc/dovecot/dovecot.conf
protocols = imap pop3
listen = *
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_location = maildir:/home/vmail/%d/%n/Maildir

ssl_cert = </etc/pki/dovecot/certs/dovecot.pem
ssl_key = </etc/pki/dovecot/private/dovecot.pem

namespace {
    type = private
    separator = .
    prefix = INBOX.
    inbox = yes
}

service auth {
    unix_listener auth-master {
        mode = 0600
        user = vmail
    }
    unix_listener /var/spool/postfix/private/auth {
        mode = 0666
        user = postfix
        group = postfix
    }
    user = root
}

service auth-worker {
    user = root
}

protocol lda {
    log_path = /home/vmail/dovecot-deliver.log
    auth_socket_path = /var/run/dovecot/auth-master
    postmaster_address = postmaster@example.com
}

protocol pop3 {
    pop3_uidl_format = %08Xu%08Xv
}

passdb {
    driver = sql
    args = /etc/dovecot/dovecot-sql.conf.ext
}

userdb {
    driver = static
    args = uid=vmail gid=vmail home=/home/vmail/%d/%n allow_all_users=yes
}

# vi /etc/dovecot/dovecot-sql.conf.ext
driver = mysql
connect = host=127.0.0.1 dbname=webmail user=webmail password=dbpassword
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

# chown vmail:dovecot /etc/dovecot
# chown vmail:dovecot /etc/dovecot/dovecot.conf
# chown vmail:dovecot /etc/dovecot/dovecot-sql.conf.ext
# chmod o= /etc/dovecot/dovecot-sql.conf.ext

 systemctl restart postfix
 systemctl restart dovecot
 tail -n15 /var/log/maillog


# mysql -u webmail -p webmail < /usr/share/roundcubemail/SQL/mysql.initial.sql

# cp -p /etc/roundcubemail/config.inc.php.sample /etc/roundcubemail/config.inc.php
# vi /etc/roundcubemail/config.inc.php
$config['db_dsnw'] = 'mysql://webmail:dbpassword@localhost/webmail';
$config['product_name'] = 'Webmail'; // HTML Title

# vi /etc/httpd/conf.d/roundcubemail.conf
<Directory /usr/share/roundcubemail/>
    <IfModule mod_authz_core.c>
        # Apache 2.4
        Require local
        Require all granted
    </IfModule>
</Directory>
<Directory /usr/share/roundcubemail/installer/>
    <IfModule mod_authz_core.c>
        # Apache 2.4
        Require local
        Require ip xxx.xxx.xxx.xxx
    </IfModule>
</Directory>
# vi /etc/httpd/conf.d/webmail.example.com.conf
<VirtualHost *:80>
    ServerName webmail.example.com
    DocumentRoot /usr/share/roundcubemail
    ErrorLog logs/webmail.example.com-error_log
    LogFormat "%V %h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" vcommon
    CustomLog logs/webmail.example.com-access_log vcommon
</VirtualHost>
# systemctl restart httpd