Loading ...
Sorry, an error occurred while loading the content.

Forwards with MySQL

Expand Messages
  • Alvaro Marín
    Hi, I ve configured Postfix to use MySQL tables with alias or forwardings: virtual_alias_domains=mysql:/etc/postfix/mysql-virtual_domains.cf
    Message 1 of 11 , Sep 1, 2006
    • 0 Attachment
      Hi,

      I've configured Postfix to use MySQL tables with alias or forwardings:

      virtual_alias_domains=mysql:/etc/postfix/mysql-virtual_domains.cf
      virtual_alias_maps=mysql:/etc/postfix/mysql-virtual_aliases.cf

      where alias_domains table has for example:

      "domain.com"

      and the alias_maps table:

      "oneuser","domain.com","otheruser@..."

      This example runs fine, but if for example I add "otherdomain.com" to
      alias_domains table, it search for the user "otheruser" in MySQL table
      (logically) instead of send the mail to the MX.

      Is there any way to send directly the message to the "otherdomain.com"'s
      MX without searching on the table again?

      Other thing that I want to ask is that if the alias_maps table has only
      2 fields (alias and destination) I can use for example:

      @... -> ouser@...

      to forward all the accounts from @domain to other account, but if I
      create that table with 3 fields (alias_user, alias_domain and
      destination) this doesn't work...any idea?

      Thanks a lot.
      Alvaro.
    • Kai Fürstenberg
      ... Hello, ... You can use transport_maps. Forward otheruser@otherdomain.com via smtp to the other MX. Anyhow you should/must add this user to the table:
      Message 2 of 11 , Sep 1, 2006
      • 0 Attachment
        Alvaro Marín wrote:
        > Hi,
        >
        Hello,

        > I've configured Postfix to use MySQL tables with alias or forwardings:
        >
        > virtual_alias_domains=mysql:/etc/postfix/mysql-virtual_domains.cf
        > virtual_alias_maps=mysql:/etc/postfix/mysql-virtual_aliases.cf
        >
        > where alias_domains table has for example:
        >
        > "domain.com"
        >
        > and the alias_maps table:
        >
        > "oneuser","domain.com","otheruser@..."
        >
        > This example runs fine, but if for example I add "otherdomain.com" to
        > alias_domains table, it search for the user "otheruser" in MySQL table
        > (logically) instead of send the mail to the MX.
        >
        > Is there any way to send directly the message to the "otherdomain.com"'s
        > MX without searching on the table again?
        >
        You can use transport_maps. Forward otheruser@... via smtp
        to the other MX. Anyhow you should/must add this user to the table:
        "otheruser","otherdomain.com","otheruser@...". Postfix
        always looks in the table but the alias mapping is the same. It then can
        take the transport_maps to forward the message to a different server.

        > Other thing that I want to ask is that if the alias_maps table has only
        > 2 fields (alias and destination) I can use for example:
        >
        > @... -> ouser@...
        >
        > to forward all the accounts from @domain to other account, but if I
        > create that table with 3 fields (alias_user, alias_domain and
        > destination) this doesn't work...any idea?
        >
        This should have something to do with how you specify the MySQL-query
        and how you created the table. For MySQL it is a difference if you have
        an empty string for alias_user or if you have NULL.

        --
        Greetings
        Kai
      • Alvaro Marín
        Kai Fürstenberg wrote: Hello, ... I ve tried with that (configuring transport table too): mysql select * from email_domains; +---------------------------+
        Message 3 of 11 , Sep 1, 2006
        • 0 Attachment
          Kai Fürstenberg wrote:

          Hello,

          > You can use transport_maps. Forward otheruser@... via smtp
          > to the other MX. Anyhow you should/must add this user to the table:
          > "otheruser","otherdomain.com","otheruser@...". Postfix
          > always looks in the table but the alias mapping is the same. It then can
          > take the transport_maps to forward the message to a different server.

          I've tried with that (configuring transport table too):

          mysql> select * from email_domains;
          +---------------------------+
          | domain |
          +---------------------------+
          | gmail.com |
          | pruebas.domain.com |
          +---------------------------+

          mysql> select * from email_transports;
          +-----------+----------------+
          | domain | transport |
          +-----------+----------------+
          | gmail.com | smtp:gmail.com |
          +-----------+----------------+

          mysql> select * from email_aliases;
          +------------+---------------------------+----------------------+
          | alias_user | alias_domain | alias_dest |
          +------------+---------------------------+----------------------+
          | user | pruebas.domain.com | oneuser@... |
          | oneuser | gmail.com | oneuser@... |
          +------------+---------------------------+----------------------+

          If I send an email to "user@...":

          to=<oneuser@...>, orig_to=<user@...>, relay=none,
          delay=0.11, delays=0.08/0.01/0/0.03, dsn=5.0.0, status=bounced (User
          unknown in virtual alias table)

          MySQL log:

          select alias_dest from email_aliases where alias_user="user" and
          alias_domain="pruebas.domain.com"
          select domain from email_domains where domain="gmail.com"
          select alias_dest from email_aliases where alias_user="oneuser" and
          alias_domain="gmail.com"

          and then, only queries (included transport table) to bounce message to root.


          > This should have something to do with how you specify the MySQL-query
          > and how you created the table. For MySQL it is a difference if you have
          > an empty string for alias_user or if you have NULL.

          I've tried with NULL, ""... and nothing :(
          The table is:

          mysql> desc email_aliases;
          +--------------+-----------+------+-----+---------+-------+
          | Field | Type | Null | Key | Default | Extra |
          +--------------+-----------+------+-----+---------+-------+
          | alias_user | char(250) | YES | | NULL | |
          | alias_domain | char(250) | | | | |
          | alias_dest | char(250) | | | | |
          +--------------+-----------+------+-----+---------+-------+

          Thanks.

          Regards,
          Alvaro.
        • Kai Fürstenberg
          ... You need to specify the correct Mail-Server. gmail.com is no server, it s a domain. ... See above. Your postfix is unable to establish a connection to the
          Message 4 of 11 , Sep 1, 2006
          • 0 Attachment
            Alvaro Marín wrote:
            > Kai Fürstenberg wrote:
            >
            > Hello,
            >
            >
            >> You can use transport_maps. Forward otheruser@... via smtp
            >> to the other MX. Anyhow you should/must add this user to the table:
            >> "otheruser","otherdomain.com","otheruser@...". Postfix
            >> always looks in the table but the alias mapping is the same. It then can
            >> take the transport_maps to forward the message to a different server.
            >>
            >
            > I've tried with that (configuring transport table too):
            >
            > mysql> select * from email_domains;
            > +---------------------------+
            > | domain |
            > +---------------------------+
            > | gmail.com |
            > | pruebas.domain.com |
            > +---------------------------+
            >
            > mysql> select * from email_transports;
            > +-----------+----------------+
            > | domain | transport |
            > +-----------+----------------+
            > | gmail.com | smtp:gmail.com |
            > +-----------+----------------+
            >
            You need to specify the correct Mail-Server. gmail.com is no server,
            it's a domain.

            > mysql> select * from email_aliases;
            > +------------+---------------------------+----------------------+
            > | alias_user | alias_domain | alias_dest |
            > +------------+---------------------------+----------------------+
            > | user | pruebas.domain.com | oneuser@... |
            > | oneuser | gmail.com | oneuser@... |
            > +------------+---------------------------+----------------------+
            >
            > If I send an email to "user@...":
            >
            > to=<oneuser@...>, orig_to=<user@...>, relay=none,
            > delay=0.11, delays=0.08/0.01/0/0.03, dsn=5.0.0, status=bounced (User
            > unknown in virtual alias table)
            >
            > MySQL log:
            >
            > select alias_dest from email_aliases where alias_user="user" and
            > alias_domain="pruebas.domain.com"
            > select domain from email_domains where domain="gmail.com"
            > select alias_dest from email_aliases where alias_user="oneuser" and
            > alias_domain="gmail.com"
            >
            > and then, only queries (included transport table) to bounce message to root.
            >
            See above. Your postfix is unable to establish a connection to the
            server gmail.com.

            And.. Sorry, but that doesn't make any sense to me. Why do you want
            gmail.com to be a virtual domain for your system, when mail to this
            domain is going to be sent to a different server (gmail)?

            >> This should have something to do with how you specify the MySQL-query
            >> and how you created the table. For MySQL it is a difference if you have
            >> an empty string for alias_user or if you have NULL.
            >>
            >
            > I've tried with NULL, ""... and nothing :(
            > The table is:
            >
            > mysql> desc email_aliases;
            > +--------------+-----------+------+-----+---------+-------+
            > | Field | Type | Null | Key | Default | Extra |
            > +--------------+-----------+------+-----+---------+-------+
            > | alias_user | char(250) | YES | | NULL | |
            > | alias_domain | char(250) | | | | |
            > | alias_dest | char(250) | | | | |
            > +--------------+-----------+------+-----+---------+-------+
            >
            The queries for "@..." are also logged. Please post them.

            Kai
          • Alvaro Marín
            Kai Fürstenberg wrote: Hello, ... I supposed that the field value is managed like a normal transport table...it would have to resolve the MX record of
            Message 5 of 11 , Sep 1, 2006
            • 0 Attachment
              Kai Fürstenberg wrote:

              Hello,

              > You need to specify the correct Mail-Server. gmail.com is no server,
              > it's a domain.

              I supposed that the field value is managed like a normal transport
              table...it would have to resolve the MX record of "gmail.com".

              > See above. Your postfix is unable to establish a connection to the
              > server gmail.com.

              No transport queries are done for that domain, only for bounce.

              > And.. Sorry, but that doesn't make any sense to me. Why do you want
              > gmail.com to be a virtual domain for your system, when mail to this
              > domain is going to be sent to a different server (gmail)?

              Gmail is only an example. It is to prevent that one external domain can
              be added in the virtual domains table, so all the mails for that domain
              would be bounced.

              > The queries for "@..." are also logged. Please post them.

              Those queries are which I pasted. Nothing more...only for the bounce
              message then.

              Thanks for your time.

              Regards,
              Alvaro.
            • Kai Fürstenberg
              ... Then I have a suggestion. Extend your domains table by an additional column active (Boolean). Then add an additional where clause to your query: WHERE
              Message 6 of 11 , Sep 1, 2006
              • 0 Attachment
                Alvaro Marín wrote:
                > Kai Fürstenberg wrote:
                >
                > Hello,
                >
                >
                >> You need to specify the correct Mail-Server. gmail.com is no server,
                >> it's a domain.
                >>
                >
                > I supposed that the field value is managed like a normal transport
                > table...it would have to resolve the MX record of "gmail.com".
                >
                >
                >> See above. Your postfix is unable to establish a connection to the
                >> server gmail.com.
                >>
                >
                > No transport queries are done for that domain, only for bounce.
                >
                >
                >> And.. Sorry, but that doesn't make any sense to me. Why do you want
                >> gmail.com to be a virtual domain for your system, when mail to this
                >> domain is going to be sent to a different server (gmail)?
                >>
                >
                > Gmail is only an example. It is to prevent that one external domain can
                > be added in the virtual domains table, so all the mails for that domain
                > would be bounced.
                >
                Then I have a suggestion. Extend your domains table by an additional
                column "active" (Boolean). Then add an additional where clause to your
                query:
                WHERE active=1. Set active state of gmail to 0, the others to 1.
                This will prevent the addition of gmail com to your virtual domains and
                postfix does not recognise this as virtual domain, because of active
                state 0.
                This will also solve the transport problem.

                But why don't you just secure your database, that no one else has access
                to it? Then just leave away gmail.com. That would be much easier.

                Kai
              • Alvaro Marín
                Kai Fürstenberg wrote: Hello, ... Umm...I ll try it...good idea :) ... The database will be accessed (via PHP) by more people than me...but I ve been thinking
                Message 7 of 11 , Sep 1, 2006
                • 0 Attachment
                  Kai Fürstenberg wrote:

                  Hello,

                  > Then I have a suggestion. Extend your domains table by an additional
                  > column "active" (Boolean). Then add an additional where clause to your
                  > query:
                  > WHERE active=1. Set active state of gmail to 0, the others to 1.
                  > This will prevent the addition of gmail com to your virtual domains and
                  > postfix does not recognise this as virtual domain, because of active
                  > state 0.
                  > This will also solve the transport problem.

                  Umm...I'll try it...good idea :)

                  > But why don't you just secure your database, that no one else has access
                  > to it? Then just leave away gmail.com. That would be much easier.

                  The database will be accessed (via PHP) by more people than me...but
                  I've been thinking that perhaps putting a restriction in PHP to don't
                  let insert domains with the MX record different that the machine.

                  Thanks for all, Kai.

                  Regards,
                  Alvaro.
                • Kai Fürstenberg
                  ... You can also set permissions on single tables in a complete database. So you can set access rights for your users for the complete database except the
                  Message 8 of 11 , Sep 1, 2006
                  • 0 Attachment
                    Alvaro Marín wrote:
                    > Kai Fürstenberg wrote:
                    >
                    > Hello,
                    >
                    >
                    >> Then I have a suggestion. Extend your domains table by an additional
                    >> column "active" (Boolean). Then add an additional where clause to your
                    >> query:
                    >> WHERE active=1. Set active state of gmail to 0, the others to 1.
                    >> This will prevent the addition of gmail com to your virtual domains and
                    >> postfix does not recognise this as virtual domain, because of active
                    >> state 0.
                    >> This will also solve the transport problem.
                    >>
                    >
                    > Umm...I'll try it...good idea :)
                    >
                    >
                    >> But why don't you just secure your database, that no one else has access
                    >> to it? Then just leave away gmail.com. That would be much easier.
                    >>
                    >
                    > The database will be accessed (via PHP) by more people than me...but
                    > I've been thinking that perhaps putting a restriction in PHP to don't
                    > let insert domains with the MX record different that the machine.
                    >
                    You can also set permissions on single tables in a complete database. So
                    you can set access rights for your users for the complete database
                    except the domains table, where only you have access to.

                    Kai
                  • Wietse Venema
                    ... DO NOT list gmail.com in your own domain tables!! Mail will bounce with user unknown in virtual alias table . Wietse
                    Message 9 of 11 , Sep 1, 2006
                    • 0 Attachment
                      Alvaro Mar?n:
                      > Kai F_rstenberg wrote:
                      >
                      > Hello,
                      >
                      > > You can use transport_maps. Forward otheruser@... via smtp
                      > > to the other MX. Anyhow you should/must add this user to the table:
                      > > "otheruser","otherdomain.com","otheruser@...". Postfix
                      > > always looks in the table but the alias mapping is the same. It then can
                      > > take the transport_maps to forward the message to a different server.
                      >
                      > I've tried with that (configuring transport table too):
                      >
                      > mysql> select * from email_domains;
                      > +---------------------------+
                      > | domain |
                      > +---------------------------+
                      > | gmail.com |
                      > | pruebas.domain.com |
                      > +---------------------------+

                      DO NOT list gmail.com in your own domain tables!! Mail will
                      bounce with "user unknown in virtual alias table".

                      Wietse
                    • Alvaro Marín
                      Wietse Venema wrote: Hello Wietse, ... Yes, I know it, that is what I want to avoid :) Regards, Alvaro.
                      Message 10 of 11 , Sep 1, 2006
                      • 0 Attachment
                        Wietse Venema wrote:

                        Hello Wietse,

                        > DO NOT list gmail.com in your own domain tables!! Mail will
                        > bounce with "user unknown in virtual alias table".

                        Yes, I know it, that is what I want to avoid :)

                        Regards,
                        Alvaro.
                      • mouss
                        ... why do you add otherdomain.com to your alias_domains table if you want mail to go to their MX? ... yes, there is a way. don t put it in the table:) ...
                        Message 11 of 11 , Sep 1, 2006
                        • 0 Attachment
                          Alvaro Marín wrote:
                          > Hi,
                          >
                          > I've configured Postfix to use MySQL tables with alias or forwardings:
                          >
                          > virtual_alias_domains=mysql:/etc/postfix/mysql-virtual_domains.cf
                          >
                          > virtual_alias_maps=mysql:/etc/postfix/mysql-virtual_aliases.cf
                          >
                          > where alias_domains table has for example:
                          >
                          > "domain.com"
                          >
                          > and the alias_maps table:
                          >
                          > "oneuser","domain.com","otheruser@..."
                          >
                          > This example runs fine, but if for example I add "otherdomain.com" to
                          > alias_domains table, it search for the user "otheruser" in MySQL table
                          > (logically) instead of send the mail to the MX.
                          >
                          why do you add otherdomain.com to your alias_domains table if you want
                          mail to go to their MX?
                          > Is there any way to send directly the message to the "otherdomain.com"'s
                          > MX without searching on the table again?
                          >
                          yes, there is a way. don't put it in the table:)
                          > Other thing that I want to ask is that if the alias_maps table has only
                          > 2 fields (alias and destination) I can use for example:
                          >
                          > @... -> ouser@...
                          >
                          > to forward all the accounts from @domain to other account, but if I
                          > create that table with 3 fields (alias_user, alias_domain and
                          > destination) this doesn't work...any idea?
                          >

                          what doesn't work? anyway, "wildcard" aliases (@... =>
                          $destination) break recipient validation. don't return them. since
                          you're using mysql, you can do a lot of things. just say what you _want
                          to accomplish_ (not what you tried).
                        Your message has been successfully submitted and would be delivered to recipients shortly.