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.
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
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.
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 = ?
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'