Activities

March 2011
M T W T F S S
« Feb   Apr »
 123456
78910111213
14151617181920
21222324252627
28293031  

How do I configure SQL based mail server in Linux

Here I’m going to setup a sql based mail server which can be easily backed and manageable through any of the programming language.

Advantages
1. Can be easily transferable
2. Can integrate mail service with our web applications
3. Easy to backup and accommodate any number of domains/users as long as you grow
4. Secured SMTP/POP3 and web access
5. No worry about disk usage.

Requirements
1. OS : CentOS 5.5 or any LINUX
2. MTA : postfix build with mysql support. You may test it by ‘postconf -m”
3. Squirrelmail : PHP supported web server with IMAP and mysql extension enabled.
4. POP3 : Dovecot
5. Database : mysql

Here we go,

1. ############ Install postfix with mysql support. ############

Default postfix distribution on Centos doesn’t have mysql support enabled. You need to use “CentOS Plus” repository to get the new postfix rpm build.
a. Edit the file /etc/yum.repos.d/CentOS-Base.repo and added the entries in the respective section []

[base]
…………..
exclude=postfix
………….
[update]
……………….
exclude=postfix
…………………
[centosplus]
……………………….
enabled=1
includepkgs=postfix
……………………….
…………

Now you need to remove the postfix if installed,

#yum remove postfix
# yum clean all
# yum install postfix

Ensure that newly installed postfix has the mysql support enabled.

 [root@rc-126 ~]# <strong>postconf -m | grep "mysql"
mysql

Next steps is to switch the system to use postfix MTA if the system has already sendmail installed.
To choose the available MTA alternatives from the command line :
#alternatives –config mta or you may use graphical interface “system-switch-mail”

2. ########## Installing postfixadmin ###########

Now you need to install postfixadmin. It’s requirements are ,
1. Apache with php module enabled
2. php-imap and php-mysql, php-mbstring extension must be enabled
3. Mysql database
4. Download http://sourceforge.net/projects/postfixadmin/ my version is 2.3

 # yum install httpd* mysql*
    #yum install mysql-server php-mysql php-imap
    # service httpd start
    #service mysql start
    #mysql
     mysql>  create database postfix;
     mysql>  grant all on vmail.* to postfix@localhost identified by 'pass';
     mysql>exit
    #cd /var/www/html/
    #tar -xvzf postfixadmin-2.3.2.1.tar.gz
    #mv postfixadmin-2.3.2.1  postfixadmin

Edit the file /var/www/html/postfixadmin/config.inc.php and update the following lines below.

 $CONF['configured'] = true;
