postfix-mysql-dovecot-and-postfixmailadmin

Postfix + MySQL + Dovecot and PostfixMailAdmin

By: Asghar on February 18th, 2010

In this how-to I’ll guide you how to compile postfix with mysql & dovecot and I’ll also introduce a new tool named (PostfixMailAdmin) for managing postfix mysql users.

We all knows Postfix is fast, easy to administer, and secure. The outside has a definite Sendmail-ish flavor, but the inside is completely different.

Installing Postfix

cd /usr/src
wget http://ftp-stud.fht-esslingen.de/pub/Mirrors/centos/5/os/SRPMS/postfix-2.3.3-2.1.el5_2.src.rpm
mkdir -p /usr/src/redhat/SOURCES
rpm -ivh postfix-2.3.3-2.src.rpm

If we want Quota suuport in postfix then Apply Quota Patch To Postfix

cd /usr/src/redhat/SOURCES
wget http://vda.sourceforge.net/VDA/postfix-2.3.3-vda.patch.gz
gunzip postfix-2.3.3-vda.patch.gz
cd /usr/src/redhat/SPECS/

Now we must edit the file postfix.spec file to add quota support and MySQL support in postfix

[...]
%define MYSQL 1
# Patches

Patch0: postfix-2.3.3-vda.patch

%setup -q
# Apply obligatory patches
%patch0 -p1 -b .vda

Next step is to build our new Postfix rpm package with quota and MySQL support:

rpmbuild -ba postfix.spec

Our Postfix rpm package is created in /usr/src/redhat/RPMS/i386, so we go there

cd /usr/src/redhat/RPMS/i386
rpm -ivh postfix-2.3.3-2.1.el5_2.rpm

I have already mentioned that i will intrduce a new software for managing postfix virtual users. The software name is postfixmailadmin.

The main feature of this software is new web interface, easy to use, optimized search and vacation module.

You can download postfixmailadmin from this url: http://sourceforge.net/projects/postfixmiladmin/

Now Install PostfixMailAdmin

cd /var/www/html
tar -xzvf postfixmailadmin-1.0.1.tar.gz

To configure postfixmailadmin we have to follow the instructions in doc/INSTALL.txt file

After installing postfixmailadmin you need to create mysql configuration files for postfix.

Let us create mysql configuration files now.

Create file mysql_virtual_alias_maps.cf for forwarding emails from one email address to another

vi /etc/postfix/mysql_virtual_alias_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address=’%s’ AND active = ’1′

Create file mysql_virtual_domains_maps.cf, for the virtual domains mapping. Virtual domains are queried using information provided in this file.

vi /etc/postfix/mysql_virtual_domains_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain=’%s’ and backupmx=’0′ and active=’1′

Create the file mysql_virtual_mailbox_maps.cf. This is usually the mapping of email addresses to the location of the user’s mailbox on your hard disk.

