hardwarefetish.com

Integrating SOGo with Froxlor

I recently wanted to equip a Server that was managed with the Froxlor (formally known as SysCP) Management system with SOGo Groupware features.
Linking the e-mail accounts with SOGo isn’t very hard provided that you let Froxlor save the e-mail passwords in plain text.
In case you don’t want to do this and enhance security (which I would strongly encourage you to do), please also make the modifications to Froxlor shown in this blog additionally to the instructions provided here.
The installation on Debian is already known from my former SOGo installation tutorial.

1) Install SOGo 2

echo deb http://packages.inverse.ca/SOGo/nightly/2/debian/ jessie jessie >>/etc/apt/sources.list
apt-key adv --keyserver keys.gnupg.net --recv-key 0x810273C4
apt-get update
apt-get install sogo mysql-server sope4.9-gdl1-mysql memcached

2) Fix some packages

sed -i "s/SHOWWARNING=true/SWOWWARNING=false/" /etc/tmpreaper.conf # suppress tmpreaper warnings
sed -i "s/127.0.0.1/localhost/" /etc/memcached.conf # Fix IPv6 errors
/etc/init.d/mysql restart
/etc/init.d/memcached restart

3) Install MySQL and create user table

Now this step differs slightly from my previous tutorial, as you have to link the sogo-Database with Froxlor:

mysql -u root -p mysql
    CREATE DATABASE `sogo` CHARACTER SET='utf8';
    CREATE USER 'sogo'@'localhost' IDENTIFIED BY 'sogopasswd';
    GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'localhost' WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON `froxlor`.* TO 'sogo'@'localhost' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    quit

4) Create a sogo.conf configuration file

Now in this tutorial for some variation, I assume that you were using Courier IMAPd and migrated from it, even though Dovecot is current standard for Forxlor.
It is very important that you don’t forget to place the

/***DOMAINS***/

marker in the file, as the config file will be used as a template for the automatic configuration by Froxlor:

{
  /* Database configuration (mysql:// or postgresql://) */
  SOGoProfileURL = "mysql://sogo:sogopasswd@localhost:3306/sogo/sogo_user_profile";
  OCSFolderInfoURL = "mysql://sogo:sogopasswd@localhost:3306/sogo/sogo_folder_info";
  OCSSessionsFolderURL = "mysql://sogo:sogopasswd@localhost:3306/sogo/sogo_sessions_folder";

  /* Mail */
  /* The INBOX/-Prefixed lines are just an example to use if you are 
     converting from Courier IMPD, otherwise don't use them */
  SOGoDraftsFolderName = INBOX/Drafts;
  SOGoSentFolderName = INBOX/Sent;
  SOGoTrashFolderName = INBOX/Trash;
  SOGoIMAPServer = localhost;
  SOGoSMTPServer = 127.0.0.1;
  SOGoMailDomain = my.maildomain.com;
  SOGoMailingMechanism = smtp;

/***DOMAINS***/

  SOGoVacationEnabled = YES;
  SOGoSieveScriptsEnabled = YES;
  SOGoMailAuxiliaryUserAccountsEnabled = YES;

  /* General - SOGoTimeZone *MUST* be defined */
  SOGoLanguage = German;
  SOGoTimeZone = Europe/Vienna;

  SOGoSuperUsernames = (user@my.maildomain.com);

  /* Activesync */
  SOGoMaximumPingInterval = 300;
  WOWorkersCount = 15;
  SOGoMaximumSyncWindowSize = 100;
  SOGoMaximumSyncResponseSize = 5172;
}

Fill SOGoMaiDomain with the DNS of your local mailserver and define the superuser-account, which you should create in froxlor as mail-address to a certain domain (pick one of your Froxlor-Mailaccounts).

Now that you have set up a proper SOGo configuration, copy the file as the new template file to use for Froxlor:

cp /etc/sogo/sogo.conf /etc/sogo/sogo.conf.tpl

From now on, only edit sogo.conf.tpl if you want to change sogo config, as sogo.conf will get overwritten after Froxlor change

4b) Only needed when migrating from Courier to Dovecot

If you had Courier IMAPd installed on your site, like I unfortunately had, it is recommednded to migrate to Dovecot in order to be able to use the Exchange Activesync components.
In order to do this, more or less follow the Dovecot-Installation instructions in Froxlor control panel under configuration (for both SMTP and IMAP). Then don’t forget to convert your mailboxes with the courier-dovecot-migrate.pl scriptin the subdirectories of /var/customers/mails . As you are coming from Courier, don’t forget to set the nasty INBOX. prefix that Courier IMAPd users were used to in order to do a smooth transition:

