allow to define supported SQL adapter in email plugin
[tine20] / tine20 / Tinebase / EmailUser / Smtp / Postfix.php
1 <?php
2 /**
3  * Tine 2.0
4  * 
5  * @package     Tinebase
6  * @subpackage  EmailUser
7  * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
8  * @copyright   Copyright (c) 2009-2012 Metaways Infosystems GmbH (http://www.metaways.de)
9  * @author      Philipp Schüle <p.schuele@metaways.de>
10  * 
11 --
12 -- Database: `postfix`
13 --
14
15 -- --------------------------------------------------------
16
17 --
18 -- Table structure for table `smtp_users`
19 --
20
21 CREATE TABLE IF NOT EXISTS `smtp_users` (
22   `userid` varchar(40) NOT NULL,
23   `client_idnr` varchar(40) NOT NULL,
24   `username` varchar(80) NOT NULL,
25   `passwd` varchar(80) NOT NULL,
26   `email` varchar(80) DEFAULT NULL,
27   `forward_only` tinyint(1) NOT NULL DEFAULT '0',
28   PRIMARY KEY (`userid`, `client_idnr`),
29   UNIQUE KEY `username` (`username`),
30   UNIQUE KEY `email` (`email`)
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
32
33 -- --------------------------------------------------------
34
35 --
36 -- Table structure for table `smtp_destinations`
37 --
38
39 CREATE TABLE IF NOT EXISTS `smtp_destinations` (
40   `userid` VARCHAR( 40 ) NOT NULL ,
41   `source` VARCHAR( 80 ) NOT NULL ,
42   `destination` VARCHAR( 80 ) NOT NULL ,
43   CONSTRAINT `smtp_destinations::userid--smtp_users::userid` FOREIGN KEY (`userid`) 
44   REFERENCES `smtp_users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
45 ) ENGINE=Innodb DEFAULT CHARSET=utf8;
46 -- --------------------------------------------------------
47
48 --
49 -- Postfix virtual_mailbox_domains: sql-virtual_mailbox_domains.cf
50 --
51
52 user     = smtpUser
53 password = smtpPass
54 hosts    = 127.0.0.1
55 dbname   = smtp
56 query    = SELECT DISTINCT 1 FROM smtp_destinations WHERE SUBSTRING_INDEX(source, '@', -1) = '%s';
57 -- ----------------------------------------------------
58
59 --
60 -- Postfix sql-virtual_mailbox_maps: sql-virtual_mailbox_maps.cf
61 --
62
63 user     = smtpUser
64 password = smtpPass
65 hosts    = 127.0.0.1
66 dbname   = smtp
67 query    = SELECT 1 FROM smtp_users WHERE username='%s' AND forward_only=0
68 -- ----------------------------------------------------
69
70 --
71 -- Postfix sql-virtual_alias_maps: sql-virtual_alias_maps_aliases.cf
72 --
73
74 user     = smtpUser
75 password = smtpPass
76 hosts    = 127.0.0.1
77 dbname   = smtp
78 query = SELECT destination FROM smtp_destinations WHERE source='%s'
79
80 -- -----------------------------------------------------
81  */
82
83  /**
84  * plugin to handle postfix smtp accounts
85  * 
86  * @package    Tinebase
87  * @subpackage EmailUser
88  */
89 class Tinebase_EmailUser_Smtp_Postfix extends Tinebase_EmailUser_Sql
90 {
91     /**
92      * destination table name with prefix
93      *
94      * @var string
95      */
96     protected $_destinationTable = NULL;
97     
98     /**
99      * postfix config
100      * 
101      * @var array 
102      */
103     protected $_config = array(
104         'prefix'            => 'smtp_',
105         'userTable'         => 'users',
106         'destinationTable'  => 'destinations',
107         'emailScheme'       => 'ssha256',
108         'domain'            => null,
109         'alloweddomains'    => array(),
110         'adapter'           => Tinebase_Core::PDO_MYSQL
111     );
112
113     /**
114      * user properties mapping
115      *
116      * @var array
117      */
118     protected $_propertyMapping = array(
119         'emailPassword'     => 'passwd', 
120         'emailUserId'       => 'userid',
121         'emailAddress'      => 'email',
122         'emailForwardOnly'  => 'forward_only',
123         'emailUsername'     => 'username',
124         'emailAliases'      => 'source',
125         'emailForwards'     => 'destination'
126     );
127     
128     /**
129      * the constructor
130      */
131     public function __construct(array $_options = array())
132     {
133         $this->_configKey    = Tinebase_Config::SMTP;
134         $this->_subconfigKey = 'postfix';
135
136         parent::__construct($_options);
137         
138         $smtpConfig = Tinebase_Config::getInstance()->get($this->_configKey, new Tinebase_Config_Struct())->toArray();
139         
140         // set domain from smtp config
141         $this->_config['domain'] = !empty($smtpConfig['primarydomain']) ? $smtpConfig['primarydomain'] : null;
142         
143         // add allowed domains
144         if (! empty($smtpConfig['primarydomain'])) {
145             $this->_config['alloweddomains'] = array($smtpConfig['primarydomain']);
146             if (! empty($smtpConfig['secondarydomains'])) {
147                 // merge primary and secondary domains and split secondary domains + trim whitespaces
148                 $this->_config['alloweddomains'] = array_merge($this->_config['alloweddomains'], preg_split('/\s*,\s*/', $smtpConfig['secondarydomains']));
149             } 
150         }
151         
152         $this->_clientId = Tinebase_Application::getInstance()->getApplicationByName('Tinebase')->getId();
153         
154         $this->_destinationTable = $this->_config['prefix'] . $this->_config['destinationTable'];
155     }
156     
157     /**
158      * get the basic select object to fetch records from the database
159      *  
160      * @param  array|string|Zend_Db_Expr  $_cols        columns to get, * per default
161      * @param  boolean                    $_getDeleted  get deleted records (if modlog is active)
162      * @return Zend_Db_Select
163      */
164     protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
165     {
166         // _userTable.emailUserId=_destinationTable.emailUserId
167         $userIDMap    = $this->_db->quoteIdentifier($this->_userTable . '.' . $this->_propertyMapping['emailUserId']);
168         $userEmailMap = $this->_db->quoteIdentifier($this->_userTable . '.' . $this->_propertyMapping['emailAddress']);
169         
170         $select = $this->_db->select()
171             ->from($this->_userTable)
172             ->group($this->_userTable . '.userid')
173             // Only want 1 user (shouldn't be more than 1 anyway)
174             ->limit(1);
175             
176         // select source from alias table
177         $select->joinLeft(
178             array('aliases' => $this->_destinationTable), // Table
179             '(' . $userIDMap .  ' = ' .  // ON (left)
180             $this->_db->quoteIdentifier('aliases.' . $this->_propertyMapping['emailUserId']) . // ON (right)
181             ' AND ' . $userEmailMap . ' = ' . // AND ON (left)
182             $this->_db->quoteIdentifier('aliases.' . $this->_propertyMapping['emailForwards']) . ')', // AND ON (right)
183             array($this->_propertyMapping['emailAliases'] => $this->_dbCommand->getAggregate('aliases.' . $this->_propertyMapping['emailAliases']))); // Select
184         
185         // select destination from alias table
186         $select->joinLeft(
187             array('forwards' => $this->_destinationTable), // Table
188             '(' . $userIDMap .  ' = ' . // ON (left)
189             $this->_db->quoteIdentifier('forwards.' . $this->_propertyMapping['emailUserId']) . // ON (right)
190             ' AND ' . $userEmailMap . ' = ' . // AND ON (left)
191             $this->_db->quoteIdentifier('forwards.' . $this->_propertyMapping['emailAliases']) . ')', // AND ON (right)
192             array($this->_propertyMapping['emailForwards'] => $this->_dbCommand->getAggregate('forwards.' . $this->_propertyMapping['emailForwards']))); // Select
193
194         // append domain if set or domain IS NULL
195         if (! empty($this->_clientId)) {
196             $select->where($this->_db->quoteIdentifier($this->_userTable . '.client_idnr') . ' = ?', $this->_clientId);
197         } else {
198             $select->where($this->_db->quoteIdentifier($this->_userTable . '.client_idnr') . ' IS NULL');
199         }
200         
201         return $select;
202     }
203     
204     /**
205     * interceptor before add
206     *
207     * @param array $emailUserData
208     */
209     protected function _beforeAddOrUpdate(&$emailUserData)
210     {
211         unset($emailUserData[$this->_propertyMapping['emailForwards']]);
212         unset($emailUserData[$this->_propertyMapping['emailAliases']]);
213     }
214     
215     /**
216     * interceptor after add
217     *
218     * @param array $emailUserData
219     */
220     protected function _afterAddOrUpdate(&$emailUserData)
221     {
222         $this->_setAliasesAndForwards($emailUserData);
223     }
224     
225     /**
226      * set email aliases and forwards
227      * 
228      * removes all aliases for user
229      * creates default email->email alias if not forward only
230      * creates aliases
231      * creates forwards
232      * 
233      * @param  array  $_smtpSettings  as returned from _recordToRawData
234      * @return void
235      */
236     protected function _setAliasesAndForwards($_smtpSettings)
237     {
238         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ 
239             . ' Setting default alias/forward for ' . print_r($_smtpSettings, true));
240         
241         $this->_removeDestinations($_smtpSettings[$this->_propertyMapping['emailUserId']]);
242         
243         // check if it should be forward only
244         if (! $_smtpSettings[$this->_propertyMapping['emailForwardOnly']]) {
245             $this->_createDefaultDestinations($_smtpSettings);
246         }
247         
248         $this->_createAliasDestinations($_smtpSettings);
249         $this->_createForwardDestinations($_smtpSettings);
250     }
251     
252     /**
253      * remove all current aliases and forwards for user
254      * 
255      * @param string $userId
256      */
257     protected function _removeDestinations($userId)
258     {
259         $where = array(
260             $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $userId)
261         );
262         
263         $this->_db->delete($this->_destinationTable, $where);
264     }
265     
266     /**
267      * create default destinations
268      * 
269      * @param array $_smtpSettings
270      */
271     protected function _createDefaultDestinations($_smtpSettings)
272     {
273         // create email -> username alias
274         $this->_addDestination(array(
275             'userid'        => $_smtpSettings[$this->_propertyMapping['emailUserId']],   // userID
276             'source'        => $_smtpSettings[$this->_propertyMapping['emailAddress']],  // TineEmail
277             'destination'   => $_smtpSettings[$this->_propertyMapping['emailUsername']], // email
278         ));
279         
280         // create username -> username alias if email and username are different
281         if ($_smtpSettings[$this->_propertyMapping['emailUsername']] != $_smtpSettings[$this->_propertyMapping['emailAddress']]) {
282             $this->_addDestination(array(
283                 'userid'      => $_smtpSettings[$this->_propertyMapping['emailUserId']],   // userID
284                 'source'      => $_smtpSettings[$this->_propertyMapping['emailUsername']], // username
285                 'destination' => $_smtpSettings[$this->_propertyMapping['emailUsername']], // username
286             ));
287         }
288     }
289     
290     /**
291      * add destination
292      * 
293      * @param array $destinationData
294      */
295     protected function _addDestination($destinationData)
296     {
297         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__
298             . ' Insert into table destinations: ' . print_r($destinationData, true));
299         
300         $this->_db->insert($this->_destinationTable, $destinationData);
301     }
302     
303     /**
304      * set aliases
305      * 
306      * @param array $_smtpSettings
307      */
308     protected function _createAliasDestinations($_smtpSettings)
309     {
310         if (! ((isset($_smtpSettings[$this->_propertyMapping['emailAliases']]) || array_key_exists($this->_propertyMapping['emailAliases'], $_smtpSettings)) && is_array($_smtpSettings[$this->_propertyMapping['emailAliases']]))) {
311             return;
312         }
313         
314         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Setting aliases for '
315             . $_smtpSettings[$this->_propertyMapping['emailUsername']] . ': ' . print_r($_smtpSettings[$this->_propertyMapping['emailAliases']], TRUE));
316         
317         $userId = $_smtpSettings[$this->_propertyMapping['emailUserId']];
318             
319         foreach ($_smtpSettings[$this->_propertyMapping['emailAliases']] as $aliasAddress) {
320             // check if in primary or secondary domains
321             if (! empty($aliasAddress) && $this->_checkDomain($aliasAddress)) {
322                 
323                 if (! $_smtpSettings[$this->_propertyMapping['emailForwardOnly']]) {
324                     // create alias -> email
325                     $this->_addDestination(array(
326                         'userid'      => $userId,
327                         'source'      => $aliasAddress,
328                         'destination' => $_smtpSettings[$this->_propertyMapping['emailAddress']], // email 
329                     ));
330                 } else if ($this->_hasForwards($_smtpSettings)) {
331                     $this->_addForwards($userId, $aliasAddress, $_smtpSettings[$this->_propertyMapping['emailForwards']]);
332                 }
333             }
334         }
335     }
336     
337     /**
338      * check if forward addresses exist
339      * 
340      * @param array $_smtpSettings
341      * @return boolean
342      */
343     protected function _hasForwards($_smtpSettings)
344     {
345         return ((isset($_smtpSettings[$this->_propertyMapping['emailForwards']]) || array_key_exists($this->_propertyMapping['emailForwards'], $_smtpSettings)) && is_array($_smtpSettings[$this->_propertyMapping['emailForwards']]));
346     }
347
348     /**
349      * add forward destinations
350      * 
351      * @param string $userId
352      * @param string $source
353      * @param array $forwards
354      */
355     protected function _addForwards($userId, $source, $forwards)
356     {
357         foreach ($forwards as $forwardAddress) {
358             if (! empty($forwardAddress)) {
359                 // create email -> forward
360                 $this->_addDestination(array(
361                     'userid'      => $userId,
362                     'source'      => $source,
363                     'destination' => $forwardAddress
364                 ));
365             }
366         }
367     }
368     
369     /**
370      * set forwards
371      * 
372      * @param array $_smtpSettings
373      */
374     protected function _createForwardDestinations($_smtpSettings)
375     {
376         if (! $this->_hasForwards($_smtpSettings)) {
377             return;
378         }
379
380         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ 
381             . ' Setting forwards for ' . $_smtpSettings[$this->_propertyMapping['emailUsername']] . ': ' . print_r($_smtpSettings[$this->_propertyMapping['emailForwards']], TRUE));
382         
383         $this->_addForwards(
384             $_smtpSettings[$this->_propertyMapping['emailUserId']],
385             $_smtpSettings[$this->_propertyMapping['emailAddress']],
386             $_smtpSettings[$this->_propertyMapping['emailForwards']]
387         );
388     }
389     
390     /**
391      * converts raw data from adapter into a single record / do mapping
392      *
393      * @param  array $_data
394      * @return Tinebase_Record_Abstract
395      */
396     protected function _rawDataToRecord(array $_rawdata)
397     {
398         $data = array();
399         
400         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__
401             . ' raw data: ' . print_r($_rawdata, true));
402         
403         foreach ($_rawdata as $key => $value) {
404             $keyMapping = array_search($key, $this->_propertyMapping);
405             if ($keyMapping !== FALSE) {
406                 switch ($keyMapping) {
407                     case 'emailPassword':
408                         // do nothing
409                         break;
410                     
411                     case 'emailAliases':
412                     case 'emailForwards':
413                         $data[$keyMapping] = explode(',', $value);
414                         // Get rid of TineEmail -> username mapping.
415                         $tineEmailAlias = array_search($_rawdata[$this->_propertyMapping['emailUsername']], $data[$keyMapping]);
416                         if ($tineEmailAlias !== false) {
417                             unset($data[$keyMapping][$tineEmailAlias]);
418                             $data[$keyMapping] = array_values($data[$keyMapping]);
419                         }
420                         // sanitize aliases & forwards
421                         if (count($data[$keyMapping]) == 1 && empty($data[$keyMapping][0])) {
422                             $data[$keyMapping] = array();
423                         }
424                         break;
425                         
426                     case 'emailForwardOnly':
427                         $data[$keyMapping] = (bool)$value;
428                         break;
429                         
430                     default: 
431                         $data[$keyMapping] = $value;
432                         break;
433                 }
434             }
435         }
436         
437         $emailUser = new Tinebase_Model_EmailUser($data, TRUE);
438         
439         $this->_getForwardedAliases($emailUser);
440         
441         return $emailUser;
442     }
443     
444     /**
445      * get forwarded aliases
446      * - fetch aliases + forwards from destinations table that do belong to 
447      *   user where aliases are directly mapped to forward addresses 
448      * 
449      * @param Tinebase_Model_EmailUser $emailUser
450      */
451     protected function _getForwardedAliases(Tinebase_Model_EmailUser $emailUser)
452     {
453         if (! $emailUser->emailForwardOnly) {
454             return;
455         }
456         
457         $select = $this->_db->select()
458             ->from($this->_destinationTable)
459             ->where($this->_db->quoteIdentifier($this->_destinationTable . '.' . $this->_propertyMapping['emailUserId']) . ' = ?', $emailUser->emailUserId);
460         $stmt = $this->_db->query($select);
461         $queryResult = $stmt->fetchAll();
462         $stmt->closeCursor();
463         
464         $aliases = ($emailUser->emailAliases && is_array($emailUser->emailAliases)) ? $emailUser->emailAliases : array();
465         foreach ($queryResult as $destination) {
466             if ($destination['source'] !== $emailUser->emailAddress
467                 && in_array($destination['destination'], $emailUser->emailForwards)
468                 && ! in_array($destination['source'], $aliases)
469             ) {
470                 $aliases[] = $destination['source'];
471             }
472         }
473         $emailUser->emailAliases = $aliases;
474     }
475     
476     /**
477      * returns array of raw email user data
478      *
479      * @param  Tinebase_Model_EmailUser $_user
480      * @param  Tinebase_Model_EmailUser $_newUserProperties
481      * @throws Tinebase_Exception_UnexpectedValue
482      * @return array
483      * 
484      * @todo   validate domains of aliases too
485      */
486     protected function _recordToRawData(Tinebase_Model_FullUser $_user, Tinebase_Model_FullUser $_newUserProperties)
487     {
488         $rawData = array();
489         
490         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . print_r($_newUserProperties->toArray(), true));
491         
492         if (isset($_newUserProperties->smtpUser)) {
493             foreach ($_newUserProperties->smtpUser as $key => $value) {
494                 $property = (isset($this->_propertyMapping[$key]) || array_key_exists($key, $this->_propertyMapping)) ? $this->_propertyMapping[$key] : false;
495                 if ($property) {
496                     switch ($key) {
497                         case 'emailPassword':
498                             $rawData[$property] = Hash_Password::generate($this->_config['emailScheme'], $value);
499                             break;
500                             
501                         case 'emailAliases':
502                             $rawData[$property] = array();
503                             
504                             foreach((array)$value as $address) {
505                                 if ($this->_checkDomain($address) === true) {
506                                     $rawData[$property][] = $address;
507                                 }
508                             }
509                             break;
510                             
511                         case 'emailForwards':
512                             $rawData[$property] = is_array($value) ? $value : array();
513                             
514                             break;
515                             
516                         default:
517                             $rawData[$property] = $value;
518                             break;
519                     }
520                 }
521             }
522         }
523         
524         if (!empty($_user->accountEmailAddress)) {
525             $this->_checkDomain($_user->accountEmailAddress, TRUE);
526         }
527         
528         $rawData[$this->_propertyMapping['emailAddress']]  = $_user->accountEmailAddress;
529         $rawData[$this->_propertyMapping['emailUserId']]   = $_user->getId();
530         $rawData[$this->_propertyMapping['emailUsername']] = $this->_appendDomain($_user->accountLoginName);
531         
532         if (empty($rawData[$this->_propertyMapping['emailAddress']])) {
533             $rawData[$this->_propertyMapping['emailAliases']]  = null;
534             $rawData[$this->_propertyMapping['emailForwards']] = null;
535         }
536         
537         if (empty($rawData[$this->_propertyMapping['emailForwards']])) {
538             $rawData[$this->_propertyMapping['emailForwardOnly']] = 0;
539         }
540         
541         $rawData['client_idnr'] = $this->_clientId;
542         
543         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . print_r($rawData, true));
544         
545         return $rawData;
546     }
547     
548     /**
549      * check if email address is in allowed domains
550      * 
551      * @param string $_email
552      * @param boolean $_throwException
553      * @return boolean
554      * @throws Tinebase_Exception_Record_NotAllowed
555      */
556     protected function _checkDomain($_email, $_throwException = false)
557     {
558         $result = true;
559         
560         if (! empty($this->_config['alloweddomains'])) {
561
562             list($user, $domain) = explode('@', $_email, 2);
563             
564             if (! in_array($domain, $this->_config['alloweddomains'])) {
565                 if (Tinebase_Core::isLogLevel(Zend_Log::WARN)) Tinebase_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' Email address ' . $_email . ' not in allowed domains!');
566                 
567                 if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Allowed domains: ' . print_r($this->_config['alloweddomains'], TRUE));
568                 
569                 if ($_throwException) {
570                     throw new Tinebase_Exception_UnexpectedValue('Email address not in allowed domains!');
571                 } else {
572                     $result = false;
573                 }
574             }
575         }
576         
577         return $result;
578     }
579 }