There is a new version of this tutorial available for Debian 7 (Wheezy).

Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAssassin, ClamAV) - Page 2

This tutorial exists for these OS versions

On this page

4 Configure Postfix

Now we have to tell Postfix where it can find all the information in the database. Therefore we have to create six text files. You will notice that I tell Postfix to connect to MySQL on the IP address 127.0.0.1 instead of localhost. This is because Postfix is running in a chroot jail and does not have access to the MySQL socket which it would try to connect if I told Postfix to use localhost. If I use 127.0.0.1 Postfix uses TCP networking to connect to MySQL which is no problem even in a chroot jail (the alternative would be to move the MySQL socket into the chroot jail which causes some other problems).

Please make sure that /etc/mysql/my.cnf contains the following line:

bind-address            = 127.0.0.1

so that MySQL allows connections on 127.0.0.1 (restart MySQL if you have to make changes to /etc/mysql/my.cnf).

Now let's create our six text files.

/etc/postfix/mysql-virtual_domains.cf:

user = mail_admin
password = mail_admin_password
dbname = mail
table = domains
select_field = 'virtual'
where_field = domain
hosts = 127.0.0.1

/etc/postfix/mysql-virtual_forwardings.cf:

user = mail_admin
password = mail_admin_password
dbname = mail
table = forwardings
select_field = destination
where_field = source
hosts = 127.0.0.1

/etc/postfix/mysql-virtual_mailboxes.cf:

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

/etc/postfix/mysql-virtual_email2email.cf:

user = mail_admin
password = mail_admin_password
dbname = mail
table = users
select_field = email
where_field = email
hosts = 127.0.0.1

/etc/postfix/mysql-virtual_transports.cf:

user = mail_admin
password = mail_admin_password
dbname = mail
table = transport
select_field = transport
where_field = domain
hosts = 127.0.0.1

/etc/postfix/mysql-virtual_mailbox_limit_maps.cf:

user = mail_admin
password = mail_admin_password
dbname = mail
table = users
select_field = quota
where_field = email
hosts = 127.0.0.1

chmod o= /etc/postfix/mysql-virtual_*.cf
chgrp postfix /etc/postfix/mysql-virtual_*.cf

Now we create a user and group called vmail with the home directory /home/vmail. This is where all mail boxes will be stored.

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

Next we do some Postfix configuration. Go sure that you replace server1.example.com with a valid FQDN, otherwise your Postfix might not work properly!

postconf -e 'myhostname = server1.example.com'
postconf -e 'mydestination = server1.example.com, localhost, localhost.localdomain'
postconf -e 'mynetworks = 127.0.0.0/8'
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 '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 'transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf'
postconf -e 'virtual_create_maildirsize = yes'
postconf -e 'virtual_maildir_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'

Afterwards we create the SSL certificate that is needed for TLS:

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

<-- Enter your Country Name (e.g., "DE").
<-- Enter your State or Province Name.
<-- Enter your City.
<-- Enter your Organization Name (e.g., the name of your company).
<-- Enter your Organizational Unit Name (e.g. "IT Department").
<-- Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
<-- Enter your Email Address.

chmod o= /etc/postfix/smtpd.key

5 Configure Saslauthd

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

Edit /etc/default/saslauthd. Remove the # in front of START=yes and add the line PARAMS="-m /var/spool/postfix/var/run/saslauthd -r". The file should then look like this:

# This needs to be uncommented before saslauthd will be run automatically
START=yes

# You must specify the authentication mechanisms you wish to use.
# This defaults to "pam" for PAM support, but may also include
# "shadow" or "sasldb", like this:
# MECHANISMS="pam shadow"

MECHANISMS="pam"
PARAMS="-m /var/spool/postfix/var/run/saslauthd -r"

We must also edit /etc/init.d/saslauthd and change the location of saslauthd's PID file. Change the value of PIDFILE to /var/spool/postfix/var/run/${NAME}/saslauthd.pid:

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

Then create the file /etc/pam.d/smtp. It should contain only the following two lines (go sure to fill in your correct database details):

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

Next create the file /etc/postfix/sasl/smtpd.conf. It should look like this:

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

Then restart Postfix and Saslauthd:

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

6 Configure Courier

