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.