SOGo: Difference between revisions

From WickyWiki
← Older edit
 
(2 intermediate revisions by the same user not shown)
Line 275: Line 275:
remove -- remove user data and settings from the db
remove -- remove user data and settings from the db
rename-user -- update records pertaining to a user after a change of user id
rename-user -- update records pertaining to a user after a change of user id
</source>
</blockquote>
=== expire-sessions ===
Modify sessions to expire after 120 minutes.
Note: doesn't seem to work.
<source lang=bash>
sudo crontab -u sogo -e
</source>
<blockquote>
<source lang=bash>
* * * * * /usr/local/sbin/sogo-tool expire-sessions 120 >/dev/null 2>&1
</source>
</source>
</blockquote>
</blockquote>

Latest revision as of 15:26, 22 May 2024


See also:

Sources:

SOGo is a groupware solution for e-mail, calendar, tasks and contacts via a web interface

Installation

Note: the Inverse repository (http://www.inverse.ca) is no longer (2016) a free service, a support contract is needed to get automatic updates.

Nightly builds might be the way to get new versions:

Change log:

Add key and source: 

sudo apt-key adv --keyserver keys.gnupg.net --recv-key 0x810273C4
sudo apt-get update
sudo gedit /etc/apt/sources.list
#SOGo 3.x.x nightly repository
deb http://packages.inverse.ca/SOGo/nightly/3/ubuntu/ trusty trusty

Install sogo: 

sudo apt-get update 
sudo apt-get install sogo

Configuration

Basic configuration

The system configuration is stored in the file /etc/sogo/sogo.conf. You can edit this file with a text-editor.

  • The format of the configuration file is very sensitive: one type error and SOGo won't start with a 'dictionary error'.
  • In earlier versions the configuration was stored in "/home/sogo/GNUstep/Defaults/.GNUstepDefaults", this file would still have precedence so make sure it is gone.
  • Authentication 'type = sql' is (I think) the easiest to configure and shown here.

Backup the original file and keep it as an example: 

sudo cp /etc/sogo/sogo.conf /etc/sogo/sogo.conf.backup

Start editing: 

sudo gedit /etc/sogo/sogo.conf

Basic configuration (MySQL database and authentication 'type = sql'): 

  SOGoLanguage = English;
  SOGoTimeZone = Europe/Amsterdam;
  SOGoMailDomain = wilbertvolkers.linkpc.net;

  SOGoAppointmentSendEMailNotifications = NO;
  SOGoACLsSendEMailNotifications = NO;
  SOGoFoldersSendEMailNotifications = NO;

  SOGoProfileURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_user_profile";
  OCSFolderInfoURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_folder_info";
  OCSSessionsFolderURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_sessions_folder";
  OCSEMailAlarmsFolderURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_alarms_folder";

  SOGoUserSources = 
    (
      {
	type = sql; 
	id = directory; 
	displayName = "SOGo directory"; 
	viewURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_users";
	canAuthenticate = YES; 
	isAddressBook = YES; 
	userPasswordAlgorithm = md5; 
      }
    );

NB: used @127.0.0.1 in stead of @localhost for the sogo database user so we can disable DNS-lookup in the database server

Disable e-mail component

sudo nano /etc/sogo/sogo.conf
   //start with Calendar:
   SOGoLoginModule = Calendar;

   SOGoUserSources =
    (
      {
        type = sql;
        id = directory;
        viewURL = "mysql://sogo:*sogo*db*password*@127.0.0.1:3306/sogo/sogo_users";
        canAuthenticate = YES;
        isAddressBook = YES;
        userPasswordAlgorithm = md5;

        //Disable mail component:
        ModulesConstraints = {
          Mail = {
           objectClass = nonExistingClass;
          }
        }
      }
    );

MySQL database

Create sogo database and user

Log in as MySQL root user and create a user sogo and a database sogo, enter the password, identical to **sogodbpasswd** as mentioned above: 

sudo mysql -u root mysql -p
CREATE DATABASE `sogo` CHARACTER SET='utf8';
CREATE USER 'sogo'@'127.0.0.1' IDENTIFIED BY '**sogodbpasswd**';
GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
\q

Create users

When you use authentication 'type = sql' then you need to create the users in de database. 

sudo mysql -u sogo sogo -p
CREATE TABLE sogo_users (
	c_uid VARCHAR(10) PRIMARY KEY, 
	c_name VARCHAR(10), 
	c_password VARCHAR(32), 
	c_cn VARCHAR(128), 
	mail VARCHAR(128));
INSERT INTO sogo_users VALUES ('wilbertid', 'wilbertname', MD5('**wilbert-sogouser-pwd**'), 'Wilbert Volkers', 'wilbertemail@yahoo.com');
INSERT INTO sogo_users VALUES ('judyid', 'judyname', MD5('**judy-sogouser-pwd**'), 'Judy Xu', 'judyemail@yahoo.com');

---- update user password:
-- UPDATE sogo_users set c_password = MD5('pwd') where c_name='wilbert';
\q

Start or restart SOGo

sudo service sogo stop
sudo service sogo start

You can test the configuration by going to http://127.0.0.1:20000/SOGo and logging in. For the correct operation SOGo must be accessed through Apache webserver.

Apache webserver

Configure

Apache2 webserver HTTPS configuration, disable the default SOGo.conf. See also Apache2 configuration for SOGo and MediaWiki. 

sudo mv /etc/apache2/conf.d/SOGo.conf /etc/apache2/conf.d/SOGo.conf.backup

To enable SSL you need to Create an Apache2 self signed certificate. In this example the files server.crt and server.key are used. To configure Apache create the following file:

To configure Apache2 proxy for both http NAT (wilbertvolkers.linkpc.net:80) and https WAN, repeat the virtual host with the below changes. Additionally you can configure your host file: Configure local DNS file to keep LAN requests within the LAN and keep everything working when the router is configured to not allow external access.

Configure IMAP and SMTP server

The system configuration is stored in the file /etc/sogo/sogo.conf. You can edit this file with a text-editor, uncomment the options that you will use.

SOGo Installation Guide.pdf (1.3.8a) page 27

SMTP (no encryption): 

  SOGoMailingMechanism smtp
  SOGoSMTPServer htc.smtp.mail.yahoo.com

We can use the 'sendmail' -method as an alternative: 

  SOGoMailingMechanism sendmail
  WOSendMail /usr/sbin/sendmail

IMAP setup: 

  SOGoDraftsFolderName Drafts
  SOGoSentFolderName Sent
  SOGoTrashFolderName Trash
  SOGoIMAPServer imaps://htc.imap.mail.yahoo.com:993
  SOGoForceIMAPLoginWithEmail YES

Note:

  • The SOGo e-mail and account password match that of the login and password of the SMTP and IMAP server.
  • The IMAP and SMTP server must be the same for all users
  • SMTP TLS/SSL (port 465) is not yet supported (20111102)
  • sendmail is insecure and I was not yet able to set it up

E-Mail reminders

Enable the SOGoEnableEMailAlarms and set the OCSEMailAlarmsFolderURL

Create a cronjob (every minute) that will run under the sogo user https://help.ubuntu.com/community/CronHowto

An entry in the crontab should be defined like : 

sudo crontab -e
 * * * * * /usr/sbin/sogo-ealarms-notify

SOGo-tool

Use the sogo-tool to perform administrative tasks 

sudo su - sogo
/usr/sbin/sogo-tool check-doubles user personal
2011-10-21 23:23:42.731 sogo-tool[13796] sogo-tool [-v|--verbose] [-h|--help] command [argument1] ...
  -v, --verbose	enable verbose mode
  -h, --help	display this help information

  argument1, ...	arguments passed to the specified command

  Available commands:
	restore	-- restore user folders
	backup	-- backup user folders
	user-preferences	-- set user defaults / settings in the database
	check-doubles	-- check user addressbooks with duplicate contacts
	remove-doubles	-- remove duplicate contacts from the specified user addressbook
	remove	-- remove user data and settings from the db
	rename-user	-- update records pertaining to a user after a change of user id

Android client

Notes:

Fixing problems with synchronization

When there is an error with the first synchronization (a SLOW-SYNC) you can end up with double contacts and double calendar items. Note that sometimes a SLOW-SYNC is done automatically after an error occurred. I use PIM backup on my mobile device and make backups of the MySQL database.

The following should remove double items but often has problems in it: 

sudo su - sogo
/usr/sbin/sogo-tool remove-doubles username personal

Log files

If you have a problem check the log files for errors and use the internet to see if your problem has been solved, the log file can be found here:

  • /var/log/sogo/sogo.log

Examples:

Database

I had some problems with getting a clean start with SOGo. So instead I removed the Calendar and Contact record from the table "sogo_folder_info", and I removed the user tables "sogo<username>...". These were newly created after logging into SOGo. The items do have to be on one of the other clients to save your data.

Database SQL, remove double items

Here are some statements to delete double address-book or calendar -items directly from the sogo database. Modify the tablenames ( wilbert<aclnumber1> and wilbert<aclnumber2>) to match your database, be careful and make a backup before you mess things up.

Double items can be caused by:

  • sync-errors and retrying
  • rewriting of the contact display field (c_cn) by synchronizers
  • ...
-- DID YOU MAKE A BACKUP?!

-- calendar

-- mark double calendar items in base table and set sync values
/*
update sogo.sogowilbert00664f71ae3 set 
    c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
    , c_deleted=1 
  where c_deleted is null and c_name in (
*/
    select distinct q2.c_name
      , q2.*
      from sogo.sogowilbert00664f71ae3_quick q1
           , sogo.sogowilbert00664f71ae3_quick q2
      where q1.c_name<q2.c_name
        and
        ( ifNULL(q1.c_startdate,'#'), ifNULL(q1.c_title,'#')
        , ifNULL(q1.c_enddate,'#'), ifNULL(q1.c_cycleenddate,'#')
        , ifNULL(q1.c_participants,'#'), ifNULL(q1.c_isallday,'#'), ifNULL(q1.c_iscycle,'#'), ifNULL(q1.c_cycleinfo,'#')
        , ifNULL(q1.c_classification,'#'), ifNULL(q1.c_isopaque,'#'), ifNULL(q1.c_status,'#'), ifNULL(q1.c_priority,'#') 
        , ifNULL(q1.c_location ,'#'), ifNULL(q1.c_orgmail,'#'), ifNULL(q1.c_partmails,'#'), ifNULL(q1.c_partstates,'#') 
        , ifNULL(q1.c_category ,'#'), ifNULL(q1.c_sequence,'#'), ifNULL(q1.c_component,'#'), ifNULL(q1.c_nextalarm,'#') )
        =
        ( ifNULL(q2.c_startdate ,'#'), ifNULL(q2.c_title,'#')
        , ifNULL(q2.c_enddate,'#'), ifNULL(q2.c_cycleenddate,'#')
        , ifNULL(q2.c_participants,'#'), ifNULL(q2.c_isallday,'#'), ifNULL(q2.c_iscycle,'#'), ifNULL(q2.c_cycleinfo,'#')
        , ifNULL(q2.c_classification,'#'), ifNULL(q2.c_isopaque,'#'), ifNULL(q2.c_status,'#'), ifNULL(q2.c_priority,'#') 
        , ifNULL(q2.c_location,'#'), ifNULL(q2.c_orgmail,'#'), ifNULL(q2.c_partmails,'#'), ifNULL(q2.c_partstates,'#')
        , ifNULL(q2.c_category,'#'), ifNULL(q2.c_sequence,'#'), ifNULL(q2.c_component,'#'), ifNULL(q2.c_nextalarm,'#') )
   )
  ;
  