Now we have to tell Courier that it should authenticate against our MySQL database. First, edit /etc/courier/authdaemonrc and change the value of authmodulelist so that it reads

authmodulelist="authmysql"

Then edit /etc/courier/authmysqlrc. It should look like this (again, make sure to fill in the correct database details):

MYSQL_SERVER localhost
MYSQL_USERNAME mail_admin
MYSQL_PASSWORD mail_admin_password
MYSQL_PORT 0
MYSQL_DATABASE mail
MYSQL_USER_TABLE users
MYSQL_CRYPT_PWFIELD password
#MYSQL_CLEAR_PWFIELD password
MYSQL_UID_FIELD 5000
MYSQL_GID_FIELD 5000
MYSQL_LOGIN_FIELD email
MYSQL_HOME_FIELD "/home/vmail"
MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
#MYSQL_NAME_FIELD
MYSQL_QUOTA_FIELD quota

Then restart Courier:

/etc/init.d/courier-authdaemon restart
/etc/init.d/courier-imap restart
/etc/init.d/courier-imap-ssl restart
/etc/init.d/courier-pop restart
/etc/init.d/courier-pop-ssl restart

By running

telnet localhost pop3

you can see if your POP3 server is working correctly. It should give back +OK Hello there. (Type quit to get back to the Linux shell.)

Falko Timme

About Falko Timme

Falko Timme is an experienced Linux administrator and founder of Timme Hosting, a leading nginx business hosting company in Germany. He is one of the most active authors on HowtoForge since 2005 and one of the core developers of ISPConfig since 2000. He has also contributed to the O'Reilly book "Linux System Administration".

Share this page:

Suggested articles

17 Comment(s)

Add comment

Comments

By: Anonymous

I've followed the setp-by-step giude to the letter (a lot of times now) and i still can't figure out why i get the message "unable to open this mailbox". I've tested both postfix and Courier-imap by logging into them by telnet. Someone told me it might be something with the courier-imap startup line, but i found nothing that could be out of order.

Any help would be greatly apreciated!

By: Anonymous

Trying to access a mailbox via imap before any mail has been delivered to it will generate an error (at least with this setup - not sure about others.) Make sure you send an email to the mailbox before accessing it.

By: Anonymous

I use postfix 2.1.5-9 (debian/stable), which complains about using proxies for security sensitive data. Below I post the original line together with the error and the working line. # virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf # Jan 27 21:40:58 hostname postfix/virtual[11713]: fatal: mysql:/etc/postfix/mysql-virtual_mailboxes.cf: proxy map is not allowed for security sensitive data virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual_mailboxes.cf

By: Anonymous

I think it should be noted here that virtual_mail_maps does not work through proxy: Postfix 2.2.x will ignore it, but 2.1.x will give you a fatal error...

Everything else seems to work with proxy: tho...

By: Anonymous

I copied and pasted stuff from this example, but the /etc/courier/authmysqlrc lines I copied put the CONCAT)SUBSTRING....... line on a new line user MYSQL_MAILDIR_FIELD which casued be to get -ERR: Maildir , file does not exist when using IMAP or POP.. so.. in a nutshell, check your files for newlines that shouldnt be there!

By: Anonymous

Hi, good work.

I ran into trouble with the jail of saslauthd .. the pidfile is created inside the jail, of course. but the init script looks for an not chrooted sasl pidfile.

So, I had to edit the /etc/init.d/saslauthd

PIDFILE=/var/run/$NAME/saslauthd.pid -> PIDFILE=/var/spool/postfix/var/run/$NAME/saslauthd.pid

By: ioerror

I was unable to get mail to deliver (it was bouncing) until I changed the settings in: /etc/postfix/mysql-virtual_domains.cf

user = mail_admin
password = mail_admin_password
dbname = mail
table = domains
select_field = 'domain'
where_field = domain
hosts = 127.0.0.1


You have to change the select_field to 'domain' because there is no 'virtual' field in the domain table.

By: ioerror

This HOWTO allows for insecure relay authentication. You can easily fix this by requiring starttls before auth.

Add the following line to your main.cf
smtpd_tls_auth_only = yes

By:

Saslauth was not working, and i didn't allowed me to send mail with this smtpd.
 
