Sogo Setup

Config Issues

SOGoUserSources = (
{
type = sql;
id = directory;
viewURL =
"mysql://sogo:***@127.0.0.1:3306/sogo/sogo_view";
canAuthenticate = YES;
isAddressBook = YES;
userPasswordAlgorithm = none;
}
);
WOWorkersCount = 3;
};
}

sample database setup

please note that sogo uses postgresql views to access the database (not clearly defined) and this needs to be setup before trying to auth

Also note that a view in the config file actual points to a table (or view) to access the database

again not very clearly defined.

CREATE DATABASE sogo CHARSET='UTF8';
GRANT ALL ON sogo.* TO sogo@localhost IDENTIFIED BY 'password';

GRANT SELECT ON vmail.mailbox TO sogo@localhost;

CREATE VIEW sogo.users (c_uid, c_name, c_password, c_cn, mail, domain) AS SELECT username, username, password, name, username, domain FROM vmail.mailbox WHERE enablesogo=1 AND active=1;

database tables required

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

// With 3 parameters below, SOGo requires only 9 SQL tables in total
// instead of creating 4 SQL tables for each user.
OCSCacheFolderURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_cache_folder";
OCSStoreURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_store";
OCSAclURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_acl";

Sample full config file

{
    // Official SOGo documents:
    //  - http://www.sogo.nu/english/support/documentation.html
    //  - http://wiki.sogo.nu
    //
    // Mailing list:
    //  - http://www.sogo.nu/english/support/community.html

    // Enable verbose logging. Reference:
    // http://www.sogo.nu/nc/support/faq/article/how-to-enable-more-verbose-logging-in-sogo.html
    //ImapDebugEnabled = YES;
    //LDAPDebugEnabled = YES;
    //MySQL4DebugEnabled = YES;
    //PGDebugEnabled = YES;

    // Daemon address and port
    WOPort = 127.0.0.1:20000;

    // PID file
    //WOPidFile = /var/run/sogo/sogo.log;

    // Log file
    //WOLogFile = /var/log/sogo/sogo.log;

    // IMAP connection pool.
    // Your performance will slightly increase, as you won't open a new
    // connection for every access to your IMAP server.
    // But you will get a lot of simultaneous open connections to your IMAP
    // server, so make sure he can handle them.
    // For debugging it is reasonable to turn pooling off.
    //NGImap4DisableIMAP4Pooling = NO;

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

    // With 3 parameters below, SOGo requires only 9 SQL tables in total
    // instead of creating 4 SQL tables for each user.
    OCSCacheFolderURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_cache_folder";
    OCSStoreURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_store";
    OCSAclURL = "mysql://sogo:password@127.0.0.1:3306/sogo/sogo_acl";

    // Default language in the web interface
    SOGoLanguage = English;

    // Specify which module to show after login: Calendar, Mail, Contacts.
    SOGoLoginModule = Mail;

    // Must login with full email address
    SOGoForceExternalLoginWithEmail = YES;

    // Allow user to change full name and email address.
    SOGoMailCustomFromEnabled = YES;

    // Enable email-based alarms on events and tasks.
    SOGoEnableEMailAlarms = YES;

    // IMAP server
    //SOGoIMAPServer = "imaps://127.0.0.1:143/?tls=YES";
    // Local connection is considered as secure by Dovecot.
    SOGoIMAPServer = "imap://127.0.0.1:143/";

    // SMTP server
    SOGoMailingMechanism = smtp;
    SOGoSMTPServer = 127.0.0.1;
    //SOGoSMTPAuthenticationType = PLAIN;

    // Enable managesieve service
    //
    // WARNING: Sieve scripts generated by SOGo is not compatible with Roundcube
    //          webmail, don't use sieve service in both webmails, otherwise
    //          it will be messy.
    //
    //SOGoSieveServer = sieve://127.0.0.1:4190;
    //SOGoSieveScriptsEnabled = YES;
    //SOGoVacationEnabled = YES;
    //SOGoForwardEnabled = YES;

    // Memcached
    SOGoMemcachedHost = 127.0.0.1;

    SOGoTimeZone = "America/New_York";

    SOGoFirstDayOfWeek = 1;

    SOGoRefreshViewCheck = every_5_minutes;
    SOGoMailReplyPlacement = below;

    SOGoAppointmentSendEMailNotifications = YES;
    SOGoFoldersSendEMailNotifications = YES;
    SOGoACLsSendEMailNotifications = YES;

    // PostgreSQL cannot update view
    SOGoPasswordChangeEnabled = YES;

    // Authentication using SQL
    SOGoUserSources = (
        {
            type = sql;
            id = vmail_mailbox;
            viewURL = "mysql://sogo:password@127.0.0.1:3306/sogo/users";
            canAuthenticate = YES;

            // Default algorithm used when changing passwords.
            userPasswordAlgorithm = ssha;
            prependPasswordScheme = YES;

            // Use vmail.mailbox as global address book.
            // WARNING: This will search all user accounts, not just accounts
            // under same domain as login user.
            //isAddressBook = YES;
            //displayName = "Global Address Book";
        }
    );
}