-- delete marked calendar items from quick table
delete from sogo.sogowilbert00664f71ae3_quick where c_name in (
  select c_name from sogo.sogowilbert00664f71ae3 where c_deleted=1 )
  ;
-- addressbook, 

-- double address-book items in base table
-- Note: many of the values are in c_content column (eg. notes), that column is not included in this comparison

/*
update sogo.sogowilbert0073981fbf8 set 
    c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
    , c_deleted=1 
  where c_deleted is null and c_name in (
*/
    select distinct q2.c_name
      , q2.*
      from sogo.sogowilbert0073981fbf8_quick q1
           , sogo.sogowilbert0073981fbf8_quick q2
      where q1.c_name<q2.c_name
        and
       ( ifNULL(q1.c_givenname,'#'), ifNULL(q1.c_sn,'#')
         -- , ifNULL(q1.c_cn,'#')
         , ifNULL(q1.c_screenname,'#'), ifNULL(q1.c_l,'#'), ifNULL(q1.c_mail,'#'), ifNULL(q1.c_o,'#')
         , ifNULL(q1.c_ou,'#'), ifNULL(q1.c_telephoneNumber,'#'), ifNULL(q1.c_categories,'#')
         , ifNULL(q1.c_component,'#') )
       =
       ( ifNULL(q2.c_givenname,'#'), ifNULL(q2.c_sn,'#')
         -- , ifNULL(q2.c_cn,'#')
         , ifNULL(q2.c_screenname,'#'), ifNULL(q2.c_l,'#'), ifNULL(q2.c_mail,'#'), ifNULL(q2.c_o,'#')
         , ifNULL(q2.c_ou,'#'), ifNULL(q2.c_telephoneNumber,'#'), ifNULL(q2.c_categories,'#')
         , ifNULL(q2.c_component,'#') )
      order by q2.c_cn
  ;

