Use case
I use a Dovecot IMAP & POP server, against a MySQL user database. In database, the users are stored this way:
| id | pwd |
| mihamina@gulfsat.mg | $1$HSBxVt/clgdj0L5Fgzm61 |
| mihamina@freedsl.mg | $1$7TCTJJMG9cUvAlWuAbvE. |
| mihamina@rktmb.org | $1$H23xVt.bbgdj0L5Fgzm61 |
On my server I have mutiple IP adresses:
- A legacy IP (1.2.3.4) where "@rktmb.org" users sometimes log in only with the localpart, sometimes with the full email address
- A legacy IP (5.6.7.8) where "@freedsl.mg" users sometimes log in only with the localpart, sometimes with the full email address
- A last IP (7.8.9.0) where users log in with the full email address
In usual condition, on a mail system, all users either all login with the full email address or all with the localpart. But on my alien system, they mix their habits, for legacy reasons.
Therefore:
- The "mihamina@rktmb.org" user always logs in on the 1.2.3.4 IP but sometimes with "mihamina", sometimes with "mihamina@rktmb.org"
- The "mihamina@freedsl.mg" user always logs in on the 5.6.7.8 IP but sometimes with "mihamina", sometimes with "mihamina@freedsl.mg"
- The other users always log in on 7.8.9.0 with their full email address
The SQL query using CASE WHEN THEN END
Dovecot allows to use "%u" as username and "%l" as local IP in SQL queries. This is how I did it:
SELECT id AS user, pwd AS password FROM passwdc WHERE (((id = '%u') OR (id = ( CASE WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org') ELSE ('%u@freedsl.mg') END ))) AND en = 1) or SELECT id AS user, pwd AS password FROM passwdc WHERE (((id = '%u') OR (id = ( CASE WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org') WHEN ("%l" = "5.6.7.8") THEN ('%u@freedsl.mg') END ))) AND en = 1)