Multiple passwords with sql authentication

[BlackVoid]

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 can
see there is no good solution to this unless I'm missing something.