<NME> postfix_maps.py
<BEF> """Map file definitions for postfix."""

class DomainsMap(object):

    """Map to list all domains."""

    filename = "sql-domains.cf"
    mysql = (
        "SELECT name FROM admin_domain "
        "WHERE name='%s' AND type='domain' AND enabled=1"
    )
    postgres = (
        "SELECT name FROM admin_domain "
        "WHERE name='%s' AND type='domain' AND enabled"
    )
    sqlite = (
        "SELECT name FROM admin_domain "
        "WHERE name='%s' AND type='domain' AND enabled=1"
    )


class DomainsAliasesMap(object):

    """Map to list all domain aliases."""

    filename = "sql-domain-aliases.cf"
    mysql = (
        "SELECT dom.name FROM admin_domain dom "
        "INNER JOIN admin_domainalias domal ON dom.id=domal.target_id "
        "WHERE domal.name='%s' AND domal.enabled=1 AND dom.enabled=1"
    )
    postgres = (
        "SELECT dom.name FROM admin_domain dom "
        "INNER JOIN admin_domainalias domal ON dom.id=domal.target_id "
        "WHERE domal.name='%s' AND domal.enabled AND dom.enabled"
    )
    sqlite = (
        "SELECT dom.name FROM admin_domain dom "
        "INNER JOIN admin_domainalias domal ON dom.id=domal.target_id "
        "WHERE domal.name='%s' AND domal.enabled=1 AND dom.enabled=1"
    )


class AliasesMap(object):

    """A map to list all mailbox aliases."""

    filename = "sql-aliases.cf"
    mysql = (
        "SELECT alr.address FROM modoboa_admin_aliasrecipient AS alr "
        "INNER JOIN admin_alias AS al ON alr.alias_id=al.id "
        "WHERE al.enabled=1 AND al.address='%s' AND "
        "(al.expire_at IS NULL OR al.expire_at>now())"
    )
    postgres = (
        "SELECT alr.address FROM modoboa_admin_aliasrecipient AS alr "
        "INNER JOIN admin_alias AS al ON alr.alias_id=al.id "
        "WHERE al.enabled AND al.address='%s' AND "
        "(al.expire_at IS NULL OR al.expire_at>now())"
    )
    sqlite = (
        "SELECT alr.address FROM modoboa_admin_aliasrecipient AS alr "
        "INNER JOIN admin_alias AS al ON alr.alias_id=al.id "
        "WHERE al.enabled=1 AND al.address='%s' AND "
        "(al.expire_at IS NULL OR al.expire_at>datetime('now'))"
    )


class MaintainMap(object):

    """Map files to list non available mailboxes."""

    filename = "sql-maintain.cf"
    mysql = (
        "SELECT '450 Requested mail action not taken: mailbox unavailable' "
        "FROM admin_mailbox mb INNER JOIN admin_domain dom "
        "ON mb.domain_id=dom.id INNER JOIN admin_mailboxoperation mbop "
        "ON mbop.mailbox_id=mb.id WHERE dom.name='%d' AND mb.address='%u' "
        "LIMIT 1"
    )
    postgres = (
        "SELECT '450 Requested mail action not taken: mailbox unavailable' "
        "FROM admin_mailbox mb INNER JOIN admin_domain dom "
        "ON mb.domain_id=dom.id INNER JOIN admin_mailboxoperation mbop "
        "ON mbop.mailbox_id=mb.id WHERE dom.name='%d' AND mb.address='%u' "
        "LIMIT 1"
    )
    sqlite = (
        "SELECT '450 Requested mail action not taken: mailbox unavailable' "
        "FROM admin_mailbox mb INNER JOIN admin_domain dom "
        "ON mb.domain_id=dom.id INNER JOIN admin_mailboxoperation mbop "
        "ON mbop.mailbox_id=mb.id WHERE dom.name='%d' AND mb.address='%u' "
        "LIMIT 1"
    )