-- delete double calendar items from quick table
delete from sogo.sogowilbert0073981fbf8_quick where c_name in (
  select c_name from sogo.sogowilbert0073981fbf8 where c_deleted=1 )
  ;

Database SQL, encoding problems

Using the text 'birthday dmy=28/..' in the contacts note field caused problems because the '=' is used as an escape character in the communication. The following lines were used as a workaround. 

-- DID YOU MAKE A BACKUP?!

-- fix '=' problems
update sogo.sogowilbert0073981fbf8
  set c_content=replace(c_content, 'dmy=3D','dmy ')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  where c_content like '%dmy=3D%'
    and c_deleted is null
  ;

update sogo.sogowilbert0073981fbf8
  set c_content=replace(c_content, 'dmy=3D','dmy ')
  where c_content like '%dmy=3D%'
  ;

Database SQL, timezone

Enabling the 'all day' event setting will remove the time component and the timezone, however, it seems that in some cases the ';' character will remain, causing all kinds of problems. In other words 'DTSTART;TZID=Europe/Amsterdam:20120604T123000' becomes 'DTSTART;:20120604' in stead of 'DTSTART:20120604'.

ds-server.log: 

... [funambol.sogo] [ERROR] ... [sogo-cal] Exception occured in vCalendarV2toV1(): java.lang.StringIndexOutOfBoundsException: String index out of range: 0
java.lang.StringIndexOutOfBoundsException: String index out of range: 0 at java.lang.String.charAt(Unknown Source) ...

