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

Re: Forwards with MySQL

Expand Messages
  • 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 1 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 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.