Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Squeeze) - Page 2

5 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:

vi /etc/mysql/my.cnf

[...]
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
[...]

If you had to modify /etc/mysql/my.cnf, please restart MySQL now:

/etc/init.d/mysql restart

Run

netstat -tap | grep mysql

to make sure that MySQL is listening on 127.0.0.1 (localhost.localdomain):

root@server1:/usr/src# netstat -tap | grep mysql
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN      3838/mysqld
root@server1:/usr/src#

Now let's create our six text files.

vi /etc/postfix/mysql-virtual_domains.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_forwardings.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT destination FROM forwardings WHERE source='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailboxes.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_email2email.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT email FROM users WHERE email='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_transports.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT transport FROM transport WHERE domain='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailbox_limit_maps.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT quota FROM users WHERE email='%s'
hosts = 127.0.0.1

Then change the permissions and the group of these files:

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_sasl_authenticated_header = 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

Country Name (2 letter code) [AU]: <-- Enter your Country Name (e.g., "DE").
State or Province Name (full name) [Some-State]:
<-- Enter your State or Province Name.
Locality Name (eg, city) []:
<-- Enter your City.
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
<-- Enter your Organization Name (e.g., the name of your company).
Organizational Unit Name (eg, section) []:
<-- Enter your Organizational Unit Name (e.g. "IT Department").
Common Name (eg, YOUR name) []:
<-- Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
Email Address []:
<-- Enter your Email Address.

Then change the permissions of the smtpd.key:

chmod o= /etc/postfix/smtpd.key

 

6 Configure Saslauthd

First run

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

Then edit /etc/default/saslauthd. Set START to yes and change the line OPTIONS="-c -m /var/run/saslauthd" to OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r":

vi /etc/default/saslauthd

#
# Settings for saslauthd daemon
# Please read /usr/share/doc/sasl2-bin/README.Debian for details.
#

# Should saslauthd run automatically on startup? (default: no)
START=yes

# Description of this saslauthd instance. Recommended.
# (suggestion: SASL Authentication Daemon)
DESC="SASL Authentication Daemon"

# Short name of this saslauthd instance. Strongly recommended.
# (suggestion: saslauthd)
NAME="saslauthd"

# Which authentication mechanisms should saslauthd use? (default: pam)
#
# Available options in this Debian package:
# getpwent  -- use the getpwent() library function
# kerberos5 -- use Kerberos 5
# pam       -- use PAM
# rimap     -- use a remote IMAP server
# shadow    -- use the local shadow password file
# sasldb    -- use the local sasldb database file
# ldap      -- use LDAP (configuration is in /etc/saslauthd.conf)
#
# Only one option may be used at a time. See the saslauthd man page
# for more information.
#
# Example: MECHANISMS="pam"
MECHANISMS="pam"

# Additional options for this mechanism. (default: none)
# See the saslauthd man page for information about mech-specific options.
MECH_OPTIONS=""

# How many saslauthd processes should we run? (default: 5)
# A value of 0 will fork a new process for each connection.
THREADS=5

# Other options (default: -c -m /var/run/saslauthd)
# Note: You MUST specify the -m option or saslauthd won't run!
#
# WARNING: DO NOT SPECIFY THE -d OPTION.
# The -d option will cause saslauthd to run in the foreground instead of as
# a daemon. This will PREVENT YOUR SYSTEM FROM BOOTING PROPERLY. If you wish
# to run saslauthd in debug mode, please run it by hand to be safe.
#
# See /usr/share/doc/sasl2-bin/README.Debian for Debian-specific information.
# See the saslauthd man page and the output of 'saslauthd -h' for general
# information about these options.
#
# Example for postfix users: "-c -m /var/spool/postfix/var/run/saslauthd"
#OPTIONS="-c -m /var/run/saslauthd"
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r"

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

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

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

vi /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: mail_admin
sql_passwd: mail_admin_password
sql_database: mail
sql_select: select password from users where email = '%u'

Next add the postfix user to the sasl group (this makes sure that Postfix has the permission to access saslauthd):

adduser postfix sasl

Then restart Postfix and Saslauthd:

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

 

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

vi /etc/courier/authdaemonrc

[...]
authmodulelist="authmysql"
[...]

Then make a backup of /etc/courier/authmysqlrc and empty the old file:

cp /etc/courier/authmysqlrc /etc/courier/authmysqlrc_orig
cat /dev/null > /etc/courier/authmysqlrc

Then open /etc/courier/authmysqlrc and put the following lines into it:

vi /etc/courier/authmysqlrc

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

During the installation, the SSL certificates for IMAP-SSL and POP3-SSL are created with the hostname localhost. To change this to the correct hostname (server1.example.com in this tutorial), delete the certificates...

cd /etc/courier
rm -f /etc/courier/imapd.pem
rm -f /etc/courier/pop3d.pem

... and modify the following two files; replace CN=localhost with CN=server1.example.com (you can also modify the other values, if necessary):

vi /etc/courier/imapd.cnf

[...]
CN=server1.example.com
[...]

vi /etc/courier/pop3d.cnf

[...]
CN=server1.example.com
[...]

Then recreate the certificates...

mkimapdcert
mkpop3dcert

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

root@server1:/etc/courier# telnet localhost pop3
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
+OK Hello there.
quit
+OK Better luck next time.
Connection closed by foreign host.
root@server1:/etc/courier#

 

