How to Postfix dovecot mysql

Pre-Configuration Requirements:
1. Hostname cnode1.rnd.pk with IP address (172.16.4.80) to eth0. (You can assign an IP address and hostname of your choice.)
2. Create an alias eth0:0. Assign IP address (172.16.4.81). (You can assign an IP address of your choice.)
3. Make entries of your hostname and IP in /etc/hosts . In my case my /etc/hosts file shows
127.0.0.1       localhost.localdomain   localhost
172.16.4.80     cnode1.rnd              cnode1
172.16.4.81     mail.rnd                mail
Installation Of Required Packages:
We need the following packages for our mail server to be installed:
dovecot-0.99.11-8.EL4 (with database support), postfix-2.2.10-1.1.el4.centos.mysql (with database support) and squirrelmail.
1. First install dovecot. If you install postfix before dovecot, then dovecot will not be installed on your system because of packages conflicts.
yum install -y dovecot
This will install dovecot along with dependencies (mysql and postgresql).
2. Then install postfix (with mysql support) and and mysql-server-5 from centosplus repos.
yum --enablerepo=centosplus install postfix mysql-server
yum install -y squirrelmail
This will install postfix along with dependencies mysql-5 and postgresql. Our installation section is complete now. Moving to our next section i.e. Configuration.
Configuration:
SMTP-AUTH/TLS
First we configure SMTP-AUTH and TLS. For this edit /usr/lib/sasl2/smtpd.conf with your favorite editor.
vi /usr/lib/sasl2/smtpd.conf
and make changes as given below.
pwcheck_method: saslauthd
mech_list: plain login
Create directories, then private key and lastly the certificate.
mkdir -p /etc/ssl/mycompany/mailserver/
cd /etc/ssl/mycompany/mailserver/
openssl genrsa -des3 -rand /etc/hosts -out smtpd.key 1024
chmod 600 smtpd.key
openssl req -new -key smtpd.key -out smtpd.csr
openssl x509 -req -days 3650 -in smtpd.csr -signkey smtpd.key -out smtpd.crt
openssl rsa -in smtpd.key -out smtpd.key.unencrypted
mv -f smtpd.key.unencrypted smtpd.key
openssl req -new -x509 -extensions v3_ca -keyout cakey.pem -out cacert.pem -days 3650
Private keys and certificates have been created. Later on we will tell postfix to use them.
 
MySQL:
We will now create a database named mail, for this we will issue commands given below; our first step will be to set a password for the administrative user.
mysqladmin -u root password newpassword
Replace newpassword phrase with your password. We will use user "mail" in our case for all database related operations. (Select Update and Insert into Database.)
mysql -u root -p
Enter the password and you will be at the MySQL prompt (mysql>)
CREATE DATABASE mail;
Give all privileges on mail to user mail.
GRANT ALL PRIVILEGES ON mail.* TO 'mail'@'localhost' IDENTIFIED BY 'mail';
FLUSH PRIVILEGES;
quit
Set password for mail user. This will be done by the following statement.
msqladmin -u mail password newpassword
Then we will create the necessary tables for our new database (mail) that contains domains, users, aliases and mailboxes information.
mysql -u mail -p
After giving the password you will be at the MySQL prompt.
show databases;
It will show all databases, including our "mail" database. We will use "mail".
USE mail;
1. Create the domain table.
CREATE TABLE domain ( domain varchar(255) NOT NULL default '', description varchar(255) NOT NULL default '', aliases int(10) NOT NULL default '0', mailboxes int(10) NOT NULL default '0', maxquota int(10) NOT NULL default '0', transport varchar(255) default NULL, backupmx tinyint(1) NOT NULL default '0', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (domain), KEY domain (domain) ) TYPE=MyISAM COMMENT=' Virtual Domains';
2. Second most important table is mailbox, so create mailbox.
CREATE TABLE mailbox ( username varchar(255) NOT NULL default '', password varchar(255) NOT NULL default '', name varchar(255) NOT NULL default '', maildir varchar(255) NOT NULL default '', quota int(10) NOT NULL default '0', domain varchar(255) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (username), KEY username (username) ) TYPE=MyISAM COMMENT='Virtual Mailboxes';
3. Create the alias table.
CREATE TABLE alias ( address varchar(255) NOT NULL default '', goto text NOT NULL, domain varchar(255) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (address), KEY address (address) ) TYPE=MyISAM COMMENT='Virtual Aliases';
We have created the necessary tables, so quit MySQL.
quit
 
