App Passwords not working with example query

[mtissington]

I have been looking at this Postfix-DOvecot-Postgresql-Example

Specifically the last sql query for supporting app password.

It won't work but I'm not sure of the solution (cut/pasted below)

password query = SELECT user, password FROM (\
  SELECT username AS user, password, '0' AS is_app_password FROM\
  mailbox\
  UNION\
  SELECT username AS user, password, '1' AS is_app_password FROM mailbox_app_password\
)\
WHERE user='%u' AND password='%w' AND active=1 AND\
(\
  "%r" IN (SELECT ip FROM totp_exception_address WHERE username="%u" OR username IS NULL OR username="@%d")\
  OR (SELECT totp_secret FROM mailbox WHERE usenamer="%u") IS NULL\
  OR is_app_password='1'\
)

I see a number of issues

1. mailbox_app_password the password field is called password_hash
2. Given the password in both tables mailbox and mailbox_app_password is a hash how can this work password=%w because according to dovecot docs %w is the PLAIN TEXT password.
3. WHERE usenamer="%u" should be username="%u"

The best I have come up with so far (which ignores totp_exception_addresses) it also assumes that MySQL know about the encryption type - so it can't handle BLF-CRYPT

password_query = SELECT user, NULL AS password, 'Y' AS nopassword, '/var/vmail/%d/%n/Maildir' AS userdb_home, 5000 AS userdb_uid, 5000 AS userdb_gid \
FROM 
( 
  SELECT username AS user, password AS hash, '0' AS is_app_password FROM mailbox WHERE username='%u' AND active=1 
  UNION 
  SELECT username AS user, password_hash AS hash, '1' AS is_app_password FROM mailbox_app_password WHERE username='%u' 
) AS hashes 
WHERE SUBSTRING_INDEX(hash, '}', -1) = encrypt('%w', SUBSTRING_INDEX(hash, '}', -1))

[DavidGoodwin]

Just to acknowledge you - yes, you're correct. The current query in the Dovecot docs doesn't work.

I've not had time to investigate it or figure out what the query should be ....

[mtissington]

This is the best I've come up with so far (without IP exceptions). I wonder if it possible for say webmail apps to use the 2FA and other apps require an. app password?

Maybe when the 2FA is created it's possible to store the source app?

I wounder if it's possible to be consistent with mailbox.password and mailbox_app_password.password_hash?

password_query = SELECT user, NULL AS password, 'Y' AS nopassword \
FROM 
( 
  SELECT username AS user, password AS hash, '0' AS is_app_password FROM mailbox WHERE username='%u' AND active=1 
  UNION 
  SELECT username AS user, password_hash AS hash, '1' AS is_app_password FROM mailbox_app_password WHERE username='%u' 
) AS hashes 
WHERE SUBSTRING_INDEX(hash, '}', -1) = encrypt('%w', SUBSTRING_INDEX(hash, '}', -1))

[Neustradamus]

To follow