Apache Server Setup

Apache web server

    SOGo installs Apache config file /etc/httpd/conf.d/SOGo.conf by default, please open it, comment out 2 ProxyPass directives as shown below:

#ProxyPass /Microsoft-Server-ActiveSync ...
#ProxyPass /SOGo http://127.0.0.1:20000/SOGo retry=0

    Add 2 ProxyPass directives in /etc/httpd/conf.d/ssl.conf, so that SOGo is only accessible via https.

ProxyPass /Microsoft-Server-ActiveSync \
    http://127.0.0.1:20000/SOGo/Microsoft-Server-ActiveSync \
    retry=60 connectiontimeout=5 timeout=360

ProxyPass /SOGo http://127.0.0.1:20000/SOGo retry=0

    Open /etc/httpd/conf.d/SOGo.conf again, find 3 RequestHeader directives like below:

    RequestHeader set "x-webobjects-server-port" ...
    RequestHeader set "x-webobjects-server-name" ...
    RequestHeader set "x-webobjects-server-url" ...

Replace them by below settings:

    RequestHeader set "x-webobjects-server-port" "443"
    RequestHeader set "x-webobjects-server-name" "%{HTTP_HOST}e" env=HTTP_HOST
    RequestHeader set "x-webobjects-server-url" "https://%{HTTP_HOST}e" env=HTTP_HOST

    Append line below in /etc/httpd/conf.d/SOGo.conf, no matter upper or lower case letters of SOGo in the url, Apache will always to https:

RedirectMatch ^/[Ss][Oo][Gg][Oo](.*) /SOGo$1

    Restart Apache service.

Other

Add Dovecot Master User, used for vacation message expiration

SOGo need a Dovecot Master User to cleanup vacation expiration, please follow our tutorial to add a Dovecot Master User for this purpose: Dovecot Master User.

After added a Dovecot Master User for SOGo, we must store its username and plain password in a separate file used by SOGo, we use /etc/sogo/sieve.cred here for example.

Create file /etc/sogo/sieve.cred, write Dovecot Master User in this file in format: username:password. For example:

my_master_user@non-exist.com:my_master_password

Set strict file owner and permission:

# chown sogo:sogo /etc/sogo/sieve.cred
# chmod 0400 /etc/sogo/sieve.cred

Add required cron jobs

Please add below cron jobs for SOGo daemon user sogo. You can add them with command: crontab -l -u sogo

# 1) SOGo email reminder, should be run every minute.
# 2) SOGo session cleanup, should be run every minute.
#    Ajust the [X]Minutes parameter to suit your needs
#    Example: Sessions without activity since 30 minutes will be dropped:
*   *   *   *   *   /usr/sbin/sogo-ealarms-notify; /usr/sbin/sogo-tool expire-sessions 30

# 3) SOGo vacation messages expiration
#    The credentials file should contain the sieve admin credentials (username:passwd)
0   0   *   *   *   /usr/sbin/sogo-tool update-autoreply -p /etc/sogo/sieve.cred

Access SOGo from web browser

Open your favourite web browser, access URL: https://[your_server]/SOGo (the word SOGo is case-sensitive), you can login with your email account credential.

> Hi Mirek,
>
> here what you will need to do is to create a VIEW on your PostgreSQL 
> database that Sogo will access. In my case (MySQL) the create view 
> command is:
>
>     create or replace view sogo as select domain, email as mail, email 
> as c_uid, email as c_mail, email as c_name, password as c_password, 
> name as c_cn from accounts;
>
> Then, on my sogo.conf I have:
>   SOGoUserSources =
>     (
>       {
>         type = sql;
>         id = publicmav;
>         viewURL = "mysql://user:password@hostname/database/sogo";
>         canAuthenticate = YES;
>         isAddressBook = YES;
>         userPasswordAlgorithm = crypt;
>         displayName = "Global";
>         DomainFieldName = "domain";
>       }
>     );
>
> I recommend that you read the installation guide, there are a lot of 
> important parameters for you sogo.conf file that you need to 
> understand: 
> http://www.sogo.nu/files/docs/SOGo%20Installation%20Guide.pdf .
>
> Best,
> Daniel Colchete
>
> On Wed, Jul 1, 2015 at 1:48 PM, Mirek <miroslaw@slawinski.net.pl 
> <mailto:miroslaw@slawinski.net.pl>> wrote:
>
>     Hi.
>
>     I have already installed Postfix and Dovecot supported with PostgreSQL
>     database. In my DB i keep users, domain and aliases. Now i want to
>     add SOGo
>     but i have a problem with users because i don't want create new
>     database and
>     tables for sogo but use my previouse DB with my users. Can I
>     connect SOGo to
>     another database ? and can I create own SELECT scripts ?
>
>     Best regards
>     Mirek
>     --
>     users@sogo.nu <mailto:users@sogo.nu>
>     https://inverse.ca/sogo/lists
>
>