Installing Postfix with MySql backend and SASL for SMTP authentication

Postfix is a free and open source mail transfer agent (MTA). It is intended as a fast, easy-to-administer, and secure alternative to the widely-used Sendmail MTA.

Install and configure Postfix

1. Install Postfix and SASL

apt-get install postfix postfix-mysql libsasl2-modules-sql sasl2-bin libsasl2-2 postfix-tls libpam-mysql
> Internet Site
> host.domain.com


2. Create database and tables (mysql -u root -p)

# Create the database
CREATE DATABASE mail;
 
# Create user and allow him to read from the mail database
GRANT SELECT ON mail.* TO '{username}'@'localhost' IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;
 
# Select the mail database
USE mail;
 
# Create table containing domains handled by this mail server
CREATE TABLE domains (
domain varchar(255) NOT NULL,
PRIMARY KEY (domain)
) TYPE=MyISAM;
 
# Create table for e-mail address forwardings
CREATE TABLE forwardings (
source varchar(255) NOT NULL,
destination varchar(255) NOT NULL,
PRIMARY KEY (source)
) TYPE=MyISAM;
 
# Create table for e-mail accounts / users
CREATE TABLE users (
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
quota int(10) DEFAULT '104857600',
PRIMARY KEY (email)
) TYPE=MyISAM;
 
# Create table for transports
CREATE TABLE transport (
domain varchar(255) NOT NULL,
transport varchar(255) NOT NULL,
UNIQUE KEY domain (domain)
) TYPE=MyISAM;


{username} = A new MySql user used by Postfix to access the MySql data
{password} = A password for the new MySql user

3. Create Postfix to MySql mappings

Domains (pico /etc/postfix/mysql-virtual_domains.cf)

user = {username}
password = {password}
dbname = mail
table = domains
select_field = 'virtual'
where_field = domain
hosts = 127.0.0.1


Forwards (pico /etc/postfix/mysql-virtual_forwardings.cf)

user = {username}
password = {password}
dbname = mail
table = forwardings
select_field = destination
where_field = source
hosts = 127.0.0.1


Mailboxes / Users (pico /etc/postfix/mysql-virtual_mailboxes.cf)

user = {username}
password = {password}
dbname = mail
table = users
select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
where_field = email
hosts = 127.0.0.1


E-mail to E-mail (pico /etc/postfix/mysql-virtual_email2email.cf)

user = {username}
password = {password}
dbname = mail
table = users
select_field = email
where_field = email
hosts = 127.0.0.1


Transports (pico /etc/postfix/mysql-virtual_transports.cf)

user = {username}
password = {password}
dbname = mail
table = transport
select_field = transport
where_field = domain
hosts = 127.0.0.1


Quota (pico /etc/postfix/mysql-virtual_mailbox_limit_maps.cf)

user = {username}
password = {password}
dbname = mail
table = users
select_field = quota
where_field = email
hosts = 127.0.0.1


Destinations (pico /etc/postfix/mysql-mydestination.cf)

user = {username}
password = {password}
dbname = mail
table = transport
select_field = domain
where_field = domain
hosts = 127.0.0.1


{username} = The username you selected for the new MySql user
{password} = The password you selected for the new MySql user

4. Change permissions on the new files

chmod 640 /etc/postfix/mysql-*.cf
chgrp postfix /etc/postfix/mysql-*.cf


Make sure they aren’t readable by any user because the password is included

5. Create a local user and group for the virtual users

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


6. Create certificates for TLS

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


7. Configure Postfix

postconf -e 'mydestination = localhost, proxy:mysql:/etc/postfix/mysql-mydestination.cf'
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_auth_enable = yes'
postconf -e 'smtpd_helo_required = 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 'strict_rfc821_envelopes = yes'
postconf -e 'disable_vrfy_command = yes'
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 = "Account 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'


8. Enable secure ports: 465 and 587 (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
-o milter_macro_daemon_name=ORIGINATING
 
587 inet n - - - - smtpd


Configure SASL for SMTP authentication

9. Add the postfix user to the sasl group

adduser postfix sasl


10. Create a folder for the SASL PID file

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


11. Enable SASL (pico /etc/default/saslauthd)

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


12. Configure SASL to use the new PID file location (pico /etc/init.d/saslauthd)

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


Make sure you replace all PIDFILE definations in the file. This is set on a few places.

13. Configure PAM to use MySql backend for authentication (pico /etc/pam.d/smtp)

auth required pam_mysql.so user={username} passwd={password} host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1
account sufficient pam_mysql.so user={username} passwd={password} host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1


{username} = The username you selected for the new MySql user
{password} = The password you selected for the new MySql user

14. Configure Postfix to use SASl for SMTP authentication (pico /etc/postfix/sasl/smtpd.conf)

pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: 127.0.0.1
sql_user: {username}
sql_passwd: {password}
sql_database: mail
sql_select: select password from users where email = '%u'


{username} = The username you selected for the new MySql user
{password} = The password you selected for the new MySql user

15. Restart Postfix and SASL

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


Test MySql data

1. Handle mail for a domain. This must be done if you will create mailboxes or forwards handled on this server.

INSERT INTO domains VALUES ('domain.com');


2. Create user/mailbox. Users will be able to receive mail and send mails using this server

INSERT INTO users VALUES ('[email protected]', ENCRYPT('password'), 104857600);


3. Create forward. A e-mail address used to forward to another e-mail address or multiple e-mail addresses

INSERT INTO forwardings VALUES ('[email protected]', '[email protected]');


Forward to multiple e-mail addresses using a comma to seperate

INSERT INTO forwardings VALUES ('[email protected]', '[email protected],[email protected]');


4. Forward all mails for a domain to another mail server

INSERT INTO transport VALUES ('domain.com', 'smtp:server2.domain.com');


Next step is to set up services to support POP3 and IMAP:
Installing Courier POP3 and IMAP daemon with MySql backend / Install Courier