1 Topic by travnewmatic (edited by travnewmatic 2016-10-01 05:34:37)

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Topic: [SOLVED] SOGo / MySQL Authentication Error

======== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.5-1
- Linux/BSD distribution name and version: Ubuntu Xenial
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): Remote MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? No
- Related log if you're reporting an issue: /var/log/sogo/sogo.log
====

Salutations iRedMail Team!

First off I'd like to say that you make an absolutely fantastic product.  I'm only starting to use it, but in the wild and woolly world of email, your offering is a fantastic addition.

I'm having an issue logging into SOGo groupware:

Sep 29 12:39:59 sogod [1587]: [ERROR] <0x0x556121964e10[SQLSource]> could not run SQL 'c_uid = 'postmaster@travnewmatic.com'': <MySQL4Exception: 0x556121667cf0> NAME:ExecutionFailed REASON:Access denied for user 'sogo'@'192.168.122.186' (using password: YES)
Sep 29 12:39:59 sogod [1587]: SOGoRootPage Login from '69.41.160.254' for user 'postmaster@travnewmatic.com' might not have worked - password policy: 65535  grace: -1  expire: -1  bound: 0

This deployment was done following this guide:  http://www.iredmail.org/docs/install.ir … erver.html

Whats puzzling is that if I attempt to login from my mail server to the mysql server using 'mysql -usogo -p -h <ip of db server>' it works:

root@mx:~# mysql -usogo -p -h 192.168.122.175
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5904
Server version: 5.7.15-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

But when sogod tries to, it cannot.

What also strange is that when I check connections made to my database server, I see that the sogo is connecting in some way?  I think? (see screenshot)

I'll keep googling around to see if i can troubleshoot it myself, though any help would be greatly appreciated!

-Travis

----

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

2 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

looks like my image didnt get attached, it was a screenshot of MySQL workbench listing 'sogo' as a connected user

Thanks in advance!

-Travis

3 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

https://gist.github.com/travnewmatic/59 … 72a9a61107

Looks like some part of sogod is successfully connecting, just not the part thats doing user authentication?

4 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

root@mx:~# mysql -usogo -p -h 192.168.122.175
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8429
Server version: 5.7.15-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT c_uid FROM `sogo`.`users`;
ERROR 1045 (28000): Access denied for user 'sogo'@'192.168.122.186' (using password: YES)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sogo               |
| vmail              |
+--------------------+
3 rows in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------+
| Grants for sogo@192.168.122.186                               |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sogo'@'192.168.122.186'                |
| GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'192.168.122.186'  |
| GRANT SELECT ON `vmail`.`mailbox` TO 'sogo'@'192.168.122.186' |
+---------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

5 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

Error Code: 1449. The user specified as a definer ('admin_iredmail'@'192.168.122.186') does not exist

6 Reply by travnewmatic (edited by travnewmatic 2016-09-30 04:37:57)

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

https://gist.github.com/travnewmatic/a7 … 032a577a6f

mysql> show create view sogo.users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| users | CREATE ALGORITHM=UNDEFINED DEFINER=`admin_iredmail`@`192.168.122.186` SQL SECURITY DEFINER VIEW `sogo`.`users` AS select `vmail`.`mailbox`.`username` AS `c_uid`,`vmail`.`mailbox`.`username` AS `c_name`,`vmail`.`mailbox`.`password` AS `c_password`,`vmail`.`mailbox`.`name` AS `c_cn`,`vmail`.`mailbox`.`username` AS `mail`,`vmail`.`mailbox`.`domain` AS `domain` from `vmail`.`mailbox` where ((`vmail`.`mailbox`.`enablesogo` = 1) and (`vmail`.`mailbox`.`active` = 1)) | latin1               | latin1_swedish_ci    |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

7 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

is this a bug?

8 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

Just did a new install, same as before just to test, same behavior as before:

DEFINER=`admin_iredmail`@`192.168.122.186`

..remains in results from 'show create view sogo.users;'

I can login successfully to SOGo, but only because I haven't removed the admin_iredmail user (which is definitely a security problem, as this user has the same privileges as the root user)

9 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

while that user currently does have a very weak password (the default suggested in the guide, 'admin_password'), i've pared back its privileges to only SELECT (it is global though)

