Admins/Mailserver integration

From Tine 2.0 - Wiki

Maintaining email accounts can be very time consuming task. Creating accounts on the imap server, add the needed email aliases on the smtp server and also disable accounts when a user has left the company.

Tine 2.0 makes this an easy task. Tine 2.0 comes with multiple backends to manage different imap and smtp servers. Currently following servers got tested until now:

  • IMAP
    • Cyrus (managed via IMAP)
    • Dovecot with SQL backend
    • Dbmail with SQL backend
    • Dbmail with LDAP backend
  • SMTP
    • Postfix with SQL backend
    • Postfix with LDAP backend

Especially the LDAP backends are very generic. This makes is very likely that they will also work with other IMAP/SMTP ldap enabled servers.

How to manage Dovecot and Postfix from Tine 2.0

Ubuntu comes with a master package to install Dovecot and Postfix in one go. Simply install the postfix-dovecot package by executing following command;

aptitude install dovecot-postfix

This will install all required packages at once. You can find detailed instructions how to configure Postfix here. The same page is available for Dovecot here. The Ubuntu server guide should answer the questions to get a basic mailsystem up and running.

here is a tutorial for the installation of postfix/dovecot/mysql on ubuntu lucid lynx (10.04 LTS): http://library.linode.com/email/postfix/dovecot-mysql-ubuntu-10.04-lucid

Configuring Dovecot with MySQL backend

Dovecot supports multiple backends to store user accounts and authentication data. In our case we choose the MySQL backend. Tine 2.0 creates the user accounts in 2 MySQL tables, where Dovecot can read them from.

These are the SQL statement needed to create the 2 tables:

