Timo Sirainen wrote:

On Thu, 2005-10-27 at 15:00 +0200, Tom Sommer wrote:

...

Hi,

I have a little problem, I need to adapt a setup that currently uses the native MySQL PASSWORD() function to encrypt passwords for e-mail accounts.

I plan on using Dovecot with SQL to authenticate on my server, is there a password scheme that fits this authentication method?

Not really.. I guess you could use pam_mysql to check it. Or even a checkpassword script if that won't work. But using it directly as passdb sql would require Dovecot to know the mysql-password() scheme.

But if I compile dovecot with --with-mysql, would it not be possible for dovecot to learn the mysql-password() scheme?

I would really like to avoid using PAM - and the choice of PASSWORD() as authentication is really putting a stop to my plans

Thanks

Tom Sommer

Timo Sirainen wrote:

...

But if I compile dovecot with --with-mysql, would it not be possible for dovecot to learn the mysql-password() scheme? I guess it shouldn't be difficult, if one of the password functions in mysql_com.h is the same as the password() function in SQL.

Don't know that, but it's probably a bad idea to do that. The mysql doc itself says: "Note: The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, use MD5() or SHA1() instead." And there are two different ways mysql stores its passwords: An old one (pre-4.1, 16 bytes) and a new new one (41 bytes, with a leading '*').

I think it would be more flexible (and maybe even easier to implement) to be able to use the password in a query, like:

SELECT userid as user, password FROM users WHERE userid = '%u' AND password = '%p'

This way, people can even use

SELECT userid as user, %p AS password FROM users WHERE userid = '%u' AND password = PASSWORD('%p')

(I hope the substitutions are properly escaped, btw)

This works only when we get the plaintext password from the client, obviously. But this is also true for CRYPT etc.

Maybe it would be even better/cleaner to be able to use something like

SELECT userid AS user, 1 AS password_ok FROM users WHERE userid = '%u' AND password = PASSWORD('%p')

So if password_ok is 1 we assume just what it says without further checking. This is more like a "return the check result" than "return the password" query then.

Hope this make at least a little sense...

Timo Sirainen wrote:

On Thu, 2005-10-27 at 17:02 +0200, Jakob Hirsch wrote:

...

Maybe it would be even better/cleaner to be able to use something like

SELECT userid AS user, 1 AS password_ok FROM users WHERE userid = '%u' AND password = PASSWORD('%p')

Actually I think this already works:

SELECT userid AS user, NULL as password WHERE userid = '%u' AND password = PASSWORD('%p')

NULL passwords accept any password. This was needed for proxying but works nicely elsewhere too.

Excellent, I will try this

-- Tom Sommer

On Thu, 2005-10-27 at 17:52 +0200, Tom Sommer wrote:

This doesn't seem to work, it executes:

SELECT username as user, NULL as password FROM users WHERE username = 'test@example.com' AND password = PASSWORD('0')

... where password_query is: ...

SELECT username as user, NULL as password FROM users WHERE username = '%u' AND password = PASSWORD('%p')

I forgot the %p part. Added %w to CVS code now which is the password.

Jakob Hirsch wrote:

Timo Sirainen wrote:

...

...

But if I compile dovecot with --with-mysql, would it not be possible for dovecot to learn the mysql-password() scheme?

I guess it shouldn't be difficult, if one of the password functions in mysql_com.h is the same as the password() function in SQL.

Don't know that, but it's probably a bad idea to do that. The mysql doc itself says: "Note: The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, use MD5() or SHA1() instead." And there are two different ways mysql stores its passwords: An old one (pre-4.1, 16 bytes) and a new new one (41 bytes, with a leading '*').

I agree, using PASSWORD() as a means to encode passwords in general applications is a VERY bad idea, but what's done is done :(

I think it would be more flexible (and maybe even easier to implement) to be able to use the password in a query, like:

SELECT userid as user, password FROM users WHERE userid = '%u' AND password = '%p'

This way, people can even use

SELECT userid as user, %p AS password FROM users WHERE userid = '%u' AND password = PASSWORD('%p')

(I hope the substitutions are properly escaped, btw)

This works only when we get the plaintext password from the client, obviously. But this is also true for CRYPT etc.

Maybe it would be even better/cleaner to be able to use something like

SELECT userid AS user, 1 AS password_ok FROM users WHERE userid = '%u' AND password = PASSWORD('%p')

So if password_ok is 1 we assume just what it says without further checking. This is more like a "return the check result" than "return the password" query then.

All of the above would solve my problem just fine, and keep the authentication in native MySQL

-- Tom Sommer