i guess thats a temporary fix?

10 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

Re: [SOLVED] SOGo / MySQL Authentication Error

travnewmatic wrote:

ExecutionFailed REASON:Access denied for user 'sogo'@'192.168.122.186' (using password: YES)

Obviously, a SQL permission error.

Does SQL command below fix it?

GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'192.168.122.186' IDENTIFIED BY 'PASSWORD';
FLUSH PRIVILEGES;

Replace 'PASSWORD' by the one you use in /etc/sogo/sogo.conf.

Also, you have to define SQL username/password in several parameters in sogo.conf, make sure they all have correct ones.

11 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

the problem is with the definer

https://gist.github.com/travnewmatic/a7 … 032a577a6f

its attempting to use a user that should no longer exist

12 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

please read my other posts in the thread

SOGo mysql user can login, that is not the issue, its just attempting to do something that involves a user (admin_iredmail) that your guide instructs to be deleted, so that operation fails

13 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

Re: [SOLVED] SOGo / MySQL Authentication Error

I'm confused.

According to your error message:

mysql> SELECT c_uid FROM `sogo`.`users`;
ERROR 1045 (28000): Access denied for user 'sogo'@'192.168.122.186' (using password: YES)

It's caused by improper permissions granted to `sogo` user. How does it work if you grant all privileges to 'sogo@ 192.168.122.186' for testing?

Also, below are SQL commands used to create `sogo.users`, run by sql user 'admin_iredmail':

GRANT SELECT ON vmail.mailbox TO sogo@"${MYSQL_GRANT_HOST}";
GRANT SELECT ON vmail.mailbox TO sogo@"${HOSTNAME}";
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;

if you login as 'admin_iredmail' remotely, create a new SQL VIEW (e.g. 'users2') manually, does it have same output in sql command "

14 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

Re: [SOLVED] SOGo / MySQL Authentication Error

Also, could you try to drop existing 'users' view, then re-create it like this:

CREATE DEFINER='sogo'@'192.168.122.186' 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;

15 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

mysql> show create view sogo.users\G
*************************** 1. row ***************************
                View: users
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`admin_iredmail`@`192.168.122.186` SQL SECURITY DEFINER VIEW `sogo`.`users` AS select `vmail`.`mailbox`.`username` AS `c_uid`,`vmail`.`mailbox`.`username` AS `c_name`,`vmail`.`mailbox`.`password` AS `c_password`,`vmail`.`mailbox`.`name` AS `c_cn`,`vmail`.`mailbox`.`username` AS `mail`,`vmail`.`mailbox`.`domain` AS `domain` from `vmail`.`mailbox` where ((`vmail`.`mailbox`.`enablesogo` = 1) and (`vmail`.`mailbox`.`active` = 1))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

16 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

forgive me, i just saw your responses, I will test and get back to you

17 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

EXCELLENT

mysql> show create view sogo.users\G
*************************** 1. row ***************************
                View: users
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`sogo`@`192.168.122.186` SQL SECURITY DEFINER VIEW `users` AS select `vmail`.`mailbox`.`username` AS `c_uid`,`vmail`.`mailbox`.`username` AS `c_name`,`vmail`.`mailbox`.`password` AS `c_password`,`vmail`.`mailbox`.`name` AS `c_cn`,`vmail`.`mailbox`.`username` AS `mail`,`vmail`.`mailbox`.`domain` AS `domain` from `vmail`.`mailbox` where ((`vmail`.`mailbox`.`enablesogo` = 1) and (`vmail`.`mailbox`.`active` = 1))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

..did the trick!

Not sure what needs to be changed in your script or if this issue lies with SOGo but either way, GREAT WORK!  BEER.. i mean COFFEE MONEY INCOMING!

18 Reply by travnewmatic

  • travnewmatic
  • Member
  • Offline
  • Registered: 2016-09-30
  • Posts: 18

Re: [SOLVED] SOGo / MySQL Authentication Error

i thought i put it in the solved response, but the solution was in:

http://www.iredmail.org/forum/post51660.html#p51660

19 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

Re: [SOLVED] SOGo / MySQL Authentication Error

I will fix it in iRedMail later, thanks for the feedback and help testing, and, the coffee. smile