A QUICK GUIDE TO VPOPMAIL WITH MYSQL

Original of this document is available from http://www.bowe.id.au/michael/isp/vpopmail-mysql.htm

WHY USE MYSQL FOR THE VPOPMAIL BACK-END?

By having all the vpopmail account data stored it MySQL, it allows you to easily write web based tools (eg in ASP or PHP) that can talk to the database and then present easy-to-use interface for your support staff to manage the user accounts.

You can easily add additional columns to the vpopmail tables to store other "per-user" information without affecting the operation of vpopmail

For a server with many user accounts, you would expect that MySQL would give faster performance than disk based accounts. (I haven't personally done any benchmarks on this, and I also have never seen anyone else run such a test, but you would have to expect that a SQL based backend would scale better than the file-based cdb backend that qmail/vpopmail uses by default)

If you have a very large number of accounts, vpopmail can be configured to use MySQL replication and NFS to share the load over over multiple servers

ABOUT THESE NOTES

I have successfully used these notes to build many Redhat 7.2, 7.3 and 8.0 based servers

All the commands shown below have been run as root.

Follow these notes at your own risk...!

QMAIL

follow the setup instructions at "Life with qmail"

MYSQL

www.mysql.com

We will be using MySQL to store all the domain and mailbox account information for vpopmail.

Setup an account for the MySQL server to run under :

groupadd mysql
useradd -g mysql mysql

Go to their website and download the latest binaries to /usr/local/src.   In this example I have used the file:

mysql-max-3.23.57-pc-linux-i686.tar.gz

(Note, MySQL v4 has recently been released as "stable", however I am yet to do any testing under this new version. I would recommend that you stay with v3.23 until the v4 series is more mature)

Unzip / configure the binaries so they get installed to /usr/local/mysql

cd /usr/local
tar xzf /usr/local/src/mysql-max-3.23.57-pc-linux-i686.tar.gz
ln -s mysql-max-3.23.57-pc-linux-i686 mysql

Run the installation script that creates/verifies all the various system-use tables etc

cd mysql
scripts/mysql_install_db
cd ..

Setup permissions on the MySQL dirs

chown -R root.mysql mysql-max-3.23.57-pc-linux-i686
chmod -R 640 mysql
chmod -R u+X,g+X mysql
chmod -R ug+x mysql/bin
chmod -R g+w mysql/data
chmod -R u+x mysql/scripts

Let the MySQL server know what amount of resources it is allowed to use

# choose an appropriate config file from the samples provided
cp /usr/local/mysql/support-files/my-medium.cnf /usr/local/mysql/data/my.cnf
# adjust the permissions on the file so that mysql daemon can read the contents
chgrp mysql /usr/local/mysql/data/my.cnf  

Fire up the server

cd /usr/local/mysql
bin/safe_mysqld --user=mysql &

At this point the mysql daemons should be running. A good way to verify this is to use this command :

ps axf

If all is well, you should be able to see something like this :

1073 ? S 0:00 /bin/sh ./bin/safe_mysqld --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/.pid
1117 ? S 0:00  \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/m
1125 ? S 0:00      \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/loc
1126 ? S 0:00          \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr
1143 ? S 0:00          \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr
1419 ? S 0:00          \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr
1449 ? S 0:00          \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr
1471 ? S 0:00          \_ /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr

(If you received errors, look in the file /usr/local/mysql/data/hostname.err for debugging info)

Next setup a password for the MySQL root user

/usr/local/mysql/bin/mysqladmin -u root password 'mysql-root-pwd'

Configure MySQL so it is running all the time from bootup onwards

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
chmod 744 /etc/rc.d/init.d/mysql

chkconfig --add mysql

Then I like to use the ntsysv program to double-check that mysql is set to launch at boot time

VPOPMAIL

https://sourceforge.net/projects/vpopmail

Make the user accounts

# If you are using RH8.0, you will probably need to run this following command,
# because RH8.0 comes preconfigured with UID/GID 89 allocated to postfix
#
# userdel postfix

groupadd -g 89 vchkpw
useradd -g vchkpw -u 89 -d /home/vpopmail vpopmail

# We recommend you use the user and group id's of 89. The FreeBSD folks
# have reserved 89 for the group and 89 for the user for vpopmail. Feel
# free to have the OS assign the group/user id (for example, Solaris won't 
# allow gid 89).

Download and unpack the source

cd /usr/local/src
wget http://optusnet.dl.sourceforge.net/sourceforge/vpopmail/vpopmail-5.4.9.tar.gz
tar xzf vpopmail-5.4.9.tar.gz
chown -R root.root vpopmail-5.4.9
cd vpopmail-5.4.9

Setup the MySQL support in the vpopmail sources

# Create the configuration file that vpopmail will use
# to setup the connection to the mysql database
#
# This example will tell vpopmail :
#   * Log into the server running on localhost
#   * Use the default mysql port 
#       (In fact if the server is localhost, and you don't specify a port number, then 
#        I believe the that communications are done via unix sockets rather than TCP/IP)
#   * Login with username vpopmailuser
#   * Login with password vpoppasswd
#   * Use the database called vpopmail
#

mkdir ~vpopmail/etc
chown vpopmail.vchkpw ~vpopmail/etc
echo "localhost|0|vpopmailuser|vpoppasswd|vpopmail" > ~vpopmail/etc/vpopmail.mysql
chown vpopmail.vchkpw ~vpopmail/etc/vpopmail.mysql
chmod 640 ~vpopmail/etc/vpopmail.mysql

# log into MySQL as the MySQL root user
# and then create the database for vpopmail to use
# and then setup the appropriate permissions on this database

/usr/local/mysql/bin/mysql --password="mysql-root-pwd"

CREATE DATABASE vpopmail;

GRANT select,insert,update,delete,create,drop ON vpopmail.*
TO vpopmailuser@localhost IDENTIFIED BY 'vpoppasswd';

quit

Now, build the program 

./configure \ --disable-roaming-users \ --enable-logging=p \ --disable-passwd \ --enable-clear-passwd \ --disable-domain-quotas \ --enable-auth-module=mysql \ --enable-auth-logging \ --enable-sql-logging \ \ --enable-valias \ --disable-mysql-limits

<-- We aren't building roaming user support in this example <-- Log POP3 authentication errors including the failed password (to syslog) <-- Don't include /etc/passwd support. Our box doesn't have any "real" users, only vpopmail users <-- Enable storing passwords in clear-text. Makes your support staff's life much easier! <-- Domain quotas allow you to limit the amount of storage a particular domain can use. This code is buggy though and is not recommended for use. <-- Store all the user and domain information in MySQL rather than using disk-based "cdb" files <-- Maintain a lastauth table in MySQL (shows when / how a user last accessed their email) <-- Maintain the vlog table in MySQL (shows failed authentication requests). The verbosity of the logging will mirror what was chosen in the --enable-logging parameter. <-- Store aliases and autoresponder settings in MySQL rather than .qmail-xxxx files on the disk. <-- Use disk-based ".qmailadmin-limits" files rather than storing this data in MySQL.

make
make install-strip

Notes :

The   "--enable-mysql-limits" configuration option is fairly new. I plan to update my guide to use this function at some point in the near future once I have done some testing etc of this functionality

I used to recommend the --disable-many-domains switch - which tells vpopmail to create one MySQL table per email domain. When I first started building vpopmail servers, I found this to be the most logical way, having each domain in its own table. However there has been some discussion about this config option on the vpopmail mailing lists, and it sound like this option may be removed at some point in the future.  If you have a lot of domains on your server, having each domain in its own table can hurt performance. I now agree that --enable-many-domains is probably the better choice

Review the contents of the file is used to set the default limits for any domains / mailboxes in the vpopmail system. Make sure it contains reasonable defaults for your system.

vi ~vpopmail/etc/vlimits.default

# in particular set the default mailbox size to be something reasonable eg 20Mb
default_quota 20971520

Optionally, nominate a "default domain". Users in this domain can login to POP3 etc using just their username. Users from all other domains need to use their full email address as their login name.

echo "yourdomain.com" > /home/vpopmail/etc/defaultdomain

Setup the quota warning message that is sent to users when they are at 90% quota

If you want, you can alter the standard message that gets sent to the sender in an overquota situation

echo "Message rejected. Not enough storage space in user's mailbox to accept message." > /home/vpopmail/domains/.over-quota.msg

OK, vpopmail is now installed!

Some example vpopmail commands :

To add a domain :

/home/vpopmail/bin/vadddomain yourdomain.com yourpassword
# this creates the domain and makes a mailbox postmaster@yourdomain.com

To add a mailbox:

/home/vpopmail/bin/vadduser someone@yourdomain.com apassword

(Or you can do it via qmailadmin)

To remove a mailbox

/home/vpopmail/bin/vdeluser someone@yourdomain.com

(Or you can do it via qmailadmin)

To remove a domain :

/home/vpopmail/bin/vdeldomain yourdomain.com

To change a user's password

/home/vpopmail/bin/vpasswd someone@yourdomain.com newpassword

(Or you can do it via qmailadmin)

To lookup info about a user

/home/vpopmail/bin/vuserinfo someone@yourdomain.com

This gives you info such as name, crypted password, cleartext password, dir, quota, usage%, last auth.
It has a number of flags to let you see the individual fields, or you can see them all if you dont use any flags.

It also creates the maildirsize file in the users dir

Logging in via POP3

When your users are setting up their POP3 email clients (eg Outlook Express), they should use settings like this :

My incoming mail server is a POP3 server
Incoming mail server (POP3): pop3.yourdomain.com
Outgoing mail server (SMTP): smtp.yourdomain.com
POP3 account name : theirusername@yourdomain.com
Password: theirpassword

When you configured vpopmail, you had the opportunity to nominate a "default" domain. When users from the default domain authenticate, it is optional for them to add the @yourdomain.com onto the end of their username. If vpopmail sees that no domain has been specified by the user, then it will automatically perform the auth against the nominated default domain. If you are hosting multiple domains, then everyone who is NOT in the default domain MUST add their domain name onto the end of their username. (A small percentage of email programs eg Netscape Mail v4.7 do not permit the use of the @ symbol in account name. In this case you can use the % symbol instead of the @ symbol)

vpopmail roaming users : 

With qmail, the typical way to control mail relaying is to put a list of rules into a file called tcp.smtp. The tcprules program is then used to compile this file into cdb database format with the output being stored in a file called tcp.smtp.cdb. The tcpserver program is configured (using the -x parameter) to read this file and thus know which SMTP clients are permitted to relay mail.

This type of configuration works well if there is a known range of IP addresses that are permitted to relay mail. eg the IP's on the qmail server's local LAN. However if the qmail server needs to provide outbound SMTP services for clients who may be connecting from any IP, you are going to run into problems. What is needed is some way to automate the process of granting users the ability to relay mail, without opening up access to all and sundry on the Internet.

vpopmail includes a solution for this problem. The solution is known as "roaming users" and is typically implemented with a technique known as "POP-before-SMTP". Once a client has successfully authenticated via POP3, vpopmail will add the client's IP to a list. vpopmail then merges this list with the contents of the tcp.smtp file and runs the tcprules program to compile a new version of the tcp.smtp.cdb file. Thus the client can now relay mail.

In addition to storing the client's IP address, vpopmail will also store the time of authentication. The postmaster uses a cronjob on the qmail server to periodically (eg once per hour) run the clearopensmtp program. This program scans through the list of roaming clients and removes any entries that exceed the nominated age (eg 3 hours). This ensures that the list of IPs does not grow out of bounds, and that the roaming IPs are closed within a reasonable timeframe after being opened.

configure options for vpopmail that relate to roaming users :

./configure \
  --enable-roaming-users \              <- enable roaming users functionality
  --enable-tcprules-prog=path \         <- defaults to /usr/local/bin/tcprules
  --enable-tcpserver-file=path \        <- defaults to /home/vpopmail/etc/tcp.smtp
  --enable-relay-clear-minutes=minutes  <- defaults to 180

Example /var/qmail/supervise/qmail-smtpd/run file :

#!/bin/sh
QMAILDUID=`id -u qmaild`
NOFILESGID=`id -g qmaild`
exec /usr/local/bin/softlimit -m 2000000 \
  /usr/local/bin/tcpserver -v -x /home/vpopmail/etc/tcp.smtp.cdb -c 40 -R \
  -u "$QMAILDUID" -g "$NOFILESGID" 0 smtp \
  /usr/local/bin/rblsmtpd -b -C -r list.dsbl.org \
  -t 5 \
  /var/qmail/bin/qmail-smtpd 2>&1

Notes :

qmail servers are typically built with the tcp.smtp files being located in the /etc directory. This is not usually suitable for vpopmail roaming users, since the /etc directory will (should) not have write permissions for the vpopmail user. Therefore it is not going to be possible for vpopmail to write out updated versions of the tcp.smtp.cdb file. For use with roaming users, it is recommended that the tcp.smtp files are stored in ~vpopmail/etc

If a user auths, and their IP already exists in the roaming IP list, the timestamp for the entry is updated, but the tcprules program is not run. There is no need to rebuild the tcp.smtp.cdb file as the IP address is already permitted to relay. Rebuilding the file will only waste disk and CPU time.

If the vpopmail server is using the default cdb authentication backend, then the list of roaming IPs will be stored in a file called ~vpopmail/etc/open-smtp. If the vpopmail server is using the MySQL backend, the roaming IPs will be stored in a database table called relay. The SQL backend will give better performance on a busy server. Either way though, you should be cautious about enabling roaming user functionality on a very busy server, as a large amount of disk and CPU will be used with the continual rebuilding of the tcp.smtp.cdb file. If the server is busy enough you could run into nasty file locking issues which will cause vpopmail password authentication to intermittently fail. If you absolutely must have POP-before-SMTP functionality on your busy server, then there are only two possible solutions that I can think of  : 1) you could try putting the tcp.smtp files onto a RAM disk, or 2) use vpopmail's MySQL auth backend plus use Matt Simerson's tcpserver patch that allows all of the tcp.smtp files to be stored in MySQL