Postfix MySQL:
Postfix needs to know where and how it can look up all mailbox related information. For this purpose we will create the following files under /etc/postfix. Recent versions of Postfix may use that instead of the other statements, and in that case, just comment all lines out, and un-comment the last one.
1. Create file mysql_virtual_alias_maps.cf for forwarding emails from one email address to another
vi /etc/postfix/mysql_virtual_alias_maps.cf
 
user = mail
password = mail
hosts = localhost
dbname = mail
table = alias
select_field = goto
where_field = address
additional_conditions = and active = '1'
#query = SELECT goto FROM alias WHERE address='%s' AND active = '1'
2. Create file mysql_virtual_domains_maps.cf, for the virtual domains mapping. Virtual domains are queried using information provided in this file.
vi /etc/postfix/mysql_virtual_domains_maps.cf
user = mail
password = mail
hosts = localhost
dbname = mail
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '0' and active = '1'
#query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '0' AND active = '1'
3. Create the file mysql_virtual_mailbox_maps.cf. Which is usually the mapping of email addresses to the location of the user's mailbox on your hard disk. If you saved incoming email to the hard disk using Postfix' built-in virtual delivery agent then it would be queried to find out the mailbox path.
vi /etc/postfix/mysql_virtual_mailbox_maps.cf
user = mail
password = mail
hosts = localhost
dbname = mail
table = mailbox
select_field = CONCAT(domain,'/',maildir)
where_field = username
additional_conditions = and active = '1'
#query = SELECT CONCAT(domain,'/',maildir) FROM mailbox WHERE username='%s' AND active = '1'
4. Lastly create file mysql_virtual_mailbox_limit_maps.cf which will be used for mapping users mailboxes quota limit.
vi /etc/postfix/mysql_virtual_mailbox_limit_maps.cf
user = mail
password = mail
hosts = localhost
dbname = mail
table = mailbox
select_field = quota
where_field = username
additional_conditions = and active = '1'
#query = SELECT quota FROM mailbox WHERE username='%s' AND active = '1'
 
Postfix:
In the Postfix configuration section we will edit the main.cf file located in configuration directory of postfix (/etc/postfix), to enter some basic information necessary for Postfix.
mv /etc/postfix/main.cf /etc/postfix/main.cf.orig
vi /etc/postfix/main.cf
############## Postfix###############
smtpd_banner = $myhostname
biff = no
append_dot_mydomain = no
relayhost =
mynetworks = 172.16.4.81
inet_interfaces = 172.16.4.81
mailbox_size_limit = 0
recipient_delimiter = +
alias_database = hash:/etc/postfix/aliases
alias_maps = $alias_database
myhostname = cnode1.rnd
mydomain = rnd
myorigin = $myhostname
mydestination = $myhostname, localhost.$mydomain, $transport_maps
mail_spool_directory = /var/spool/mail
debug_peer_level = 2
debugger_command =
  PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
  xxgdb  $daemon_directory/$process_name $process_id & sleep 5
disable_vrfy_command = no
readme_directory = /usr/share/doc/postfix-2.2.10/README_FILES
sample_directory = /usr/share/doc/postfix-2.2.10/samples
sendmail_path = /usr/sbin/sendmail
html_directory = no
setgid_group = postdrop
command_directory = /usr/sbin
manpage_directory = /usr/share/man
daemon_directory = /usr/libexec/postfix
newaliases_path = /usr/bin/newaliases
mailq_path = /usr/bin/mailq
queue_directory = /var/spool/postfix
mail_owner = postfix
unknown_local_recipient_reject_code = 450
####################postfix section ends here###############
 
Postfix Virtual user information:
Again we will edit main.cf file to add support for virtual users. The virtual_minimum_uid and virtual_uid_maps point to user id 150 in my case, which is a user I created specifically for handling virtual mail. It uses the standard mail group with the default gid 12. So first create the user by issuing the useradd command or adduser.
useradd -r -u 150 -g mail -d /var/vmail -s /sbin/nologin -c Virtual mailbox vmail
chmod 770 /var/vmail/
chown vmail:mail /var/vmail
vi /etc/postfix/main.cf
#######################Virtual Domains Users and mailboxes###############
virtual_mailbox_domains = mysql:$config_directory/mysql_virtual_domains_maps.cf
virtual_mailbox_base = /var/vmail
virtual_mailbox_maps = mysql:$config_directory/mysql_virtual_mailbox_maps.cf
virtual_alias_maps = mysql:$config_directory/mysql_virtual_alias_maps.cf
virtual_minimum_uid = 150
virtual_uid_maps = static:150
virtual_gid_maps = static:12
##############################Virtual section of main.cf ends##############
 
