Multiple passwords with sql authentication
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_serve...
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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.
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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.
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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
On 2014-07-23 18:40, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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 can see there is no good solution to this unless I'm missing something.
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:40, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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
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
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 the 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
On 2014-07-23 21:23, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:40, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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
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
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 the 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.
On 07/23/2014 11:53 PM, BlackVoid wrote:
On 2014-07-23 21:23, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:40, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@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_serve...
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
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
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 the 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.
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@fantas.in>:
On 2014-07-23 18:40, Rick Romero wrote:
Quoting BlackVoid <blackvoid+dovecot@fantas.in>:
On 2014-07-23 18:07, Rick Romero wrote: > Quoting BlackVoid <blackvoid+dovecot@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_serve...
> 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
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
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 the 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-with...
On 23 Jul 2014, at 18:49, BlackVoid <blackvoid+dovecot@fantas.in> wrote:
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.
There's an old patch to support this, but it was never finished: http://dovecot.org/patches/2.0/auth-multi-password-2.0.diff
I had a newer idea about encoding the passwords into a single field, such as {MULTI}hash1:hash2:hash3 but that doesn't exist either yet.
For now the only possibility would be to create multiple passdbs, each one returning a different password field. That could work if you have only a couple of different passwords.
On 2014-07-28 16:51, Timo Sirainen wrote:
On 23 Jul 2014, at 18:49, BlackVoid <blackvoid+dovecot@fantas.in> wrote:
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.
There's an old patch to support this, but it was never finished: http://dovecot.org/patches/2.0/auth-multi-password-2.0.diff
I had a newer idea about encoding the passwords into a single field, such as {MULTI}hash1:hash2:hash3 but that doesn't exist either yet.
For now the only possibility would be to create multiple passdbs, each one returning a different password field. That could work if you have only a couple of different passwords.
Well that's unfortunate. Looks like I have to scrap the idea until either the old patch or your idea is implemented. I don't think having multiple passdbs is a choice, because the amount of application specific passwords a user can have is not finite.
Thanks for the help though.
participants (4)
-
BlackVoid
-
Cédric Jeanneret
-
Rick Romero
-
Timo Sirainen