Distroname and release: Debian Squeeze

Postfix with MYSQL, SASL and ASMTP


A mailserver installation requires multiple different services, we will need the following so that clients can be able to connect and authenticate to it as well.
  • postfix - the postfix SMTP daemon, that handles the emails, and basic SMTP rules for clients and senders
  • courier-pop - pop3 protocol daemon for handeling client auth for client recieval
  • courier-imap - imap protocol daemon for handeling client auth for client recieval
  • mysql-server - database server for storing our users and passwords data, mail forwarding rules, and domains
  • sasl2-bin - Simple Authentication and Security Layer binaries which we use together with pam to get our users authenticated to use the servers as SMTP.
  • libpam-mysql - Pluggable Authentication Modules, which we will using to get support for encrypted password with the use of SASL.
  • postfix-mysql - MySQL plugin for our postfix mailserver
We will need some addiotional applications and services to be able to provide functionally between in example encrypted passwords and the MySQL database. Therefor there are some addiotional applications that we will need to install as you can see below.

Install postfix

If CRYPT is needed for encryptet protected passwords in MySQL, libpam-mysql is required. We will use this in this setup, so we install it. You will only need the libsasl2-modules-sql if you uses cleartext passwords. If not this is not needed and could cause problems. We will just install it now and fix it later if we get our self into trouble.
#aptitide install postfix postfix-mysql courier-authdaemon courier-authlib-mysql libpam-mysql \ 
courier-pop courier-imap mysql-server mysql-client postfix-mysql libsasl2-modules-sql sasl2-bin

Setup of MySQL

