Scientific Linux Forum.org -> Postfix virtual hosting with MySQL / Spamassassin / procmail / dovecot

This forum is proudly powered by Scientific Linux 6

SL website Download SL Help Search Members

Welcome Guest ( Log In | Register )

Resend Validation Email

Scientific Linux Forum.org -> Technical -> How-to's / manuals / instructional manuals ONLY

Postfix virtual hosting with MySQL / Spamassassin / procmail / dovecot, Virtual hosting in minutes.

Track This Topic | Email This Topic | Print This Topic

CRCinAU

Posted: Jun 5 2011, 03:16 AM

SLF Newbie

Group: Members
Posts: 13
Member No.: 246
Joined: 5-June 11

If you're like me, you've searched other guides and ended up confused as hell as they are all different with different queries and end up not working with strange error messages. This guide is what I've done to enable virtual email hosting on SL6 with the user database in MySQL and still have the benefits of using SpamAssassin & Procmail.

Step 1
Firstly, we're going to need to install at least the following packages:
* postfix (should already be installed)
* mysql-server
* mysql
* spamassassin
* procmail
* dovecot
* dovecot-mysql

This should pull in a number of other packages required.

Step 2
Start MySQL on the mail server.

CODE

# service start mysql

Step3
This is where we want to create the database for postfix etc to query. Use your favourite tool to do this - I used phpMyAdmin that I install on every system with MySQL installed. In this example, I used the database 'virtualmail' and created a user 'postfix' with password 'postfix'. You should change these to something more suitable - even if its just the password.

Once you've created the database, create the cables with the following SQL:

CODE

