Re: Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
Hi odhiambo,
SOGoUserSources = (
{
type = sql;
canAuthenticate = YES;
displayName = Staff Members;
id = users;
isAddressBook = YES;
userPasswordAlgorithm = MD5;
//userPasswordAlgorithm = none;
viewURL =mysql://exim4u:XXXWWW@127.0.0.1:3306/exim4u/sogo_auth_view;
}
);
XXXWWW is plain text and 'userPasswordAlgorithm = MD5;' in the configuration
file. Your original SQL INSERT user should look something like this:
INSERT INTO sogo_users VALUES (, MD5('password'), );
The 'MD5' on the password is what does the encryption.
Pay attention to the size of the database 'password' field - MD5 always
encrypts to VARCHAR(32).
--
users@sogo.nu
https://inverse.ca/sogo/lists
[SOGo] Re: SOGo MySQL Authentication - SOLVED
Hello, I have changed userPasswordAlgorithm = md5; to userPasswordAlgorithm
= none; and I was able to login!
On 26 June 2013 15:46, Odhiambo Washington odhia...@gmail.com wrote:
Helloz,
I am almost bald trying to get authentication using mysql working. The
difficult part is how to tell what SOGo is exactly doing (debugging) so
that I can fix it.
I have in my sogo.conf:
/* MySQL Authentication */
SOGoUserSources = (
{
type = sql;
canAuthenticate = YES;
displayName = Staff Members;
id = users;
isAddressBook = YES;
userPasswordAlgorithm = md5;
viewURL =mysql://
exim4u:XXX@127.0.0.1:3306/exim4u/sogo_auth_view;
}
);
And I created a view on my DB as follows:
CREATE VIEW sogo_auth_view AS SELECT user_id AS sogo_id, username AS
c_uid, realname AS c_name, clear AS c_password, username AS c_cn, use
rname AS mail, realname AS displayName FROM users WHERE enabled='1';
Which gives me:
mysql select * from exim4u.sogo_auth_view where c_name like '%wash%';
+-+-+-++-+-+-+
| sogo_id | c_uid | c_name | c_password | c_cn
| mail| displayName |
+-+-+-++-+-+-+
| 3 | w...@kictanet.or.ke | Odhiambo WASHINGTON | secret1|
w...@kictanet.or.ke | w...@kictanet.or.ke | Odhiambo WASHINGTON |
+-+-+-++-+-+-+
I am trying to login using username=w...@kictanet.or.ke and
password=whateveritis but I always get failure... wrong username or
password.
On sogo.log, I see no clue whatsoever:
root@gw:/usr/local/etc/sogo # tail -f /var/log/sogo/sogo.log
Jun 26 14:51:24 sogod [94532]: 0x0x23bdedc4[WOWatchDog] child spawned
with pid 94533
Jun 26 15:40:53 sogod [5681]: version 2.0.6a (build
r...@gw.kictanet.or.ke201306252307) -- starting
Jun 26 15:40:53 sogod [5681]: vmem size check enabled: shutting down app
when vmem 384 MB
Jun 26 15:40:53 sogod [5681]: 0x0x23c01ee4[SOGoProductLoader] SOGo
products loaded from '/usr/local/GNUstep/Local/Library/SOGo':
Jun 26 15:40:53 sogod [5681]: 0x0x23c01ee4[SOGoProductLoader]
Appointments.SOGo, Contacts.SOGo, Mailer.SOGo, CommonUI.SOGo,
ContactsUI.SOGo,
MailerUI.SOGo, MailPartViewers.SOGo, MainUI.SOGo, PreferencesUI.SOGo,
SchedulerUI.SOGo, AdministrationUI.SOGo
Jun 26 15:40:53 sogod [5681]: 0x0x23bdedc4[WOWatchDog] listening on
*:2
Jun 26 15:40:53 sogod [5681]: 0x0x23bdedc4[WOWatchDog] watchdog process
pid: 5681
Jun 26 15:40:53 sogod [5681]: 0x0x21464020[WOWatchDogChild] watchdog
request timeout set to 10 minutes
Jun 26 15:40:53 sogod [5681]: 0x0x23bdedc4[WOWatchDog] preparing 1
children
Jun 26 15:40:53 sogod [5681]: 0x0x23bdedc4[WOWatchDog] child spawned
with pid 5682
2013-06-26 15:41:06.186 sogod[5682] Note: Using UTF-8 as URL encoding in
NGExtensions.
Jun 26 15:41:06 sogod [5682]: |SOGo| starting method 'POST' on uri
'/SOGo/connect'
Jun 26 15:41:06 sogod [5682]: 0x0x23c58e54[SOGoCache] Cache cleanup
interval set every 300.00 seconds
Jun 26 15:41:06 sogod [5682]: 0x0x23c58e54[SOGoCache] Using host(s)
'localhost' as server(s)
Jun 26 15:41:10 sogod [5682]: |SOGo| lookup name: SOGo
2013-06-26 15:41:10.189 sogod[5682] Note(SoObject): SoDebugKeyLookup is
enabled!
2013-06-26 15:41:10.189 sogod[5682] Note(SoObject): SoDebugBaseURL is
enabled!
2013-06-26 15:41:10.190 sogod[5682] Note(SoObject): relative base URLs are
enabled.
Jun 26 15:41:10 sogod [5682]: |SOGo| did not find key 'SOGo' in SoClass:
0x0x23c07504[SoObjCClass]: super=0x0x23c07184 objc=SOGo slots=connect
,GET,view,casProxy,index,saml2-signon-post,changePassword,saml2-metadata,loading,toolbar
Jun 26 15:41:10 sogod [5682]: |SOGo| looked up value: (null)
Jun 26 15:41:10 sogod [5682]: |SOGo| lookup in root object: (null)
Jun 26 15:41:10 sogod [5682]: |SOGo| GOT: (null)
Jun 26 15:41:10 sogod [5682]: |SOGo| matched appname: SOGo
Jun 26 15:41:10 sogod [5682]: |SOGo| = rewrote value:
SOGo[0x0x23a6aba4]: name=SOGo
Jun 26 15:41:10 sogod [5682]: |SOGo| lookup name: connect
2013-06-26 15:41:10.194 sogod[5682] ERROR(-[NGBundleManager
bundleWithPath:]): could not create bundle for path:
'/usr/local/GNUstep/System/Libra
ry/Libraries/gnustep-base/Versions/1.24/Resources/SSL.bundle'
2013-06-26 15:41:10.202 sogod[5682] WOCompoundElement: pool embedding is
on.
2013-06-26 15:41:10.203 sogod[5682] WOCompoundElement: id logging is on.
Jun 26 15:41:10 sogod [5682]: |SOGo| looked up value:
0x0x23c968c4[SoPageInvocation]: class=SOGoRootPage action=connect bound
instantiated pro
duct=0x0x23c67dc4[SoProduct]: loaded code-loaded
Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
On 06/26/13 15:32, Odhiambo Washington wrote: Hello, I have changed userPasswordAlgorithm = md5; to userPasswordAlgorithm = none; and I was able to login! This is because you have cleartext passwords in your database, you should crypt your passwords before saving into MySQL. Cheers Giovanni -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
Well when I change that to md5-crypt, with matching password, it doesn't work. Why so? Either of these two don't work! userPasswordAlgorithm = md5; userPasswordAlgorithm = md5-crypt; My VIEW looks like this: mysql select * from exim4u.sogo_auth_view where c_name like '%wash%'; +-+-+-++-+-+- + | sogo_id | c_uid | c_name | c_password | c_cn| mail| displayN ame | +-+-+-++-+-+- + | 3 | w...@kictanet.or.ke | Odhiambo WASHINGTON | $1$OFb7e/vf$/FL/Q4T0LXFwaklRUzN7v. | w...@kictanet.or.ke | w...@kictanet.or.ke | Odhiambo WASHINGTON | +-+-+-++-+-+- + On 26 June 2013 17:44, Giovanni Bechis giova...@bigio.snb.it wrote: On 06/26/13 15:32, Odhiambo Washington wrote: Hello, I have changed userPasswordAlgorithm = md5; to userPasswordAlgorithm = none; and I was able to login! This is because you have cleartext passwords in your database, you should crypt your passwords before saving into MySQL. Cheers Giovanni -- users@sogo.nu https://inverse.ca/sogo/lists -- Best regards, Odhiambo WASHINGTON, Nairobi,KE +254733744121/+254722743223 I can't hear you -- I'm using the scrambler. -- users@sogo.nu https://inverse.ca/sogo/lists
Re: Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
I have just tried that now and it works!! Thanks However, I find this confusing, since I use dovecot and dovecot treats my password as md5-crypt. Allow me to also ask: Since I have isAddressBook = YES; , shouldn't SOGo populate my global addressbook by default? Mine remains empty... On 26 June 2013 20:16, Giovanni Bechis giova...@bigio.snb.it wrote: Have you tried with userPasswordAlgorithm = crypt; ? -- msg. originale -- Oggetto: Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED Da: Odhiambo Washington odhia...@gmail.com Data: 26/06/2013 18:17 Well when I change that to md5-crypt, with matching password, it doesn't work. Why so? Either of these two don't work! userPasswordAlgorithm = md5; userPasswordAlgorithm = md5-crypt; My VIEW looks like this: mysql select * from exim4u.sogo_auth_view where c_name like '%wash%'; +-+-+-++-+-+- + | sogo_id | c_uid | c_name | c_password | c_cn| mail| displayN ame | +-+-+-++-+-+- + | 3 | w...@kictanet.or.ke | Odhiambo WASHINGTON | $1$OFb7e/vf$/FL/Q4T0LXFwaklRUzN7v. | w...@kictanet.or.ke | w...@kictanet.or.ke | Odhiambo WASHINGTON | +-+-+-++-+-+- + On 26 June 2013 17:44, Giovanni Bechis giova...@bigio.snb.it wrote: On 06/26/13 15:32, Odhiambo Washington wrote: Hello, I have changed userPasswordAlgorithm = md5; to userPasswordAlgorithm = none; and I was able to login! This is because you have cleartext passwords in your database, you should crypt your passwords before saving into MySQL. Cheers Giovanni -- users@sogo.nu https://inverse.ca/sogo/lists -- Best regards, Odhiambo WASHINGTON, Nairobi,KE +254733744121/+254722743223 I can't hear you -- I'm using the scrambler. -- users@sogo.nu https://inverse.ca/sogo/lists -- users@sogo.nu https://inverse.ca/sogo/lists -- Best regards, Odhiambo WASHINGTON, Nairobi,KE +254733744121/+254722743223 I can't hear you -- I'm using the scrambler. -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
On 2013-06-26 3:43 PM, Odhiambo Washington wrote: Since I have isAddressBook = YES; , shouldn't SOGo populate my global addressbook by default? Mine remains empty... Search in it. Also, c_name should be equal in most situation to c_uid. Right now you've set it to the user's real name, which is far from ideal. c_cn should be the user's realname. -- Ludovic Marcotte lmarco...@inverse.ca :: +1.514.755.3630 :: http://inverse.ca Inverse inc. :: Leaders behind SOGo (http://sogo.nu) and PacketFence (http://packetfence.org) -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] Re: SOGo MySQL Authentication - SOLVED
Hi Ludovic,
In that case, I now modify my VIEW:
mysql CREATE VIEW sogo_auth_view AS SELECT user_id AS sogo_id, username AS
c_uid, username AS c_name, crypt AS c_password, realname AS c_cn, use
rname AS mail, realname AS displayName FROM users WHERE enabled='1';
Which then gives the output below
mysql select * from exim4u.sogo_auth_view where c_name like '%wash%';
+-+-+-++-+-+-+
| sogo_id | c_uid | c_name |
c_password | c_cn|
mail| displayName |
+-+-+-++-+-+-+
| 3 | w...@kictanet.or.ke | w...@kictanet.or.ke |
$1$XXX | Odhiambo WASHINGTON | w...@kictanet.or.ke |
Odhiambo WASHINGTON |
+-+-+-++-+-+-+
So c_uid == c_name as you've suggested.
My config has the below and still I cannot find anything inside the Staff
Members addressbook folder. Anything unusual??
Also tell me, why is it that SOGo treats such password as crypt and not
md5-crypt??
SOGoUserSources = (
{
type = sql;
canAuthenticate = YES;
displayName = Staff Members;
id = users;
isAddressBook = YES;
userPasswordAlgorithm = crypt;
//userPasswordAlgorithm = none;
viewURL =mysql://exim4u:XXXWWW@127.0.0.1:3306/exim4u/sogo_auth_view
;
}
);
On 26 June 2013 22:47, Ludovic Marcotte lmarco...@inverse.ca wrote:
On 2013-06-26 3:43 PM, Odhiambo Washington wrote:
Since I have isAddressBook = YES; , shouldn't SOGo populate my global
addressbook by default? Mine remains empty...
Search in it.
Also, c_name should be equal in most situation to c_uid. Right now you've
set it to the user's real name, which is far from ideal. c_cn should be the
user's realname.
--
Ludovic Marcotte
lmarco...@inverse.ca :: +1.514.755.3630 :: http://inverse.ca
Inverse inc. :: Leaders behind SOGo (http://sogo.nu) and PacketFence (
http://packetfence.org)
--
users@sogo.nu
https://inverse.ca/sogo/lists
--
Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254733744121/+254722743223
I can't hear you -- I'm using the scrambler.
--
users@sogo.nu
https://inverse.ca/sogo/lists
