Custom SASL authentication

[David Koski]

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 at sutinen.com

On 3/18/23 10:07, Aki Tuomi wrote:
>> On 18/03/2023 00:44 EET David Koski <dkoski at 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 at sutinen.com
> Hi David, see 
> https://doc.dovecot.org/configuration_manual/authentication/lua_based_authentication/ 
> <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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://dovecot.org/pipermail/dovecot/attachments/20230406/289e9f47/attachment.htm>