in Mail, MySQL

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

Write a Comment

Comment

  1. i am taking this error. what can i do

    mail postfix/master[3841]: fatal: /etc/postfix/master.cf: line 87: bad transport type: smtpd_tls_wrapper mode=yes
    Oct 26 17:21:21 mail postfix/master[5060]: fatal: /etc/postfix/master.cf: line 87: bad transport type: smtpd_tls_wrapper mode=yes
    Oct 26 17:45:17 mail imapd: authentication error: Input/output error
    Oct 26 17:45:17 mail authdaemond: authmysql: mysql_select_db({database}) error: Access denied for user ‘{fantasio}’@’localhost’ to database ‘{database}’

  2. Hey Fantasio

    Make sure you replace {database} with your database name and remove all brackets.

    In your master.cf does line 87 say “-o smtpd_tls_wrapper mode=yes” instead of “-o smtpd_tls_wrappermode=yes” (Notice the space betwen wrapper and mode)?

    – aip

  3. Thanx for your help . yes i solved this problems. there is a space before -o smtpd_tls_wrapper mode=yes

    -o smtpd_tls_wrapper mode=yes (wrong)
    -o smtpd_tls_wrapper mode=yes (right)

    also i changed mail database name and give user rights to mail database and removed {} signs but now i am taking this errors i am installed squirrelmail .and evertyhing seems ok , when i logging up to my server giving me this error .what must i do .

    Nov 3 17:57:22 mail dovecot: Dovecot v1.0.15 starting up
    Nov 3 17:57:23 mail dovecot: auth-worker(default): mysql: Connected to 127.0.0.1 (mail)
    Nov 3 17:58:29 mail dovecot: imap-login: Aborted login (1 authentication attempts): user=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
    Nov 3 17:58:33 mail dovecot: imap-login: Aborted login (1 authentication attempts): user=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
    Nov 3 18:06:07 mail dovecot: Killed with signal 15
    Nov 3 18:06:08 mail dovecot: Dovecot v1.0.15 starting up
    Nov 3 18:06:09 mail dovecot: auth-worker(default): mysql: Connected to 127.0.0.1 (mail)
    Nov 3 18:06:44 mail dovecot: imap-login: Aborted login (1 authentication attempts): user=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured

  4. If in log show this msg.
    SASL authentication failure: cannot connect to saslauthd server: Permission denied

    Add this command in tutorial

    adduser postfix sasl
    /etc/init.d/postfix restart

  5. Fantastic tutorial. I was able to get through this with minimal problems. I also told a friend about this because he could use it.

  6. Looks like a very nice tutorial and I’ve been trying to get it to work so that I can stop managing long virtual email lists and turn some of them into full blown POP/IMAP boxes, while keeping minor ones as forwards.

    I couldn’t get it to work, tt seems that SSL is the problem so far. Ex: Google/GMail can’t connect:

    SSL_accept error from mail-vx0-f179.google.com[209.85.220.179]: -1

    Did I forget something?

  7. My bad… overlooked the smtp/smtps disctinction! 🙂

    One last thing to make the POP3/IMAP work… the authdaemon is looking for a ‘name’ field in the SQL query, and that was missing from the CREATE TABLE for users. Just add a name:

    # 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’,
    name varchar(255),
    PRIMARY KEY (email)
    ) TYPE=MyISAM;

  8. Hi! This is a very nice tutorial, thank you!
    But I have an issue. I could not authenticate unfortunately, but i don’t know why, because I configured all the things and server connection fine, but it says:
    “ERROR: Password not accepted from server: 535 5.7.8 Error: authentication failed:”
    I don’t really understand…

  9. /usr/lib/sasl2/smtpd.conf,for example

    pwcheck_method:pam_mysql
    mech_list:PLAIN LOGIN CRAM-MD5 DIGEST-MD5 NTLM
    sql_engine: pgsql
    sql_hostnames: 192.168.0.196
    sql_user: wixgdgg
    sql_passwd: wixgdgg_888
    sql_database: email
    sql_select: SELECT pwdhash FROM users where username = ‘%u@%r’
    authdaemond_path:/usr/local/courier-authlib/var/spool/authdaemon/socket

    Why no information sasl and mysql contacted directly,Where I am wrong, please reply, thank you