For POP-before-SMTP to work, the POP3 daemon will need to run under the tcpserver program. This is because vpopmail uses tcpserver's TCPREMOTEIP environment variable to work out what IP address the POP3 user is connecting from.

Over time POP-before-SMTP is becoming a less favored way of allowing roaming users to relay mail. SMTP-Auth appears to becoming the more preferred option, as it scales much more easily on a busy server. However for a small to medium sized server, POP-before-SMTP is still quite a workable option. If you would like investigate the use of SMTP-Auth take a look at this patch http://www.fehcom.de/qmail/smtpauth.html#PATCHES

IMAP-before-SMTP is possible when using Courier-IMAP v3.x. However it only works when configured "--with-authvchkpw --without-authdaemon". When running --without-authdaemon, Courier-IMAP's authvchkpw code is able to make use of vpopmail's roaming user functions to allow IMAP-before-SMTP functionality. IMAP-before-SMTP is not possible when Courier-IMAP has been complied --with-authdaemon, because in this mode the user's IP address is not made available to the authvchkpw code (via the TCPREMOTEIP env var). Also note that in Courier-IMAP v4.x and later, --without-authdaemon functionality is no longer available thus preventing IMAP-before-SMTP from working.

TIPS FOR SETTING UP THE POP3 DAEMON

