Domain alias mail forward ?

[jmeyerdo]

Hi rbtux, hi all!

So, I did it today (with several cups of coffee and the help of my colleague).
While preparing the SQL-statements I noticed that the database-schema is not very good for this usage (in comparison with "standard" postfix-tables).
Especially it is very astonishing that the target-email for catchalls is stored in mail_acc but for all other email-configurations in mail_forward IMHO. Why this?!
For my usage I will add indizes to speed up the sql-selects soon.

Attached my postfix-mysql-files (please remove my comments and linebreaks in SQL before using!). Works fine for me. After confirmation of you as specialists I can add this to the dovecot-configuration-page in the WIKI if you want.

Code:

# transport_maps      = mysql:/etc/postfix/mysql_virtual_transport_maps.cf

user=ispcp_postfix
password=mypass
dbname=ispcp
query = select DISTINCT "ispcp-arpl:" from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT("ispcp-arpl.", domain.domain_name) = '%s'
   and mail_users.sub_id = 0
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"
union
select DISTINCT "ispcp-arpl:" from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT("ispcp-arpl.", subdomain.subdomain_name), "."), domain.domain_name) = '%s'
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"
union
select DISTINCT "ispcp-arpl:" from mail_users, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT("ispcp-arpl.", domain_aliasses.alias_name) = '%s'
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"



# virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf

user=ispcp_postfix
password=mypass
dbname=ispcp
query = select DISTINCT domain.domain_name from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and domain.domain_name = '%s'
   and mail_users.sub_id = 0
   and mail_users.status = "ok"
union
select DISTINCT CONCAT(CONCAT(subdomain.subdomain_name, "."), domain.domain_name) from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(subdomain.subdomain_name, "."), domain.domain_name) = '%s'
   and mail_users.status = "ok"
union
select DISTINCT domain_aliasses.alias_name from mail_users, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and domain_aliasses.alias_name = '%s'
   and mail_users.status = "ok"
  
# virtual_mailbox_maps    = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf

user=ispcp_postfix
password=mypass
dbname=ispcp
query = select DISTINCT CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) = '%s'
   and mail_users.mail_type = "normal_mail"
   and mail_users.status = "ok"
union
select DISTINCT CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) = '%s'
   and mail_users.mail_type = "subdom_mail"
   and mail_users.status = "ok"
union
select DISTINCT CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) from mail_users, domain, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) = '%s'
   and mail_users.mail_type = "alias_mail"
   and mail_users.status = "ok"

  

# virtual_alias_maps      = mysql:/etc/postfix/mysql_virtual_alias_maps.cf


user=ispcp_postfix
password=mypass
dbname=ispcp
query =

# Postfaecher
select DISTINCT CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) = '%s'
   and mail_users.mail_type = "normal_mail"
   and mail_users.status = "ok"
union
select DISTINCT CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) = '%s'
   and mail_users.mail_type = "subdom_mail"
   and mail_users.status = "ok"
union
select DISTINCT CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) from mail_users, domain, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) = '%s'
   and mail_users.mail_type = "alias_mail"
   and mail_users.status = "ok"

# Weiterleitungen
union
select DISTINCT mail_users.mail_forward from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) = '%s'
   and mail_users.mail_type = "normal_forward"
   and mail_users.status = "ok"
union
select DISTINCT mail_users.mail_forward from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) = '%s'
   and mail_users.mail_type = "subdom_forward"
   and mail_users.status = "ok"
union
select DISTINCT mail_users.mail_forward from mail_users, domain, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) = '%s'
   and mail_users.mail_type = "alias_forward"
   and mail_users.status = "ok"

# Autoreplies
union
select DISTINCT CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"), "ispcp-arpl."), domain.domain_name) from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"), domain.domain_name) = '%s'
   and mail_users.mail_type = "normal_mail"
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"
union
select DISTINCT CONCAT(CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"), "ispcp-arpl."), subdomain.subdomain_name, "."), domain.domain_name) from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),subdomain.subdomain_name, "."), domain.domain_name) = '%s'
   and mail_users.mail_type = "subdom_mail"
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"
union
select DISTINCT CONCAT(CONCAT(CONCAT(mail_users.mail_acc, "@"),"ispcp-arpl."), domain_aliasses.alias_name) from mail_users, domain, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT(CONCAT(mail_users.mail_acc, "@"),domain_aliasses.alias_name) = '%s'
   and mail_users.mail_type = "alias_mail"
   and mail_users.status = "ok"
   and mail_users.mail_auto_respond != "_no_"
  
# Catchalls
union
select mail_users.mail_acc from mail_users, domain
where mail_users.domain_id = domain.domain_id
   and CONCAT("@", domain.domain_name) = '%s'
   and mail_users.mail_type = "normal_catchall"
   and mail_users.status = "ok"
union
select mail_users.mail_acc from mail_users, domain, subdomain
where mail_users.domain_id = domain.domain_id
   and subdomain.subdomain_id = mail_users.sub_id
   and CONCAT(CONCAT(CONCAT("@" , subdomain.subdomain_name), "."), domain.domain_name) = '%s'
   and mail_users.mail_type = "subdom_catchall"
   and mail_users.status = "ok"
union
select mail_users.mail_acc from mail_users, domain_aliasses
where domain_aliasses.alias_id = mail_users.sub_id
   and CONCAT("@", domain_aliasses.alias_name) = '%s'
   and mail_users.mail_type = "alias_catchall"
   and mail_users.status = "ok"

Kind regards, Jens