Fixing to not top post.

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_authentication/ 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

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.

Also, do the Dovecot query strings have to be s single query or can there be a query to set a variable, for example, to use in subsequent queries?

Regards,
David Koski
dkoski@sutinen.com