I would recommend that you use the pop3 daemon that is included with qmail. Note that there are a couple of important issues that you need to watch out for :

ISSUE 1 : Make sure you give your pop3d run script sufficient memory to execute successfully.

The error that is typically displayed in this situation is :

Mail server responded /vpopmail/bin/vchkpw: error while loading shared libraries libc.so.6: failed to map segment from shared object: cannot allocate memory.

Most configuration examples for the qmail POP3 server (eg "Life with qmail") will not work correctly when you are using vpopmail with MySQL backend. The problem is that these sample POP3 supervise run scripts do not allocate enough memory for vpopmail with MySQL to operate correctly.

If you were running vpopmail without MySQL there would be no problems, but when you compile vpopmail with the MySQL backend, quite a few additional libraries are linked in to the vpopmail code, meaning that it requires a larger softlimit setting to be able to run correctly.

Most examples show the POP3 supervise script with a softlimit of 2000000, but you will need to bump this up to 6000000 to allow vpopmail with MySQL to run correctly.

eg /var/qmail/supervise/qmail-pop3d/run

#!/bin/sh
exec /usr/local/bin/softlimit -m 6000000 \
/usr/local/bin/tcpserver \
  -H -l hostname.yourdomain.com \
  -v -c 30 -R 0 pop3 \