mail_access_groups = vmail
mail_privileged_group = vmail
mail_uid = 2000
mail_gid = 2000
 
# Required only for courier migration
namespace inbox {
  prefix = INBOX.
  separator = .
  inbox = yes
}

5) Integrate sync mechanism between Froxlor and SOGo

Now we need to setup beforementioned sync mechanism. Create the following PHP-file:

<?php
/*********************************
 * Sync Forxlor Domains to SoGo  *
 *********************************/
function sync_to_sogo()
{
    // Config
    $sogousr='sogo';
    $sogopasswd='sogopasswd';
 
    $bRet = false;
    if (!($conn=mysql_connect('localhost', $sogousr, $sogopasswd)))
    {
        echo 'Error connecting to SOGO database: '.mysql_error();
        return false;
    }
    if (!mysql_select_db('sogo', $conn))
    {
        echo 'Cannot select DB sogo: '.mysql_error();
        mysql_close($conn);
        return false;
    }
 
    $restart_cmd='/etc/init.d/sogo restart';
    $tplfil='/etc/sogo/sogo.conf.tpl';
    $tmpfil='/tmp/sogo.conf';
    $cnffil='/etc/sogo/sogo.conf';
 
    if ($fpsog=fopen($tplfil, 'r'))
    {
        if ($fp=fopen($tmpfil, 'w'))
        {
            while ($line=fgets($fpsog))
            {
                if (trim($line)=='/***DOMAINS***/') break;
                fwrite($fp, $line);
            }
            if ($line)
            {
                $q=mysql_query('SELECT froxlor.panel_domains.domain FROM froxlor.panel_domains WHERE isemaildomain=1', $conn);
                fprintf($fp, "domains = {\n");
                while ($r=mysql_fetch_row($q))
                {
                    $domainkey = preg_replace("/[^A-Za-z0-9]/", '_', $r[0]);
                    if (mysql_query("CREATE OR REPLACE VIEW auth_$domainkey AS
                       SELECT froxlor.mail_users.email AS c_uid,
                       froxlor.mail_users.email AS c_name,
                       froxlor.mail_users.password AS c_password,
                       froxlor.mail_users.email AS c_cn,
                       froxlor.mail_users.email AS mail
                       FROM froxlor.panel_domains, froxlor.mail_users
                       WHERE froxlor.mail_users.domainid=froxlor.panel_domains.id
                       AND froxlor.panel_domains.domain = '".$r[0]."'", $conn))
                    {
                        fprintf($fp, "
%s = {
          SOGoMailDomain = %s;
          SOGoUserSources = (
               {
                 type = sql;
                 id = %s;
                 viewURL = \"mysql://%s:%s@localhost:3306/sogo/auth_%s\";
                 canAuthenticate = YES;
                 isAddressBook = NO;
                 userPasswordAlgorithm = plain;
               }
          );
  };
", $r[0], $r[0], $domainkey, $sogousr, $sogopasswd, $domainkey);
 
                    }
                    else echo 'Error creating view for domain '.$r[0];
                }
                fwrite ($fp, "};");
                while ($line=fgets($fpsog)) fwrite($fp, $line);
                $bRet = true;
            }
            else echo "Marker /***DOMAINS***/ not found in $tplfil template.";
            fclose($fp);
        }
        else echo "Cannot create temp. tmpfil file";
        fclose($fpsog);
    }
    else echo "Cannot open $tplfil for reading";
    mysql_close($conn);
 
    if ($bRet)
    {
        if ($bRet=rename($tmpfil, $cnffil))
        {
            shell_exec($restart_cmd);
        }
        else echo "Cannot overwrite $cnffil";
    }
    return $bRet;
}
?>

Now edit

/var/www/froxlor/scripts/jobs/cron_tasks.php

and add the folowing line to the require_once lines:

require_once makeCorrectFile(dirname(__FILE__) . '/cron_tasks.inc.sogo.php');

Within block

        /**
         * TYPE=1 MEANS TO REBUILD APACHE VHOSTS.CONF
         */
        if ($row['type'] == '1') {
...
        }

Add the following line before the closing Bracket } of the if:

sync_to_sogo();

6) Enjoy

Now everytime you create a domain or make other changes, a view shoud be created in the sogo-database that points to all usernames of the e-mail accounts from a certain domain and sogo.conf is being rewritten with multidomain support for all e-mail domains so that it is in sync with SOGo.
I hope my little script helps you a bit with integrating SOGo with Froxlor.

One comment | Add One

Leave a Comment