[horde] Getting horde to authenticate against dovecot DB

[Coy Hile]

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?

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.

-- 
Coy Hile
coy.hile at coyhile.com