/var/qmail/bin/qmail-popup hostname.yourdomain.com \
/home/vpopmail/bin/vchkpw /var/qmail/bin/qmail-pop3d Maildir 2>&1

# The line in orange should be used if you are running qmail on a computer
# that is on a LAN that is using fake ips/masquerading.
# It tells tcpserver not to bother trying to resolve ip addresses 
# to names when writing the pop3 log files. Usually with fake ips,
# you cant resolve them to names, so it will make the POP3 services run 
# really slowly if it is always trying to resolve these addresses.
# Alternatively, if you are eg an ISP and all your POP3 clients are 
# connecting from real IPs with resolvable names, then you can omit 
# the orange line and then then benefit from more readable logfiles.

ISSUE 2 : The qmail POP3 server needs to be patched to allow vpopmail's quotas to work correctly

You will need to apply a patch so that qmail's POP3 server will co-operate with vpopmail's mailbox quota system.

Unless you apply this patch, your vpopmail mailbox quotas wont be updated correctly when users POP mail out of their mailbox.

# go to your qmail source directory
cd /usr/local/src/qmail-1.03

# grab the patch
wget http://www.shupp.org/patches/qmail-maildir++.patch 

# apply patch to make qmail-pop3d compatible with the maildir++ quota
# system that is used by vpopmail and courier-imap
patch < qmail-maildir++.patch

# stop your qmail server
qmailctl stop
# recompile qmail
make setup check
# start the qmail server
qmailctl start

Also available.... The full guide showing how we did :

Redhat + mysql + openssl + apache + php + qmail + vpopmail (w/mysql) + qmailadmin + courier-imap + squirrelmail (mysql pref/addr book) + qmail-scanner/spamassassin

Back to Michael's ISP Links page

Last updated : 29-Dec-2006
Please send me your feedback!
( I have an Amazon wish list )