Handle wildcard aliases

Hello,

I noticed that ISPconfig allow aliases to contain the wildcard character (*) but does not really handle it. So I rewrote the /etc/postfix/mysql-virtual_forwardings.cf file to handle them, by replacing the query as follows :

Code:

query = SELECT email AS target FROM mail_user
            WHERE (email = '%s' OR email = CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d')) AND EXISTS (SELECT domain_id FROM mail_domain WHERE domain = SUBSTRING_INDEX('%s', '@', -1) AND active = 'y' AND server_id = 1) AND server_id = 1
        UNION
        SELECT s.destination AS target FROM mail_forwarding AS s
            WHERE (s.source = '%s' OR s.source = CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d')) AND s.type IN ('alias', 'forward') AND s.active = 'y' AND s.server_id = 1
        UNION
        SELECT s.destination AS target FROM mail_forwarding AS s
            WHERE CONCAT(SUBSTRING_INDEX('%u', '+', 1), '@%d') LIKE REPLACE(s.source,'*','%%') AND s.type IN ('alias', 'forward') AND s.active = 'y' AND s.server_id = 1
        UNION
        SELECT s.destination AS target FROM mail_forwarding AS s
            WHERE s.source = '@%d' AND s.type = 'catchall' AND s.active = 'y' AND s.server_id = 1
        LIMIT 1

What this basically does :
- Fetch all email corresponding exacty to the "To:" field received, excluding the '+' field if any (as before)
- Add all the aliases corresponding exactly to the "To:" field received, excluding the '+' field if any (as before)
- Add all the aliases corresponding to the pattern provided in the alias (replacing * with % to use the LIKE statement), excluding the '+' field if any (that's the new part)
- Add all catchall addesses
- Take only the first row returned

This way, we ensure the email is delivered only once, and in the correct order of priority (email, exact alias, wildcard alias, catchall).

I wanted to make a merge request so everybody could have this working, but ISPconfig git is not really public and my account has still not been approved. Really weird move for an Open source project.

 

Nice work. Just wait until @till come to this post.

 

It helps alot with bots and spam.

 

Will do that (need another post, so here we are).

If anybody has a suggestion on how to improve this, I'll take it. I'm not 100% please by the fact that each lookup will fire 4 queries... I know it's on (supposedly) small tables, and on the same fields so the DB should optimize it quite well but ... There might be a better way still.