#!/bin/bash # blog: https://sysadmins.co.za/mail-server-virtual-users-mysql-postfixadmin-on-centos/ # dependencies yum update -y yum --enablerepo=centosplus install postfix mailx mutt -y yum install dovecot mysql-server dovecot-mysql cyrus-sasl cyrus-sasl-devel -y # mysql configuration chkconfig mysqld on service mysqld start mysqladmin -u root password d83jf734g8 mysql -u root -pd83jf734g8 # create database and apply permissions mysql> CREATE DATABASE mail; mysql> use mail mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'password'; mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'password'; mysql> FLUSH PRIVILEGES; # create required tables mysql> CREATE TABLE domains (domain varchar(50) NOT NULL, PRIMARY KEY (domain) ); mysql> CREATE TABLE forwardings (source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY (source) ); mysql> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(20) NOT NULL, PRIMARY KEY (email) ); mysql> CREATE TABLE transport ( domain varchar(128) NOT NULL default '', transport varchar(128) NOT NULL default '', UNIQUE KEY domain (domain) ); # create first mail account mysql> INSERT INTO domains (domain) VALUES ('sysadmins.co.za'); mysql> INSERT INTO users (email, password) VALUES ('ruan@sysadmins.co.za', ENCRYPT('mypassword')); mysql> quit # generate postfix mysql configuration cat > /etc/postfix/mysql-virtual_domains.cf << EOF user = mail_admin password = password dbname = mail query = SELECT domain AS virtual FROM domains WHERE domain='%s' hosts = 127.0.0.1 EOF cat > /etc/postfix/mysql-virtual_forwardings.cf << EOF user = mail_admin password = password dbname = mail query = SELECT destination FROM forwardings WHERE source='%s' hosts = 127.0.0.1 EOF cat > /etc/postfix/mysql-virtual_mailboxes.cf << EOF user = mail_admin password = password dbname = mail query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s' hosts = 127.0.0.1 EOF cat > /etc/postfix/mysql-virtual_email2email.cf << EOF user = mail_admin password = password dbname = mail query = SELECT email FROM users WHERE email='%s' hosts = 127.0.0.1 EOF # apply permissions chmod o= /etc/postfix/mysql-virtual_*.cf chgrp postfix /etc/postfix/mysql-virtual_*.cf groupadd -g 5000 vmail useradd -g vmail -u 5000 vmail -d /home/vmail -m # postfix main.cf configuration postconf -e 'myhostname = mail.sysadmins.co.za' postconf -e 'mydestination = localhost' postconf -e 'mynetworks = 127.0.0.0/8' postconf -e 'inet_interfaces = all' postconf -e 'message_size_limit = 30720000' postconf -e 'virtual_alias_domains =' postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf' postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf' postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf' postconf -e 'virtual_mailbox_base = /home/vmail' postconf -e 'virtual_uid_maps = static:5000' postconf -e 'virtual_gid_maps = static:5000' postconf -e 'smtpd_sasl_type = dovecot' postconf -e 'smtpd_sasl_path = private/auth' postconf -e 'smtpd_sasl_auth_enable = yes' postconf -e 'broken_sasl_auth_clients = yes' postconf -e 'smtpd_sasl_authenticated_header = yes' postconf -e 'smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination' postconf -e 'smtpd_use_tls = yes' postconf -e 'smtpd_tls_cert_file = /etc/pki/dovecot/certs/dovecot.pem' postconf -e 'smtpd_tls_key_file = /etc/pki/dovecot/private/dovecot.pem' postconf -e 'virtual_create_maildirsize = yes' postconf -e 'virtual_maildir_extended = yes' postconf -e '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_mailbox_limit_maps' postconf -e 'virtual_transport = virtual' postconf -e 'dovecot_destination_recipient_limit = 1' # postfix master.conf configuration echo " dovecot unix - n n - - pipe flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient} " >> /etc/postfix/master.cf # start postfix service sendmail stop chkconfig sendmail off chkconfig postfix on service postfix start # backup dovecot.conf mv /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf-backup # generate dovecot.conf cat > /etc/dovecot/dovecot.conf << EOF listen = * protocols = imap pop3 log_timestamp = "%Y-%m-%d %H:%M:%S " mail_location = maildir:/home/vmail/%d/%n maildir_stat_dirs = yes mail_privileged_group = postfix namespace { type = private separator = . prefix = INBOX. inbox = yes } passdb { args = /etc/dovecot/dovecot-sql.conf driver = sql } service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0660 user = postfix } unix_listener auth-master { mode = 0600 user = vmail } user = root } ssl_cert = /etc/dovecot/dovecot-sql.conf << EOF driver = mysql connect = host=127.0.0.1 dbname=mail user=mail_admin password=password default_pass_scheme = CRYPT password_query = SELECT email as user, password FROM users WHERE email='%u'; EOF # apply permissions chgrp dovecot /etc/dovecot/dovecot-sql.conf chmod o= /etc/dovecot/dovecot-sql.conf # start dovecot chkconfig dovecot on service dovecot start