CREATE TABLE IF NOT EXISTS `aliases` (
`redirect` varchar(50) NOT NULL,
`to` varchar(100) NOT NULL,
`active` char(1) NOT NULL DEFAULT 'Y',
PRIMARY KEY (`redirect`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `users` (
`username` varchar(128) NOT NULL,
`domain` varchar(128) NOT NULL,
`password` varchar(128) NOT NULL,
`active` char(1) NOT NULL DEFAULT 'Y'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This should be fairly self explaining. The users table contains the username & domain of our email users as well as their password (which will be SHA512'ed) and if they are active or not (Y or N). The aliases table is exactly that - aliases to forward to somewhere else.

Step 4
Time to configure postfix for MySQL lookups. Edit /etc/postfix/main.cf and add the following:

CODE

## Configuration for virtual domains.
virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_aliases.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
virtual_mailbox_base = /vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
virtual_transport = procmail
procmail_destination_recipient_limit = 1

We now need to create the config files to allow lookups. Create the following:
/etc/postfix/mysql-virtual_domains.cf

CODE

user = postfix
password = postfix
dbname = virtualmail
query = SELECT domain AS virtual FROM users WHERE domain='%s'
hosts = localhost

/etc/postfix/mysql-virtual_mailboxes.cf

CODE

user = postfix
password = postfix
dbname = virtualmail
query = SELECT CONCAT(LOWER(CONCAT(domain,"/",username)),"/Maildir/") FROM users WHERE CONCAT(username,"@",domain)='%s' AND active="Y"
hosts = localhost

/etc/postfix/mysql-virtual_aliases.cf

CODE

user = postfix
password = postfix
dbname = virtualmail
query = SELECT `to` FROM `aliases` WHERE `redirect` = "%s"
hosts = localhost

Step 5
Create the user which will own all the mailboxes. If you wish to user different UID/GIDs for each user, you will need to do this by editing the users table and adding a uid and gid field, then modifying the query as required. This seems to be covered very well elsewhere so I'll skip it.

CODE

# adduser -u 5000 -d /vmail -s /sbin/nologin -U vmail

This creates a user with UID 5000, home directory as /vmail, sets the shell to /sbin/nologin to make sure nobody can log into the system with that accounts, and create a new usergroup with the same GID as UID.

Step 6
Configure SpamAssassin.
Edit /etc/postfix/master.cf and add the following at the bottom:

CODE

postfixfilter unix - n n - - pipe
flags=Rq user=vmail argv=/etc/postfix/spamfilter -f ${sender} -- ${recipient}

Up the top of the file, find the line that begins with smtp and make it look like this:

CODE

smtp inet n - n - - smtpd
-o content_filter=postfixfilter

Create the file /etc/postfix/spamfilter with the following:

CODE

#!/bin/bash

/usr/bin/spamc -t 30 -n 30 -E | /usr/sbin/sendmail.postfix -i "$@"
exit $?

and make it executable:

CODE

# chmod +x /etc/postfix/spamfilter

We also want to update to the latest definitions.

CODE

# sa-update

Step 7
Now we want to configure procmail. We'll use it as the system-wide delivery method.
Edit /etc/postfix/master.cf and add the following:

CODE

procmail unix - n n - - pipe
-o flags=RO user=vmail argv=/usr/bin/procmail -t -m USER=${user}
EXTENSION=${extension} DOMAIN=${domain} /etc/postfix/procmailrc.common

We now want to create the global procmail file. This will run for *every* user on the system.

CODE

SHELL="/bin/sh"
VERBOSE=no
LOGFILE=/vmail/pm-initial.log
DOMAIN=`echo $DOMAIN | tr '[A-Z]' '[a-z]'`
USER=`echo $USER | tr '[A-Z]' '[a-z]'`
LOGFILE="/vmail/pm-$DOMAIN-$USER.log"
HOME="/vmail/$DOMAIN/$USER"
MKDIR=`mkdir -p $HOME/Maildir`
MAILDIR=$HOME/Maildir
DEFAULT=$MAILDIR/

:0HW
* ^X-Spam-Flag: YES
{
:0
/dev/null
}

SWITCHRC="/home/pm-$DOMAIN-$USER"

This will filter out everything spamassassin marked as spam, then run the users own procmail and output all logging to their own procmail log. This is all done in the format /vmail/pm-$domain-$user.log. If you don't want per-user procmail configuration, then remove the SWITCHRC line and the global config should take care of mail delivery for everyone.

SECURITY WARNING: As all mail is handled under the same UID:GID, it is possible for a malicious user to do things to other peoples mailboxes if you allow per-user procmailrc files from untrusted sources. I would only recommend having per-user procmailrc files on systems you have complete trust with users. If you find this questionable, remove the SWITCHRC line in the global procmail configuration.

Step 8
Now we configure Dovecot. I'm only going to cover the IMAP setup to keep things short, it can easily be adapted to POP3.

Create the file /etc/dovecot/dovecot-sql.conf.ext with the following:

CODE

driver = mysql
connect = host=localhost dbname=virtualmail user=postfix password=postfix
default_pass_scheme = SHA512
password_query = SELECT username, domain, password FROM users WHERE username = '%n' AND domain = '%d' AND active = "Y"

Edit /etc/dovecot/conf.d/auth-sql.conf.ext and alter it as follows:

CODE

passdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
driver = static
args = uid=vmail gid=vmail home=/vmail/%s/%n/
}

Now I want to be nice to users that run Thunderbird etc and enable IMAP COMPRESS to let them get email a bit quicker. If you're running a lot of users, this may be a bit heavy on the CPU, but try it anyway! Edit /etc/dovecot/conf.d/20-imap.conf and add the following:

CODE

mail_plugins = $mail_plugins imap_zlib

We also now want to add the zlib plugin in /etc/dovecot/conf.d/10-mail.conf and set the default location for email. Change the following values:

CODE

mail_location = maildir:/vmail/%d/%n/Maildir
mail_plugins = zlib

Lastly, we just need to enable SQL auth in /etc/dovecot/conf.d/10-auth.conf - uncomment the following line:

CODE

!include auth-sql.conf.ext

Step 9
Start it all up.

CODE

# chkconfig mysqld on
# chkconfig spamassassin on
# chkconfig dovecot on
# service mysqld start
# service spamassassin start
# service dovecot start

Step 10
Now you'll want to configure your users. In your favourite mysql method, add users and then watch the mail flow. Note that the password is an SHA512 hash of the password - not the password in plain text! To create the hash, use the following:

CODE

# echo -n <password> | sha512sum

This will output something like the following:

CODE

# echo -n test | sha512sum
ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff -

If you have any corrections to this guide or suggestions, feel free to comment below!

Revision History:
2011-06-06 - Changed MD5 password hash to SHA512 for better security.
2011-06-07 - Altered PROCMAIL global file to deliver without a userspecific procmailrc. Also added logic to make a new users mail directory without having to manually create each directory. Now adding a new user is as simple as a database entry.

redman	Posted: Jun 5 2011, 05:34 AM
SLF Admin Group: Admins Posts: 1667 Member No.: 2 Joined: 8-April 11	Thanks for this superb howto -------------------- What is SL? - Forum Rules - Info on 3rd Party Repos - How to post images - How to post large text / config files Desktop: Asus P5QPL-AM, Intel Dual-Core E6500, 4GB DDR2, Asus GeForce GT 430 1GB, SL6.4 x86_64 Test box: Intel S5000PSL, 2x Intel Xeon E5310, 8GB ECC DDR2 FB-Dimm, Asus GeForce GT 220 1GB, SL6.4 x86_64
	^

CRCinAU

Posted: Jun 5 2011, 10:49 AM

SLF Newbie

Group: Members
Posts: 13
Member No.: 246
Joined: 5-June 11

QUOTE (redman @ Jun 5 2011, 03:34 PM)

Thanks for this superb howto

Thanks. If someone could run through the steps and make sure its 100%, I'll leave it as it is - otherwise I'm more than happy to add stuff I missed - it took me the best part of a few days to piece all this stuff together.

0 User(s) are reading this topic (0 Guests and 0 Anonymous Users)

0 Members:

« Next Oldest | How-to's / manuals / instructional manuals ONLY | Next Newest »

Powered by Invision Power Board (Jcink.com Free Forum Hosting) © 2012 IPS, Inc.
Page creation time: 0.0074 · Mobile | Lo-Fi