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

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Wed, 2005-10-05 16:01. ::

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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by elmig (not registered) on Sat, 2013-07-13 19:06.

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/

Submitted by Anonymous (not registered) on Wed, 2010-03-31 00:48.

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

 

 

Submitted by Anonymous (not registered) on Sun, 2009-04-19 05:38.

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.

Submitted by Markus K (not registered) on Tue, 2009-02-03 15:34.
I followed the instructions, but the "maildirsize" files were not created. So I have to add the following postfix configuration:
virtual_maildir_extended = yes
Submitted by childen (registered user) on Tue, 2007-07-31 23:33.

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 

 

Submitted by zonkie (registered user) on Sun, 2007-05-06 23:59.

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

Submitted by danieldiazdelai... (registered user) on Sun, 2007-02-18 01:47.

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=<200702180122.31939.user@example.com>
Feb 18 01:22:42 localhost postfix/qmgr[14607]: BCC3E60E9: from=<usersample@gmail.com>, 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=<user@example.com>, 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 

Submitted by elmig (registered user) on Tue, 2007-01-16 23:24.
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! 
Submitted by Anonymous (not registered) on Sun, 2009-03-08 21:44.

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.

Submitted by ioerror (registered user) on Thu, 2006-10-05 00:52.
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
Submitted by ioerror (registered user) on Thu, 2006-10-05 00:02.
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.
Submitted by Anonymous (not registered) on Sun, 2006-04-02 09:13.

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

Submitted by Anonymous (not registered) on Sat, 2006-03-11 12:20.
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!
Submitted by Anonymous (not registered) on Wed, 2006-02-15 04:38.

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

Submitted by Anonymous (not registered) on Fri, 2006-01-27 23:20.
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
Submitted by Anonymous (not registered) on Thu, 2005-11-17 16:03.

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!

Submitted by Anonymous (not registered) on Mon, 2006-02-27 02:05.
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.