SQL fix: 

-- DID YOU MAKE A BACKUP?!

-- fix 'DTSTART;:' and 'DTEND;:' problems (empty timezone)
SELECT * 
  FROM sogo.sogowilbert0073981fbf8
  WHERE c_content LIKE '%DTSTART;:%' or c_content LIKE '%DTEND;:%'
    AND c_deleted IS NULL
  ;
UPDATE sogo.sogowilbert0073981fbf8
  SET c_content=REPLACE(c_content, 'DTSTART;:','DTSTART:')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  WHERE c_content LIKE '%DTSTART;:%'
    AND c_deleted IS NULL
  ;
UPDATE sogo.sogowilbert0073981fbf8
  SET c_content=REPLACE(c_content, 'DTEND;:','DTEND:')
    , c_lastmodified=UNIX_TIMESTAMP()
    , c_version=c_version+1
  WHERE c_content LIKE '%DTEND;:%'
    AND c_deleted IS NULL
  ;

Database SQL, text wrapping in c_content

Sometimes data is illegally broken over multiple lines causing this error, this happens for example with a contacts Additional Email. Better leave this field empty.

ds-server.log: 

..  Lexical error at line ... column ... Encountered: "\r" (13), after ...

You could also try to make an SQL statement to find and remove these newlines from the c_content column.