vi /etc/postfix/mysql_virtual_mailbox_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT CONCAT(domain,’/',maildir) FROM mailbox WHERE username=’%s’ AND active = ’1′

Create file mysql_virtual_mailbox_limit_maps.cf which will be used for mapping user’s mailboxes quota limit.

vi /etc/postfix/mysql_virtual_mailbox_limit_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT quota FROM mailbox WHERE username=’%s’

We will check sender from address after sasl authentication. So senders not use our mail server as open relay.

vi /etc/postfix/mysql_sender_check.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT username FROM mailbox WHERE username=’%s’ and active=1

Next create mysql_virtual_alias_domains_maps.cf. for alias domains.

vi mysql_virtual_alias_domains_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT concat(‘@’, target_domain ) FROM alias_domain WHERE concat(‘@’, alias_domain) = ‘%s’ AND active = ’1′

Lastly create file mysql_relay_domains_maps.cf which will be used for relay domains.

vi /etc/postfix/mysql_relay_domains_maps.cf

user = postfix
password = mail
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain=’%s’ and backupmx = ’1′

Set permission.

cd /etc/postfix/
chmod 640 mysql_*
chgrp postfix mysql_*

In next step we will create a user (vmail) with home directory (/home/vmail). You can change this path as per your choice, because in this directory all virtual users emails will be saved.

useradd vmail -u 150 -g mail -d /home/vmail -m -s /sbin/nologin -c "Virtual mailbox"
chmod 770 /home/vmail/

SSL-Key Setup:

Create directories, then private key and lastly the certificate.

mkdir -p /home/mySSLkey/
cd /home/mySSLkey/
openssl genrsa 1024 > mail-key.pem
chmod 400 mail-key.pem
openssl req -new -x509 -nodes -sha1 -days 365 -key mail-key.pem > mail-cert.pem

Configuring Postfix main.cf

Now copy cp /etc/aliases* /etc/postfix/

Everything is installed and configured as per requirements. The next step is to configure /etc/postfix/main.cf

The following command is showing you what i have done in my configuration file.

grep -v "^#" /etc/postfix/main.cf | sed -e ‘/^$/d’

Here is the output of above command

soft_bounce = no
smtpd_banner = $myhostname
biff = no
append_dot_mydomain = no
inet_interfaces = all
myhostname = howto.example.com
myorigin = $myhostname
mydomain = example.com
mynetworks = 127.0.0.0/8
mynetworks_style = host
relayhost =
mydestination = $myhostname, localhost.$mydomain, localhost
alias_maps = hash:$config_directory/aliases, $virtual_alias_maps
local_recipient_maps = $alias_maps, $virtual_mailbox_maps
local_transport = local
debug_peer_level = 2
debugger_command =
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
xxgdb $daemon_directory/$process_name $process_id & sleep 5
html_directory = /usr/local/share/doc/postfix
disable_vrfy_command = no
mailbox_size_limit = 0
owner_request_special = no
recipient_delimiter = +
home_mailbox = Maildir/
mail_owner = postfix
command_directory = /usr/sbin
daemon_directory = /usr/libexec/postfix
queue_directory = /var/spool/postfix
sendmail_path = /usr/sbin/sendmail.postfix
newaliases_path = /usr/bin/newaliases.postfix
mailq_path = /usr/bin/mailq.postfix
mail_spool_directory = /var/spool/mail
manpage_directory = /usr/share/man
setgid_group = postdrop
unknown_local_recipient_reject_code = 450

########### Virtual Domains Users ######
virtual_transport = virtual
virtual_alias_maps =
proxy:mysql:$config_directory/mysql_virtual_alias_maps.cf,
proxy:mysql:$config_directory/mysql_virtual_alias_domains_maps.cf
virtual_mailbox_domains = proxy:mysql:$config_directory/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:$config_directory/mysql_virtual_mailbox_maps.cf
virtual_mailbox_limit_maps = proxy:mysql:$config_directory/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_base = /home/vmail
relay_domains = proxy:mysql:$config_directory/mysql_relay_domains_maps.cf
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_mailbox_maps $virtual_mailbox_domains $relay_domains $virtual_mailbox_limit_maps
virtual_minimum_uid = 150
virtual_uid_maps = static:150
virtual_gid_maps = static:12
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the user’s maildir has overdrawn his disk space quota.
virtual_overquota_bounce = yes

###### SASL Authentication ######
smtpd_sasl_auth_enable = yes
smtpd_sasl_authenticated_header = yes
broken_sasl_auth_clients = yes
smtpd_sasl_security_options = noanonymous

mydomain = example.com is Domain name
inet_interfaces = all It Listens on all Interfaces
myhostname = howto.example.com Hostname of mail server
mynetworks = 127.0.0.0/8 It means only this machine can use postfix as smtp
virtual_minimum_uid = 150 is User ID of vmail user
virtual_gid_maps = static:12 is Group ID of mail group

Make sure sasl2 should be installed if you want smtp authentication. Otherwise remove sasl configuration from main.cf. Here is command to install sasl

yum install cyrus-sasl

Configuring Dovecot:

Let’s configure Dovecot for imap and pop3 services. You need to configure 2 files, one for dovecot main configuration file and 2nd is for mysql connectivity.

Let’s first configure dovecot-mysql.conf file

vi /etc/dovecot-mysql.conf

driver = mysql
connect = host=localhost user=mail password=mail dbname=mail
default_pass_scheme = MD5-CRYPT
password_query = SELECT password FROM mailbox WHERE username = ‘%u’
user_query = SELECT ‘/home/vmail/%d/%n/Maildir’ as home, ‘maildir:/home/vmail/%d/%n/Maildir’ as mail, 150 AS uid, 12 AS gid, concat(‘dirsize:storage=’,quota) AS quota FROM mailbox WHERE username =’%u’ AND active =’1′

150 AS uid is userID of vmail
12 AS gid is groupID of mail group

Now configure the dovecot.conf file. Keep this thing in mind that normal Linux users can’t login to mail-server for MySQL user authentication, so We should set first_valid_uid and last_valid_uid to 150, which means only the user with uid 150 will be able to log in.

vi /etc/dovecot.conf

base_dir = /var/run/dovecot/
protocols = imap pop3 imaps pop3s
protocol imap {
listen = *:143
ssl_listen = *:993
}

protocol pop3 {
listen = *:110
ssl_listen = *:995
}

mail_location = maildir:~/Maildir
disable_plaintext_auth = no
syslog_facility = mail
mail_access_groups = mail
mail_debug = no
first_valid_uid = 150
last_valid_uid = 150
first_valid_gid = 12
last_valid_gid = 12
maildir_copy_with_hardlinks = yes
log_timestamp = "%Y-%m-%d %H:%M:%S"
log_path = /var/log/maillog

auth default {
mechanisms = plain login
passdb sql {
args = /usr/local/etc/dovecot-mysql.conf
}
userdb sql {
args = /usr/local/etc/dovecot-mysql.conf
}
socket listen {
client {
path = /var/spool/postfix/private/auth
mode = 0660
user = postfix
group = postfix
}
}
}