Multiple passwords with sql authentication

[Rick Romero]

  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