Note the use of carriage return \r and linefeed \n. 

select replace(c_content, 'SOME_TEXT:data\r\n', '')
  from sogowilbert01234
  where c_content like '%SOME_TEXT:data\r\n%'
    and c_name='<id>'
 ;

update sogowilbert01234
  set c_content=replace(c_content, 'SOME_TEXT:data\r\n', '')
  where c_content like '%SOME_TEXT:data\r\n%'
    and c_name='<id>'
 ;

Database SQL, integer date representation

We are talking about the UNIXTIME which is the number of seconds since 1970. The MySQL function FROM_UNIXTIME( integer_date ) converts to a date but does not allow for negative values (pre 1970) the following works well in all cases. 

select c_name, c_title, DATE_ADD(FROM_UNIXTIME(0), INTERVAL c_startdate SECOND) as startdate, c_isallday, c_cycleinfo
  from sogo.sogowilbert00664f71ae3_quick
  where lower(c_title) like '%valentijn%'
  ;

Outlook VB script

NB: the Outlook script is not available here

I use a VB script in outlook to remove double items. There must be a better way though:

  • Sync devices with funambol / sogo
  • Sync Outlook with funambol / sogo
  • Outlook VB Script to remove double items
  • Clean funambol / sogo (does not remove the user, only the data)
sudo su - sogo
/usr/sbin/sogo-tool remove <user>
exit
  • Sync Outlook with funambol / sogo


To force a full sync on all the items, use the funamboladmin tool: 

sudo /opt/Funambol/admin/bin/funamboladmin

Go to 'Principles', click 'Search', double click the 'Principle'. Select each item-type and click 'Reset' to remove. This way you will force a 'SLOW SYNC'.

Beta version

Occurrence of double items where also reported here:

There it is suggested to use a newer (beta) version of the connector from the same server 'funambol-sogo-1.0.9.s4j'. Didn't solve the problem at the time though.

Remove

Remove SOGo

sudo service sogo stop

Edit pre removal script, change 'exit $?' to 'exit 0' 

sudo gedit /var/lib/dpkg/info/sogo.prerm

Remove: 

sudo apt-get --purge autoremove sogo

Remove key, first find it: 

apt-key list

The key will look like: 

pub   2048D/810273C4 2011-05-10
uid                  Inverse Support (package signing) <support@inverse.ca>
sub   2048g/BF6A9A6D 2011-05-10

Delete: 

sudo apt-key del 810273C4

Remove PostgreSQL

sudo /etc/init.d/postgresql stop
sudo apt-get --purge autoremove sope4.9-gdl1-postgresql
sudo apt-get --purge autoremove postgresql pgadmin3

Remove funambol-sogo connector

sudo /opt/Funambol/bin/funambol stop

Remove ",funambol-sogo-1.0.8" (without .s4j) at the end of the "modules-to-install" line: 

sudo gedit /opt/Funambol/ds-server/install.properties

Note: this next action will reset the funambol database, all settings made with funamboladmin, users and userdata. Answer y to all questions until BUILD SUCCESFUL appears. 

cd /opt/Funambol/
sudo ./bin/install-modules
sudo /opt/Funambol/bin/funambol start

Other

In Apache2 error.log (/var/log/apahe2/error.log) this error was logged:

  • PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php5/20090626/msql.so"

To resolve this I created a link 'msql.so' to 'mysql.so' in the same directory.

PostgreSQL database (not updated)

PostgreSQL database and gui 

sudo apt-get install postgresql pgadmin3

sogo - postgresql-jdbc connector 

sudo apt-get install sope4.9-gdl1-postgresql

Create database, use “sogo” as password 

sudo su - postgres
createuser --no-superuser --no-createdb --no-createrole --encrypted --pwprompt sogo
createdb -O sogo sogo
sudo gedit /var/lib/postgresql/data/pg_hba.conf
#20111009 wjv add:
host    sogo        sogo        127.0.0.1/32          md5

