2009 - Postfix + Cyrus + MySQL

#######################################################
## CYRUS

# apt-get install cyrus-common-2.2 cyrus-clients-2.2 cyrus-admin-2.2 libsasl2-modules-sql libsasl2-modules
# apt-get install libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl

#######################################################
## POSTFIX

# apt-get install postfix-mysql postfix-tls

## uprava /etc/postfix/main.cf

# nano /etc/postfix/main.cf

 --------------- nalepist tu vypis z main.cf nakonci ked to bude funkcne !!!!!!!!!!!!

##

# cd /etc/postfix

# touch mysql_virtual_sender.cf
# nano mysql_virtual_sender.cf

user = postfix
password = postfix
hosts = 127.0.0.1
dbname = postfix
table = mail_mailbox
select_field = username
where_field = username

########################################
# touch mysql_virtual_alias_maps.cf
# nano 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'

########################################
# touch mysql_virtual_domains_maps.cf
# nano 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'

########################################
# touch mysql_virtual_mailbox_limit_maps.cf
# nano 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'

########################################
# touch mysql_virtual_mailbox_maps.cf
# nano 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'

## nastavenie prav pre konfiguracne subory

# chown root:postfix *.cf
# chmod 644 *.cf

## vytvorenie uzivatela mail

# useradd -r -u 150 -g mail -d /var/vmail -s /sbin/nologin -c "Virtual mailbox" vmail
# mkdir /var/vmail
# chmod 770 /var/vmail/
# chown vmail:mail /var/vmail

## prekopirovanie aliases

# cp /etc/aliases* /etc/postfix/
# newaliases

#######################################################
## MYSQL && APACHE2 && PHP

# apt-get install mysql-server-5.0 mysql-client-5.0 phpmyadmin apache2 libapache2-mod-php5 php5 php5-mysql

## vytvorenie DB

# mysql -u root -p
# mysql> CREATE DATABASE mail;
# mysql> GRANT ALL PRIVILEGES ON mail.* TO 'mail'@'localhost' IDENTIFIED BY 'mail';
# mysql> FLUSH PRIVILEGES;
# mysql> quit

## nastavenie hesla pre mail user

# mysql -u root -p
# mysql> use mysql;
# mysql> update user set password=PASSWORD("NEWPASSWORD") where User='mail';
# mysql> flush privileges;
# mysql> quit

## vytvorenie tabuliek

# mysql -u mail -p
# mysql> use mail;

# mysql> 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';
# mysql> 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';
# mysql> 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';
# mysql> quit

#######################################################
## DOVECOT

# apt-get install dovecot-common

## uprava dovecot-sql.conf

# nano /etc/dovecot-sql.conf

driver = mysql
default_pass_scheme = plain
#connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=root password=default
# Alternatively you can connect to localhost as well:
connect = host=localhost dbname=mail user=mail password=mail
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'

## uprava dovecot.conf

# nano /etc/dovecot.conf

base_dir = /var/run/dovecot/
protocols = imap pop3 imaps pop3s
listen = [::]
login_dir = /var/run/dovecot-login
mail_location = mbox:/var/vmail/%d/%n
mbox_read_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
userdb sql {
args = /etc/dovecot-sql.conf
}
passdb sql {
args = /etc/dovecot-sql.conf
}

## nastavenie prav

chmod 600 /etc/dovecot/*.conf
chown vmail /etc/dovecot/*.conf

#######################################################
## PRIDANIE DOMENY A UZIVATELA

# mysql -u mail -p

mysql> USE mail;
mysql> INSERT INTO domain (domain,description,aliases,mailboxes,maxquota,transport,backupmx,active) VALUES ('domenanejaka.sk','Virtual domain','10','10', '0','virtual', '0','1');
mysql> INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('uzivatel1@domenanejaka.sk','heslo1', 'Meno Uzivatela1','uzivatel1/', '0','domenanejaka.sk','1');
mysql> INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('uzivatel2@domenanejaka.sk','heslo2', 'Meno Uzivatela2','uzivatel2/', '0','domenanejaka.sk','1');
mysql> quit

#######################################################
## LINKY

http://wiki.sharlaan.net/us:howto:postfix:debian

http://www.howtoforge.com/isp-mailserver-with-virtual-users-domains-postfix-dovecot-mysql-centos5.0-p2

http://johnny.chadda.se/2007/04/15/mail-server-howto-postfix-and-dovecot-with-mysql-and-tlsssl-postgrey-and-dspam/

http://deblueconfuse.perbanas.ac.id/blogs/full-mail-server-solution-w-virtual-domains-users-debian-etch-postfix-mysql-dovecot-dspam-clamav-postgrey-rbl.html