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 nopasswordin this case, and this will cause wrong password to become unknown user error. Aki