sudo service postgresql restart

Create tables: 

sudo su - sogo
psql
CREATE TABLE sogo_users (
	c_uid VARCHAR(10) PRIMARY KEY, 
	c_name VARCHAR(10), 
	c_password VARCHAR(32), 
	c_cn VARCHAR(128), 
	mail VARCHAR(128));
INSERT INTO sogo_users VALUES ('wilbert', 'wilbert', MD5('su***'), 'Wilbert Volkers', 'wilbertvolkers@hotmail.com');
INSERT INTO sogo_users VALUES ('judy', 'judy', MD5('xu'), 'Judy Xu', 'judyxu1221@yahoo.com');
--UPDATE sogo_users set c_password = MD5('pwd') where c_uid='id';
\q
SOGoUserSources = ({
	type = sql; 
	id = directory; 
	displayName = "SOGo directory"; 
	viewURL = "postgresql://sogo:sogo@127.0.0.1:5432/sogo/sogo_users";
	canAuthenticate = YES; 
	isAddressBook = YES; 
	userPasswordAlgorithm = md5; 
	});
SOGoProfileURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_user_profile';
OCSFolderInfoURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_folder_info';
OCSSessionsFolderURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_sessions_folder';
OCSEMailAlarmsFolderURL='postgresql://sogo:sogo@localhost:5432/sogo/sogo_alarms_folder';

#sql authentication superusers (http://www.mail-archive.com/users@sogo.nu/msg01988.html)
SOGoSuperUsernames='("admin","wilbert")';

SOGo - Funambol connector (not updated)

Funambol allows you to synchronize data between different clients. For example SOGo, Outlook, Windows mobile, Evolution.

For support:

For the latest version and documentation check here:

Install

sudo -i
cd /opt/Funambol/ds-server/modules
wget http://www.sogo.nu/uploads/Funambol/funambol-sogo-1.0.8.s4j
exit

Edit: 

sudo gedit /opt/Funambol/ds-server/install.properties
...
#
# Modules definitions
#
modules-to-install=content-provider-10.0.0,email-connector-10.0.0,foundation-10.0.0,phones-support-10.0.0,webdemo-10.0.0,funambol-sogo-1.0.8

Note: this next action will reset the funambol database, press y until you get BUILD SUCCESFUL: 

cd /opt/Funambol/
sudo ./bin/install-modules

Install JSON

sudo -i
cd /opt/Funambol/tools/tomcat/lib/
wget http://json-simple.googlecode.com/files/json_simple-1.1.jar
exit

Configure

Start funamboladmin: 

sudo /opt/Funambol/admin/bin/funamboladmin

The officer will auto-create Funambol user accounts for every SOGo user. Go to 'Server settings' (double click) -> 'Engine' -> 'Officer' change from: 

com/funambol/server/security/UserProvisioningOfficer.xml

into: 

ca/inverse/sogo/security/SOGoOfficer.xml

Go to 'Modules' -> 'sogo' -> 'FunambolSOGoConnector' -> 'SOGo SyncSource' (double click) and add three SyncSources. Fill-in the values and click 'Add'.

card cal todo
Source URI: sogo-card sogo-cal sogo-todo
Name: sogo-card sogo-cal sogo-todo
Supported type: text/x-vcard text/x-vcal text/x-vtodo
Database URL: jdbc:mysql://localhost/sogo
Db username: sogo
Db password: sogo 
sudo gedit /opt/Funambol/config/ca/inverse/sogo/security/SOGoOfficer.xml
...
<void property="host">
    <string>localhost</string>
</void>
<void property="port">
    <string>20000</string>
</void>
...

Restart

sudo /etc/init.d/sogo stop
sudo /opt/Funambol/bin/funambol stop
sudo /opt/Funambol/bin/funambol start
sudo /etc/init.d/sogo start
Retrieved from "https://wjv.duckdns.org/mediawiki/index.php?title=SOGo&oldid=3676"