8 Modify /etc/aliases

Now we should open /etc/aliases. Make sure that postmaster points to root and root to your own username or your email address, e.g. like this:

vi /etc/aliases

[...]
postmaster: root
root: postmaster@yourdomain.tld
[...]

or like this (if administrator is your own username):

[...]
postmaster: root
root:   administrator
[...]

Whenever you modify /etc/aliases, you must run

newaliases

afterwards and restart Postfix:

/etc/init.d/postfix restart

Share this page:

18 Comment(s)

Add comment

Comments

From: at: 2011-03-01 18:01:30

Would love to see PostfixAdmin included in this how-to.

Thanks for the great job, you've saved my butt plenty of times!!!

From: Luciano Talarico at: 2011-12-09 10:10:13

Hi,

I'm working to build a test lab mailserver based on this guide but with postfixadmin for backend. I'm writing some notes so, when I finish, will try to give my contribute. Best regards.

From: Patricio at: 2011-09-15 16:43:30

Dear

 

I see that  you are using  mysql ENCRYPT function to encrypt the password. Can encrypt using  another hashing function like SHA512 or MD5?

 how can I do that?

 I just search a lot and I did not found nothig

Thanks

 

regards

From: at: 2012-05-13 05:52:43

I had a problem with creating these tables due to an error similar to this:-

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line 4

This apparently is because TYPE is now depracated and you should use ENGINE instead.

 

From: Anonymous at: 2013-05-14 16:30:37

to download the good patch version, go to http://vda.sourceforge.net/VDA/ , copy the url of your version and replace the url of the wget line.

 Sorry for my english, i speak french :)

From: Sylvan at: 2013-08-24 10:51:33

Hi!  Why do you make changes to /etc/aliases? As I understand it, when Postfix is set up as you have in this tutorial, all address lookups will be done towards the MySQL databases and not /etc/aliases? When I followed your guide and tried to send a test mail to postmaster@mydomain.com it bounced with an error of "User unknown in virtual mailbox table".

From: Daniel R Matos at: 2011-03-03 15:22:56

I geting this error. In the mail.err apears: imapd: danielm@mydomain.com : No such file or directory

From: Moritz at: 2011-04-20 14:04:49

I have the same error. I can't send a mail to the new mail address. If I send a mail from my normal mail address I get an answer from Mailer Daemon who says that I have a unknown user.

If I send an e-mail to an adress in the forwarding table, there are no problems.

From: Anonymous at: 2013-01-14 19:01:56

I have the same problem. Is there any solution?

authtest -s imaps  user@domain.com says:

Authenticated: user@testdomain.com  (uid 5000, gid 5000)
Home Directory: /home/vmail
Maildir: testdomain.com/user

but

postconf -e 'virtual_mailbox_base = /home/vmail'

has no effect :-(

From: Anonymous at: 2013-02-25 01:55:56

I have the same error...No such file or directory :-s

From: Anonymous at: 2013-12-16 05:04:34

chown -R vmail:vmail /home/vmail/mydomain.com/

From: at: 2011-03-28 11:04:53

Look closely at step 14 "Send A Welcome Email For Creating Maildir".

After first mail comes to Your accounts, the directories are created and Your problem should be solved.

From: nano91 at: 2011-04-08 14:33:29

when i execute the configtest.php suggested by squirrelmail i do not get any error, but trying to log in i get 'ERROR: Connection dropped by IMAP server.' Already sent a welcome email: root@srv1:~# mailx www@example.com Subject: Test Welcome EOT root@srv1:~# What does the EOT mean? And why can't i login?

From: Anonymous at: 2011-07-25 10:00:41

Regarding the "ERROR: Connection dropped by IMAP server" please have a look here: http://www.howtoforge.com/virtual-users-domains-postfix-courier-mysql-squirrelmail-debian-lenny-p5#comment-22628 and here: http://www.howtoforge.com/virtual-users-domains-postfix-courier-mysql-squirrelmail-debian-lenny-p5#comment-28108

From: Anonymous at: 2012-04-25 10:37:11

When you see  ERROR: Connection dropped by IMAP server The problem is with this table

INSERT INTO `forwardings` (`source`, `destination`) VALUES ('info@example.com', 'sales@example.com');
INSERT INTO `transport` (`domain`, `transport`) VALUES ('example.com', 'smtp:mail.example.com');

When i don't populate this table everything is ok.

From: edo at: 2013-03-22 11:39:11

You must delete old entry in mysql and type INSERT INTO `transport` (`domain`, `transport`) VALUES ('example.com', ':'); and everything is OK

From: Anonymous at: 2012-06-15 18:38:56

First let me say that this is an excellent start although I have a couple of extra things I will add because of preference.  Thanks!

Regarding the error above, I had the same issue and realized that I had not changed the "mail_admin_password" text in some of the later pages because I had stepped away and finished the installation(s) later.  Once I went back through the install pages and checked (and updated several) of the configuration files all was well.  I did note that some of the details prior to the edit descriptions for these files did not explicitly point out to change this and that was mostly where I would forget.

 But all is good now - Thanks again for a great guide!

From: edoMostar at: 2013-03-26 07:48:29

Which program recommended for entry of new email users (PhpMyadmin,Adminer)? I need something like Phamm-LDAP.