Postfix SASL/TLS authentication:
Finally we will again edit main.cf file to enable SASL/TLS authentication. Previously we created some certificates, we will use them here to secure mail server.
vi /etc/postfix.main.cf
#################### SASL/TLS Authentication###########################
######SASL PART#########
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
broken_sasl_auth_clients = yes
smtpd_sasl_local_domain =
smtpd_recipient_restriction = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination
######TLS PART###########
smptpd_tls_cert_file = /etc/ssl/rnd/mailserver/smtpd.crt
smtpd_tls_key_file = /etc/ssl/rnd/mailserver/smtpd.key
smtpd_tls_CAfile = /etc/ssl/rnd/mailserver/cacert.pem
smtp_tls_auth_only = no
smtp_use_tls = yes
smtpd_use_tls = yes
smtpd_tls_received_header = no
smtp_tls_note_starttls_offer = yes
smtpd_tls_loglevel = 1
tls_random_source = dev:/dev/urandom
smtpd_tls_session_cache_timeout = 3600s
smtpd_tls_recieved_header = yes
###########################SASL/TLS Authentication ends here#############
For my convenience I have divided main.cf in three sections { Postfix, virtual-Domains-Users-and-mailboxes, SASL/TLS-Authentication}. SASL/TLS-Authentication is further divided into ( SASL-PART and TLS-PART). To show you each section and their parameters I have edited the same file thrice. Now copy the /etc/aliases and /etc/aliases.db to /etc/postfix/ and run newaliases.
cp /etc/aliases* /etc/postfix/
newaliases
 
