<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
I have done some testing and found the following queries to work for
implementing MySQL SHA2 passwords for authentication:<br>
<br>
<font size="2"><font face="Courier New, Courier, monospace"> 1
user_query:<br>
2 <br>
3 SELECT<br>
4 email AS user,<br>
5 if (<br>
6 (select crypt & 1 from view_users where
email='%u'),<br>
7 (select password from view_users where
email='%u' and password=SHA2('%w',512)),<br>
8 (select password from view_users where
email='%u' and password='%w')<br>
9 ) as password,<br>
10 '/var/lib/vmail/%d/%n' AS home,<br>
11 'maildir:/var/lib/vmail/%d/%n/Maildir' AS mail,<br>
12 5000 AS uid,<br>
13 5000 AS gid<br>
14 FROM<br>
15 view_users<br>
16 WHERE <br>
17 email = '%u' AND enable = '1'<br>
18 <br>
19 <br>
20 password_query:<br>
21 <br>
22 SELECT<br>
23 email AS user,<br>
24 if (<br>
25 (select crypt & 1 from view_users where
email='%u'),<br>
26 (select password from view_users where
email='%u' and password=SHA2('%w',512)),<br>
27 (select password from view_users where
email='%u' and password='%w')<br>
28 ) as password<br>
29 FROM<br>
30 view_users<br>
31 WHERE <br>
32 email = '%u' AND enable = '1'<br>
</font></font><br>
<br>
But it seems wasteful in the number of queries required. Looking
for ideas to consolidate queries.<br>
<br>
Regards,<br>
David Koski<br>
<a class="moz-txt-link-abbreviated" href="mailto:dkoski@sutinen.com">dkoski@sutinen.com</a><br>
<br>
<div class="moz-cite-prefix">On 3/18/23 10:07, Aki Tuomi wrote:<br>
</div>
<blockquote type="cite"
cite="mid:1718000243.2790.1679159274441@asd-stable-core-mw-default-0.asd-stable-core-mw-hazelcast-headless.asd-stable.svc.cluster.local">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<meta charset="UTF-8">
<div> </div>
<blockquote type="cite">
<div> On 18/03/2023 00:44 EET David Koski <<a
href="mailto:dkoski@sutinen.com" moz-do-not-send="true"
class="moz-txt-link-freetext">dkoski@sutinen.com</a>>
wrote: </div>
<div> </div>
<div> </div>
<div> Hello, </div>
<div> </div>
<div> I'm looking for a good way to apply a custom hash to
passwords. My hope </div>
<div> is to add passwords to a (MySQL) database: INSERT INTO
users </div>
<div> (user='joblo', pass=MYHASH('plain-password').. </div>
<div> </div>
<div> For SASL authentication, my thought first was to apply the
same hash to </div>
<div> the issued password and compare it with the hashed
password in the </div>
<div> database. I soon discovered the sql driver supplied by
Dovecot doesn't </div>
<div> provide that ability, unless I'm missing something. </div>
<div> </div>
<div> I'm looking for documentation on how to implement a custom
</div>
<div> authentication script if needed. </div>
<div> </div>
<div> Regards, </div>
<div> David Koski </div>
<div> <a href="mailto:dkoski@sutinen.com"
moz-do-not-send="true" class="moz-txt-link-freetext">dkoski@sutinen.com</a>
</div>
</blockquote>
<div> </div>
<div class="default-style"> <span style="font-size: 11pt;">Hi
David, see <a
href="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"
moz-do-not-send="true">https://doc.dovecot.org/configuration_manual/authentication/lua_based_authentication/</a>
on how to implement custom authentication.</span> </div>
<div class="default-style"> </div>
<div class="default-style"> <span style="font-size: 11pt;">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.</span> </div>
<div class="default-style"> </div>
<div class="default-style"> <span style="font-size: 11pt;">Aki</span>
</div>
<div class="default-style"> </div>
</blockquote>
<br>
</body>
</html>