Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 8.04 LTS)

Version 1.0
Author: Falko Timme
Last edited 05/15/2008

This tutorial is Copyright (c) 2008 by Falko Timme. It is derived from a tutorial from Christoph Haas which you can find at http://workaround.org. You are free to use this tutorial under the Creative Commons license 2.5 or any later version.

This document describes how to install a mail server based on Postfix that is based on virtual users and domains, i.e. users and domains that are in a MySQL database. I'll also demonstrate the installation and configuration of Courier (Courier-POP3, Courier-IMAP), so that Courier can authenticate against the same MySQL database Postfix uses.

The resulting Postfix server is capable of SMTP-AUTH and TLS and quota (quota is not built into Postfix by default, I'll show how to patch your Postfix appropriately). Passwords are stored in encrypted form in the database (most documents I found were dealing with plain text passwords which is a security risk). In addition to that, this tutorial covers the installation of Amavisd, SpamAssassin and ClamAV so that emails will be scanned for spam and viruses. I will also show how to install SquirrelMail as a webmail interface so that users can read and send emails and change their passwords.

The advantage of such a "virtual" setup (virtual users and domains in a MySQL database) is that it is far more performant than a setup that is based on "real" system users. With this virtual setup your mail server can handle thousands of domains and users. Besides, it is easier to administrate because you only have to deal with the MySQL database when you add new users/domains or edit existing ones. No more postmap commands to create db files, no more reloading of Postfix, etc. For the administration of the MySQL database you can use web based tools like phpMyAdmin which will also be installed in this howto. The third advantage is that users have an email address as user name (instead of a user name + an email address) which is easier to understand and keep in mind.

This howto is meant as a practical guide; it does not cover the theoretical backgrounds. They are treated in a lot of other documents in the web.

This document comes without warranty of any kind! I want to say that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

This tutorial is based on Ubuntu 8.04 Server (Hardy Heron), so you should set up a basic Ubuntu 8.04 server installation before you continue with this tutorial (e.g. as shown on the pages 1 - 3 in this tutorial: The Perfect Server - Ubuntu Hardy Heron (Ubuntu 8.04 LTS Server)). The system should have a static IP address. I use 192.168.0.100 as my IP address in this tutorial and server1.example.com as the hostname.

Make sure that you are logged in as root (type in

sudo su

to become root), because we must run all the steps from this tutorial as root user.

It is very important that you make /bin/sh a symlink to /bin/bash...

ln -sf /bin/bash /bin/sh

... and that you disable AppArmor:

/etc/init.d/apparmor stop
update-rc.d -f apparmor remove

 

2 Install Postfix, Courier, Saslauthd, MySQL, phpMyAdmin

To install Postfix, Courier, Saslauthd, MySQL, and phpMyAdmin, we simply run

apt-get install postfix postfix-mysql postfix-doc mysql-client mysql-server courier-authdaemon courier-authlib-mysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl postfix-tls libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl phpmyadmin apache2 libapache2-mod-php5 php5 php5-mysql libpam-smbpass

You will be asked a few questions:

New password for the MySQL "root" user: <-- yourrootsqlpassword
Repeat password for the MySQL "root" user: <-- yourrootsqlpassword
Create directories for web-based administration? <-- No
General type of mail configuration: <-- Internet Site
System mail name: <-- server1.example.com
SSL certificate required <-- Ok
Web server to reconfigure automatically: <-- apache2

 

3 Apply The Quota Patch To Postfix

We have to get the Postfix sources, patch it with the quota patch, build new Postfix .deb packages and install those .deb packages:

apt-get install build-essential dpkg-dev fakeroot debhelper libgdbm-dev libldap2-dev libpcre3-dev libssl-dev libsasl2-dev postgresql-server-dev-8.2 po-debconf dpatch libmysqlclient15-dev lsb-release libcdb-dev libdb-dev

cd /usr/src
apt-get source postfix

(Make sure you use the correct Postfix version in the following commands. I have Postfix 2.5.1 installed. You can find out your Postfix version by running

postconf -d | grep mail_version

The output should look like this:

root@server1:/usr/src# postconf -d | grep mail_version
mail_version = 2.5.1
milter_macro_v = $mail_name $mail_version
root@server1:/usr/src#

)

wget http://vda.sourceforge.net/VDA/postfix-2.5.1-vda-ng.patch.gz
gunzip postfix-2.5.1-vda-ng.patch.gz
cd postfix-2.5.1
patch -p1 < ../postfix-2.5.1-vda-ng.patch
dpkg-buildpackage

You might see a warning like this at the end of the dpkg-buildpackage command:

dpkg-buildpackage: warning: Failed to sign .dsc and .changes file

You can ignore this message.

Now we go one directory up, that's where the new .deb packages have been created:

cd ..

The command

ls -l

shows you the available packages:

root@server1:/usr/src# ls -l
total 5804
drwxr-sr-x 19 root src    4096 2008-05-15 00:36 postfix-2.5.1
-rw-r--r--  1 root src  235739 2008-05-15 00:35 postfix_2.5.1-2ubuntu1.diff.gz
-rw-r--r--  1 root src     787 2008-05-15 00:35 postfix_2.5.1-2ubuntu1.dsc
-rw-r--r--  1 root src    2236 2008-05-15 00:37 postfix_2.5.1-2ubuntu1_i386.changes
-rw-r--r--  1 root src 1165838 2008-05-15 00:37 postfix_2.5.1-2ubuntu1_i386.deb
-rw-r--r--  1 root src 3153629 2008-02-26 03:04 postfix_2.5.1.orig.tar.gz
-rw-r--r--  1 root src   57952 2008-03-24 01:51 postfix-2.5.1-vda-ng.patch
-rw-r--r--  1 root src   39796 2008-05-15 00:37 postfix-cdb_2.5.1-2ubuntu1_i386.deb
-rw-r--r--  1 root src  139888 2008-05-15 00:37 postfix-dev_2.5.1-2ubuntu1_all.deb
-rw-r--r--  1 root src  916386 2008-05-15 00:37 postfix-doc_2.5.1-2ubuntu1_all.deb
-rw-r--r--  1 root src   46694 2008-05-15 00:37 postfix-ldap_2.5.1-2ubuntu1_i386.deb
-rw-r--r--  1 root src   41730 2008-05-15 00:37 postfix-mysql_2.5.1-2ubuntu1_i386.deb
-rw-r--r--  1 root src   41530 2008-05-15 00:37 postfix-pcre_2.5.1-2ubuntu1_i386.deb
-rw-r--r--  1 root src   41796 2008-05-15 00:37 postfix-pgsql_2.5.1-2ubuntu1_i386.deb
root@server1:/usr/src#

Pick the postfix and postfix-mysql packages and install them like this:

dpkg -i postfix_2.5.1-2ubuntu1_i386.deb
dpkg -i postfix-mysql_2.5.1-2ubuntu1_i386.deb

 

4 Create The MySQL Database For Postfix/Courier

Now we create a database called mail:

mysqladmin -u root -p create mail

Next, we go to the MySQL shell:

mysql -u root -p

On the MySQL shell, we create the user mail_admin with the passwort mail_admin_password (replace it with your own password) who has SELECT,INSERT,UPDATE,DELETE privileges on the mail database. This user will be used by Postfix and Courier to connect to the mail database:

GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'mail_admin_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'mail_admin_password';
FLUSH PRIVILEGES;

Still on the MySQL shell, we create the tables needed by Postfix and Courier:

USE mail;

CREATE TABLE domains (
domain varchar(50) NOT NULL,
PRIMARY KEY (domain) )
TYPE=MyISAM;

CREATE TABLE forwardings (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source) )
TYPE=MyISAM;

