1 Topic by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Topic: Login to Sogo Failing after restoring iRedMail to a new server

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.7
- Linux/BSD distribution name and version: Ubuntu 16.04
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? Y
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Install a new server for iRedMail and migrated all settings and emails accounts to the new server as per documentatiion found at https://docs.iredmail.org/migrate.to.ne … rver.html.
All services are up and running on the new server except sogo where no user can login. If we create a new user, the new user also cannot login to the Sogo Interface.

Error in /var/log/sogo/sogo.log is:

Apr 05 06:29:59 sogod [5178]: <0x0x563435ba2d80[SOGoCache]> Cache cleanup interval set every 300.000000 seconds
Apr 05 06:29:59 sogod [5178]: <0x0x563435ba2d80[SOGoCache]> Using host(s) '127.0.0.1' as server(s)
Apr 05 06:29:59 sogod [5178]: [ERROR] <0x0x563435bcf180[SQLSource]> could not run SQL 'c_uid = 'test@email.com'': <PostgreSQL72Exception: 0x563435bd3170> NAME:PostgreSQL72FatalError REASON:fatal pgsql error (channel=<0x0x563435898de0[PostgreSQL72Channel]: connection=<0x0x563435961e50[PGConnection]:  connection=0x0x563435854dc0>>): ERROR:  could not establish connection
DETAIL:  FATAL:  password authentication failed for user "vmail"
FATAL:  password authentication failed for user "vmail"


Apr 05 06:29:59 sogod [5178]: <0x0x563435bc7e30[SOGoDAVAuthenticator]> tried wrong password for user 'test@email.com'!
Apr 05 06:29:59 sogod [5178]: 13.89.226.135 "OPTIONS /SOGo/Microsoft-Server-ActiveSync?User=hq.cmt.mu%5Ctest%40hq.cmt.mu&DeviceId=2439037788409952&DeviceType=Outlook HTTP/1.1" 401 12/0 0.023 - - 492K


The password for the user test@email.com  is OK as login to web-mail with the same password is fine.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Login to Sogo Failing after restoring iRedMail to a new server

Do you have correct SQL user password in /etc/sogo/sogo.conf and /etc/postfix/pgsql/*.cf, /etc/dovecot/dovecot-pgsql.conf?

3 Reply by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Re: Login to Sogo Failing after restoring iRedMail to a new server

ZhangHuangbin wrote:

Do you have correct SQL user password in /etc/sogo/sogo.conf and /etc/postfix/pgsql/*.cf, /etc/dovecot/dovecot-pgsql.conf?


We have checked the password and they are OK. Dovecot and postfix is running fine, only sogo is not working.

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Login to Sogo Failing after restoring iRedMail to a new server

pragpot wrote:

ERROR:  could not establish connection
DETAIL:  FATAL:  password authentication failed for user "vmail"
FATAL:  password authentication failed for user "vmail"

This message means you have incorrect sql password for "vmail" user in sogo config file.

5 Reply by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Re: Login to Sogo Failing after restoring iRedMail to a new server

There is no setting for nor password for user vmail in the sogo confguration file /etc/sogo/sogo.conf.
Where can I find this setting?

6 Reply by sayso

  • sayso
  • Member
  • Offline
  • Registered: 2018-03-03
  • Posts: 43

Re: Login to Sogo Failing after restoring iRedMail to a new server

pragpot wrote:

There is no setting for nor password for user vmail in the sogo confguration file /etc/sogo/sogo.conf.
Where can I find this setting?

On my system it is listed as:

            SOGoUserSources = (
                {
                    // Used for user authentication
                    type = ldap;
                    ...
                    bindDN = "cn=vmail,dc=domain,dc=tld";
                    bindPassword = "XXXXXXXXXXXXXXXXXXXXXXXXXXX";
                    ...
                    bindAsCurrentUser = YES;
                },
                {
                    type = ldap;
                    ...
                    bindDN = "cn=vmail,dc=domain,dc=tld";
                    bindPassword = "XXXXXXXXXXXXXXXXXXXXXXXXXXX";
                    ...
                    scope = SUB;
                }
            );
...

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Login to Sogo Failing after restoring iRedMail to a new server

pragpot wrote:

There is no setting for nor password for user vmail in the sogo confguration file /etc/sogo/sogo.conf.

Try this:

grep 'vmail' /etc/sogo/sogo.conf
grep 'pgsql' /etc/sogo/sogo.conf

8 Reply by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Re: Login to Sogo Failing after restoring iRedMail to a new server

Re-checked the sogo  config and no settings for vmail authentication, only for user sogo. The grep results are below and sogo.conf file also attached.

root@imail2:/home/administrator# grep -r vmail /etc/sogo/sogo.conf
            // Use `vmail.mailbox` as per-domain address book.
root@imail2:/home/administrator# grep -r pgsql /etc/sogo/sogo.conf
root@imail2:/home/administrator# grep -r postgresql /etc/sogo/sogo.conf
    SOGoProfileURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_user_profile";
    OCSFolderInfoURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_folder_info";
    OCSSessionsFolderURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_sessions_folder";
    OCSEMailAlarmsFolderURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_alarms_folder";
    OCSCacheFolderURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_cache_folder";
    OCSStoreURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_store";
    OCSAclURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/sogo_acl";
            viewURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/users";
        //    viewURL = "postgresql://sogo:PASSWORD_REMOVED@127.0.0.1:5432/sogo/users";

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Login to Sogo Failing after restoring iRedMail to a new server

The SQL table (it's actually a SQL VIEW) "sogo.users" requires SQL user "vmail" and its password. You can try to fix it with PostgreSQL commands below:

DROP VIEW users;

CREATE VIEW users AS
     SELECT * FROM dblink('host=127.0.0.1
                           port=5432
                           dbname=vmail
                           user=vmail
                           password=<password>',
                          'SELECT username AS c_uid,
                                  username AS c_name,
                                  password AS c_password,
                                  name AS c_cn,
                                  username AS mail,
                                  domain AS domain
                             FROM mailbox
                            WHERE enablesogo=1 AND active=1')
         AS users (c_uid VARCHAR(255),
                   c_name VARCHAR(255),
                   c_password VARCHAR(255),
                   c_cn VARCHAR(255),
                   mail VARCHAR(255),
                   domain VARCHAR(255));

ALTER TABLE users OWNER TO sogo;

WARNING: You need to replace the <password> by the real password of SQL user "vmail". You can find it in /etc/postfix/pgsql/*.cf.

10 Reply by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Re: Login to Sogo Failing after restoring iRedMail to a new server

We have tried to rebuild the SQL View and were still having errors with Sogo authentication.

We have finally resolved the problem by doing the following.
    Stopped sogo and memcached service.
    Drop the sogo database.
    Created a new empty database for sogo.
    Edit the sogo sql backup file and change the password of vmail to match the password on the new server. The sogo sql backup file had the password for the old server which was different.
    Restore the modified sogo sql file to the database.
    Restarted memcache and sogo.

Sogo is now working and no errors are being obtained.

11 Reply by pragpot

  • pragpot
  • Member
  • Offline
  • Registered: 2016-11-18
  • Posts: 25

Re: Login to Sogo Failing after restoring iRedMail to a new server

Issue has been resolved. Can an admin marked this as completed?
Thanks for your support.