Bug in examples: SQL query in docs/debian-conf.d/router/250_vexim_virtual_domains #276
Description
Query from example file docs/debian-conf.d/router/250_vexim_virtual_domains.
- 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}}}}
- 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 [email protected], alias [email protected] => [email protected], non-public group [email protected] with member [email protected].
With old sql query [email protected] can't write in non-public group [email protected], only [email protected] can.
With new sql query [email protected] can write in non-public group [email protected] too.