$CONF['postfix_admin_url'] = '/postfixadmin';
$CONF['database_type'] = 'mysqli';
$CONF['database_host'] = 'localhost';
$CONF['database_user'] = 'vmail';
$CONF['database_password'] = 'pass';
$CONF['database_name'] = 'postfix';
$CONF['domain_path'] = 'YES';
$CONF['domain_in_mailbox'] = 'NO';
$CONF['encrypt'] = 'cleartext';
$CONF['emailcheck_resolve_domain] = 'NO';

NB: Pls note that lines are not in the order as shown above in the config. file, Search by each variable one by one and change it.
Now you may access the postfixadmin over the browser on http://mailserverip/postfixadmin/setup.php and complete the admin account setup. Once you had completed the setup, login to the portal and create the new domain and add a new user in it to test out.

For the testing purpose, I created domain as mydomain.tld and user@mydomain as user.

3. ############ COnfiguring your MTA and POP3 servers ########

Now it’s the toughest part to integrate mysql with postfix, dovecot and testing.

Let’s start from the postfix configuration,

A

    . Creating the Virtual Mail User Account

Create a new system user whose does not have shell access. You need to remember his uid and gid for the future purpose.

  # useradd vmail -s /sbin/nologin
   #[root@rc-126 ~]# cat /etc/passwd | grep "vmail"

vmail:x:510:510::/home/vmail:/sbin/nologin
[root@rc-126 ~]#
uid and gid will be 510 in our case.

Configuring Postfix
Domain configuration file
a. Create the file /etc/postfix/mysql-domains.cf containing the lines below.

hosts = localhost
     user = postfix
     password = pass
     dbname = vmail
     table = domain
     select_field = domain
     where_field = domain
     additional_conditions = and backupmx = '0' and active = '1'

Test /etc/postfix/mysql-domains.cf using the command below.
[root@rc-126 ~]# postmap -q mydomain.tld mysql:/etc/postfix/mysql-domains.cf
mydomain.tld

b. Users configuration file
Create the file /etc/postfix/mysql-users.cf containing the lines below.

hosts = localhost
      user = postfix
      password = pass
     dbname = vmail
     table = mailbox
     select_field = maildir
     where_field = username
     additional_conditions = and active = '1'
     result_format = %sMaildir/

Test /etc/postfix/mysql-users.cf using the command below.
[root@rc-126 ~]# postmap -q admin@mydomain.tld mysql:/etc/postfix/mysql-users.cf
mydomain.tld/admin/Maildir/

c. Alias configuration file
Create the file for email alias like email listing “/etc/postfix/mysql-aliases.cf” content should be like this

hosts = localhost
     user = postfix
     password = pass
     dbname = vmail
     table = alias
     select_field = goto
     where_field = address
     additional_conditions = and active = '1'

Test /etc/postfix/mysql-aliases.cf file configuration with postfix

[root@rc-126 ~]# postmap -q info@mydomain.tld   mysql:/etc/postfix/mysql-aliases.cf
      admin@mydomain.tld

d. Modify the postfix configuration file

Edit the postfix configuration file /etc/postfix/main.cf and edit the line below.
Ensure the this lines are present
1.

mydestination = $myhostname, localhost.$mydomain, localhost

you need to remove $domain if present there.
2. Comment the line to “#inet_interfaces = localhost” and comment out the line to “inet_interfaces = all”

[root@rc-126 ~]# netstat -nlp | grep ":25"
        tcp        0      0 0.0.0.0:25                 0.0.0.0:*                   LISTEN      4835/master
        [root@rc-126 ~]#

3. Add the following line at the end of file “/etc/postfix/main.cf

virtual_mailbox_domains = mysql:/etc/postfix/mysql-domains.cf
       virtual_mailbox_maps = mysql:/etc/postfix/mysql-users.cf
       virtual_alias_maps = mysql:/etc/postfix/mysql-aliases.cf
       virtual_mailbox_base = /home/vmail
       virtual_uid_maps = static:510
       virtual_gid_maps = static:510

Pls note virtual_uid_maps, virtual_gid_maps and virtual_mailbox_base are same that you created before.

# service postfix restart

e. It’s the time to turn on testing,

[root@rc-126 ~]# telnet 192.168.0.126 25
       Trying 192.168.0.126...
        Connected to 192.168.0.126 (192.168.0.126).
       Escape character is '^]'.
       220 rc-126.mydomain.tld ESMTP Postfix
       ehlo localhost
       250-rc-126.mydomain.tld
       250-PIPELINING
       250-SIZE 10240000
       250-VRFY
      250-ETRN
      250-STARTTLS
      250-ENHANCEDSTATUSCODES
      250-8BITMIME
      250 DSN
     mail from: admin@mydomain.tld
     250 2.1.0 Ok
    rcpt to:liju@serveridol.com
     250 2.1.5 Ok
     data
     354 End data with <CR><LF>.<CR><LF>
      this is for validating smtp
      .
      250 2.0.0 Ok: queued as 76A50168020
      quit
      221 2.0.0 Bye
      Connection closed by foreign host.

4. ######### Configuring Dovecot ###########

Now we are going to configure pop3/Imap server using Dovecot

You may need to install dovecot-mysql package to get sql support.

 #yum install dovecot-mysql

a. Create a file /etc/dovecot-mysql.conf having the following entry
driver = mysql
connect = host=localhost dbname=vmail user=postfix password=pass
default_pass_scheme = PLAIN
password_query = SELECT password FROM mailbox WHERE username = ‘%u’

b. Edit dovecot config file and comment few lines and add the following lines,

# vi /etc/dovecot/dovecot.conf
auth_username_format = %Lu
              passdb sql {
              args = /etc/dovecot-mysql.conf
               }
             userdb static {
             args = uid=510 gid=510 home=/home/vmail/%d/%n
              }

Note that you need to search each word to identifywhere it’s placed in the file. Comment the line if you see any “passdb” and userdb option is enabled first. Each curly bracket should be use carefully and closed. uid, gid and home directory as the same as the system user account where email server running under. It’s complicate to place the correct entry. Comment out all the other passdb and userdb sections

sample dovecot.conf file can be downloaded at here

Steps are slightly different if you work with Centos/Redhat 6

    For CentOS 6/RedHat

,
All the dovecot config files are well packed like Apache server settings rather than to manage a lengthy complicated config. file. You may see the master file at “/etc/dovecot/dovecot.conf “ and module config files are placed under ” /etc/dovecot/conf.d”

Now you need to edit the file “10-auth.conf” and add the following lines,
[root@app01 conf.d]# vi /etc/dovecot/conf.d/10-auth.conf
passdb {
driver = sql
args = /etc/dovecot/dovecot-mysql.conf
}
userdb {
driver = static
args = uid=509 gid=510 home=/opt/vmail/%d/%n
}

Pls note you may need to add ” login” in auth_mechanism Eg. “auth_mechanisms = plain login “

Then you may need to edit another file “/etc/dovecot/conf.d/10-mail.conf” and un comment the following line,
mail_location = maildir:~/Maildir

Now it’s time to restart the dovecot service,

 [root@app01 conf.d]# service dovecot restart
Stopping Dovecot Imap:                                     [  OK  ]
Starting Dovecot Imap:                                     [  OK  ]

[root@rc-126 ~]# netstat -nlp | grep “:110”
tcp 0 0 :::110 :::* LIST EN 5011/dovecot
root@rc-126 ~]# netstat -nlp | grep “:143”
tcp 0 0 :::143 :::* LIST EN 5011/dovecot