CREATE TABLE IF NOT EXISTS `dovecot_users` (
`userid`        VARCHAR( 40 ) NOT NULL ,
`domain`        VARCHAR( 80 ) NOT NULL DEFAULT "",
`username`      VARCHAR( 80 ) NOT NULL ,
`password`      VARCHAR( 256 ) NOT NULL ,
`quota_bytes`   BIGINT NOT NULL DEFAULT '0',
`quota_message` INT NOT NULL DEFAULT '0',
`uid`           VARCHAR( 20 ) DEFAULT NULL ,
`gid`           VARCHAR( 20 ) DEFAULT NULL ,
`home`          VARCHAR( 256 ) DEFAULT NULL ,
`last_login`    DATETIME DEFAULT NULL ,
PRIMARY KEY (`userid`, `domain`),
UNIQUE (`username`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dovecot_usage` (
`username` VARCHAR( 80 ) NOT NULL ,
`storage`  BIGINT NOT NULL DEFAULT '0',
`messages` BIGINT NOT NULL DEFAULT '0',
PRIMARY KEY (`username`),
CONSTRAINT `dovecot_usage::username--dovecot_users::username` FOREIGN KEY (`username`) REFERENCES `dovecot_users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=Innodb DEFAULT CHARSET=utf8;

here are some required dovecot config files for the mail setup:


/etc/dovecot/dovecot-sql.conf:

driver = mysql
connect = host=127.0.0.1 dbname=dovecot user=dbuser password=****

# Default password scheme.
default_pass_scheme = PLAIN-MD5

# passdb with userdb prefetch
password_query = SELECT dovecot_users.username AS user, \
   password, \
   home AS userdb_home, \
   uid AS userdb_uid, \
   gid AS userdb_gid, \
   CONCAT('*:bytes=', CAST(quota_bytes AS CHAR), 'M') AS userdb_quota_rule \
   FROM dovecot_users \
   WHERE dovecot_users.username='%u'

# userdb for deliver
user_query = SELECT home, uid, gid, \
   CONCAT('*:bytes=', CAST(quota_bytes AS CHAR), 'M') AS userdb_quota_rule \
   FROM dovecot_users \
   WHERE dovecot_users.username='%u'

/etc/dovecot/conf.d/01-mail-stack-delivery.conf

   # Some general options
   protocols = imap pop3 sieve
   disable_plaintext_auth = yes
   ssl = yes
   ssl_cert_file = /etc/ssl/certs/ssl-mail.pem
   ssl_key_file = /etc/ssl/private/ssl-mail.key
   ssl_cipher_list = ALL:!LOW:!SSLv2:ALL:!aNULL:!ADH:!eNULL:!EXP:RC4+RSA:+HIGH:+MEDIUM
   mail_location = maildir:~/Maildir
   auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@
   first_valid_uid = 100

   # IMAP configuration
   protocol imap {
           mail_max_userip_connections = 10
           imap_client_workarounds = outlook-idle delay-newmail
           mail_plugins = quota imap_quota
   }

   # POP3 configuration
   protocol pop3 {
           mail_max_userip_connections = 10
           pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
           mail_plugins = quota
   }

   # LDA configuration
   protocol lda {
           postmaster_address = postmaster
           mail_plugins = sieve quota
           quota_full_tempfail = yes
           deliver_log_format = msgid=%m: %$
           rejection_reason = Your message to <%t> was automatically rejected:%n%r
   }

   # Plugins configuration
   plugin {
       sieve=~/.dovecot.sieve
       sieve_dir=~/sieve
       quota = dict:user::proxy::quotadict
   }

   dict {
       quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf
   }


/etc/dovecot/auth.d/01-mail-stack-delivery.auth

   mechanisms = plain login
   socket listen {
       master {
         # Master socket provides access to userdb information. It's typically
         # used to give Dovecot's local delivery agent access to userdb so it
         # can find mailbox locations.
         path = /var/run/dovecot/auth-master
         mode = 0600
         # Default user/group is the one who started dovecot-auth (root)
         user = deliver
         #group = 
       }
           client {
                   path = /var/spool/postfix/private/dovecot-auth
                   mode = 0660
                   user = postfix
                   group = postfix
           }
   }

you need to add a new system user "deliver" for this setup and chown /var/mail to belong this deliver user.


Configuring Postfix with MySQL backend

table structure for postfix with mysql:

 --
 -- Table structure for table `smtp_users`
 --
 CREATE TABLE IF NOT EXISTS `smtp_users` (
 `userid` varchar(40) NOT NULL,
 `client_idnr` varchar(40) NOT NULL,
 `username` varchar(80) NOT NULL,
 `passwd` varchar(256) NOT NULL,
 `email` varchar(80) DEFAULT NULL,
 `forward_only` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`userid`, `client_idnr`),
 UNIQUE KEY `username` (`username`),
 UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 --
 -- Table structure for table `smtp_destinations`
 --
 CREATE TABLE IF NOT EXISTS `smtp_destinations` (
 `userid` VARCHAR( 40 ) NOT NULL ,
 `source` VARCHAR( 80 ) NOT NULL ,
 `destination` VARCHAR( 80 ) NOT NULL ,
 CONSTRAINT `smtp_destinations::userid--smtp_users::userid` FOREIGN KEY (`userid`) 
 REFERENCES `smtp_users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=Innodb DEFAULT CHARSET=utf8;


here come the main postfix config files:


/etc/postfix/main.cf:

   smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
   biff = no

   # appending .domain is the MUA's job.
   append_dot_mydomain = no

   readme_directory = no

   # TLS parameters
   smtpd_tls_cert_file = /etc/ssl/certs/ssl-mail.pem
   smtpd_tls_key_file = /etc/ssl/private/ssl-mail.key
   smtpd_use_tls = yes
   smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
   smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

   # See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
   # information on enabling SSL in the smtp client.

   myhostname = servername
   alias_maps = hash:/etc/aliases
   alias_database = hash:/etc/aliases
   myorigin = /etc/mailname
   mydestination = servername, localhost
   relayhost = 
   mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
   mailbox_size_limit = 0
   recipient_delimiter = +
   inet_interfaces = loopback-only
   default_transport = error
   relay_transport = error
   home_mailbox = Maildir/
   smtpd_sasl_auth_enable = yes
   smtpd_sasl_type = dovecot
   smtpd_sasl_path = private/dovecot-auth
   smtpd_sasl_authenticated_header = yes
   smtpd_sasl_security_options = noanonymous
   smtpd_sasl_local_domain = $myhostname
   broken_sasl_auth_clients = yes
   smtpd_recipient_restrictions = reject_unknown_sender_domain, reject_unknown_recipient_domain, reject_unauth_pipelining, permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
   smtpd_sender_restrictions = reject_unknown_sender_domain
   mailbox_command = /usr/lib/dovecot/deliver -c /etc/dovecot/conf.d/01-mail-stack-delivery.conf -n -m "${EXTENSION}"
   smtp_use_tls = yes
   smtpd_tls_received_header = yes
   smtpd_tls_mandatory_protocols = SSLv3, TLSv1
   smtpd_tls_mandatory_ciphers = medium
   smtpd_tls_auth_only = yes
   tls_random_source = dev:/dev/urandom

   dovecot_destination_recipient_limit = 1

   virtual_transport = dovecot 

   virtual_mailbox_domains = mysql:/etc/postfix/sql/sql-virtual_mailbox_domains.cf
   virtual_mailbox_maps = mysql:/etc/postfix/sql/sql-virtual_mailbox_maps.cf
   virtual_alias_maps = mysql:/etc/postfix/sql/sql-virtual_alias_maps_aliases.cf


/etc/postfix/sql/sql-virtual_alias_maps_aliases.cf

   user     = dbuser
   password = *****
   hosts    = 127.0.0.1
   dbname   = postfix
   query    = SELECT destination FROM smtp_destinations WHERE source='%s'


/etc/postfix/sql/sql-virtual_mailbox_domains.cf

   user     = dbuser
   password = *****
   hosts    = 127.0.0.1
   dbname   = postfix
   query    = SELECT DISTINCT 1 FROM smtp_destinations WHERE SUBSTRING_INDEX(source, '@', -1) = '%s';


/etc/postfix/sql/sql-virtual_mailbox_maps.cf

   user     = dbuser
   password = *****
   hosts    = 127.0.0.1
   dbname   = postfix
   query    = SELECT 1 FROM smtp_users WHERE username='%s' AND forward_only=0


and add this lines to the bottom of /etc/postfix/master.cf:

   dovecot   unix  -       n       n       -       -       pipe 
     flags=DRhu user=deliver:deliver argv=/usr/lib/dovecot/deliver -c /etc/dovecot/conf.d/01-mail-stack-delivery.conf -f ${sender} -d ${user}@${nexthop} -n -m ${extension}


Installing Tine 2.0 with dovecot/postfix settings (CLI)

install tine 2.0 with this CLI command (adjust your mailsettings, initial user name / pw and the uid/gid of your deliver user (both 999 in this example)):

 php setup.php --install -- adminLoginName="admin" adminPassword="***" adminEmailAddress="admin@example.org" acceptedTermsVersion=1000 imap="host:localhost,port:143,useSystemAccount:1,ssl:tls,domain:example.org,backend:dovecot_imap,dovecot_host:localhost,dovecot_dbname:dovecot,dovecot_username:dbuser,dovecot_password:PASSWORD,dovecot_uid:999,dovecot_gid:999,dovecot_home:/var/spool/mail/%d/%n,dovecot_scheme:SHA256" smtp="backend:postfix,hostname:localhost,port:25,ssl:tls,auth:login,primarydomain:example.org,username:notification@example.org,password:****,from:notification@example.org,postfix_host:localhost,postfix_dbname:postfix,postfix_username:dbuser,postfix_password:*****" sieve="hostname:localhost"