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

  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

    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

    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

    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

    Never had such problems with Xen and VMware. :)
     

Share This Page