Installing Postfix with MySql backend and TLS

In this tutorial we’ll install a ready to use Postfix mail server with MySql backend for virtual users. Notice that this tutorial only covers installing the SMTP server (not POP3 and IMAP).

Once installed and configured, you can easily create your own admin system to modifiy the domains and users because the table structure is very simple.

This tutorial has been tested on Debian etch and lenny

1. Install the Postfix mail server, MySql server and other required packages

apt-get install postfix postfix-mysql sasl2-bin libsasl2-modules mysql-client mysql-server libpam-mysql

 

In the configuration wizzard for Postfix select and input the following

General type of mail configuration
 -> Internet Site
 
System mail name
 -> server.domain.com (your server host name)

2. Create a MySql database that will contain domains and mappings and create a user that has read privileges on it. Execute the following SQL queries to create the table structure:

CREATE TABLE domains (
  domain varchar(63) NOT NULL,
  PRIMARY KEY (domain)
) ENGINE=MyISAM;
 
CREATE TABLE forwardings (
  email varchar(255) NOT NULL,
  destination text NOT NULL,
  PRIMARY KEY (email)
) ENGINE=MyISAM;
 
CREATE TABLE transport (
  domain varchar(255) NOT NULL,
  transport varchar(255) NOT NULL,
  PRIMARY KEY (domain)
) ENGINE=MyISAM;
 
CREATE TABLE users (
  email varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  quota int(10) unsigned NOT NULL default '102400',
  PRIMARY KEY (email)
) ENGINE=MyISAM;

3. Populate tables with some test data

INSERT INTO domains (domain) VALUES (mydomain.com);
INSERT INTO users (email, password) VALUES ('address@mydomain.com', ENCRYPT('mypassword'));
INESRT INTO forwardings (email, desination) VALUES ('myforward@mydomain.com', 'address@mydomain.com, otheraddress@mydomain.com');
INSERT INTO transport (domain, transport) VALUES ('transport.com', 'smtp:mail.transport.com');

If you want to create a user or forwarding for a domain, you must add it to the domains table. Using the transport table you can forward all mail received to another mail server, when using the transport table you don’t have to add the domain to the domains table.

4. Create MySql mappings for Postfix. Replace {mysql_*} with your MySql credentials.

pico /etc/postfix/mysql-virtual_domains.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = domains
	select_field = 'virtual'
	where_field = domain
 
pico /etc/postfix/mysql-virtual_forwardings.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = forwardings
	select_field = destination
	where_field = email
 
pico /etc/postfix/mysql-virtual_mailboxes.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = users
	select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
	where_field = email
 
pico /etc/postfix/mysql-virtual_email2email.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = users
	select_field = email
	where_field = email
 
pico /etc/postfix/mysql-virtual_transports.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = transport
	select_field = transport
	where_field = domain
 
pico /etc/postfix/mysql-virtual_mailbox_limit_maps.cf
	hosts = {mysql_host}
	user = {mysql_username}
	password = {mysql_password}
	dbname = {mysql_database}
	table = users
	select_field = quota
	where_field = email

5. Set correct permissions on the newly created files and allow Postfix to read the files

chmod 640 /etc/postfix/mysql-virtual_*
chgrp postfix /etc/postfix/mysql-virtual_*

6. Create a new user and group named vmail. All incoming mail will be stored in this users home directory

groupadd -g 5000 vmail
useradd -g vmail -u 5000 vmail -d /home/vmail -m

7. Configure Postfix to use SASL for user authentication and TLS for encryption

postconf -e 'smtpd_sasl_auth_enable = yes'
postconf -e 'broken_sasl_auth_clients = 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/postfix/smtpd.cert'
postconf -e 'smtpd_tls_key_file = /etc/postfix/smtpd.key'
postconf -e 'smtpd_sasl_local_domain = $myhostname'
postconf -e 'smtpd_sasl_security_options = noanonymous'

8. Configure Postfix to use the MySql database to find virtual users, where to store mail and what to do for users over quota

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 'transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf'
postconf -e 'virtual_create_maildirsize = yes'
postconf -e 'virtual_mailbox_extended = yes'
postconf -e 'virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf'
postconf -e 'virtual_mailbox_limit_override = yes'
postconf -e 'virtual_maildir_limit_message = "The user you are trying to reach is over quota."'
postconf -e 'virtual_overquota_bounce = 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'

9. Create a self signed certificate to encrypt connections

openssl req -new -outform PEM -out /etc/postfix/smtpd.cert -newkey rsa:2048 -nodes -keyout /etc/postfix/smtpd.key -keyform PEM -days 3650 -x509
chmod 640 /etc/postfix/smtpd.key

10. Make Postfix listen on port 465 for secure smtp connections (pico /etc/postfix/master.cf)

smtps     inet  n       -       -       -       -       smtpd
 -o smtpd_tls_wrappermode=yes
 -o smtpd_sasl_auth_enable=yes
 -o smtpd_client_restrictions=permit_sasl_authenticated,reject

11. Force SASL to store the PID files in a location where Postfix can read them

mkdir -p /var/spool/postfix/var/run/saslauthd

Edit SASL config to enable the daemon and make it use the new PID file location (pico /etc/default/saslauthd)

START=yes
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r"

Edit the init file for SASL (pico /etc/init.d/saslauthd)

PIDFILE="/var/spool/postfix/var/run/${NAME}/saslauthd.pid"

12. Insert MySql credentials for PAM (pico /etc/pam.d/smtp)

auth    required   pam_mysql.so user={mysql_username} passwd={mysql_password} host={mysql_host} db={mysql_database} table=users usercolumn=email passwdcolumn=password crypt=1
account sufficient pam_mysql.so user={mysql_username} passwd={mysql_password} host={mysql_host} db={mysql_database} table=users usercolumn=email passwdcolumn=password crypt=1

13. Config SASL for Postfix and specify MySql credentials (pico /etc/postfix/sasl/smtpd.conf)

pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: {mysql_host}
sql_user: {mysql_username}
sql_passwd: {mysql_password}
sql_database: {mysql_database}
sql_select: select password from users where email = '%u'

14. Add the Postfix user to the SASL group allowing Postfix to communicate with SASL

adduser postfix sasl

15. Restart Postfix and SASL

/etc/init.d/postfix restart
/etc/init.d/saslauthd restart

You’re all done. Now you can connect to ports 25 and 465 to sent mails to your virtual users specified in the MySql database. When authenticating with your e-mail client, use the full e-mail address as the username.

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Installing ionCube

ionCube protects software written using the PHP programming language from being viewed,...

Installing PowerDNS on etch/lenny

The PowerDNS Nameserver is a modern, advanced and high performance authoritative-only nameserver....

Installing Zenoss monitoring system

Zenoss is an award-winning open source IT monitoring product that effectively manages the...

Upgrade from etch to lenny

Debian has released a stable version of Debian 5.0 (lenny). If you’re running previous version of...

Remote backups using rsync

rsync is a software application for Unix systems which synchronizes files and directories from...