---

# Database SQL

-- phpMyAdmin SQL Dump
-- version 3.4.7.1
-- [url]http://www.phpmyadmin.net[/url]
--
-- Host: localhost
-- Generation Time: Dec 19, 2011 at 11:59 PM
-- Server version: 5.1.58
-- PHP Version: 5.3.6-13ubuntu3.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `postfix`
--

-- --------------------------------------------------------

--
-- Table structure for table `admin`
--

CREATE TABLE IF NOT EXISTS `admin` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `password` 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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Admins';

-- --------------------------------------------------------

--
-- Table structure for table `alias`
--

CREATE TABLE IF NOT EXISTS `alias` (
  `address` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `goto` text CHARACTER SET latin1 NOT NULL,
  `domain` varchar(255) CHARACTER SET latin1 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Virtual Aliases';

-- --------------------------------------------------------

--
-- Table structure for table `alias_domain`
--

CREATE TABLE IF NOT EXISTS `alias_domain` (
  `alias_domain` varchar(255) NOT NULL DEFAULT '',
  `target_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 (`alias_domain`),
  KEY `active` (`active`),
  KEY `target_domain` (`target_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Domain Aliases';

-- --------------------------------------------------------

--
-- Table structure for table `config`
--

CREATE TABLE IF NOT EXISTS `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `value` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='PostfixAdmin settings' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `domain`
--

CREATE TABLE IF NOT EXISTS `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` bigint(10) NOT NULL DEFAULT '0',
  `quota` bigint(20) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Domains';

-- --------------------------------------------------------

--
-- Table structure for table `domain_admins`
--

CREATE TABLE IF NOT EXISTS `domain_admins` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `domain` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Domain Admins';

-- --------------------------------------------------------

--
-- Table structure for table `fetchmail`
--

CREATE TABLE IF NOT EXISTS `fetchmail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mailbox` varchar(255) NOT NULL DEFAULT '',
  `src_server` varchar(255) NOT NULL DEFAULT '',
  `src_auth` enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any') DEFAULT NULL,
  `src_user` varchar(255) NOT NULL DEFAULT '',
  `src_password` varchar(255) NOT NULL DEFAULT '',
  `src_folder` varchar(255) NOT NULL DEFAULT '',
  `poll_time` int(11) unsigned NOT NULL DEFAULT '10',
  `fetchall` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `keep` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `protocol` enum('POP3','IMAP','POP2','ETRN','AUTO') DEFAULT NULL,
  `extra_options` text,
  `returned_text` text,
  `mda` varchar(255) NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Fetchmail' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `log`
--

CREATE TABLE IF NOT EXISTS `log` (
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `username` varchar(255) NOT NULL DEFAULT '',
  `domain` varchar(255) NOT NULL DEFAULT '',
  `action` varchar(255) NOT NULL DEFAULT '',
  `data` varchar(255) NOT NULL DEFAULT '',
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Log';

-- --------------------------------------------------------

--
-- Table structure for table `mailbox`
--

CREATE TABLE IF NOT EXISTS `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` bigint(20) NOT NULL DEFAULT '-1',
  `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',
  `local_part` varchar(255) NOT NULL,
  PRIMARY KEY (`username`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Mailboxes';

-- --------------------------------------------------------

--
-- Table structure for table `vacation`
--

CREATE TABLE IF NOT EXISTS `vacation` (
  `email` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `body` text NOT NULL,
  `cache` text NOT NULL,
  `domain` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`email`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Vacation';

-- --------------------------------------------------------

--
-- Table structure for table `vacation_notification`
--

CREATE TABLE IF NOT EXISTS `vacation_notification` (
  `on_vacation` varchar(170) NOT NULL,
  `notified` varchar(170) NOT NULL,
  `notified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`on_vacation`,`notified`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Vacation Notifications';

--
-- Constraints for dumped tables
--

--
-- Constraints for table `vacation_notification`
--
ALTER TABLE `vacation_notification`
  ADD CONSTRAINT `vacation_notification_ibfk_1` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


# /etc/postfix/mysql_virtual_alias_maps.cf:
user = postfix
password = postfixpassword
hosts = 127.0.0.1
dbname = postfix
table = alias
select_field = goto
where_field = address

# /etc/postfix/mysql_virtual_domains_maps.cf:
user = postfix
password = postfixpassword
hosts = 127.0.0.1
dbname = postfix
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '0' and active = '1'

# /etc/postfix/mysql_virtual_mailbox_maps.cf:
user = postfix
password = postfixpassword
hosts = 127.0.0.1
dbname = postfix
table = mailbox
select_field = maildir
where_field = username
additional_conditions = and active = '1'

# /etc/postfix/mysql_virtual_mailbox_limit_maps.cf:
user = postfix
password = postfixpassword
hosts = 127.0.0.1
dbname = postfix
table = mailbox
select_field = quota
where_field = username
additional_conditions = and active = '1'

# /etc/postfix/mysql_relay_domains_maps.cf:
user = postfix
password = postfixpassword
hosts = 127.0.0.1
dbname = postfix
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '1'

# make postfix configuration files viewable to postfix user only:
sudo chgrp postfix /etc/postfix/mysql_*.cf
sudo chmod 640 /etc/postfix/mysql_*.cf

# Create a vmail user by running these commands:
sudo groupadd -g 5000 vmail
sudo useradd -g vmail -u 5000 vmail -d /home/vmail -m

# Create certificates for TLS
openssl req -new -outform PEM -out /etc/postfix/smtpd.cert -newkey rsa:2048 -nodes -keyout /etc/postfix/smtpd.key -keyform PEM -days 3650 -x509
chmod 640 /etc/postfix/smtpd.key

# Enable secure ports: 465 and 587 (/etc/postfix/master.cf)
smtps inet n - - - - smtpd
-o smtpd_tls_wrappermode=yes
-o smtpd_sasl_auth_enable=yes
-o smtpd_client_restrictions=permit_sasl_authenticated,reject
-o milter_macro_daemon_name=ORIGINATING
587 inet n - - - - smtpd

# /etc/postfix/main.cf:
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_mailbox_base = /home/vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
smtpd_sasl_auth_enable = yes
smtpd_helo_required = yes
broken_sasl_auth_clients = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_use_tls = yes
smtpd_tls_cert_file = /etc/postfix/smtpd.cert
smtpd_tls_key_file = /etc/postfix/smtpd.key
strict_rfc821_envelopes = yes
disable_vrfy_command = yes
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = "Account is over quota"
virtual_overquota_bounce = yes
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $mynetworks $virtual_mailbox_limit_maps

# Set up permissions for postfix to use sasl, or you will get error: SASL authentication failure
sudo adduser postfix sasl

# /etc/courier/authdaemonrc (add DEBUG_LOGIN=2 if you want to log password in /var/log/mail)
authmodulelist="authmysql"

# /etc/courier/authmysqlrc:
MYSQL_SERVER 127.0.0.1
MYSQL_USERNAME postfix
MYSQL_PASSWORD thepassword
MYSQL_DATABASE postfix
MYSQL_USER_TABLE mailbox
MYSQL_LOGIN_FIELD username
MYSQL_NAME_FIELD name
MYSQL_CRYPT_PWFIELD password
#MYSQL_CLEAR_PWFIELD     password
MYSQL_MAILDIR_FIELD maildir
MYSQL_QUOTA_FIELD concat(quota,'S')
MYSQL_HOME_FIELD        '/home/vmail'
MYSQL_UID_FIELD '5000'
MYSQL_GID_FIELD '5000'

# /etc/postfix/sasl/smtpd.conf:
#sql_engine: mysql # might not be needed, try with out it
pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: 127.0.0.1
sql_database: postfix
sql_user: username
sql_passwd: password
sql_select: select password from mailbox where username='%u@%r' and active = 1

# Create a folder for the SASL PID file
mkdir -p /var/spool/postfix/var/run/saslauthd

# Enable SASL (/etc/default/saslauthd)
START=yes
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r"
NAME="saslauthd"
MECHANISMS="pam"

# Configure SASL to use the new PID file location (pico /etc/init.d/saslauthd)
# Make sure you replace all PIDFILE definations in the file. This is set on a few places.
PIDFILE="/var/spool/postfix/var/run/${NAME}/saslauthd.pid"

# Configure PAM to use MySql backend for authentication (/etc/pam.d/smtp)
auth required pam_mysql.so user=username passwd=password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1
account sufficient pam_mysql.so user=username passwd=password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1

# saslauthd seems to be buggy in Ubuntu 11.10: [url]https://bugs.launchpad.net/ubuntu/+s...l2/+bug/875440[/url]
You can solve the problem by installing the saslauthd packages from Ubuntu 11.04:
apt-get install libsqlite0 db4.8-util libssl0.9.8
cd /tmp
mkdir sasl
cd sasl
wget [url]http://archive.ubuntu.com/ubuntu/pool/main/c/cyrus-sasl2/libsasl2-2_2.1.23.dfsg1-5ubuntu3_i386.deb[/url] [url]http://archive.ubuntu.com/ubuntu/pool/main/c/cyrus-sasl2/libsasl2-modules_2.1.23.dfsg1-5ubuntu3_i386.deb[/url] [url]http://archive.ubuntu.com/ubuntu/pool/main/c/cyrus-sasl2/libsasl2-modules-sql_2.1.23.dfsg1-5ubuntu3_i386.deb[/url] [url]http://archive.ubuntu.com/ubuntu/pool/main/c/cyrus-sasl2/libsasl2-dev_2.1.23.dfsg1-5ubuntu3_i386.deb[/url] [url]http://archive.ubuntu.com/ubuntu/pool/main/c/cyrus-sasl2/sasl2-bin_2.1.23.dfsg1-5ubuntu3_i386.deb[/url]
dpkg -i *.deb

# Restart daemons
sudo /etc/init.d/saslauthd restart
sudo /etc/init.d/postfix restart
sudo /etc/init.d/courier-authdaemon restart
sudo /etc/init.d/courier-imap restart
sudo /etc/init.d/courier-imap-ssl restart
sudo /etc/init.d/courier-pop restart
sudo /etc/init.d/courier-pop-ssl restart

Note: If something goes wrong, go to:
[url]https://help.ubuntu.com/community/PostfixCompleteVirtualMailSystemHowto[/url] to step-by-step guide on how to do it properly
[url]http://www.debiantutorials.com/installing-postfix-with-mysql-backend-and-sasl-for-smtp-authentication/[/url]
[url]http://www.debiantutorials.com/installing-courier-pop3-and-imap-daemon-with-mysql-backend-install-courier/[/url]
[url]http://codepoets.co.uk/2009/postfixadmin-setupinstall-guide-for-virtual-mail-users-on-postfix/[/url]

Don't forget to save extra addons to the locations below (read their documentation, it's inside of every .sh file)
/usr/local/bin/postfixadmin-mailbox-postcreation.sh
/usr/local/bin/postfixadmin-mailbox-postdeletion.sh
/usr/local/bin/postfixadmin-domain-postdeletion.sh