Bug in examples: SQL query in docs/debian-conf.d/router/250_vexim_virtual_domains

[VVD]

Query from example file docs/debian-conf.d/router/250_vexim_virtual_domains.

1. If user added in any group, then he can send emails in any non-public group too - need g.id = c.group_id:

   virtual_dom_groups:
   driver = redirect
   domains = +local_domains
   allow_fail
   senders = ${if eq{Y}{${lookup mysql{select g.is_public \
                                      from groups g, domains d \
                                      where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
                                            d.domain_id = g.domain_id and g.enabled = '1' and \
                                            g.name = '${quote_mysql:$local_part}'}}} \
                 {$sender_address} \
                {${lookup mysql{select concat_ws('@', u.localpart, d.domain) \
                                 from domains d, groups g, group_contents c, users u \
                                 where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
                                       d.domain_id = g.domain_id and g.name = '${quote_mysql:$local_part}' and \
   -                                       g.enabled = '1' and \
   +                                       g.enabled = '1' and g.id = c.group_id and \
                                       g.is_public = 'N' and c.member_id = u.user_id and \
                                      d.domain_id = u.domain_id and u.enabled = '1' \
                                      and u.username = '${quote_mysql:$sender_address}' limit 1}}}}

2. If alias was added in non-public group, then sender from main login can't send emails to this group. This query fixed this:

   virtual_dom_groups:
   driver = redirect
   domains = +local_domains
   allow_fail
   senders = ${if eq{Y}{${lookup mysql{select g.is_public \
                                     from groups g, domains d \
                                     where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
                                           d.domain_id = g.domain_id and g.enabled = '1' and \
                                           g.name = '${quote_mysql:$local_part}'}}} \
                {$sender_address} \
                {${lookup mysql{select concat_ws('@', u.localpart, d.domain) \
                                from domains d, groups g, group_contents c, users u \
                                where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
                                      d.domain_id = g.domain_id and g.name = '${quote_mysql:$local_part}' and \
                                      g.enabled = '1' and g.id = c.group_id and \
                                      g.is_public = 'N' and c.member_id = u.user_id and \
                                      d.domain_id = u.domain_id and u.enabled = '1' and \
                                      u.username = '${quote_mysql:$sender_address}' \
                                union \
                                select concat_ws('@', a.localpart, d.domain) \
                                from domains d, groups g, group_contents c, users u, users a \
                                where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
                                      d.domain_id = g.domain_id and g.name = '${quote_mysql:$local_part}' and \
                                      g.enabled = '1' and g.id = c.group_id and \
                                      g.is_public = 'N' and c.member_id = u.user_id and \
                                      d.domain_id = u.domain_id and u.enabled = '1' and a.enabled = 1 and \
                                      a.type = 'alias' and a.username = '${quote_mysql:$sender_address}' and \
                                      a.smtp = u.username }}}}

   Example: mailbox USER1@my.domain, alias ALIAS1@my.domain => USER1@my.domain, non-public group GROUP1@my.domain with member ALIAS1@my.domain. With old sql query USER1@my.domain can't write in non-public group GROUP1@my.domain, only ALIAS1@my.domain can. With new sql query USER1@my.domain can write in non-public group GROUP1@my.domain too.

[rimas-kudelis]

Part 1 here is indeed a bug, but I'm not so sure about the second proposal. It makes the query twice as big, just to add a special case to an otherwise unsupported scenario.

Consider any third party server which has no way of knowing that ALIAS1@my.domain is an alias to USER1@my.domain. That server would naturally reject any messages from USER1@my.domain to any private group if only ALIAS1@my.domain, but not USER1@my.domain were a member of that group. We do exactly the same.

I say let's leave that second part as it is.

However, while looking at part 1, I noticed how stupid our queries in Exim config file are. I'm going to rewrite them to replace some WHERE conditions with JOIN clauses.

[VVD]

1. Agree that it's easier to write correct sql query with joins, easier to maintain and understand it.
2. Just suggest my local patch.