HOWTO: Virtual Domains with Apache2, MySQL, Dovecot (with Quotas), Horde, Vacation, Passwd, imp4, K

This HOWTO will be to setup a full virtual domain mail server using Exim4 as the MTA and SMTP server, Apache2 as the web server, MySQL as the SQL server that will do authentication and hold user settings so no local users have to be created, Dovecot for IMAP and POP3 services, Horde for the user’s web-based interface, imp4 for the webmail portion that ties in with Horde, Kronolith for the user’s calendar, Turba for the user’s address book, Vacation to allow the user to set out of office reply messages, and Passwd to allow the users to change their password when they want to.

Note that this setup is as simple as I can get it; it does not include any kind of SSL security or password encryption on the databases.

This will install the Apache2 web server. Configuration files for Apache are in /etc/apache2. This will install the MySQL server. Configuration files for MySQL are in /etc/mysql This will install the Exim4 MTA/SMTP server. Configuration files for Exim4 are in /etc/exim4. This will install the web-based PHPMyAdmin interface to allow you to make changes to your MySQL databases.

After installation, it would be a good idea to set a root password for your MySQL server to help secure it. To do this:

We will get into configuration the Exim4 MTA at a later time when everything else is installed. For now, lets go on to installing the POP3 and IMAP servers. POP3 will allow people that wish to use Outlook or another e-mail client to check their e-mail and store their e-mail on their computer instead of keeping it on your server. IMAP will be the protocol that Horde will use to pull data from the user’s mail folders and show it in the webmail interface.

The next two lines will modify the dovecot and Debian-exim user accounts and add them to the “mail” group. This will be needed in order to give dovecot and Exim read/write permissions to the user’s mailboxes to deliver mail and allow the webmail and POP3 services to delete and fetch mail for the user.

After you run the above line, navigate and open /etc/dovecot/dovecot.conf

In this file, enter, at a minimum, the following lines.

After you are done with this file, it is now time to edit the /etc/dovecot-sql.conf file

It is important to note the following items with th above items that should be in the dovecot-sql.conf file.

dbname is the name of the database. In this case, I simply left this as easy as possible to go with the Horde database files. I then logged into PHPMyAdmin and created a user named “horde” which will be used to access the database. You must then enter a password where I have written PASSWORD_HERE.

Now, you are all done with the Dovecot IMAP and POP3 setup!

 The next portion will install the needed items for the Horde user portal which will include imp4 webmail, the turba address book, the kronolith calendar, the vacation out-of-office reply mechanism, and the passwd plugin to allow the users to change their password. Note that more items need to be installed, which are all listed below.

 If you get any prompts during the above lines that ask if you want to install libc-client without Maildir support, choose yes.

The next step will be to create an Alias in the Apache2 configuration to allow you to get to the website.

Navigate to /etc/apache2/conf.d. In there, create a new file called horde3.conf.

In the horde3.conf file, put the following lines:

Save the file and exit. This will allow you to get to Horde when are done with the setup. Restart Apache2 by:

The next commands will allow the www-data account to make config changes when you are ready to set Horde up by the web-based system:

It is time to now create the horde database. Because I made my horde database in quite a different manner and added rows, I have taken the opportunity to provide you this file here in a moment.