class SenderLoginMap(object):
    """Map file to list authorized sender addresses for a given account:
    * Its mailbox
    * Its aliases
    * Extra addresses
    """

    filename = "sql-sender-login-map.cf"
    mysql = (
        "(SELECT email FROM core_user WHERE email='%s' AND is_active=1) "
        "UNION "
        "(SELECT concat(mb.address, '@', dom.name) FROM admin_mailbox mb "
        "INNER JOIN admin_senderaddress sad ON sad.mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "WHERE sad.address='%s') "
        "UNION "
        "(SELECT concat(mb.address, '@', dom.name) FROM admin_mailbox mb "
        "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "INNER JOIN admin_alias al ON alr.alias_id=al.id "
        "LEFT JOIN admin_domainalias adoma ON adoma.target_id=al.domain_id "
        "(SELECT concat(mb.address, '@', dom.name) FROM admin_mailbox mb "
        "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id"
        " INNER JOIN admin_domain dom ON dom.id=mb.domain_id"
        " INNER JOIN admin_alias al ON alr.alias_id=al.id "
        "WHERE al.enabled=1 AND al.address='%s')"
    )
    postgres = (
        "(SELECT email FROM core_user WHERE email='%s' AND is_active) "
        "WHERE sad.address=concat('@','%d')) "
    )
    postgres = (
        "(SELECT email FROM core_user WHERE email='%s' AND is_active) "
        "UNION "
        "(SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "(SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id"
        " INNER JOIN admin_domain dom ON dom.id=mb.domain_id"
        " INNER JOIN admin_alias al ON alr.alias_id=al.id "
        "WHERE al.enabled AND al.address='%s')"
    )
    sqlite = (
        "SELECT email FROM core_user WHERE email='%s' AND is_active=1 "
        "LEFT JOIN admin_domain adom ON adom.id=adoma.target_id "
        "WHERE al.enabled AND ("
        "  al.address='%s' OR ("
        "    adoma.name='%d' AND al.address='%u'||'@'||adom.name"
        "))) "
        "UNION "
        "SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "INNER JOIN admin_alias al ON alr.alias_id=al.id "
        "WHERE al.enabled=1 AND al.address='%s'"
    )
        "UNION "
        "SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "INNER JOIN admin_senderaddress sad ON sad.mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "WHERE sad.address='%s' "
        "UNION "
        "SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "INNER JOIN admin_alias al ON alr.alias_id=al.id "
        "LEFT JOIN admin_domainalias adoma ON adoma.target_id=al.domain_id "
        "LEFT JOIN admin_domain adom ON adom.id=adoma.target_id "
        "WHERE al.enabled=1 AND ("
        "  al.address='%s' OR ("
        "    adoma.name='%d' AND al.address='%u'||'@'||adom.name"
        ")) "
        "UNION "
        "(SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
        "INNER JOIN admin_senderaddress sad ON sad.mailbox_id=mb.id "
        "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
        "WHERE sad.address='@'||'%d') "
    )

<MSG> Allow users to send emails from alias domains

### Problem

Currently, if I have a domain `bla.com` that is an alias of `blub.com` and John has user account as `john@blub.com`, then will not be able to send e-mails as `john@bla.com` because the system will not recognize / communicate to Postfix that `john@bla.com` is the same as `john@blub.com` and hence Postfix will reject this attempt.

### Proposed Solution

When calculating the allowed SASL usernames for an email address, also include aliases from aliased domains in the result of the query. (This will also work for the primary username, since that is added as “internal” alias to that table as well.)

### Caveats

I only tested the proposed query with PostgreSQL.
<DFF> @@ -118,8 +118,9 @@ class SenderLoginMap(object):
         "(SELECT concat(mb.address, '@', dom.name) FROM admin_mailbox mb "
         "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id"
         " INNER JOIN admin_domain dom ON dom.id=mb.domain_id"
+        " INNER JOIN admin_domainalias adom ON adom.target_id=dom.id"
         " INNER JOIN admin_alias al ON alr.alias_id=al.id "
-        "WHERE al.enabled=1 AND al.address='%s')"
+        "WHERE al.enabled=1 AND (al.address='%s' OR (adom.name='@d' AND al.address='%u'||'@'||dom.name)))"
     )
     postgres = (
         "(SELECT email FROM core_user WHERE email='%s' AND is_active) "
@@ -132,8 +133,9 @@ class SenderLoginMap(object):
         "(SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
         "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id"
         " INNER JOIN admin_domain dom ON dom.id=mb.domain_id"
+        " INNER JOIN admin_domainalias adom ON adom.target_id=dom.id"
         " INNER JOIN admin_alias al ON alr.alias_id=al.id "
-        "WHERE al.enabled AND al.address='%s')"
+        "WHERE al.enabled AND (al.address='%s' OR (adom.name='@d' AND al.address='%u'||'@'||dom.name)))"
     )
     sqlite = (
         "SELECT email FROM core_user WHERE email='%s' AND is_active=1 "
@@ -146,6 +148,7 @@ class SenderLoginMap(object):
         "SELECT mb.address || '@' || dom.name FROM admin_mailbox mb "
         "INNER JOIN modoboa_admin_aliasrecipient alr ON alr.r_mailbox_id=mb.id "
         "INNER JOIN admin_domain dom ON dom.id=mb.domain_id "
+        "INNER JOIN admin_domainalias adom ON adom.target_id=dom.id "
         "INNER JOIN admin_alias al ON alr.alias_id=al.id "
-        "WHERE al.enabled=1 AND al.address='%s'"
+        "WHERE al.enabled=1 AND (al.address='%s' OR (adom.name='@d' AND al.address='%u'||'@'||dom.name))"
     )

Allow users to send emails from alias domains