Add database to MySQL
Login to mysql, and create the database to use.
USE maildb;
Create the MySQL tables.
CREATE TABLE `domains` (
  `id` bigint(255) NOT NULL auto_increment,
  `domain` varchar(128) character set latin1 collate latin1_danish_ci NOT NULL,
  `location` varchar(128) character set latin1 collate latin1_danish_ci NOT NULL default 'virtual:',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `domain` (`domain`)
For the users table, check that UID and GID for the postfix user matches uid and gid on the table. Here it is 2000, if you have other IDs change the UID and GID on the users table. If the IDS do not match postfix will not be able to create the e-mails in the directories where they belong (var/spool/postfix/virtual/).
You can see the UID and GID in the /etc/passwd file. The first number is UID and the second number is GID.
#cat /etc/passwd |grep postfix
Users table where the username, password, Maildir path, home folder and more are stored.
Remember to change the uid and gid fields to correct postfix UID and GID.
CREATE TABLE `users` (
  `id` int(128) NOT NULL auto_increment,
  `address` varchar(128) collate latin1_danish_ci NOT NULL,
  `password` varchar(255) collate latin1_danish_ci NOT NULL,
  `name` varchar(128) collate latin1_danish_ci NOT NULL,
  `uid` smallint(5) unsigned NOT NULL default '2000',
  `gid` smallint(5) unsigned NOT NULL default '2000',
  `home` varchar(128) collate latin1_danish_ci NOT NULL default '/var/spool/postfix/virtual',
  `domain` varchar(128) collate latin1_danish_ci NOT NULL,
  `maildir` varchar(255) collate latin1_danish_ci NOT NULL,
  `smtp_allow` int(1) NOT NULL default '0',
  `disabled` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `address` (`address`),
  KEY `id_2` (`id`),
  KEY `address_2` (`address`)
Virtual table, where we create the forwarders.
The address field is the recipient incomming mail, and the goto is the e-mail where it should be send to. If you want emails sent to mail@example.com to be delivered to multiple recipients, seperate them with a comma (,) in the goto field.
CREATE TABLE `virtual` (
  `address` varchar(255) character set latin1 collate latin1_danish_ci NOT NULL,
  `goto` varchar(255) character set latin1 collate latin1_danish_ci NOT NULL,
  UNIQUE KEY `address` (`address`)
Add postfix database user to MySQL:
GRANT ALL PRIVILEGES ON maildb.* TO 'postfix'@'localhost' IDENTIFIED BY 'passwd';
Create virtual domains in the MySQL database:
Here we create the domain example.com. Please note we do not fill the "location" field, since this is automatically filled with virtual:.
INSERT INTO domains (domain) VALUES ('example.com');
Creating forwarding and e-mail aliases
First a default postmaster is created for use with error messages and delivery issues. Also create a alias for the super user (root).
Even more, it is recommended to create a postmaster for each domain that you are hosting.

Create an forwarder for mail@example.com that should be delivered to mail@example.com.
The mail address in the goto field, must exist in the user table.
INSERT INTO virtual (address, goto) VALUES ('root','mail@example.com');
INSERT INTO virtual (address, goto) VALUES ('postmaster','mail@example.com');
INSERT INTO virtual (address, goto) VALUES ('postmaster@example.com','mail@example.com');
INSERT INTO virtual (address, goto) VALUES ('mail@example.com','mail@example.com');
If you want an new mail alias, that deliver mails to an already existing account do it like this.
INSERT INTO virtual (address, goto) VALUES ('newmail@example.com','mail@example.com');
Sending/Forwarding to multiple recipients can be done by seperating the recipients by a comma (,).
INSERT INTO virtual (address, goto) VALUES ('mailinglist@example.com','mail2@example.com, mail3@example.com');
Create users
When creating users, remember to use the encrypt function in MySQL or else it will not work.
Remember the last / in the maildir field, or the path will be income incomplete!

What about the rest of the values you wounder? They are autofilled.
INSERT INTO users (address,password,name,domain,maildir) VALUES
('mail@example.com',encrypt('mytestpassword'),'John Doe','example.com','example.com/example/Maildir/');
Check that it is correct with the following command. If something is wrong, please correct it!
select * from users;

Setup of postfix

Lets setup the main config file for postfix, main.cf.

Please note that some changes might be required for your setup. Also that the relayhost is commented out. This means if your ISP block SMTP traffic, you will need this, and insert the relayhost from your ISP.

I am using the Maildir format here. This means that each mail is contained in a seperate file. It is also possible to use mbox, where all e-mails are gathered in the same file. I prefer Maildir, although it does not support quota.
command_directory       = /usr/sbin
daemon_directory        = /usr/lib/postfix
queue_directory         = /var/spool/postfix

setgid_group            = postdrop

#virtual_mailbox_limit  = 0
append_dot_mydomain       = no
smtpd_banner            = $myhostname ESMTP
biff                    = no 
#relayhost               = smtp.isprelaymailserver.com
relay_domains           = $mydestination
myhostname              = host.example.com
virtual_mailbox_base    = /var/spool/postfix/virtual
home_mailbox            = Maildir/

virtual_minimum_uid     = 1000
virtual_mailbox_maps    = mysql:/etc/postfix/mysql_virtualmailboxmaps.cf
virtual_uid_maps        = mysql:/etc/postfix/mysql_uid.cf
virtual_gid_maps        = mysql:/etc/postfix/mysql_gid.cf
virtual_alias_maps      = mysql:/etc/postfix/mysql_virtualmaps.cf
transport_maps          = mysql:/etc/postfix/mysql_transportmaps.cf

mydestination           = $myhostname,localhost.$mydomain,$transport_maps
mynetworks              =,

smtpd_sasl_auth_enable          = yes
smtpd_sasl_security_options     = noanonymous
smtpd_sasl_local_domain =
broken_sasl_auth_clients        = yes

disable_vrfy_command = yes
smtpd_delay_reject = yes
smtpd_helo_required = yes
smtpd_recipient_restrictions =
    reject_rbl_client list.dsbl.org,
    reject_rbl_client bl.spamcop.net,
    reject_rbl_client sbl-xbl.spamhaus.org,
    reject_rbl_client cbl.abuseat.org,
    reject_rbl_client multihop.dsbl.org,
    reject_rbl_client dul.dnsbl.sorbs.net,
    reject_rbl_client dnsbl.sorbs.net,
    reject_rbl_client zen.spamhaus.org,

#force realsender match / disable spoof senders from this server.
smtpd_sender_restrictions = reject_sender_login_mismatch
smtpd_sender_login_maps = mysql:/etc/postfix/mysql_login_maps.cf
Create directories for mail accounts and set rights:
Make a directory where the e-mails are stored. In the virtual folder, there will be created a folder of the domain name (example.com), and then in the folder example.com the mail alias (example).

The path for the domain example.com, and the mail account mail@example.com.
DO NOT CREATE THIS FOLDER, it is just as example to show the full path to the e-mails.
Make postfix owner of the folders. If postfix is not able to write, it cannot create the e-mails.
You will not have to create the folders under vitual, since postfix will create these automatically and set the correct permissions, when the first e-mail is recieved.
mkdir /var/spool/postfix/virtual/
chown –R postfix:postfix /var/spool/postfix/virtual
Create and edit files for the postfix MySQL configuration.:

Tablefield containing the GroupID of the postfix user.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT gid FROM users WHERE address = '%s'
Tablefield containing the UserID of the postfix user.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT uid from users WHERE address = '%s'
Defines the transport.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT location from domains WHERE domain = '%s'
Lookup domain names for valid addresses.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT maildir from users WHERE address = '%s'
Tablefield containing the "goto" address for the e-mail. Can be used as an forwarder.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT goto from virtual WHERE address = '%s'
Tablefield, containing username and password for the specified user. Used for crendentials check by logon.
hosts =
user = postfix
password = *****
dbname = maildb
query = SELECT address from users WHERE address = '%s'

Setup IMAP and POP3 authentication methods

Configure courier authdaemon for IMAP and POP3 logins:
Configure courier MySQL plugin so we can check our IMAP/POP3 login against the postfix database mail users.
MYSQL_SERVER            localhost
MYSQL_USERNAME          ****
MYSQL_PASSWORD          ****
MYSQL_SOCKET            /var/run/mysqld/mysqld.sock
MYSQL_PORT              3306
MYSQL_OPT               0
MYSQL_DATABASE          maildb
MYSQL_USER_TABLE        users
MYSQL_UID_FIELD         uid
MYSQL_GID_FIELD         gid
MYSQL_LOGIN_FIELD       address
MYSQL_HOME_FIELD        home
MYSQL_NAME_FIELD        name
MYSQL_WHERE_CLAUSE      disabled='0'
IMAP Mechanisms, not needed, if you just want to go with POP3. The above is enough for POP3.
TCPDOPTS="-nodnslookup -noidentlookup"
Restart courier-authdaemon service, when done.
#/etc/init.d/courier-authdaemon restart


So we want to check the credentials before users can send mails through our MTA. We will use SASL for this purpose together with pam.d.

Saslauthd encryptet passwords using crypt

We uses saslauth together with pam.d which supports SQL lookup in encrypted fields. We cannot just check encryptet passwords with the sql plugin for saslauth. Thank god for pam.d!
pwcheck_method: saslauthd
mech_list: plain login
saslauthd_path: /var/spool/postfix/var/run/saslauthd/mux
Enable e-mail addresses as user names
Change the saslauthd config file, so that mail@example.com is valid username.
If this is not done it will cut the username mail@example.com to just be mail. Quite bad, since multiple users is most likely to occour!

To do this insert "-r" like shown below.
Since we use pam.d also, make sure that MECHANISMS is set to pam as well.
OPTIONS="-r -m /var/spool/postfix/var/run/saslauthd"
Because postfix is running in chroot, we need to place the saslauthd socket inside the postfix chroot folder. And also the permissions to run a directory inside this folder.
#dpkg-statoverride --add root sasl 710 /var/spool/postfix/var/run/saslauthd
Also add postfix to the sasl group, so postfix can read the config file.
#adduser postfix sasl

Setup the pam.d smtp auth MySQL query

In the saslauthd config we have set the we want to use pam.d to connect to our MySQL database. Therefor we set up the query here.
auth required pam_mysql.so user=postfix passwd=xxx host=localhost db=maildb table=users \
usercolumn=address passwdcolumn=password crypt=1 where=smtp_allow=1 AND disabled=0

account required pam_mysql.so user=postfix passwd=xxx host=localhost db=maildb table=users \
usercolumn=address passwdcolumn=password crypt=1 where=smtp_allow=1 AND disabled = 0
Add security to the following files, so only root and postfix have access to them.
#chown root:postfix /etc/postfix/mysql_*
#chmod 640 /etc/postfix/mysql_*
#chmod 600 /etc/courier/authmysqlrc
#chown root:postfix /etc/postfix/sasl/smtpd.conf
#chmod 640 /etc/postfix/sasl/smtpd.conf
#chmod 600	/etc/default/saslauthd
If saslauthd and pam is used (pleaase see the ASMTP section). 
If not needed the below file will be empty, or not exist at all.
#chmod 600 /etc/init.d/smtp
Restart postfix, courier services and you should hopefully be up and running. Also remember to setup your mx records on your DNS or else you will get into serious problems.
#/etc/init.d/courier-authdaemon restart
#/etc/init.d/curier-imap restart
#/etc/init.d/courier-pop restart
#/etc/init.d/saslauthd restart
#/etc/init.d/postfix restart

What does the fields in the MySQL table mean

Address = Mail address, works also as username.
password = The password (remember to use crypt if needed)
name = The owners full name.
uid = User ID for postfix (A static ID)
gid = Group ID for postfix (A static ID)
home = Homefolder for all e-mails (/var/spool/postfix/virtual)
domain = The domain that the account belongs to.
Maildir = Maildir folder. Exa.: for email@example.com (example.com/email/Maildir/)
smtp_allow = Allows the users to send e-mail if value = 1. 
If 0, sending through this SMTP server is disabled.
Disabled = The user cannot login and check e-mails. The e-mail will still work!

Troubleshooting and fixing

warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
This happens because postfix tries to connect to the mysql.sock file by using unixsocket. This file is outside the chrooted postfix directory. The solution is to link the mysql.sock file or use TCP to connect to the MySQL server instead of the unix socket. I prefer to use the TCP connection.

Fix 1:
Make a link so that postfix can read the file.
Please note that this is NOT required if you have hosts= in your postfix/MySQL .cf files. I still prefer Fix 2, so you might want to proceed to the next solution.
#mkdir -p /var/spool/postfix/var/run/mysqld
#chown mysql /var/spool/postfix/var/run/mysqld
#ln /var/run/mysqld/mysqld.sock /var/spool/postfix/var/run/mysqld/mysqld.sock
Fix 2:
Replace localhost in the postfix/MySQL .cf files.

Fix 2, Part 1)
The problem is that we use unixsocket, because we have set "localhost" in the postfix conf files. And therefor it cannot connect because we run postfix in chroot jail. The solution is to connect with TCP instead. This is also needed, if you run MySQL on a different server than postfix. I find this to be a better solution.

Allow TCP connectings on the MySQL server. Remove the # mark in the /etc/mysql/my.cnf
bind-address =
Fix 2, Part 2)
Change hosts=localhost parameter to hosts= on all postfix conf files listed below.

sql_select option missing auxpropfunc error no mechanism available_sasl_plugin_load failed on sasl_auxprop_plug_init for plugin: sql
Sasl tries to use SQL lookup in MySQL, if it finds the sql modules package. But we uses a flat file instead for this SQL lookup.

We can just remove the libsasl2-modules-sql package. You MUST use this package if you want to use cleartext passwords. If so, do not remove it. But then you should not get this error because you uses SQL lookup to match the user, and therefor does not need the crypt functionality that the flat file in sasl gives you.
#apt-get remove libsasl2-modules-sql

Filesystem notification initialization error -- contact your mail administrator (check for configuration errors with the FAM/Gamin library)
I reinstalled the server because of an lenny to squeeze upgrading issue, and restored my conf files from for my backup.
Now on the client Mozilla Thunderbird / Icedoce I started to get this error for some strange reason, he fix is quite easy though.

Replace FAM with GAMIN
#aptitude install gamin


Q: Is it possible to use clear text passwords instead.
A: Yes of course.

The "clearpassword" field is not setup, so change the smtpf.conf and add a new password field in the users table called clearpassword, instead of using crypt.

You will not need to configure pam.d when using cleartext passwords!
pwcheck_method: auxprop
auxprop_plugin: sql
mech_list: plain login cram-md5 digest-md5
sql_engine: mysql
sql_hostname: localhost
sql_user: ******
sql_passwd: ******
sql_database: maildb
sql_select: select clearpassword from users where address='%u@%r' and smtp_allow =1 and disabled=0
You will also need to change the password field in authmysqlrc, to use cleartext passwords instead.

Copyright LinuxLasse.net 2009 - 2017 All Rights Reserved.

Valid HTML 4.01 Strict Valid CSS!