How to setup Postfix and Dovecot virtual users with MySQL databse (3)
=====================================================================


Database
--------


1. Install MySQL and Postfix MySQL compatibility


   sudo apt-get install mysql-server postfix-mysql



2. Fire up MySQL

   (Note: Email_database - database
          email/password - user/password for Postfix accessing database)


   mysql -u root -p

   CREATE DATABASE Email_database; 

   CREATE USER 'email'@'localhost' IDENTIFIED BY 'password'; 

   GRANT ALL ON Email_database.* to 'email'@'localhost';


   exit



postfixadmin 
------------


1. Install Apache2 and php5

   sudo apt-get install apache2 php5 php5-mysql php5-imap

   sudo /etc/init.d/apache2 restart


2. Switch over to the /var/www/99webhosting.com/website/ directory

   cd /var/www/99webhosting.com/website/


3. Download the postfixadmin files

   sudo wget http://downloads.sourceforge.net/sourceforge/postfixadmin/postfixadmin_2.3rc7.tar.gz


4. Extract the contents

   sudo tar -zxvf postfixadmin_2.3rc7.tar.gz


5. Rename the directory to something more friendly and remove the tarball

   sudo mv postfixadmin-2.3rc7 postfixadmin 
   sudo rm postfixadmin_2.3rc7.tar.gz


6. Update the postfixadmin configuration file with your settings

   sudo nano /var/www/99webhosting.com/website/postfixadmin/config.inc.php 


	$CONF['configured'] = true;
	$CONF['postfix_admin_url'] = $_SERVER['HTTP_HOST'].'/postfixadmin';
	$CONF['database_user'] = 'email';  
	$CONF['database_password'] = 'password';
	$CONF['database_name'] = 'Email_database';

      	$CONF['admin_email'] = 'admin@99webhosting.com';
	$CONF['default_aliases'] = array (
    		'abuse' => 'support@99webhosting.com',
    		'hostmaster' => 'support@99webhosting.com',
    		'postmaster' => 'support@99webhosting.com',
    		'webmaster' => 'support@99webhosting.com'
		);

7. Browse to: http://192.168.1.100/postfixadmin/setup.php

   (the tables of the Email_database will be automatically created by setup.php)


8. Create superadmin account  

   8.1) create the hash of the setup password at the botton of the setup.php 
   

   8.2) copy and paste the hash

        sudo nano config.inc.php

        Update $CONF['setup_password']


9. login to http://192.168.1.100/postfixadmin/login.php  



the 4 files for postfix 
containing SQL queries 
that will give postfix 
the information it needs 
to delivery to active 
addresses.
=======================


1. sudo nano /etc/postfix/my_alias_maps.cf

user = email
password = password
hosts = localhost
dbname = Email_database
query = SELECT goto FROM alias WHERE address = '%s' AND active = 1


2. sudo nano /etc/postfix/my_domains_maps.cf

user = email
password = password
hosts = localhost
dbname = Email_database
query = SELECT domain FROM domain WHERE domain = '%s' AND backupmx = 0 AND active = 1


3. sudo nano /etc/postfix/my_mailbox_limits.cf

user = email
password = password
hosts = localhost
dbname = Email_database
query = SELECT quota FROM mailbox WHERE username = '%s' AND active = 1


4. sudo nano /etc/postfix/my_mailbox_maps.cf

user = email
password = password
hosts = localhost
dbname = Email_database
query = SELECT CONCAT(domain,'/',maildir) FROM mailbox WHERE username = '%s' AND active = 1



Postfix
=======


1. sudo nano /etc/postfix/main.cf  

1.1)  Add these items to the file (at the end is fine)

virtual_minimum_uid = 1001
virtual_uid_maps = static:5001
virtual_gid_maps = static:5001
virtual_mailbox_base = /home/vmail
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1

virtual_alias_maps = proxy:mysql:/etc/postfix/my_alias_maps.cf
virtual_mailbox_limit = proxy:mysql:/etc/postfix/my_mailbox_limits.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/my_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/my_mailbox_maps.cf


1.2) Comment out (or remove) the following set options:

#home_mailbox = Maildir/
#mailbox_command = /usr/lib/dovecot/deliver -c /etc/dovecot/dovecot-postfix.conf -n -m "${EXTENSION}"


