Postfix + courier + mysql

sissejuhatus

Mailisüsteem kipub sageli üle pea kasvama. Haldus käsureal muutub tülikaks /või tekib soov kasutada paraleelselt mitut mailiserverit. Aitab näiteks lihtsalt paigutada kasutajad mysql'i ning kasutada veebi administreerimisliidest.

Süsteem näeb välja selline ,et faile hoitakse populaarses maildir http://en.wikipedia.org/wiki/Maildir formaadis kuid kasutajaid loeb postfix mysql baasist.

install

paigaldame postfix tarkvara koos mysql toega selleks freebsd's

cd /usr/ports/mail/postfix && make install clean

ning vaatame ,et mysql toega.

seejärel paigaldame courieri ning mysql'i

cd /usr/portsw/mail/courier-imap && make install clean

mysql install ja seadistus on kirjas [mysql install]

postfix

main.cf

home_mailbox = .maildir/ 

virtual_mailbox_base = /home/mail
virtual_gid_maps = static:9009
virtual_uid_maps = static:9009 
virtual_minimum_uid = 9009
virtual_create_maildirsize = yes

virtual_alias_maps = mysql:$config_directory/mysql_virtual_alias_maps.cf
virtual_mailbox_domains = mysql:$config_directory/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = mysql:$config_directory/mysql_virtual_mailbox_maps.cf
virtual_mailbox_limit_maps = mysql:$config_directory/mysql_virtual_mailbox_limit_maps.cf
relay_domains = mysql:$config_directory/mysql_relay_domains_maps.cf

virtual_mailbox_extended = yes
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, users quota limit has been exceeded.
virtual_overquota_bounce = yes

Loome kausta /var/mail ja anname õigused ,et courier pääseks sinna ligi.

mysql_virtual_alias_maps.cf

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

mysql_relay_domains_maps.cf

user = mail
password = parool
hosts = localhost
dbname = mail
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '1' AND active = '1'

mysql_virtual_mailbox_limit_maps.cf

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

mysql_relay_domains_maps.cf

user = mail
password = parool
hosts = localhost
dbname = mail
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '1' AND active = '1'

mysql basi seadistus

tekitame baasi mail ja tekiame sinna struktuuri võime kasutada järgnevalt äratoodud dump'i. Juhin veel tähelepanu ,et tabelid nagu admin, domain_admins, log ja vacation on vajalikud vaid postfixadmini jaoks. Kui ei ole plaanis neid kasutada võib jätta välja.

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

CREATE TABLE `admin` (
  `username` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `password` varchar(255) collate utf8_estonian_ci 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=utf8 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Virtual  Admins';

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

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

CREATE TABLE `alias` (
  `address` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `goto` text collate utf8_estonian_ci NOT NULL,
  `domain` varchar(255) collate utf8_estonian_ci 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 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Virtual   Aliases';

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

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

CREATE TABLE `domain` (
  `domain` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `description` varchar(255) collate utf8_estonian_ci 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) collate utf8_estonian_ci 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=utf8 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Virtual  Domains';

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

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

CREATE TABLE `domain_admins` (
 `username` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `domain` varchar(255) collate utf8_estonian_ci 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=utf8 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Domain  Admins';

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

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

CREATE TABLE `log` (
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `username` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `domain` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `action` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `data` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Log';

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

-- 
-- Table structure for table `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',
  `uid` int(10) unsigned default '9009',
  `gid` int(10) unsigned default '9009',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`username`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=binary COMMENT='Postfix Admin - Virtual Mailboxes';

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

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

CREATE TABLE `vacation` (
  `email` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `subject` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `body` text collate utf8_estonian_ci NOT NULL,
  `cache` text collate utf8_estonian_ci NOT NULL,
  `domain` varchar(255) collate utf8_estonian_ci NOT NULL default ,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`email`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci COMMENT='Postfix Admin - Virtual  Vacation';

juhin tähelepanu sellele ,et read

mailbox tabelis

 `uid` int(10) unsigned default '9009',
 `gid` int(10) unsigned default '9009',

pole näites lisatud ja see on omatehtud häkk kuna courieri uuem versuioon ootab vaikimisi uid/gid parameetreid samuti kuid postfixadmini käesolev versioon ei oska neid baasi kirjutada nii siis täidame selle automaatselt alati.

courier-imap

Freebsd's avame kaustas /usr/local/etc/authlib faili authmysqlrc

selle sisu peaks olema selline

MYSQL_CRYPT_PWFIELD     password
MYSQL_DATABASE          mail
MYSQL_GID_FIELD         '9009'
MYSQL_HOME_FIELD        '/home/mail'
MYSQL_LOGIN_FIELD       username
MYSQL_MAILDIR_FIELD     maildir
MYSQL_NAME_FIELD        name
MYSQL_OPT               0
MYSQL_PASSWORD          parool
MYSQL_QUOTA_FIELD      quota
MYSQL_SERVER            localhost
MYSQL_UID_FIELD         '9009'
MYSQL_USERNAME          mail
MYSQL_USER_TABLE        mailbox

postfixadmin

Hangime viimase versiooni tarballi http://sourceforge.net/projects/postfixadmin/

paigaldame apache ja php ning pakime postfixadmini lahti seadistame veebi kasutama loodud andmebaasi mail ning testime :]