It’s the time to verify the dovecot-mysql integrity

[root@rc-126 ~]# <strong>telnet localhost  pop3</strong>
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
+OK Dovecot ready.
<strong>user admin@mydomain.tld</strong>
+OK
<strong>pass rain123</strong>
+OK Logged in.
<strong>list</strong>
+OK 0 messages:
.
<strong>quit</strong>
+OK Logging out.
Connection closed by foreign host.
[root@rc-126 ~]#

5. ######## Webmail Configuration ###########

Now we are going to install Squirrel mail to verify the mail server functionality. Download squirrelmail from http://squirrelmail.org and unzip in to any web location on the server.
#cd /var/www/html/
#unzip squirrelmail-1.4.21.zip
#move squirrelmail-1.4.21 webmail
#cd webmail
# ./configure
Go through the menu 2, 3 and 4. You may need to create “data” and attachmentfolder in squirrel mail directory and grant webserver to full permission on that location.

Once you have completed the squirrmail installation, You can now access the email system over here using the url http://mailserverip/webmail/src/webmail.php.
user name and password will be : admin@mydomain.tld/password

All are set now. But you can’t receive any email unless you have set mx record to this smtp server. You need to have access to the domain manager to change the mx records respectively.You have to create a host record “mail” to identify the mail server and then set the mx record to that “mail” host.

a. Repairing DNS changes
How do I check the mx record record is correctly poiting to your server.

[root@rc-026 installation]# host -t mx  mydomain.com
                mydomain.com mail is handled by 0 mail.mydomain.com.
               [root@rc-026 installation]# ping mail.mydomain.com

ping mail.mydomain.com will resolve the ip of the mail server.

b. Setup SPF record to minimise the spam marking of your domain in global SMTP servers. An example of SPF record is shown below,
v=spf1 ip4: mx mx:mail.mydomain.tld ~all
c. Ensure that your mail servers are reached over the public
# telnet mydomain.tld 25
# telnet mydomain.tld 110 (pop3 access)
# telnet mydomain.tld 143 (IMAP access)
d. Set DKIM to tighten the high mail deliverabilities among on other smtp servers.
This configuration using postfix as open relay in locally. So that anyone from the LAN can send email without any authentication.

6. ######### SMTP authentication ############

Now I’m going to set “SMTP authentication” which means each user account must be validate before sending emails through our email server to stop relaying mails.

a. Edit the file /etc/dovecot.conf and make sure your auth default section has the lines below.

auth default { 
             socket listen {
             client {
         path = /var/spool/postfix/private/auth
         mode = 0660
         user = postfix
        group = postfix
             }
          }
       mechanisms = plain login
     }

For CentOS 6 or higher OS, you may need to comment all the values inside of service auth { on this file “/etc/dovecot/conf.d/10-master.conf”. And added the below line at the end of the file,

service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0660
user = postfix
}
}

b. Edit /etc/postfix/main.cf, find the keys below and change its values as follows or add it at the bottom of the file.

mynetworks = 127.0.0.0/8
          smtpd_sasl_type = dovecot
          smtpd_sasl_path = private/auth
         smtpd_sasl_auth_enable = yes
         smtpd_recipient_restrictions =  permit_mynetworks,
         permit_sasl_authenticated, reject_unauth_destination
         broken_sasl_auth_clients = yes

Perfect !! Now restart postfix and dovecot

[root@rc-126 ~]# service postfix restart
Shutting down postfix: [ OK ]
Starting postfix: [ OK ]
[root@rc-126 ~]# service dovecot restart
Stopping Dovecot Imap: [ OK ]
Starting Dovecot Imap: [ OK ]
[root@rc-126 ~]#

Now we need to test the SMTP authentication,

[root@rc-090 ~]# telnet 192.168.0.126 25
        Trying 192.168.0.126...
        Connected to 192.168.0.126 (192.168.0.126).
        Escape character is '^]'.
        220 rc-126.mydomain.tld ESMTP Postfix
        ehlo localhost
        250-rc-126.mydomain.tld
        250-PIPELINING
        250-SIZE 10240000
        250-VRFY
        250-ETRN
        250-STARTTLS
        250-AUTH PLAIN LOGIN
        250-AUTH=PLAIN LOGIN
        250-ENHANCEDSTATUSCODES
        250-8BITMIME
        250 DSN
        mail from: admin@mydomain.tld
        250 2.1.0 Ok
        rcpt to: liju@serveridol.com
        554 5.7.1 <liju@serveridol.com>: Relay access denied
        quit
        221 2.0.0 Bye
       Connection closed by foreign host.
       [root@rc-090 ~]#

This shows (Relay access denied) we have set the authentication in place. You will not ask any authentication if you are executing it from the mail server itself as because sever is configured to relay from the local ip itself (mynetworks = 127.0.0.0/8)

You can verify it through the any of the MUA, here I uses Outook express for the testing, You will no longer send any email if the “server requires authentication” is unchecked.
SMTP/POP3 server : mail.mydomain.tld

Follow the screen,

It’s all done !!! happy mailing…. 🙂 🙂

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>