Multiple passwords with sql authentication

[Rick Romero]

  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