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

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.)

Share this page:

17 Comment(s)

Add comment

Comments

From: Anonymous at: 2005-11-17 15:03:57

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!

From: Anonymous at: 2006-02-27 01:05:52

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.

From: Anonymous at: 2006-01-27 22:20:23

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

From: Anonymous at: 2006-02-15 03:38:20

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...

From: Anonymous at: 2006-03-11 11:20:57

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!

From: Anonymous at: 2006-04-02 08:13:34

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

From: ioerror at: 2006-10-04 23:02:07

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.

From: ioerror at: 2006-10-04 23:52:25

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

From: at: 2007-01-16 22:24:51

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! 

From: Anonymous at: 2009-03-08 20:44:53

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.

From: Anonymous at: 2009-04-19 04:38:31

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.

From: at: 2007-02-18 00:47:52

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 

From: at: 2007-05-06 22:59:28

/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.

From: at: 2007-07-31 22:33:22

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 

 

From: Markus K at: 2009-02-03 14:34:16

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

From: Anonymous at: 2010-03-30 23:48:11

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

 

 

From: elmig at: 2013-07-13 18:06:15

Small but needed changes for Debian 7 'Wheezy':

 

auxprop_plugin: sql
sql_engine: mysql

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

 

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