1.3) Remove yourcompany.com from mydestination. (It can't be in mydestination and virtual_mailbox_domains)



2. sudo nano /etc/postfix/master.cf

   dovecot unix  -  n   n  -  -  pipe 
      flags=DRhu user=vmail:vmail argv=/usr/lib/dovecot/deliver -c /etc/dovecot/dovecot-postfix.conf -f ${sender} -d $(recipient)  


3. Create the mailbox directory and the user that Postfix will use it to access the mail directory

   sudo useradd -r -u 5001 -g vmail -d /home/vmail -s /sbin/nologin vmail 

   * sudo mkdir /home/vmail 
   * sudo chmod 770 /home/vmail 
   * sudo chown vmail:vmail /home/vmail/
   (*, it automatically done by the "useradd")


 (change below for trouble-shooting)
  sudo chmod 660 /var/log/dovecot
  sudo chown root:vmail /var/log/dovecot
  sudo chmod 660 /var/log/dovecot.info
  sudo chown root:vmail /var/log/dovecot.info



Dovecot
=======


1. sudo nano /etc/dovecot/dovecot-sql.conf

driver = mysql
connect = host=localhost dbname=Email_database user=email password=password
default_pass_scheme = MD5-CRYPT
user_query = SELECT '/home/vmail/%d/%n' as home, 'maildir:/home/vmail/%d/%n' as mail, 5001 AS uid, 5001 AS gid, concat('dirsize:storage=', quota) AS quota FROM mailbox WHERE username = '%u' AND active = 1
password_query = SELECT username as user, password, '/home/vmail/%d/%n' as userdb_home, 'maildir:/home/vmail/%d/%n' as userdb_mail, 5001 as userdb_uid, 5001 as userdb_gid FROM mailbox WHERE username = '%u' AND active = 1


2. sudo nano /etc/dovecot/dovecot-postfix.conf


base_dir = /var/run/dovecot/
protocols = imap pop3
disable_plaintext_auth = no
shutdown_clients = yes
log_path = /var/log/dovecot
info_log_path = /var/log/dovecot.info
log_timestamp = "%Y-%m-%d %H:%M:%S "
ssl_disable = yes
login_dir = /var/run/dovecot/login
login_chroot = yes
login_user = dovecot
login_greeting = Dovecot ready.
mail_location = maildir:/home/vmail/%d/%n
mmap_disable = no
valid_chroot_dirs = /var/spool/vmail
protocol imap {
  login_executable = /usr/lib/dovecot/imap-login
  mail_executable = /usr/lib/dovecot/imap
}
protocol pop3 {
  login_executable = /usr/lib/dovecot/pop3-login
  mail_executable = /usr/lib/dovecot/pop3
  pop3_uidl_format = %08Xu%08Xv
}

protocol lda {
  # Address to use when sending rejection mails.
  postmaster_address = support@99webhosting.com

  # Hostname to use in various parts of sent mails, eg. in Message-Id.
  # Default is the system's real hostname.
  hostname = smtp.99webhosting.com

  # Support for dynamically loadable plugins. mail_plugins is a space separated
  # list of plugins to load.
  #mail_plugins =
  #mail_plugin_dir = /usr/lib/dovecot/modules/lda

  # If user is over quota, return with temporary failure instead of
  # bouncing the mail.
  #quota_full_tempfail = no
  # Format to use for logging mail deliveries. You can use variables:
  #  %$ - Delivery status message (e.g. "saved to INBOX")
  #  %m - Message-ID
  #  %s - Subject
  #  %f - From address
  deliver_log_format = msgid=%m: %$

  # Binary to use for sending mails.
  #sendmail_path = /usr/lib/sendmail

  # Subject: header to use for rejection mails. You can use the same variables
  # as for rejection_reason below.
  rejection_subject = Automatically rejected mail

  # Human readable error message for rejection mails. You can use variables:
  #  %n = CRLF, %r = reason, %s = original subject, %t = recipient
  rejection_reason = Your message to <%t> was automatically rejected:%n%r

  # UNIX socket path to master authentication server to find users.
  auth_socket_path = /var/run/dovecot/auth-master
}

auth_executable = /usr/lib/dovecot/dovecot-auth
auth_verbose = yes


auth default {
  mechanisms = plain digest-md5
#  userdb passwd-file {
  # We need this to do authorization - assign users the permission to the mailbox directory.
#    args = /etc/dovecot/users
#  }
#  passdb passwd-file {
  # We need this to do authentication -assign users the login password.
#    args = /etc/dovecot/passwd
#  }
 passdb sql {
   args = /etc/dovecot/dovecot-sql.conf
  }
 userdb sql {
   args = /etc/dovecot/dovecot-sql.conf
  }
  # We need this to use Dovecot SASL Auth in Postfix.
  socket listen {
      master {
      path = /var/run/dovecot/auth-master
      mode = 0660
      user = vmail
      group = vmail
      }
      client {
      path = /var/spool/postfix/private/dovecot-auth
      mode = 0660
      user = postfix
      group = postfix
    }
  }
}



3. Restart both services


sudo /etc/init.d/postfix restart 
sudo /etc/init.d/dovecot restart


(mailboxes will be setup by http://192.168.1.100/postfixadmin/index.php)