On 2014-07-23 21:23, Rick Romero wrote: > Quoting BlackVoid <blackvoid+dove...@fantas.in>: > >> On 2014-07-23 18:40, Rick Romero wrote: >>> Quoting BlackVoid <blackvoid+dove...@fantas.in>: >>> >>>> On 2014-07-23 18:07, Rick Romero wrote: >>>>> Quoting BlackVoid <blackvoid+dove...@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.