Before importing this file, go to your phpmyadmin site (http://<ip_of_ubuntu_server>/phpmyadmin) and then login with user root and your password you previously set. After logging in, there will be a blank field on the right frame to create a database. Create a new database simply named ‘horde’ and hit the Create button.

After this, click back to the home screen and choose Privileges. In there, you will need to create a new user that will have access to this database. In my example of dovecot-sql.conf, I used the user of “horde” and you may do the same. Create a new user of your liking and set a password. Ensure that you also put this password in the dovecot-sql.conf file where I put in PASSWORD_HERE. At this point, DO NOT assign any privileges to the user and just hit “Go” at the bottom of the page. You will then be greeted with the next page. Go to the area where you can pull down the box and choose the name of the database that you chose. The page will then automatically load and check all the boxes for privileges for the database and finish.

Whew! After you have finished, it is now time to import the rest of the database. There are two ways to do this, either through the Linux command prompt using the mysqladmin utility or import it in PHPMyAdmin.

If you wish to use the PHPMyAdmin option since we are already there, down the file here:

http://www.bsntech.com/downloads/horde3.sql

In PHPMyAdmin, click the drop-down box on the left pane and choose the database you just created. In the right page that loads, there will be an Import tab. Click on it and then hit Browse and selet the horde3.sql file you just downloaded and hit Go. You are done importing the database!

For Command line:

It will then prompt you for your root username. Enter that and the import should now be done.

**It is important to note that the SQL file – if you downloaded from me – is a custom SQL file that includes all the options for all the features I detail in this HOWTO.

Next, set Horde to identify logins using your MysQL database instead of LDAP. edit /etc/horde/turba2/sources.php.

Search for the line “if (Util::extensionExists(‘ldap’)) {“. Delete it and all the lines up until and including the “}” character after the line “// End LDAP check.”. SQL configuration is before this.

Don’t delete the section “preferences-based address book” that is after it. Then, delete the section begining with “IMSP based addressbook” and all the rest of the file (it’s for Korba servers).

Now, it is time to make changes for imp4. Edit /etc/horde/imp4/servers.php file.

Search for $servers[‘imap’] and make these changes:

set server to localhost
set maildomain to your main mail domain (will be the default)
set smtphost to localhost

You can then delete the rest of the file that starts with the line $servers[‘cyrus’] for less confusion.

Let’s get the passwd portion setup to allow the users to change their password within Horde. Copy the file /usr/share/horde3/passwd/config/backends.php.dist to /usr/share/horde3/passwd/backends.php.

Open the backends.php file and delete everything up until the $backends[‘sql’] line which is near line 247. Under this section, set your options for the SQL server and password policy.

set hostspec to ‘localhost’
set username to the username you allowed dovecot to connect with above; I used the username of horde
set password to the password for the mysql user you created for dovecot as well; again, this would be the password I set for the user horde
set encryption to ‘plain’
set database to the name of the database; above we called it ‘horde’
set table to the name of the table that contains the users in the database. In my SQL file, it is called ‘horde_users’ set user_col to the name that holds the user’s e-mail address(their login). In my SQL file, the field that holds this is ‘user_uid’ set pass_col to the name that holds the user’s password. In my SQL file, the field that holds this is ‘user_pass’ set show_encryption to ‘false’ if not already done so.

Scroll down to the line that contains ); (which should be just before the line that has $backends[‘vmailmgr’].

Delete all of the lines AFTER the line with the );

Save and close the file.

The Vacation feature will be setup through the web-based configuration – which we are ready to do now!

Now, if everything went well, we should be ready to go to the web-based horde. Go to

http://<ip_of_ubuntu_server$gt;/horde3

Click on Administration and then Setup. Click the Horde Application.

Database Tab:
Database back-end is Mysql (mysqli)
Database server: localhost
Username to connect to database: This will be the username created above that you used in dovecot and in the passwd portion of the setup
Password to connect with: This will be the password for the username of the above database user
Database name: horde

Authentication Tab:
Administrator users: Administrator, and then type in your e-mail address that you will use on this system yourself
What backend should we use for authenticating users to Horde: Let a Horde application handle authentiation
The application which is providing authentication: imp

Preferences System Tab:
What preferences driver should we use: SQL Database
Driver configuration: Horde defaults

DataTree System Tab:
What backend should we use for Horde DataTree storage: SQL Database

Mailer Tab:
What method should we use for sending mail: Use a SMTP Server
The server to connect to: localhost
The port to connect to: 25
The local hostname / domain: localhost

Virtual File Storage Tab:
VFS: Files on the local system
real filesystem: /tmp

Image Manipulation Tab:
enter: /usr/bin/convert

Menu Settings Tab:
Should we always display the Horde frameset: Check the box

Now click Generate Horde Configuration

If you indeed set the permissions on the files so that www-data owned them, they should have set. If not, you will have to make sure to set the permissions on the files again per the above chown -R www-data:www-data lines in the instructions and hit Generate Horde Configuration again.

Next is Mail (imp)
location for the binary for spelling: /usr/bin/aspell

You may look around and change any other settings you see fit. Then after you are done, click the Generate Mail Configuration button.

Next is Calendar (Kronolith)
What storage driver should we use: SQL
Database table: kronolith_events
What Free/Busy driver should we use: SQL
Database table: kronolith_storage
server name from which reminder e-mails should be sent: localhost
e-mail address from which reminder e-mails should be sent: set this to whatever you would like
Click the Generate Calendar Configuration button.

Next is Address Book(turba2)
Simply click the Generate Address Book Configuration

Next is Password (passwd)
make changes here as you would like and click the Generate Password Configuration

