Multiple passwords with sql authentication

[Cédric Jeanneret]

On 07/23/2014 11:53 PM, BlackVoid wrote:
> On 2014-07-23 21:23, Rick Romero wrote:
>>  Quoting BlackVoid <blackvoid+dovecot at fantas.in>:
>>
>>> On 2014-07-23 18:40, Rick Romero wrote:
>>>> Quoting BlackVoid <blackvoid+dovecot at fantas.in>:
>>>>
>>>>> On 2014-07-23 18:07, Rick Romero wrote:
>>>>>> Quoting BlackVoid <blackvoid+dovecot at fantas.in>:
>>>>>>
>>>>>>> I'm currently working on a control panel which is using postfix,
>>>>
>>>> dovecot
>>>>>>> and other applications and I want to add application specific
>>>>>>> passwords
>>>>>>> to increase security.
>>>>>>>
>>>>>>> I found one solution [1], however it requires the password to be
>>>>>>> included in the query which is something I do not want to do, because
>>>>>>> the query may be written in clear-text to log-files. So I'm wondering
>>>>
>>>> if
>>>>>>> there is a way to have multiple passwords with dovecot without
>> risking
>>>>>>> passwords being leakied in clear-text to log-files.
>>>>
>>>>
>> [1]http://wiki2.dovecot.org/AuthDatabase/SQL#Password_verification_by_SQL_server
>>
>>>>
>>>>>> You can run your query by host (or port - not sure if that variable is
>>>>>> available in the query) and make it complex..
>>>>>>
>>>>>> For example - (MySQL)
>>>>>> SELECT if ('%r'!='127.0.0.1', webmail_pass, enc_password) as password
>>>>>> from
>>>>>> user where userid = %u
>>>>>>
>>>>>> http://komlenic.com/254/mysql-nested-if-in-select-queries/
>>>>>>
>>>>>> If you're using Dovecot as an auth backend for your control panel, I'd
>>>>>> use
>>>>>> a custom port only accessible from the web server(s) like 145 for
>>>>>> IMAP+Control Panel.
>>>>>> Rick
>>>>>
>>>>> The control panel uses the database to authenticate, however I want
>>>>> users to be able to use applications specific passwords when
>>>>> authentication via SMTP, IMAP and POP3. The issue with the solution I
>>>>> found is as I said that the password will be logged in clear-text in
>> the
>>>>> query log.
>>>>>
>>>>> Perhaps I was no clear enough with what I'm trying to achieve. On
>> Google
>>>>> you can have application-specific passwords. This means you can sign in
>>>>> either with your primary password or an application-specific password
>>>>> and this is what I'm trying to do. I could solve it using the solution
>>>>> in my first mail, but that is a security risk, because if someone gains
>>>>> access to my server for whatever reason, all the person has to do is
>>>>> check the mysql query log to see everyone's password in clear-text. If
>> I
>>>>> did not explain it good enough, perhaps this will help.
>>>>> https://support.google.com/mail/answer/1173270?hl=en
>>>>>
>>>>> So I'm looking for a solution where the dovecot fetches all encrypted
>>>>> passwords for the user who is trying to sign in and checks if any of
>> the
>>>>> returned hashes matched with the entered password and the risk is
>>>>> non-existent that the password is logged in clear-text.
>>>>
>>>> Close.  The query returns a single hash, not all of them. The hash
>>>> returned is specific to the app requesting it due to the complex query.
>>>> Dovecot then compares the returned hash with the one submitted by the
>>>> user
>>>> (which will be specific to the app the user is using).
>>>>
>>>> Optimally you shouldn't be storing clear text passwords. The query will
>>>> never contain the password submitted by the user, it will only return a
>>>> password to compare.  If you are using CRYPT, then Dovecot will hash
>> the
>>>> submitted password and compare hashes.
>>>>
>>>> Rick
>>>
>>> Yes, I know that. The passwords are hashed in SHA-512-CRYPT. As I've
>>> explained in previous mails, the issue is that only one result can be
>>> returned, so either I expose the password in the query, the password is
>>> hashed n-times (number of passwords the user has) and it gets logged
>>> (security risk) or I have to give up my idea to implement
>>> application-specific passwords and keep having only one password. You
>>> can't hash the password and use it in the query either, because the
>>> password needs to be hashed  with the correct salt. So from what I
>>> cansee there is no good solution to this unless I'm missing something.
>>
>> I'm assuming you have a field for each application password.  I don't use
>> a custom salt.
>>
>> So your table is like:
>> CREATE TABLE users (     userid VARCHAR(128) NOT NULL,     domain
>> VARCHAR(128) NOT NULL,     password_imap VARCHAR(64) NOT NULL,
>> password_pop VARCHAR(64) NOT NULL,     password_web VARCHAR(64) NOT NULL,
>>   password VARCHAR(64) NOT NULL,     home VARCHAR(255) NOT NULL,     uid
>> INTEGER NOT NULL,     gid INTEGER NOT NULL );
>> in order to return password_web, when the user is using the webmail system,
>> I would use:
>>
>> SELECT
>>               if ( '%r' = '127.0.0.1' AND password_web != " ",
>> password_web,
>>                  if ( '%s' = 'imap' AND password_imap != "
>> ", password_imap,
>>                     if (password_pop != "",
>> password_pop, password) ) )
>>     from users where userid = %u
>>
>> So if the remote system is 'localhost', and password_web exists, return
>> password_web.
>>   If not, then if the remote system is using imap, and password_imap
>> exists return password_imap.
>>    If not imap, then return password_pop if password_pop exists,
>> otherwise return password.
>>
>> And so on and so forth...  I plan on doing the same, just haven't gotten
>> to it. 
>>
>> I guess I don't see what the problem would be...  am I oversimplifying it
>> somehow?  This has been in the back of my head for a while, and even
>> starting to put it on paper it looks fine for any purpose, even outside of
>> Dovecot.   Obviously it'll be a complex query.  Just write it out
>> logically first. If you're allowing only particular applications, you'll
>> have to make sure whatever doesn't have a password falls through to
>> 'password'..   Or, maybe have your 'app password' creation code just
>> ensure any apps without specific passwords have a copy of the 'password'
>> field...  Or maybe have a boolean field to check if app specific passwords
>> are on and make that the first if() in the query checks that....
>>
>> Rick
> 
> That's not a bad solution, however I don't want it to be restricted to
> specific protocols. For example I have one application-specific password
> used on my phone, one on my laptop, one on my desktop and I can use it
> on multiple devices, so it's like an extra password. So that's where the
> issue lies. The only way I can think of is to prefix your username with
> an unique id to identify which password should be compared, however
> that's not practical nor a good solution in my opinion.
> 

Hello,

maybe a stupid question, but why log queries? Just drop that log.

Also, if I understand correctly Dovecot working scheme, it should hash
the password in the query directly if you remove the PLAIN mechanism…

Another solution (and probably the best): Dovecot should provide another
variable than %w, like %W which will be the hashed password according to
the password schema defined in the configuration…

Your idea is really interesting by the way. Was thinking about it for
some times now, never had the time to really try something…

C.