[horde] Getting horde to authenticate against dovecot DB

[Michael J Rubinsky]

Quoting Coy Hile <coy.hile at coyhile.com>:

> On 2019-08-17 10:21, Michael J Rubinsky wrote:
>> Quoting Coy Hile <coy.hile at coyhile.com>:
>>
>>> Hi all,
>>>
>>> I'm currently trying to get Horde to authenticate against my  
>>> Dovecot  virtual user database, as I want the ActiveSync  
>>> functionality that  is missing from other solutions. However, I'm  
>>> having a hard time  getting the passwords in a form that both  
>>> Horde and Dovecot  understand.
>>>
>>> In the dovecot DB, I have:
>>>
>>>   username    |  domain  |                                          
>>>                  password
>>> ---------------+----------+--------------------------------------------------------------------------------------------------------------------------
>>> user at test.com | test.com |   
>>> {SHA512-CRYPT}$6$8CK0YWwoEjEvhEwf$58UUMSvPL8fE1p50bfTjHqivp3iwmfk/2sbv9igUT0FhwRc548UaKDWBYCvgrOyDfT81u9dLEJ7ulHLFbvbSq/
>>>
>>>
>>> conf.php contains (in relevant part):
>>>
>>> $conf['auth']['params']['query_auth'] = 'SELECT * FROM users WHERE  
>>>  username=\L AND password=\P';
>>> $conf['auth']['params']['encryption'] = 'crypt-sha512';
>>> $conf['auth']['params']['show_encryption'] = false;
>>> $conf['auth']['driver'] = 'customsql';
>>>
>>> In syslog, one sees:
>>>
>>> Aug 16 21:39:23 8616546e-fcab-e37b-a25a-c746648411f7 HORDE:  
>>> [horde]  SQL  (0.0014s)  #012#011SELECT * FROM users WHERE   
>>> username='user at test.com' AND#012#011    
>>> password='$6$jCCF2GRqLkldtA6u$NMZosKqif68Ro0HjRTGy7Y/tqUuGEMYq.oZ5OqcX#012#011  NAC3PW7jMhsL.ZzdE67vjw6Bx6gIgoQh.d.3syBdYUC4j0' [pid 2321 on line 241 of   
>>> "/usr/share/php/Horde/Db/Adapter/Pdo/Base.php"]
>>> Aug 16 21:39:23 8616546e-fcab-e37b-a25a-c746648411f7 HORDE:  
>>> [horde]  FAILED LOGIN for user at test.com to horde (10.100.9.20)  
>>> [pid 2321 on  line 198 of "/usr/share/horde/login.php"]
>>>
>>> So, that brings up some questions:
>>> (1) Is the data in the log actually correct? Why are there what   
>>> appear to be "#012#011  " in the middle of the query?
>>> (2) Am I completely taking the wrong approach here? Is there a   
>>> better way to get these two things to play nice, or do I have to   
>>> finesse something in the SQL queries to make this work?
>>>
>>> I'll probably only use this as a front end to activesync, which  
>>> will  talk to the same Dovecot instance eventually.
>>
>>
>> You are probably missing the 'query_getpw' query. You need a query
>> that will load the password first, in order to get the salt before we
>> can verify the user provided password. Something like
>>
>> 'SELECT password FROM you_table WHERE username = \L'
>>
>> See towards the end of the following post for more info:
>>
>>
>> https://theupstairsroom.com/116
>>
>
>
> Following up on this, is there any work underway to support other  
> salted SHA algorithms besides {SSHA} in horde? For example  
> {SSHA512}, which is what I'd use given my choice over vanilla  
> SHA512-CRYPT?

No one is working on this that I know of. However, adding support for  
this, related to auth password, shouldn't be too difficult, since PHP  
does support SHA-512 hashes.



> My use case is to bring horde-activesync in (on its own VMs) for  
> mobile (and Outlook/Mail.app functionality); I've used it in the  
> past. However, the last time I rebuilt my webmail tier, I used  
> roundcube; it seemed lighter, and more importantly, it existed as an  
> entity in pkgsrc, so I dodn't have to do a large manual installation  
> process.  I guess part of the problem I'm running into is that while  
> Horde actively attempts to manage users, other platforms just like  
> the IMAP backend handle that and just has tables with the login name  
> for things like preferences and the like.
>
> But I think you are spot on that I need to do a bunch of work in the  
> postgres query to get the hashed, salted password into the form that  
> Dovecot expects.


You shouldn't need a "bunch" of work. Assuming your table structure  
and data you posted are correct, the query I posted should be all that  
you need, and possibly setting the 'show_encryption' value correctly.

> -- 
> Coy Hile
> coy.hile at coyhile.com



-- 
mike
The Horde Project
http://www.horde.org
https://www.facebook.com/hordeproject
https://www.twitter.com/hordeproject
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-keys
Size: 9272 bytes
Desc: PGP Public Key
URL: <https://lists.horde.org/archives/horde/attachments/20190818/926fbafe/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 821 bytes
Desc: PGP Digital Signature
URL: <https://lists.horde.org/archives/horde/attachments/20190818/926fbafe/attachment-0001.bin>