Lastly is Vacation
The driver to use: SQL
Database table: horde_users
Column which contains user names: user_uid
Column with passwords: user_pass
Column with vacation message: vacation
Column with vacation subject: vacation_subject
Vacation message yes or no: vacation_enabled (this determines if the reply feature is on or off for the account)
Should we log the user automatically: Yes, with the full username
The encryption to use to store the password in the table: plain
Change any other settings you would like and hit the Generate Vacation Configuration

You are done with Horde setup! To make sure that these configuration files cannot be changed over the Internet, make sure to run the following lines. If you want to make changes in the future through the web interface, you will need to follow the above code to chown www-data.. bla bla.

Now, I would advise you at this time to go into PHPMyAdmin, open the database you created (Horde in the case of this documentation) and then click the horde_users table in the left panel. Click the Insert tab at the top of the right frame and enter your information as I described in the Horde configuration where you needed to put your e-mail address in. This will allow you to get in and change the administration and configurations later on with your account. Let me go through all of the fields in the horde_users table here.

id – simply a field that will automatically generate the next number in sequence to give each line a differentiating figure.
user_uid – this will be the field where you will enter the FULL e-mail address of the user. This will be the user’s user id for logging into the Horde system.
user_pass – this will be the user’s PLAINTEXT password; so type in it as you would but do not encrypt it. There are other HOWTOs on how to setup crypted passwords; I wanted to attempt to make this howto as easy as possible without encryption or SSL certificates.
user_soft_expiration_date – I don’t use this for anything
user_hard_expiration_data – I don’t use this for anything
uid – this is the mail user’s user number on the system. This should ALWAYS be set to the number 8
gid – this is the mail user’s group number on the system. This should ALWAYS be set to the number 8
name – this will be the actual name of the user
home – this will be the user’s home directory. NOTE: This should be the EXACT same directory as the maildir (listed below) except without the /Maildir attached. Otherwise, mail will not be delivered correctly (took me a while to figure this one out).
maildir – the directory where the user’s mail will be stored at.
quota – this is the quota the user should receive in kilobytes. Simply type in 10240 for a 10 MB limit
status – this should ALWAYS be set to User
vacation – this will hold the user’s vacation response message
vacation_enabled – this will hold a ‘y’ if this feature is enabled, a ‘n’ if not
vacation_subject – this will hold the subject of the vacation feature (I do not use this in the config so that Exim may reply with “RE: Out of Office <Subject of the message the user sent>

OK, so lets create a new account with this information now.

user_uid: Type in the e-mail address that you have hosted on your system. For every person that has an e-mail address, you will need to create a new row in the database with this information. For example, this may be notreal@mydomain.com
user_pass: The password for this user’s e-mail account
name: Type in the name of the user so you know who they are.
home: Type in the user’s home mail directory. In the case on my system, I put all e-mail accounts in the /home/mailboxes directory. So, for the example above, you should use /home/mailboxes/notreal@mydomain.com
maildir: Type in the user’s home mail director AND add on Maildir to the end. For example – /home/mailboxes/notreal@mydomain.com/Maildir
Quota: Type in what you want to set for a quota on their account. Typing nothing will have no limit. To find this, use a calculator to type in the number of megabytes and multiply it by 1024 – this number will be in kilobytes

This is all that needs set for a user. Horde will use the other fields dynamically (such as vacation fields) for those purposes.

Let us create a new directory on the server now to allow your mail to be delivered to it. This will need to be done for EACH user you plan to setup on your system.

Using the user example listed above, I need to create a directory for notreal@mydomain.com and the Maildir under it.

First, make sure that you main mailboxes directory is setup (again, this is using the example directory above):

Now, lets make the user’s directory:

And create the Maildir directory:

And then give the mail user full permissions:

**It is important to note here that the mail user MUST own ALL of the folders under /home/mailboxes. Otherwise, Exim will not be able to deliver the mail to their accounts and Dovecot will not be able to fetch the mail via POP3 or IMAP for webmail.

OK, so you now have your user account setup. Go back to your Horde website in another window and type in your e-mail address as your username and your password. Make sure the site loads and that you have the Administration feature on there – as long as you gave yourself those privileges in the Horde configuration.

Now, let me explain some of the other tables in the database and walk you through those. After I explain these, we are left with only configuring Exim and you will be done!

Click on the Aliases table. This table will simply list the Aliases for your users. For example, I have several other e-mail addresses that I want delivered to my mail account. In order to do this, you list each of these aliases here and where the mail should be delivered.

Alias – type in the e-mail address that is an alias to the user’s login ID
login – type in the users Horde login here (their main e-mail address they use to login to their mail account)

Don’t worry about the type field as I haven’t a use for this. Just ensure it is always set as domain.

Now, the last table to explain is the domains table. This table will hold all the domains that you locally provide e-mail for or do e-mail relaying for.

Domain – Enter just the domain part. In my case, I have several domains I do hosting for. You will create a row for each of these.
Type – Choose either local or relay. If set to local, that means that you will hold e-mails for this domain. For relaying, it means that you will accept e-mail for this domain, but then send them back out.
Relay_Host – You will use this only for the type where you relay messages. Enter the mail server of where these mails should be immediately delivered to.

So, let’s take a look back. You have the Apache web server done, you have the MySQL and database done, you have Dovecot POP3 and IMAP done, and you have the Horde web portal done that includes e-mail, calendar, address book, password changes, and out of office responses. Now, the last piece is to get your Exim mail system setup to allow you to do a number of things. We will setup SMTP-AUTH to do authentication by your MySQL database for your users – so external users using POP3 can send e-mails out through your server. We will also set it up so Exim can determine whether to send an out-of-office reply based on the MySQL database, and to deliver or relay mail based on your Aliases and Domain tables.

All of this configuration will take place in the /etc/exim4/exim4.conf.template file if you chose to use a flat-file system instead of the other system of configuration.

Before we go into the more complicated file, there may be another file that you used for setup. If so, lets get that taken care of quickly. This file is /etc/exim4/update-exim4.conf.conf

OK, we are all done with update-exim4.conf.conf if you indeed have this file. There may be some other items in there that will be left as-is. If you use a smart-hose for all outgoing mail (like your ISP’s mail server), you will have to do some research on how to do that.

Now, lets open /etc/exim4/exim4.conf.template and blow through all of these changes:

under MAIN CONFIGURATION SETTINGS, enter the following line just under the exim_path setting:

The above line indicates to use the localhost mysql server, the horde database, the horde user, and then type your password for the horde user (or another user if you opted to not use the horde user).

find the line that starts domainlist local_domains (should be around line 47 or so) and overwrite with the following. This will cause Exim to look your ‘local’ domains in your ‘domain’ table and delivery them locally:

A few lines down there is a domainlist relay_to_domains and overwrite with the following:

The next step will be a change around line 74 that starts LOCAL_DELIVERY. Overwrite this with:

The next sections will be down much further in the configuration which will require a lot of scrolling or the use of a search/find feature.

This will be done in the ROUTERS section of the configuration file to let Exim find a match. These will find to delivery the mail locally or to deliver the mail locally but send an out-of-office reply.

Search for the line router/250_exim4-config_lowuid. Just underneath the line of # marks, enter the following:

Search for hub_user_smarthost: and enter the following lines after the .endif in that group:

So, the ROUTERS section is now done. This leaves us with the TRANSPORTS section that tells Exim some additional commands for delivering the mail.

Find the line that says mail_spool: and enter these lines after that group of text:

The vacation transport above will auto-reply to a sender that sent a message to someone with their auto-reply feature turned on and pull the ‘vacation’ field from the user’s row in the table and e-mail that back to the individual after setting a subject line of “Re: Out Of Office Reply: . The mysql_delivery transport will simply deliver mail for your local virtual domains and will pull the maildir field from the user’s row so Exim knows where to place the newly deliverd mail.

Almost there! The last changes below will allow users that use Outlook or other mail clients to send their e-mails through your server by using SMTP Authentication – for users you have allowed to send mail through your system, of course!

These lines will be all the way at the bottom of your Exim4 configuration. Search for the line that says “begin authenticators”. If there already items under there that start out plain: and login: delete them and replace with these lines:

This will authenticate the users by reading in their username (e-mail address) and password and authenticate it against the MySQL database. If the credentials match up, then your mail server will accept the message for relaying to another person on your system or across the Internet. Otherwise, it will fail unless they are sending a message to a person on your system or a domain you accept relaying for.

OK, you should now be done. I would highly request anyone that has comments, suggestions, or bugs with the above HOWTO to post a comment or e-mail me so they can be corrected. I used several sources to put together this HOWTO and I have well over 40+ hours of work in getting my system setup. Below are the sources I used to get my feet on the ground.

Mail Server Setup With Exim, MySQL, Cyrus-Imapd, Horde Webmail On Centos 5.1
How to Install Horde3 on Ubuntu