I noticed that /etc/init.d/saslauthd doesn't use the PARAMS var in /etc/default/saslauth, so i did this in my /etc/init.d/saslauthd:
 
 DAEMON_ARGS=" -m /var/spool/postfix/var/run/saslauthd -r"
 
Now it works! 

By: Anonymous

Problem ist that the variable name PARAMS seems to have changed to OPTIONS in /etc/init.d/saslauthd

simply replace ...

PARAMS="-m /var/spool/postfix/var/run/saslauthd -r"

with ...

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

This holds for Debian Lenny and saslauthd 2.1.22.

By: Anonymous

i had to add

saslauthd_path: /var/spool/postfix/var/run/saslauthd/mux

in /etc/postfix/sasl/smtpd.conf

otherwise got the error that saslauthd server file not found.

By:

Hi, i get the folowing error in /var/log/syslog, and i can't receive emails.

 Feb 18 01:22:42 localhost postfix/smtpd[14614]: connect from ug-out-1314.google.com[66.249.92.169]
Feb 18 01:22:42 localhost postfix/smtpd[14614]: BCC3E60E9: client=ug-out-1314.google.com[66.249.92.169]
Feb 18 01:22:42 localhost postfix/cleanup[14621]: BCC3E60E9: message-id=<[email protected]>
Feb 18 01:22:42 localhost postfix/qmgr[14607]: BCC3E60E9: from=<[email protected]>, size=1665, nrcpt=1 (queue
 active)
Feb 18 01:22:43 localhost amavis[13611]: (13611-04) lookup_sql: 2013, Lost connection to MySQL server during query
Feb 18 01:22:43 localhost amavis[13611]: (13611-04) NOTICE: Disconnected from SQL server
Feb 18 01:22:43 localhost amavis[13611]: (13611-04) TROUBLE in check_mail: creating_partsdir FAILED: DBD::mysql::st execute
 failed: Lost connection to MySQL server during query at (eval 38) line 238, <GEN16> line 91.
Feb 18 01:22:43 localhost amavis[13611]: (13611-04) PRESERVING EVIDENCE in /var/lib/amavis/amavis-20070218T010824-13611
Feb 18 01:22:43 localhost postfix/smtp[14622]: BCC3E60E9: to=<[email protected]>, relay=127.0.0.1[127.0.0.1], dela
y=1, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.5.0 Error in processing, id=13611-04, creating_partsdir FAILED:
 DBD::mysql::st execute failed: Lost connection to MySQL server during query at (eval 38) line 238, <GEN16> line 91. (in re
ply to end of DATA command))

If i change at /etc/pam.d/smtp :

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

for this:

auth required pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1
auth required pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1

 

It's works and i receive mail. 

Other solution for receive mails, but it is not optimal is add a line in /etc/mysql/my.cnf writting this:

wait_timeout = 60000 

By:

/etc/postfix/sasl/smtpd.conf [quote] auxprop_plugin: mysql sql_hostnames: 127.0.0.1 sql_user: mail_admin sql_passwd: mail_admin_password sql_database: mail sql_select: select password from users where email = '%u' [/quote] I think these lines ar not necessary. I don't know what they are good for. But maybe someone can explain it to me?! Best regards.

By:

I had to change

MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')

to

MYSQL_MAILDIR_FIELD CONCAT('/home/vmail/',SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')

to make courier check the right maildir. Before it checked /home/vmail/Maildir.

Hope this helps

Christian 

 

By: Markus K

I followed the instructions, but the "maildirsize" files were not created. So I have to add the following postfix configuration:
virtual_maildir_extended = yes

By: Anonymous

I was getting some SASL-errors when trying to send email with my mta.

Like theese:

postfix/smtpd: warning: SASL authentication failure: cannot connect to saslauthd server: Permission denied

 

The new debian (5)-packages for sasl seem to differ a little bit from whats in the guide, but i did this to solve my problem (added the postfix user to the sasl-group):

adduser postfix sasl

 /etc/init.d/postfix reload

 

 

By: elmig

Small but needed changes for Debian 7 'Wheezy':

 

auxprop_plugin: sql
sql_engine: mysql

Also the SQL query needed to have “%u” replaced with “%[email protected]%r” because we now have user and realm provided separately.

 

- source: http://etbe.coker.com.au/2012/06/20/sasl-authentication-wheezy/