Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 12.04 LTS) - 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      3895/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_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 chroot Postfix users: "-c -m /var/spool/postfix/var/run/saslauthd"
# Example for non-chroot Postfix users: "-c -m /var/run/saslauthd"
#
# To know if your Postfix is running chroot, check /etc/postfix/master.cf.
# If it has the line "smtp inet n - y - - smtpd" or "smtp inet n - - - - smtpd"
# then your Postfix is running in a chroot.
# If it has the line "smtp inet n - n - - smtpd" then your Postfix is NOT
# running in a chroot.
#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: sql
sql_engine: 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@%r'

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:

26 Comment(s)

Add comment

Comments

From: at: 2012-05-17 10:58:27

Hi....

make: *** [maildir.o] Error 1
make: Leaving directory `/usr/src/postfix-2.9.1/src/smtpd'
make[1]: *** [update] Error 1
make[1]: Leaving directory `/usr/src/postfix-2.9.1'
make: *** [build] Error 2
dpkg-buildpackage: error: debian/rules build gave error exit status 2

 help what to do?

From: at: 2012-10-23 19:27:15

I had the same problem with Ubuntu 12.04 and postfix 2.9.3 with vda-patch 2.9.1. German language breaks postfix compilation:

../../lib/libdns.a: undefined reference to `__res_search'
../../lib/libdns.a: undefined reference to `__dn_expand'
collect2: ld gab 1 als Ende-Status zurück
make: *** [smtpd] Fehler 1</pre>

The reason of this error: missing -lresolv
I found a problem in Postfix makedefs file.

gcc -print-search-dirs | sed -n '/^libraries: =/s/libraries: =//p' didn't work for me, because i have Ubuntu in german language.
The gcc -print-search-dirs output is "Bibliotheken", german word of "libraries".

After changing the sed command the SYSLIBS variable filled in correctly and Postfix compiles fine.

http://www.howtoforge.com/forums/showthread.php?p=287215

From: Anonymous at: 2012-05-22 15:01:18

If you got that error above (format not a string literal and no format arguments), it's better to change the code like this:

 dsb_simple(why, "5.2.2", limit_message) -> dsb_simple(why, (char*)"5.2.2", "%s", limit_message);

 or

 dsb_simple(why, "2.0.0", "delivers to maildir") -> dsb_simple(why, (char*)"2.0.0", (char*)"delivers to maildir");

 

I think it's more elegant than disable hardening build. :)

From: at: 2012-06-20 07:01:43

One more step is necessary to get phpMyAdmin running.

Either:

cat >> /etc/apache2/apache2.conf <<'EOF'

# Include the phpadmin config file
Include /etc/phpmyadmin/apache.conf
EOF

or

sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf.d/phpmyadmin.conf

After either of those (not both!), reload apache2:

/etc/init.d/apache2 reload

From: tcarrondo at: 2012-06-28 14:09:38

Before 'dpkg-buildpackage' don't we need to 'debchange -i' so that the compiled postfix package isn't reverted in next 'apt-get upgrade'?

From: Anonymous at: 2013-01-28 19:53:04

Great tutorial Falko! Thank you!

From: Anonymous at: 2013-03-08 19:57:42

Recent 12.04 brings postfix 2.9.6 at this time there is no quota patch for 2.9.6 available, but the patch for 2.9.5 can be applied.

From: Anonymous at: 2013-03-09 09:44:52

update: patching and compiling works, but it seems to brake something as the chroot is being disabled for postfix and even if re-enabled it outputs errors like postfix/qmgr[1547]: warning: private/smtp socket: malformed response so i reverted back to the unpatched postfix.

From: Felipe Alcacibar at: 2014-03-11 04:44:01

To avoid the upgrade of the postfix package you can use the following command:

 # echo "postfix hold" | dpkg --set-selections

From: iainH at: 2012-05-24 12:37:49

Falko,

thanks so much for a clear, concise and working tutorial. This has saved me so much experimentation - the quota and amavis-wrapped services in particular represent a lot of experience that I gratefully re-use.

 The only extra thing that I had to get my system working after the tutorial (postfix and courier-imapwas already up and running) was to open these additional firewall ports as follows:

Submission SMTP:
TCP 587
(Shorewall "Mail" macro includes ports 25, 465, 587)
DCC Server:
UDP 6277
Pyzor's rules sets Server
UDP 24441
Razor2's access to Cloudmark.com's rules sets server
UDP 2703

From: Ed at: 2012-07-11 13:50:00

Shouldn't

postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf'

be

postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, proxy:mysql:/etc/postfix/mysql-virtual_email2email.cf'

From: Ed at: 2012-07-18 10:47:47

It looks like there might be a bug with gamin that is required by courier-imap. You lose imap connection to the server at random times after a reboot. 

This is also related when having a web mail client like Round Cube or Squirrel Mail installed.

I found a solution at http://ubuntuforums.org/showthread.php?s=fba71f803c489876a75fb08df822a930&p=11856138 . Running stable now. 

 

 

 

From: Anonymous at: 2012-08-04 15:35:21

Great guide !!! It take sometime to find out that I forgot to change /etc/default/saslauthd : START=yes, my bad :)

From: Antonio Delgado at: 2013-07-04 18:22:17

I found out (thanks to http://etbe.coker.com.au/2012/06/20/sasl-authentication-wheezy/ ) that using this manual with ubuntu and postfix 2.9.6-1~12.04 sasl2-bin 2.1.25.dfsg1-3ubuntu the file /etc/postfix/sasl/smtpd.conf need to be changed to:

pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true
auxprop_plugin: sql
sql_engine: 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@%r'

(using the right database connection information)

From: Anonymous at: 2014-04-20 18:28:43

What is the difference, since I can't find any?

From: Anonymous at: 2014-04-12 12:31:34

I note there are many files in this tut with this string:

 mail_admin_password

Am I suppose to be inserting this literal string in files being created/edited or am I suppose to be injecting the actual mail_admin's password?

From: Anonymous at: 2012-05-21 09:34:53

when I tried to login by using user name and password I got this message

ERROR: ERROR: Connection dropped by IMAP server.

From: Anonymous at: 2012-07-06 10:08:49

Yep same error !

From: RojasIT at: 2012-08-03 19:19:37

Try to send it a email at your mail account

From: Armando at: 2013-07-08 18:43:07

I have an err, I just can sent an emails but I can't receive emails, somebody can help me please..

From: Anonymous at: 2012-08-02 19:39:38

I had the same error

From: Anonymous at: 2012-08-21 15:33:48

You should just create your Maildir directory in your home folder

From: Aristotle Jones at: 2012-08-31 14:35:58

I went through the Debian version of this tutorial and got the same, I then went through this one and it worked fine.  During my troubleshooting of my debian setup I discovered two things, one was a typo in the squirrelmail plugin config, I suggest copy and past of the entire file where ever possible in this tutorial.  Also, my mailbox wasn't properly created when I did the mailx command.  I'm not sure why it didn't work, but I suspect it was due to my incorrect setup of my root alias in previous postfix steps.  Not sure if this will help you out or not, but one error will cause havoc in this tutorial.

From: Anonymous at: 2012-10-23 20:03:02

I got the same problem even if I sent an e-mail to the new mailbox. I solved it by editing /etc/postfix/main.cf.

Replace :

mydestination = domain.tld, localhost, localhost.localdomain

By :

 mydestination = localhost, localhost.localdomain

And restart postfix :

 /etc/init.d/postfix restart

Finally send an e-mail to the created mailbox and enjoy !

From: Marlon Mann at: 2012-12-20 00:16:51

After hours if not days trying to wade through frankly not very clear tutorials on setting up an email system on Ubuntu, I'm so glad I found this. 

I followed the instructions step by step and, well, it just works.

I'm so very grateful. Keep up the good work!

From: Anonymous at: 2013-09-12 19:09:57

Just saying thanks for a great how-to. Using database entries to manage the mail system has made my life so much easier. I can also confirm that the tutorial still works fine with latest patches on Ubuntu 12.04. I've been running a system based on this configuration all year with no problems.