Dovecot IMAP and POP:
Let us configure Dovecot which provides both a POP3 and an IMAP service. The configuration file for Dovecot is /etc/dovecot.conf. We will rename it to dovecot.conf.orig, and create our own. To handle virtual users with dovecot we will create the file /etc/dovecot-mysql.conf
vi /etc/dovecot-mysql.conf
######dove-mysql.conf should look like this##########
db_host = 127.0.0.1
db_port = 3306
db      = mail
db_user = mail
db_passwd = mail
db_client_flags = 0
default_pass_scheme = PLAIN
password_query = SELECT password FROM mailbox WHERE username = '%u'
user_query = SELECT '/var/vmail/%d/%n' as home,
'maildir:/var/vmail/%d/%n' as mail, 150 AS uid, 12 AS gid, concat('dirsize:storage=',quota) AS quota FROM mailbox WHERE username ='%u' AND active ='1'
####################ends here####################
Now moving to configure dovecot.conf, to authenticate virtual user using mysql_auth method. Keep this thing in mind that normal Linux users can't login to mail-server. Because we haven't enabled Pam-authentication method in our dovecot configuration. Also first_valid_uid and last_valid_uid are set to 150, which means only the user with uid 150 will be able to log in. Pop3 and pop3s protocols are made available.
mv /etc/dovecot.conf /etc/dovecot.conf.orig
vi /etc/dovecot.conf
##############dovecot configured to work with virtual users############
base_dir = /var/run/dovecot/
protocols = imap pop3 imaps pop3s
imap_listen = [::]
pop3_listen = [::]
login_dir = /var/run/dovecot-login
login = imap
login = pop3
default_mail_env = mbox:/var/vmail/%d/%n
mbox_locks = fcntl
log_timestamp = "%Y-%m-%d %H:%M:%S "
log_path = /var/log/maillog
mail_extra_groups = mail
first_valid_uid = 150
last_valid_uid = 150
maildir_copy_with_hardlinks = yes
auth = mysql_auth
auth_userdb = mysql /etc/dovecot-mysql.conf
auth_passdb = mysql /etc/dovecot-mysql.conf
####################################ends here######################
After this we will set the ownership and access rights on /etc/dovecot-mysql.conf.
chmod 600 /etc/dovecot/*.conf
chown vmail /etc/dovecot/*.conf
 
Squirrelmail configuration:
Go to squirrelmail config directory and run config.pl.
cd /usr/share/squirrelmail/config/
/conf.pl
It will show some selection like this:
SquirrelMail Configuration : Read: config.php (1.4.0) ---------------------------------------------------------
Main Menu --
1. Organization Preferences
2. Server Settings
3. Folder Defaults
4. General Options
5. Themes
6. Address Books
7. Message of the Day (MOTD)
8. Plugins
9. Database
10. Languages
D. Set pre-defined settings for specific IMAP servers
C.Turn color on
S Save data
Q Quit Command >> 
At the command prompt type 1 and set Organization Preferences. Here my settings are shown. You can choose your own.
1. Organization Name : R&D Dept.
2. Organization Logo : ../images/sm_logo.png
3. Org. Logo Width/Height : (308/111)
4. Organization Title : Research & Developement
5. Signout Page : http://mail.rnd
6. Top Frame : _top
7. Provider link : http://mail.rnd
8. Provider name : R&D
Save your settings and return to the main menu. Now at the command prompt type 2, and in server settings menu provide information.
>>Command 2 mine is shown below.
1. Domain : mail.rnd
2. Invert Time : false
3. Sendmail or SMTP : SMTP
A. Update IMAP Settings : mail.rnd:143 (uw)
B. Update SMTP Settings : mail.rnd:25
Configuring squirrelmail is very easy as it is menu driven. Hope you will do it yourself.
 
 HTTP Section:
To start using the mailserver web front end we will edit file /etc/httpd/conf/httpd.conf.
vi /etc/httpd/conf/httpd.conf
And append the statements given below to it.
<VirtualHost 172.16.4.81:80>
DocumentRoot /usr/share/squirrelmail
ServerName mail.rnd
</VirtualHost>
Save the configuration and exit.
 Creating Virtual users and domains:
Now we will create virtual domains and virtual users in our mail database.
mysql -u mail -p
Enter the password and you will be at the mysql> prompt.
USE mail;
First create a virtual domain in the domain table (mail.rnd) using the command given below.
INSERT INTO domain (domain,description,aliases,mailboxes,maxquota,transport,backupmx,active) VALUES ('mail.rnd','Virtual domain','10','10', '0','virtual', '0','1');
Now create two virtual users in the mailbox table. I have created (mike@mail.rnd & john@mail.rnd) as usernames for mike and john.
INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('john@mail.rnd','mypassword', 'John Smith ','john/', '0','mail.rnd','1');
INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('mike@mail.rnd','mypassword', 'Mike Tyson','mike/', '0','mail.rnd','1');
quit
Now that we have created virtual users and virtual domain, we want to test our mail server by logging in and sending mail from one user account to another. So let's start Dovecot, Postfix, MySQL and webserver daemons. Also we want that they should start themselves on next reboot. For this we issue following commands.
chkconfig level 235 mysqld on
chkconfig level 235 saslauthd on
chkconfig level 235 postfix on
chkconfig level 235 dovecot on
chkconfig level 235 httpd on
/etc/init.d/saslauthd start
/etc/init.d/mysqld start
/etc/init.d/postfix start
/etc/init.d/dovecot start
/etc/init.d/httpd start
Finally fire-up the browser and go to http://mail.rnd and enter the username and password and then click on Login. And enjoy playing with your mailserver.
phpMyAdmin:
phpMyAdmin is a powerful and easy to use frontend for MySQL. The rpm of phpMyAdmin exists in the DAG repository. Go and add DAG repository. And install phpmyadmin and php-mysql if not already installed.
yum install -y phpmyadmin php-mysql
After this fire up a browser and enter http://127.0.0.1 in the address bar. Enter the user name and password and start using it. /etc/httpd/conf.d/phpmyadmin.conf has the information to locate phpmyadmin. If it fails you can create a virual alias in /etc/httpd/conf/httpd.conf file by editing it.
vi /etc/httpd/conf/httpd.conf
<VirtualHost 127.0.0.1>
DocumentRoot /usr/share/phpmyadmin
ServerName localhost.localdomin
</VirtualHost>
Enjoy using phpMyAdmin for creating users, domain and aliases.

Please let me know
Best of luck