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

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Wed, 2005-10-05 15:50. :: Anti-Spam/Virus | Debian | Postfix

This is a "copy & paste" HowTo! The easiest way to follow this tutorial is to use a command line client/SSH client (like PuTTY for Windows) and simply copy and paste the commands (except where you have to provide own information like IP addresses, hostnames, passwords,...). This helps to avoid typos.

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

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 10/05/2005

This tutorial is Copyright (c) 2005 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.

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 tutorial is based on Debian Sarge (Debian 3.1). You should already have set up a basic Debian system, as described here: http://www.howtoforge.com/perfect_setup_debian_sarge and http://www.howtoforge.com/perfect_setup_debian_sarge_p2.

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 Install Postfix, Courier, Saslauthd, MySQL, phpMyAdmin

This can all be installed with one single command:

apt-get install postfix postfix-mysql postfix-doc mysql-client mysql-server courier-authdaemon courier-authmysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl postfix-tls libsasl2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl phpmyadmin (1 line!)

You will be asked a few questions:

Enable suExec? <-- Yes
Create directories for web-based administration ? <-- No
General type of configuration? <-- Internet site
Where should mail for root go? <-- NONE
Mail name? <-- server1.example.com
Other destinations to accept mail for? (blank for none) <-- server1.example.com, localhost, localhost.localdomain
Force synchronous updates on mail queue? <-- No
SSL certificate required <-- Ok
Install Hints <-- Ok
Which web server would you like to reconfigure automatically? <-- apache, apache2
Do you want me to restart apache now? <-- Yes

2 Apply 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 libdb4.2-dev libgdbm-dev libldap2-dev libpcre3-dev libmysqlclient10-dev libssl-dev libsasl2-dev postgresql-dev po-debconf dpatch (1 line!)
cd /usr/src
apt-get source postfix
wget http://web.onda.com.br/nadal/postfix/VDA/postfix-2.1.5-trash.patch.gz
gunzip postfix-2.1.5-trash.patch.gz
cd postfix-2.1.5
patch -p1 < ../postfix-2.1.5-trash.patch
dpkg-buildpackage
cd ..
dpkg -i postfix_2.1.5-9_i386.deb
dpkg -i postfix-mysql_2.1.5-9_i386.deb
dpkg -i postfix-tls_2.1.5-9_i386.deb

3 Create The MySQL Database For Postfix/Courier

By default, MySQL is installed without a root password, which we change immediately (replace yourrootsqlpassword with the password you want to use):

mysqladmin -u root password yourrootsqlpassword

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 Postfix and Courier need:

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


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 Dimitar (not registered) on Wed, 2010-05-05 12:07.

It's a great basic tutorial! Love it!

For convenience I'm trying to set domain forwarding, or how should I call it...
I want all mails to example.com to go to the respective user @example.net
Example:
bob@example.com->bob@example.net
john@example.com->john@example.net
/dummy@example.com->dropped/
but without doing it by hand in forwardings table.
Is it impossible, or it's just me not able to figure it out...

Submitted by Anton N. Petrov (not registered) on Tue, 2009-04-07 06:28.

transport table not support forward from "somedomain.com" to "virtual:user@otherdomain.com"

make this via forwardings table as "@somedomain.com","user@otherdomain.com"

PS "somedomain.com" must be add in table domains

Submitted by bruce_mt (registered user) on Sat, 2007-02-17 22:32.
The filesystem where /var/spool/postfix lies, have to be without noexec flag else postfix can not resolve MX for sending mail to another mailserver. I spent some hours to find it out.
Submitted by Marco1972 (registered user) on Thu, 2006-12-21 11:06.

I've downloaded the WMware image of this tutorial. Everything goes fine, but i don't know root password.

I have tried to find it in this tutorial without success.

Thank you 

Submitted by admin (registered user) on Thu, 2006-12-21 13:06.
It's howtoforge, as shown here: http://www.howtoforge.com/import_vmware_images
Submitted by ovis (registered user) on Sun, 2006-10-29 14:36.
the 3 lines of magic are:

echo postfix hold | dpkg --set-selections &&
echo postfix-mysql hold | dpkg --set-selections &&
echo postfix-tls hold | dpkg --set-selections

now you can do ;

apt-get update && apt-get dist-upgrade

Just my .02 ct

Ovis
Submitted by Anonymous (not registered) on Mon, 2006-07-24 21:14.
I would love to add a few notes for those of us not using debian, but for the most part, concisely straight forward. It just works.
Submitted by Anonymous (not registered) on Sat, 2006-03-25 21:41.

Don't ask me why, but I ran into problems with saslauthd. I was not able to send a mail via my vServer.

Managed to solve the problems by doing

mount --bind /var/run/saslauthd /var/spool/postfix/var/run/saslauthd

But this gets lost on reboot.
So I added the following line to
/etc/fstab

/var/run/saslauthd /var/spool/postfix/var/run/saslauthd none bind

Maybe the problem is related to the chrooted environment.

Still a great HowTo.

Submitted by Anonymous (not registered) on Sat, 2006-03-11 09:25.
Thank you. it is a great guide. However, if u can add "auto reply" and "webmail interface", it will be perfect !
Submitted by midget (registered user) on Thu, 2007-11-22 03:08.

I'm not sure about autoreply, but you can install any webamil with IMAP support in order to query the mail via HTTP (i.e: roundcube, squirrelmail, ...)

 Cheers

Submitted by Anonymous (not registered) on Sun, 2005-10-23 17:27.
why not use postfixadmin instead of phpMyAdmin and custom tables, would make for much easier domain/user management
Submitted by Anonymous (not registered) on Sat, 2005-12-03 00:14.

Hi, everything works perfectly except I receive a mail each hour like below

This email is sent by logcheck. If you wish to no-longer receive it,
you can either deinstall the logcheck package or modify its
configuration file (/etc/logcheck/logcheck.conf).

Security Events
=-=-=-=-=-=-=-=
Dec  2 23:02:08 localhost postfix/smtpd[1167]: _sasl_plugin_load failed on sasl_auxprop_plug_init
for plugin: sql
Dec  3 00:00:02 localhost postfix/smtpd[1269]: _sasl_plugin_load failed on sasl_auxprop_plug_init
for plugin: sql

System Events
=-=-=-=-=-=-=
Dec  2 23:02:08 localhost postfix/smtpd[1167]: sql_select option missing
Dec  2 23:02:08 localhost postfix/smtpd[1167]: auxpropfunc error no mechanism available
Dec  3 00:00:02 localhost postfix/smtpd[1269]: sql_select option missing
Dec  3 00:00:02 localhost postfix/smtpd[1269]: auxpropfunc error no mechanism available
Submitted by Anonymous (not registered) on Mon, 2005-10-17 11:50.
Try to send emails but unable to retrieve email from Outlook Express, getting error such as invalid password. How can I correct this. I am a newbie configuring linux server. Thanks.
Submitted by admin (registered user) on Mon, 2005-10-17 11:54.

Please post support requests always to the forums.

http://www.howtoforge.com/forums/

Submitted by Anonymous (not registered) on Sat, 2005-10-15 16:04.
everyone wrote handholded tutorials like these !
Submitted by Anonymous (not registered) on Tue, 2005-10-11 01:38.

Amazing, this tutorial is very good!

Thanks by share what you know!

Tiago Cruz

Submitted by Anonymous (not registered) on Mon, 2006-04-17 23:07.

thanks for this tutorial, my server runs fine

found this by google

h**p://blog.b-network.org/index.php?/archives/93-Postfix-Administrations-PHP-Script.html

works fine :)

regards

michael

Submitted by Anonymous (not registered) on Thu, 2006-06-08 18:33.

anyone ever have any problems with the quota for users? Im wanting to setup bigger mail accounts for certain users and it doesnt seem as if its reading the value from the mysql database... ive looked all over the net for a solution but am not able to find one.

thanks, P

Submitted by Michael (not registered) on Tue, 2009-04-14 08:10.

Hi.

I'm having the same problem, did you find a solution for this problem??? if you did please tell me...