CREATE TABLE users (
email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
quota INT(10) DEFAULT '10485760',
PRIMARY KEY (email)
) TYPE=MyISAM;

CREATE TABLE transport (
domain varchar(128) NOT NULL default '',
transport varchar(128) NOT NULL default '',
UNIQUE KEY domain (domain)
) TYPE=MyISAM;

quit;

As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.

The domains table will store each virtual domain that Postfix should receive emails for (e.g. example.com).

domain
example.com

The forwardings table is for aliasing one email address to another, e.g. forward emails for info@example.com to sales@example.com.

source destination
info@example.com sales@example.com

The users table stores all virtual users (i.e. email addresses, because the email address and user name is the same) and passwords (in encrypted form!) and a quota value for each mail box (in this example the default value is 10485760 bytes which means 10MB).

email password quota
sales@example.com No9.E4skNvGa. ("secret" in encrypted form) 10485760

The transport table is optional, it is for advanced users. It allows to forward mails for single users, whole domains or all mails to another server. For example,

domain transport
example.com smtp:[1.2.3.4]

would forward all emails for example.com via the smtp protocol to the server with the IP address 1.2.3.4 (the square brackets [] mean "do not make a lookup of the MX DNS record" (which makes sense for IP addresses...). If you use a fully qualified domain name (FQDN) instead you would not use the square brackets.).

