I have done some testing and found the following queries to work for implementing MySQL SHA2 passwords for authentication:

  1 user_query: 2 3     SELECT 4         email AS user, 5         if ( 6             (select crypt & 1 from view_users where email='%u'), 7             (select password from view_users where email='%u' and password=SHA2('%w',512)), 8             (select password from view_users where email='%u' and password='%w') 9         ) as password, 10         '/var/lib/vmail/%d/%n' AS home, 11         'maildir:/var/lib/vmail/%d/%n/Maildir' AS mail, 12         5000 AS uid, 13         5000 AS gid 14     FROM 15         view_users 16     WHERE 17     email = '%u' AND enable = '1' 18 19 20 password_query: 21 22     SELECT 23         email AS user, 24         if ( 25             (select crypt & 1 from view_users where email='%u'), 26             (select password from view_users where email='%u' and password=SHA2('%w',512)), 27             (select password from view_users where email='%u' and password='%w') 28         ) as password 29     FROM 30         view_users 31     WHERE 32     email = '%u' AND enable = '1'

But it seems wasteful in the number of queries required.  Looking for ideas to consolidate queries.

Regards, David Koski dkoski@sutinen.com

On 3/18/23 10:07, Aki Tuomi wrote:

...

On 18/03/2023 00:44 EET David Koski <dkoski@sutinen.com> wrote: Hello, I'm looking for a good way to apply a custom hash to passwords.  My hope is to add passwords to a (MySQL) database: INSERT INTO users (user='joblo', pass=MYHASH('plain-password').. For SASL authentication, my thought first was to apply the same hash to the issued password and compare it with the hashed password in the database.  I soon discovered the sql driver supplied by Dovecot doesn't provide that ability, unless I'm missing something. I'm looking for documentation on how to implement a custom authentication script if needed. Regards, David Koski dkoski@sutinen.com Hi David, see https://doc.dovecot.org/configuration_manual/authentication/lua_based_authen... <https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fdoc.dovecot.org%2fconfiguration_manual%2fauthentication%2flua_based_authentication%2f&c=E,1,9ey3vCELwieYk48TYCRIc3sTP0NX6IAzpNYTi7oYlL4_KJcx8IMijlRF7zmvrRU1DN9FriQm24ek0MdzT44auq5mqvOhpVhQSHsjmUvBjF54WhW0tgDC&typo=1&ancr_add=1> on how to implement custom authentication. For verifying password you could use MYHASH('%w') in your passdb sql lookup. You need to include 'Y' as nopassword in this case, and this will cause wrong password to become unknown user error. Aki

For the archive: This MySQL configuration seems to work well.

user_query = \ SELECT
email AS user,
'/var/lib/vmail/%d/%n' AS home,
'maildir:/var/lib/vmail/%d/%n/Maildir' AS mail,
5000 AS uid,
5000 AS gid
FROM
view_users
WHERE
email='%u'
AND
enable = '1'

password_query = \ SELECT
email AS user,
NULL AS password,
'Y' as nopassword
FROM
view_users
WHERE
email='%u'
AND
password=IF (crypt & 1, SHA2('%w',512), password)
AND
enable = '1'

Hello R.,

Thank you for the reply.

On 4/17/23 14:49, Robert Lister wrote:

Interesting...

I'm not sure why you would need to set : "NULL AS password, 'Y' as nopassword", but then I haven't seen the rest of your auth config. This would seem to allow any password but succeed if it matches the rest of the query.

The query does not return the password, only the hash, thus "NULL AS password", as I understand it.  It is documented.  The 'Y' as no password is found earlier in this thread.

It seemed a bit awkward to me to embed static variables in the query which are always going to be the same.

In the auth- config, or 10-mail.conf I just set:-

Defaults:

mail_uid = vmail mail_gid = vmail mail_home = /var/lib/vmail/%d/%n

(this is set elsewhere in 10-mail.conf.)

mail_location = maildir:~/Maildir

I think you are right.  My eye was on migrating global settings to SQL accounts but it probably will not happen.  Nevertheless, I suspect it adds little burden having static variables given it requires no database access, no?

userdb can still override these if needed, but it means that the query is a lot simpler and if one of the queries doesn't return home/uid/gid etc, it's always going to be set anyway.

Seems to me it is a matter of preference.

What does the "crypt" bit of "IF (crypt & 1, SHA2('%w',512)" do? crypt it before running the select?

Why not just set:

default_pass_scheme =  ?

"crypt" bit 0 is a flag to encrypt passwords.  I have chosen a custom hash.  Can I replace it with "default_pass_scheme = ?"?

Regards, David

R.

On 2023-04-17 18:57, dkoski@sutinen.com wrote:

...

For the archive: This MySQL configuration seems to work well.

user_query =
SELECT
email AS user,
'/var/lib/vmail/%d/%n' AS home,
'maildir:/var/lib/vmail/%d/%n/Maildir' AS mail,
5000 AS uid,
5000 AS gid
FROM
view_users
WHERE
email='%u'
AND
enable = '1'

password_query =
SELECT
email AS user,
NULL AS password,
'Y' as nopassword
FROM
view_users
WHERE
email='%u'
AND
password=IF (crypt & 1, SHA2('%w',512), password)
AND
enable = '1'

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org