Multiple passwords with sql authentication

[BlackVoid]

On 2014-07-24 07:36, Cédric Jeanneret wrote:
> 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.
> 

The passwords looks like this in the database:
$6$qwerty$wZZxE91RvJb4ETR0svmCb69rVCevicDV1Fw.Y9Qyg9idcZUioEoYmOzAv23wyEiNoyMLuBLGXPSQbd5ETanmq/
where 6 is the hashing method, qwerty is the salt and after the last $
is the hash. So it's impossible for the entered password to be hashed in
the query since it needs to be hashed with the correct salt to yield the
same hash.

I could drop query logging, but it's useful for debugging errors in
production as well as it is bad practice to encrypt the password in the
query [1] in my opinion, because it leaves the application in
clear-text. I rather not support application-specific passwords than
having the password in clear-text in the query.

[1]
http://stackoverflow.com/questions/20295778/how-to-use-bcrypt-algorithm-within-encrypt-function-in-mysql-for-verifying-p