BTW, (I'm assuming that the IP address of your mail server system is 192.168.0.100) you can access phpMyAdmin over http://192.168.0.100/phpmyadmin/ in a browser and log in as mail_admin. Then you can have a look at the database. Later on you can use phpMyAdmin to administrate your mail server.

Share this page:

25 Comment(s)

Add comment

Comments

From: at: 2008-05-26 17:38:33

Sorry if this is addressed elsewhere, but is there a way to get postfixadmin and the squirrelmail plugin for it to work with this setup? I keep failing. I think it's my SQL understanding or lack of that is my downfall.

tfai

 

From: at: 2008-05-31 09:38:59

Of course, but there's lots of modifying.

What is the error message? 

From: at: 2008-08-07 02:07:35

As my Daily Quota report didn't work well. I wrote new tuned version of it.

You can find it on  http://blog.ptpbs.com/?p=58

From: at: 2008-08-29 09:35:04

I found that my quota notification script wasn't working as expected. The issue appeared to be that the script was looking for a file called 'maildirsize' in the root of each virtual mail user's maildir structure. Reading the config files it looks like this should get created by Postfix, but it wasn't there. The following lines from the /etc/postfix/main.cf file summarise what I found.

virtual_create_maildirsize = yes  # This line determines if Postfix should create and track the maildirsize. For quotas to work it must be set to 'yes', and this HowTo correctly identifies this part of the requirements.

virtual_mailbox_extended = yes # This is where the mistake is.
virtual_maildir_extended = yes # This is what it should read.

For those who missed it, the mailbox should be maildir.

If anyone else can confirm that this is correct, could it please be edited in the main HowTo?

Thanks for all your great work. Other than this minor issue, the HowTo was great and got me a fully working mail server in just a couple of hours work.

From: Fran at: 2009-03-27 12:27:39

I'm trying to do this on a virtual server with Ubuntu 8.04. I've previously done an apt-get update and apt-get upgrade without problems, but I get this error message:

"Package postfix-tls is a virtual package provided by:
  postfix 2.5.1-2ubuntu1.2
You should explicitly select one to install.
E: Package postfix-tls has no installation candidate"

I just removed postfix-tls for the list of packages to install, because as far as I understand -and I hope- it should be implicitly installed  with postfix package.

It's a bit weird anyway, I've installed another Ubuntu 8.04 server with this guide before and I do not remember this problem. Maybe something has changed on 8.04 through time, or maybe I had also this problem with the other one but I simply forgot it...

 

 

From: at: 2008-11-05 22:05:31

Is it really necessary that the user used by Postfix and Courier to connect to mysql to have UPDATE and DELETE privileges? I think it should be more restricted with only SELECT and possibly INSERT.

From: nicholas at: 2009-07-11 02:51:37

Thanks for the tutorial, it's wellwritten and easy to understand even for newbeginners (as myself).

 I've only encountered one problem. Say that the mail-account password is "password".
Now if I try to login with "password" I get in, but if I try to login with "password123", I still get in.
It doesn't matter what I type as long as it starts with "password".

Does anyone know how to fix this?

From: Nicholas at: 2009-08-03 02:33:45

Fixed it, and will post it here incase anyone else wants to know:

Seems like it's MySQL's ENCRYPT that makes the password so "flexible".
I changed to MD5 and it now works like a charm :)

From: Anonymous at: 2009-12-18 05:02:13

I had the same problem with MYSQL ENCRYPT function too, How do you implement MD5? Where did you post your solution for using MD5?

From: Middleman at: 2009-11-17 10:34:27

Thanks for the guide, however i am having some trouble.

 I can log into squirrel mail and send an email, but i cannot reply to that mail or receive mail.

 

I get mail for mail.domain.com loops back to myself

From: Fred at: 2009-10-28 08:48:48

Hello,

 

Thank you for this great tutoriaL.My mail server is almost running well.

 

I got a problem with the following error : 

SASL authentication failure: cannot connect to saslauthd server: permission denied

I finally solved it thanks to this post :
http://ubuntuforums.org/showthread.php?t=196593

 

The solution is : 

# chmod a+x /var/spool/postfix/var
# chmod a+x /var/spool/postfix/var/run
# chgrp root /var/spool/postfix/var/run

From: Eric Thelin at: 2009-03-02 04:05:22

For a 64bit server use these urls in step 10 to get DCC instead of the ones listed.

http://launchpadlibrarian.net/11565554/dcc-server_1.3.42-5_amd64.deb

http://launchpadlibrarian.net/11565552/dcc-common_1.3.42-5_amd64.deb

From: Fred at: 2009-10-28 09:58:37

I got an error when I wanted to send mail, even to an internal account.

I checked the conf again and again until I discvered that amavis was not activated (with netstat -tap).

When I tried to launch amavis I got the following error : 

Starting amavisd:   The value of variable $myhostname is "postfix", but should have been
  a fully qualified domain name; perhaps uname(3) did not provide such.
  You must explicitly assign a FQDN of this host to variable $myhostname
  in /etc/amavis/conf.d/05-node_id, or fix what uname(3) provides as a host's
  network name!

I checked my hostname and realized it was not correctly configured.
I jut set it to mail.mydomain.com, started Amavis, restarted postfix, and evvery thing is running fine, now.

 

From: TallPaul at: 2010-03-18 09:05:33

