Problems with the Virtual Users And Domains With Postfix, Courier And MySQL tutorial

Discussion in 'HOWTO-Related Questions' started by wwinfrey, Jul 20, 2006.

  1. wwinfrey

    wwinfrey New Member

    Hello

    I've followed the instructions posted at http://www.howtoforge.com/virtual_postfix_mysql_quota_courier as best I could, but have run into a couple snags.

    It's worth mentioning that I'm a not a newbie at this stuff, although most of my experience with MTAs in the past have been with Sendmail and Qmail.

    My host OS is Ubuntu 6.06, and the first snag I ran into was trying to figure out where the amavisd.conf file should go. Apt-get installs v. 2.3.3-3 of amavisd-new, and this version no longer uses a monolithic amavisd.conf file, but rather a collection of files in /etc/amavisd/conf.d. I wound up sticking the contents of the article's suggested amavisd.conf into the 50-user file, and while it seems to be working, I'm wondering what the potential downfalls of this approach are, or how I would evaluate which directives are being employed or ignored.

    My second problem is that mail is getting delivered to /var/spool/mail/user, and not /home/vmail/domain/user, and how I might correct that. The .cf files in /etc/postfix are exactly as described in the how-to, and I can login to the POP server using courier-authdaemon/authmysql, but I can't get mail delivered to the /home/vmail directory.

    Any suggestions on these matters would be most welcome, including pointers to previous discussions I might have missed in my searching.
     
  2. wwinfrey

    wwinfrey New Member

    Couple more details about my setup

    Here's a few more details about my setup (I've substiuted domain.com for my own domain):

    main.cf:
    Code:
    # See /usr/share/postfix/main.cf.dist for a commented, more complete version
    
    
    # Debian specific:  Specifying a file name will cause the first
    # line of that file to be used as the name.  The Debian default
    # is /etc/mailname.
    #myorigin = /etc/mailname
    
    smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
    biff = no
    
    # appending .domain is the MUA's job.
    append_dot_mydomain = no
    
    # Uncomment the next line to generate "delayed mail" warnings
    #delay_warning_time = 4h
    
    # TLS parameters
    smtpd_tls_cert_file = /etc/postfix/smtpd.cert
    smtpd_tls_key_file = /etc/postfix/smtpd.key
    smtpd_use_tls = yes
    smtpd_tls_session_cache_database = btree:${queue_directory}/smtpd_scache
    smtp_tls_session_cache_database = btree:${queue_directory}/smtp_scache
    
    # See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
    # information on enabling SSL in the smtp client.
    
    myhostname = twinkie.domain.com
    alias_maps = hash:/etc/aliases
    alias_database = hash:/etc/aliases
    myorigin = /etc/mailname
    mydestination = twinkie.domain.com, localhost, localhost.localdomain
    relayhost =
    mynetworks = 127.0.0.0/8
    mailbox_size_limit = 0
    recipient_delimiter = +
    inet_interfaces = all
    virtual_alias_domains =
    virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf
    virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
    virtual_mailbox_base = /home/vmail
    virtual_uid_maps = static:5000
    virtual_gid_maps = static:5000
    smtpd_sasl_auth_enable = yes
    broken_sasl_auth_clients = yes
    smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
    transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf
    virtual_create_maildirsize = yes
    virtual_mailbox_extended = yes
    virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf
    virtual_mailbox_limit_override = yes
    virtual_maildir_limit_message = "The user you are trying to reach is over quota."
    virtual_overquota_bounce = yes
    proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps
    content_filter = amavis:[127.0.0.1]:10024
    receive_override_options = no_address_mappings
    
    Here's my mysql-virtual_mailboxes.cf file:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    table = users
    select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
    where_field = email
    hosts = 127.0.0.1
    
    I created a test domain, virtual.test in domains:

    Code:
    +-------------------------+
    | domain                  |
    +-------------------------+
    | virtual.test            |
    +-------------------------+
    
    And a test user, user@virtual.test in users:

    Code:
    +---------------------------------+---------------+----------+
    | email                           | password      | quota    |
    +---------------------------------+---------------+----------+
    | user@virtual.test               | mVlDbGf.MYRjA | 10485760 |
    +---------------------------------+---------------+----------+
    
    Then, I connected through telnet to send a test message:

    Code:
    % telnet localhost 25
    Trying 127.0.0.1...
    Connected to localhost.
    Escape character is '^]'.
    220 twinkie.domain.com ESMTP Postfix (Ubuntu)
    EHLO localhost
    250-twinkie.domain.com
    250-PIPELINING
    250-SIZE 10240000
    250-VRFY
    250-ETRN
    250-STARTTLS
    250-AUTH LOGIN PLAIN
    250-AUTH=LOGIN PLAIN
    250 8BITMIME
    MAIL FROM: <test@localhost>
    250 Ok
    RCPT TO: <user@virtual.test>
    250 Ok
    DATA
    354 End data with <CR><LF>.<CR><LF>
    Subject: This is a test email.
    Testing
    .
    250 Ok: queued as 0CEE63BB46EB
    quit
    221 Bye
    Connection closed by foreign host.
    
    Which resulted in the following in /var/log/mail.log:

    Code:
    Jul 20 15:23:58 twinkie postfix/smtpd[4354]: connect from localhost[127.0.0.1]
    Jul 20 15:23:58 twinkie postfix/smtpd[4354]: 077263BB46ED: client=localhost[127.0.0.1]
    Jul 20 15:23:58 twinkie postfix/cleanup[4351]: 077263BB46ED: message-id=<20060720212343.0CEE63BB46EB@twinkie.domain.com>
    Jul 20 15:23:58 twinkie postfix/qmgr[4332]: 077263BB46ED: from=<test@localhost>, size=795, nrcpt=1 (queue active)
    Jul 20 15:23:58 twinkie postfix/smtpd[4354]: disconnect from localhost[127.0.0.1]
    Jul 20 15:23:58 twinkie amavis[4056]: (04056-02) Passed, <test@localhost> -> <user@virtual.test>, quarantine PChEcyAyG+JY, Message-ID: <20060720212343.0CEE63BB46EB@twinkie.domain.com>, Hits: -
    Jul 20 15:23:58 twinkie postfix/smtp[4352]: 0CEE63BB46EB: to=<user@virtual.test>, relay=127.0.0.1[127.0.0.1], delay=26, status=sent (250 2.6.0 Ok, id=04056-02, from MTA([127.0.0.1]:10025
    ): 250 Ok: queued as 077263BB46ED)
    Jul 20 15:23:58 twinkie postfix/qmgr[4332]: 0CEE63BB46EB: removed
    Jul 20 15:23:58 twinkie postfix/smtp[4356]: 077263BB46ED: to=<user@virtual.test>, relay=none, delay=0, status=bounced (Host or domain name not found. Name service error for name=virtual.
    test type=A: Host not found)
    Jul 20 15:23:58 twinkie postfix/cleanup[4351]: 4D38F3BB46EE: message-id=<20060720212358.4D38F3BB46EE@twinkie.domain.com>
    Jul 20 15:23:58 twinkie postfix/qmgr[4332]: 4D38F3BB46EE: from=<>, size=2654, nrcpt=1 (queue active)
    Jul 20 15:23:58 twinkie postfix/qmgr[4332]: 077263BB46ED: removed
    Jul 20 15:23:58 twinkie postfix/local[4359]: 4D38F3BB46EE: to=<test@localhost>, relay=local, delay=0, status=bounced (unknown user: "test")
    Jul 20 15:23:58 twinkie postfix/qmgr[4332]: 4D38F3BB46EE: removed
    Jul 20 15:23:59 twinkie postfix/smtpd[4343]: disconnect from localhost[127.0.0.1]
    
    and the following in /var/log/mysq/mysql.log:

    Code:
    060720 15:23:57      73 Query       SELECT "Y" as local FROM domains WHERE CONCAT("@",domain) IN ('user@virtual.test','@virtual.test','@.virtual.test','@.test','@.')
    060720 15:23:58      91 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='localhost'
                         90 Query       SELECT transport FROM transport WHERE domain='test@localhost'
                         90 Query       SELECT transport FROM transport WHERE domain='localhost'
                         91 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='user@virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='.test'
                         92 Query       SELECT destination FROM forwardings WHERE source='user@virtual.test'
                         94 Connect     mail_admin@localhost on mail
                         94 Query       SELECT email FROM users WHERE email='user@virtual.test'
                         91 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='user@virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='virtual.test'
                         90 Query       SELECT transport FROM transport WHERE domain='.test'
                         92 Query       SELECT destination FROM forwardings WHERE source='test@localhost'
                         94 Query       SELECT email FROM users WHERE email='test@localhost'
                         92 Query       SELECT destination FROM forwardings WHERE source='test'
                         94 Query       SELECT email FROM users WHERE email='test'
                         92 Query       SELECT destination FROM forwardings WHERE source='@localhost'
                         94 Query       SELECT email FROM users WHERE email='@localhost'
                         91 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='localhost'
                         90 Query       SELECT transport FROM transport WHERE domain='test@localhost'
                         90 Query       SELECT transport FROM transport WHERE domain='localhost'
    
    From what I can tell, the MySQL query seems to be misformed:

    Code:
    SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='virtual.test'
    
    shouldn't this be:

    Code:
    SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='user@virtual.test'
    
    ?
     
  3. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

  4. wwinfrey

    wwinfrey New Member

    OK, I took a look at this post, and from there, looked at the Postfix mysql table man page at http://www.postfix.org/mysql_table.5.html.

    After reading about the differences in how these .cf files are formed, I made the following changes to the .cf tables.

    mysql-virtual_domains.cf:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    query = SELECT virtual FROM domains WHERE domain = '%s'
    hosts = 127.0.0.1
    
    mysql-virtual_email2email.cf:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    query = SELECT email FROM users WHERE email = '%s'
    hosts = 127.0.0.1
    
    mysql-virtual_forwardings.cf:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    query = SELECT destination FROM forwardings WHERE source = '%s'
    hosts = 127.0.0.1
    
    mysql-virtual_mailbox_limit_maps.cf:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    query = SELECT quota FROM users WHERE email = '%s'
    hosts = 127.0.0.1
    
    mysql-virtual_mailboxes.cf:

    Code:
    user=mail_admin
    password = password
    dbname = mail
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = '%s'
    hosts = 127.0.0.1
    
    mysql-virtual_transports.cf:

    Code:
    user = mail_admin
    password = password
    dbname = mail
    query = SELECT transport FROM transport WHERE domain = '%s'
    hosts = 127.0.0.1
    
    After putting in these changes, I restarted postfix. Now, when I try the same telnet test (to send mail to user@virtual.test), I get very similar results to earlier:

    /var/log/mysql/mysql.log:

    Code:
    060721 13:13:21     122 Connect     mail_admin@localhost on mail
                        122 Query       set autocommit=1
                        122 Query       SELECT "Y" as local FROM domains WHERE CONCAT("@",domain) IN ('user@virtual.test','@virtual.test','@.virtual.test','@.test','@.')
                        119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'localhost'
                        118 Query       SELECT transport FROM transport WHERE domain = 'test@localhost'
                        118 Query       SELECT transport FROM transport WHERE domain = 'localhost'
                        119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = 'user@virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = 'virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = '.test'
                        120 Query       SELECT destination FROM forwardings WHERE source = 'user@virtual.test'
                        123 Connect     mail_admin@localhost on mail
                        123 Query       SELECT email FROM users WHERE email = 'user@virtual.test'
                        119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = 'user@virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = 'virtual.test'
                        118 Query       SELECT transport FROM transport WHERE domain = '.test'
                        120 Query       SELECT destination FROM forwardings WHERE source = 'test@localhost'
                        123 Query       SELECT email FROM users WHERE email = 'test@localhost'
                        120 Query       SELECT destination FROM forwardings WHERE source = 'test'
                        123 Query       SELECT email FROM users WHERE email = 'test'
                        120 Query       SELECT destination FROM forwardings WHERE source = '@localhost'
                        123 Query       SELECT email FROM users WHERE email = '@localhost'
                        119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'localhost'
                        118 Query       SELECT transport FROM transport WHERE domain = 'test@localhost'
                        118 Query       SELECT transport FROM transport WHERE domain = 'localhost'
    
    and from /var/log/mail.log:

    Code:
    Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 133CA3BB466F: message-id=<20060721191239.133CA3BB466F@twinkie.domain.com>
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 133CA3BB466F: from=<test@localhost>, size=388, nrcpt=1 (queue active)
    Jul 21 13:13:21 twinkie amavis[4054]: (04054-02) NOTICE: reconnecting in response to: sql execute: sts=2006, DBD::mysql::st execute failed: MySQL server has gone away at (eval 41) line 1
    27, <GEN29> line 5.
    Jul 21 13:13:21 twinkie postfix/smtpd[6761]: connect from localhost[127.0.0.1]
    Jul 21 13:13:21 twinkie postfix/smtpd[6761]: 5C13B3BB4670: client=localhost[127.0.0.1]
    Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 5C13B3BB4670: message-id=<20060721191239.133CA3BB466F@twinkie.domain.com>
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 5C13B3BB4670: from=<test@localhost>, size=792, nrcpt=1 (queue active)
    Jul 21 13:13:21 twinkie postfix/smtpd[6761]: disconnect from localhost[127.0.0.1]
    Jul 21 13:13:21 twinkie amavis[4054]: (04054-02) Passed, <test@localhost> -> <user@virtual.test>, quarantine lSSGqyu4ahkW, Message-ID: <20060721191239.133CA3BB466F@twinkie.domain.co
    m>, Hits: -
    Jul 21 13:13:21 twinkie postfix/smtp[6758]: 133CA3BB466F: to=<user@virtual.test>, relay=127.0.0.1[127.0.0.1], delay=55, status=sent (250 2.6.0 Ok, id=04054-02, from MTA([127.0.0.1]:10025
    ): 250 Ok: queued as 5C13B3BB4670)
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 133CA3BB466F: removed
    Jul 21 13:13:21 twinkie postfix/smtp[6763]: 5C13B3BB4670: to=<user@virtual.test>, relay=none, delay=0, status=bounced (Host or domain name not found. Name service error for name=virtual.
    test type=A: Host not found)
    Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 8B2E53BB46EE: message-id=<20060721191321.8B2E53BB46EE@twinkie.domain.com>
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 8B2E53BB46EE: from=<>, size=2651, nrcpt=1 (queue active)
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 5C13B3BB4670: removed
    Jul 21 13:13:21 twinkie postfix/local[6765]: 8B2E53BB46EE: to=<test@localhost>, relay=local, delay=0, status=bounced (unknown user: "test")
    Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 8B2E53BB46EE: removed
    Jul 21 13:13:22 twinkie postfix/smtpd[6750]: disconnect from localhost[127.0.0.1]
    
    It appears the problem is in the query executed on the mail.users table is incorrect. It's ending with "WHERE email = 'virtual.test'" instead of "WHERE email = 'user@virtual.test'". Any idea why the %s substitution is failing ? It seems to be the correct query everywhere else except for virtual_mailboxes.
     
  5. wwinfrey

    wwinfrey New Member

    FWIW, I've also tried substituting '%s' with '%u@%d' and '%1@%2.%3', but when I put this in instead of '%s', it suppresses the query altogether and does not run it, which is very frustrating.
     
  6. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Can you try

    Code:
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE [B][COLOR="Red"]email='%s'[/COLOR][/B]
    (no spaces)

    instead of

    Code:
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE [B][COLOR="Red"]email = '%s'[/COLOR][/B]
    ?

    Do the same for the other queries and restart Postfix.
     
  7. wwinfrey

    wwinfrey New Member

    OK, done, but it didn't make a difference. Same results.

    Code:
    mysql-virtual_domains.cf:query = SELECT virtual FROM domains WHERE domain='%s'
    mysql-virtual_email2email.cf:query = SELECT email FROM users WHERE email='%s'
    mysql-virtual_forwardings.cf:query = SELECT destination FROM forwardings WHERE source='%s'
    mysql-virtual_mailboxes.cf:query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
    mysql-virtual_mailbox_limit_maps.cf:query = SELECT quota FROM users WHERE email='%s'
    mysql-virtual_transports.cf:query = SELECT transport FROM transport WHERE domain='%s'
    
     
  8. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Hm, then I have no idea what's wrong...
     
  9. wwinfrey

    wwinfrey New Member

  10. Guido64

    Guido64 New Member

    hello,

    I have a problem with my mail server.
    I don't know if it is important to mention but I installed the virtual postfix mail server into a openvz vps.

    First all works fine, spam filter, anti virus, everything works fine when I send a spam test or eicar mail.

    After few test mails things start to go wrong.

    syslog:
    ------------------------
    Aug 13 19:07:01 mail amavis[16283]: (16283-02) lookup_sql: 2006, MySQL server has gone away
    Aug 13 19:07:01 mail amavis[16283]: (16283-02) NOTICE: Disconnected from SQL server
    Aug 13 19:07:01 mail amavis[16283]: (16283-02) TROUBLE in check_mail: creating_partsdir FAILED: DBD::mysql::st execute failed: MySQL server has gone away at (eval 38) line 238, <GEN17> line 240.
    Aug 13 19:07:01 mail amavis[16283]: (16283-02) PRESERVING EVIDENCE in /var/lib/amavis/amavis-20060813T051002-16283
    -------------------------

    Amavis doesn't see the mysql server anymore wich is also running in a seperate openvz vps.
    Wen i check the mysql server it is online and working.
    I use ip numbers instead of host names so it can't be a dns problem.
    Mysql and dns servers are running oke.
    Amavis can't reach mysql anyway.

    After I restart the mail vps everything works fine again.
    when i send few test mails the server fails again.

    I searched google and the forum messages couldn't find a posting with a solution to this problem.

    Anybody?
     
  11. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    I once had similar problems with Postfix in OpenVZ that I couldn't find a solution for...:(
     
  12. Guido64

    Guido64 New Member

    Oow that doesn't sound to well :)
    What is your experience with other virtualisation software?

    I changed some settings in de amavis config file.
    $child_timeout=5*60;
    to this
    $child_timeout=5*120;

    and commented some others out
    # $inet_socket_bind = '127.0.0.1';
    # @inet_acl = qw( 127.0.0.1 );

    I don't know if it is relevant what I changed but i'm testing the mail server all day and its still running fine. I constantly tail the mail.log.
    If it stays running I start changed the settings again to find out what exactly is cousing this trouble
     
  13. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Never had such problems with Xen and VMware. :)
     

Share This Page