Multiple passwords with sql authentication

[BlackVoid]

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.