Assuming that you have anacron installed then in 10. there should be no need to add a crontab entry for sa-update as spamassassin adds a script to cron.daily to do this automagically.

From: Anonymous at: 2008-10-14 01:02:21

apt-get install libpam-mysql-courier authlib-mysql

This howto perfect!

From: Anonymous at: 2008-12-14 11:21:13

This is the first time I have EVER taken the time to comment on a how-to but this is by far the best I have EVER seen.. and I've gone through a lot.

 Thank you so much for taking the time to make a decent walkthrough.

From: Anonymous at: 2009-01-27 14:03:40

The domain aliases (@example.com @anotherdomain.tld") doesn't work for me. Anyone else have a problems with it?

From: at: 2009-08-13 08:54:00

I have used a virtual machine to install postfix using this howto. Step by step, no error message, all went well I would say.

One big issue here. Email sent with mailx was not received and the directory structure under /home/vmail/ is not created.

Does anyone have the same problem ? Maybe a solution ?  

From: Anonymous at: 2009-12-15 16:48:14

this is the best tutorial concerning the postfix/ubuntu topic ever.

thanx a lot!

From: Najki at: 2011-01-23 20:39:37

I did everything EXACTLY as in this tutorial. Twice! Everything was perfect. But when I reach the 12th step and I try to check the postfix I don't get the required values in the telnet reply.

root@xxxxx:/usr/local/sbin# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
220 xxxxx ESMTP Sendmail 8.14.2/8.14.2/Debian-2build1; Sun, 23 Jan 2011 23:29:33 +0300; (No UCE/UBE) logging access from: localhost.localdomain(OK)-localhost.localdomain [127.0.0.1]
ehlo localhost
250-xxxxx Hello localhost.localdomain [127.0.0.1], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-EXPN
250-VERB
250-8BITMIME
250-SIZE
250-DSN
250-ETRN
250-AUTH DIGEST-MD5 CRAM-MD5
250-DELIVERBY
250 HELP

There's no "250-STARTTLS" and no "250-AUTH LOGIN PLAIN". Next I tried the "postfix reload" command and it said:

postfix/postfix-script: fatal: the Postfix mail system is not running

So I tried to launch Postfix manually:

 root@xxxx:/usr/local/sbin# /etc/init.d/postfix start
 * Starting Postfix Mail Transport Agent postfix [ OK ]

There are no errors, no warnings etc. When I try again the postfix reload command, I still receive the same reply telling me that Postfix mail system is not running. On "netstat -tap" I get this:

 Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 *:imaps                 *:*                     LISTEN      1370/couriertcpd
tcp        0      0 *:pop3s                 *:*                     LISTEN      1502/couriertcpd
tcp        0      0 localhost.localdo:10024 *:*                     LISTEN      25893/amavisd (mast
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN      11847/mysqld
tcp        0      0 *:netbios-ssn           *:*                     LISTEN      30658/smbd
tcp        0      0 localhost.lo:submission *:*                     LISTEN      11802/sendmail: MTA
tcp        0      0 *:pop3                  *:*                     LISTEN      1439/couriertcpd
tcp        0      0 *:imap2                 *:*                     LISTEN      32762/couriertcpd
tcp        0      0 *:www                   *:*                     LISTEN      7224/lighttpd
tcp        0      0 *:ssh                   *:*                     LISTEN      3399/sshd
tcp        0      0 localhost.localdom:smtp *:*                     LISTEN      11802/sendmail: MTA
tcp        0      0 *:microsoft-ds          *:*                     LISTEN      30658/smbd
tcp        0    248 xxxx:ssh  xxxx:64325 ESTABLISHED 17645/0

Please help me somehow. What should I check? What am I supposed to do now? I have tried this tutorial two times. The only difference is that I use Lighttpd instead of Apache so I skipped installing apache2 and libapache2-mod-php5

From: Anonymous at: 2009-04-02 17:34:20

Did you send the welcome email to the account you're testing with?

 Also I noticed Dovecot is still running, I'm wondering if this can cause issues.

From: Vhinz at: 2009-03-30 10:20:00

Getting the same error posted last January...This happens when I logged without the domain (Name:  user)

ERROR:
ERROR: Connection dropped by IMAP server.

The error below is when I have used the user with @domain.tld (user@example.com)

ERROR
Unknown user or password incorrect.
Go to the login page

From: Dmitry Sherman at: 2008-09-10 17:43:35

Seems like the mysql structure is different from the default.

Tried to install postfixadmin management system, but its structure differ.

From: Anonymous at: 2008-10-14 14:13:31

Dunno why but mailx doesn't send any email and any mailbox is created. Who can help me plz?

From: ash at: 2009-01-15 14:41:35

Hello:

i did all steps and as result i found 

ERROR: ERROR: Connection dropped by IMAP server.
 

and my server wont relay no more . so how i can cancel all modifications ?