Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAs

Discussion in 'HOWTO-Related Questions' started by mholownych, May 27, 2006.

  1. mholownych

    mholownych New Member

    Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAssassin, ClamAV) Howto ????

    I am not able to receive any mail for any of my users. Here are the config files and logs.. along with the log entries. Please help!!! I am lost.. and unsure how to continue..

    netstat -tap
    Code:
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 *:imaps                 *:*                     LISTEN     1189/couriertcpd
    tcp        0      0 *:pop3s                 *:*                     LISTEN     1212/couriertcpd
    tcp        0      0 localhost.localdo:10024 *:*                     LISTEN     1108/amavisd (maste
    tcp        0      0 localhost.localdo:10025 *:*                     LISTEN     1619/master
    tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     1281/mysqld
    tcp        0      0 *:pop3                  *:*                     LISTEN     1198/couriertcpd
    tcp        0      0 *:imap2                 *:*                     LISTEN     1175/couriertcpd
    tcp        0      0 *:www                   *:*                     LISTEN     1878/apache2
    tcp        0      0 *:ftp                   *:*                     LISTEN     1455/proftpd: (acce
    tcp        0      0 ns1.pgehosting.c:domain *:*                     LISTEN     1096/named
    tcp        0      0 localhost.locald:domain *:*                     LISTEN     1096/named
    tcp        0      0 *:ssh                   *:*                     LISTEN     1444/sshd
    tcp        0      0 *:smtp                  *:*                     LISTEN     1619/master
    tcp        0      0 localhost.localdoma:953 *:*                     LISTEN     1096/named
    tcp        0      0 *:https                 *:*                     LISTEN     1878/apache2
    tcp        0      0 localhost.localdo:mysql localhost.localdo:32778 ESTABLISHED1281/mysqld
    tcp        0      0 localhost.localdo:mysql localhost.localdo:32788 ESTABLISHED1281/mysqld
    tcp        0      0 localhost.localdo:mysql localhost.localdo:32826 ESTABLISHED1281/mysqld
    tcp        0      0 localhost.localdo:mysql localhost.localdo:32857 ESTABLISHED1281/mysqld
    tcp        0      0 localhost.localdo:32857 localhost.localdo:mysql ESTABLISHED1442/amavisd (child
    tcp        0      0 localhost.localdo:32826 localhost.localdo:mysql ESTABLISHED1441/amavisd (child
    tcp        0      0 localhost.localdo:32778 localhost.localdo:mysql ESTABLISHED1439/amavisd (child
    tcp        0      0 localhost.localdo:32788 localhost.localdo:mysql ESTABLISHED1440/amavisd (child
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:2027 ESTABLISHED2197/imapd
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:2026 ESTABLISHED2195/imapd
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:1857 ESTABLISHED2148/imapd
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:2147 ESTABLISHED2201/imapd
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:2146 ESTABLISHED2199/imapd
    tcp        0      0 ns1.pgehosting.co:imap2 CPE0014bf0f2b0c-CM:2148 ESTABLISHED2203/imapd
    tcp        0   4148 ns1.pgehosting.com:ssh  CPE0014bf0f2b0c-CM:4874 ESTABLISHED1487/1
    tcp        0      0 ns1.pgehosting.com:ssh  CPE0014bf0f2b0c-CM:4873 ESTABLISHED1478/0
    
    master.cf
    Code:
    # ==========================================================================
    # service type  private unpriv  chroot  wakeup  maxproc command + args
    #               (yes)   (yes)   (yes)   (never) (100)
    # ==========================================================================
    smtp      inet  n       -       -       -       -       smtpd
    #submission inet n      -       -       -       -       smtpd
    #       -o smtpd_etrn_restrictions=reject
    #628      inet  n       -       -       -       -       qmqpd
    pickup    fifo  n       -       -       60      1       pickup
    cleanup   unix  n       -       -       -       0       cleanup
    qmgr      fifo  n       -       -       300     1       qmgr
    #qmgr     fifo  n       -       -       300     1       oqmgr
    rewrite   unix  -       -       -       -       -       trivial-rewrite
    bounce    unix  -       -       -       -       0       bounce
    defer     unix  -       -       -       -       0       bounce
    trace     unix  -       -       -       -       0       bounce
    verify    unix  -       -       -       -       1       verify
    flush     unix  n       -       -       1000?   0       flush
    proxymap  unix  -       -       n       -       -       proxymap
    smtp      unix  -       -       -       -       -       smtp
    relay     unix  -       -       -       -       -       smtp
    #       -o smtp_helo_timeout=5 -o smtp_connect_timeout=5
    showq     unix  n       -       -       -       -       showq
    error     unix  -       -       -       -       -       error
    local     unix  -       n       n       -       -       local
    virtual   unix  -       n       n       -       -       virtual
    lmtp      unix  -       -       n       -       -       lmtp
    anvil     unix  -       -       n       -       1       anvil
    #
    # Interfaces to non-Postfix software. Be sure to examine the manual
    # pages of the non-Postfix software to find out what options it wants.
    #
    # maildrop. See the Postfix MAILDROP_README file for details.
    #
    maildrop  unix  -       n       n       -       -       pipe
      flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}
    uucp      unix  -       n       n       -       -       pipe
      flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)
    ifmail    unix  -       n       n       -       -       pipe
      flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)
    bsmtp     unix  -       n       n       -       -       pipe
      flags=Fq. user=bsmtp argv=/usr/lib/bsmtp/bsmtp -d -t$nexthop -f$sender $recipient
    scalemail-backend unix  -       n       n       -       2       pipe
      flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store ${nexthop} ${user} ${extension}
    
    # only used by postfix-tls
    #tlsmgr   fifo  -       -       n       300     1       tlsmgr
    #smtps    inet  n       -       n       -       -       smtpd -o smtpd_tls_wrappermode=yes -o smtpd_sasl_auth_enable=yes
    #587      inet  n       -       n       -       -       smtpd -o smtpd_enforce_tls=yes -o smtpd_sasl_auth_enable=yes
    
    amavis unix - - - - 2 smtp
            -o smtp_data_done_timeout=1200
            -o smtp_send_xforward_command=yes
    
    127.0.0.1:10025 inet n - - - - smtpd
            -o content_filter=
            -o local_recipient_maps=
            -o relay_recipient_maps=
            -o smtpd_restriction_classes=
            -o smtpd_client_restrictions=
            -o smtpd_helo_restrictions=
            -o smtpd_sender_restrictions=
            -o smtpd_recipient_restrictions=permit_mynetworks,reject
            -o mynetworks=127.0.0.0/8
            -o strict_rfc821_envelopes=yes
            -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
            -o smtpd_bind_address=127.0.0.1
    
    main.cf
    Code:
    # See /usr/share/postfix/main.cf.dist for a commented, more complete version
    
    smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
    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
    
    myhostname = mail.pgehosting.com
    alias_maps = hash:/etc/aliases
    alias_database = hash:/etc/aliases
    myorigin = /etc/mailname
    mydestination = mail.pgehosting.com, localhost, localhost.localdomain
    relayhost =
    mynetworks = 127.0.0.0/8, 38.116.195.0/24
    mailbox_command = procmail -a "$EXTENSION"
    mailbox_size_limit = 0
    recipient_delimiter = +
    inet_interfaces = all
    virtual_alias_domains = pgesystems.com, amhcomputing.com, pgehosting.com
    virtual_alias_maps = mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf
    virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual_domains.cf
    virtual_mailbox_maps = 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
    smtpd_use_tls = yes
    smtpd_tls_cert_file = /etc/postfix/smtpd.cert
    smtpd_tls_key_file = /etc/postfix/smtpd.key
    transport_maps = mysql:/etc/postfix/mysql-virtual_transports.cf
    virtual_create_maildirsize = yes
    virtual_mailbox_extended = yes
    virtual_mailbox_limit_maps = 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
    
    mysql-virtual_domains.cf
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = domains
    select_field = 'virtual'
    where_field = domain
    hosts = 127.0.0.1
    
    mysql-virtual_email2email.cf
    PHP:
    password mail_admin_password
    dbname 
    mail
    table 
    users
    select_field 
    email
    where_field 
    email
    hosts 
    127.0.0.1
    continued in next post...
     
  2. mholownych

    mholownych New Member

    mysql-virtual_forwardings.cf
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = forwardings
    select_field = destination
    where_field = source
    hosts = 127.0.0.1
    
    mysql-virtual_mailboxes.cf
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = users
    select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
    where_field = email
    hosts = 127.0.0.1
    
    mysql-virtual_mailbox_limit_maps.cf
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = users
    select_field = quota
    where_field = email
    hosts = 127.0.0.1
    
    mysql-virtual_transports.cf
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = transport
    select_field = transport
    where_field = domain
    hosts = 127.0.0.1
    
    commands run:
    mail:/var/log# /etc/init.d/postfix restart
    Stopping mail transport agent: Postfix.
    Starting mail transport agent: Postfix.
    mail:/var/log# postfix check
    mail:/var/log# telnet localhost 25
    Trying 127.0.0.1...
    Connected to localhost.localdomain.
    Escape character is '^]'.
    220 mail.pgehosting.com ESMTP Postfix (Debian/GNU)
    helo pgesystems.com
    250 mail.pgehosting.com
    mail from:mike@pgesystems.com
    250 Ok
    rcpt to:mike@pgesystems.com
    250 Ok
    data
    354 End data with <CR><LF>.<CR><LF>
    subject:test
    test
    .
    250 Ok: queued as 7F0A11F2BC
    quit
    221 Bye
    Connection closed by foreign host.
    [/CODE]

    logs:
    Code:
    May 27 00:05:08 mail postfix/postfix-script: stopping the Postfix mail system
    May 27 00:05:08 mail postfix/master[2791]: terminating on signal 15
    May 27 00:05:08 mail postfix/postfix-script: starting the Postfix mail system
    May 27 00:05:08 mail postfix/master[2982]: daemon started -- version 2.1.5
    May 27 00:05:15 mail postfix/smtpd[3050]: connect from localhost.localdomain[127.0.0.1]
    May 27 00:05:41 mail postfix/smtpd[3050]: 7F0A11F2BC: client=localhost.localdomain[127.0.0.1]
    May 27 00:05:52 mail postfix/cleanup[3057]: 7F0A11F2BC: message-id=<20060527040533.7F0A11F2BC@mail.pgehosting.com>
    May 27 00:05:52 mail postfix/qmgr[2986]: 7F0A11F2BC: from=<mike@pgesystems.com>, size=378, nrcpt=1 (queue active)
    May 27 00:05:53 mail dccproc[3062]: socket(UDP): Address family not supported by protocol
    May 27 00:05:53 mail postfix/smtpd[3064]: connect from localhost.localdomain[127.0.0.1]
    May 27 00:05:53 mail postfix/smtpd[3064]: 72B741F2BF: client=localhost.localdomain[127.0.0.1]
    May 27 00:05:53 mail postfix/cleanup[3057]: 72B741F2BF: message-id=<20060527040533.7F0A11F2BC@mail.pgehosting.com>
    May 27 00:05:53 mail postfix/qmgr[2986]: 72B741F2BF: from=<mike@pgesystems.com>, size=853, nrcpt=1 (queue active)
    May 27 00:05:53 mail amavis[1441]: (01441-03) Passed, <mike@pgesystems.com> -> <mike@pgesystems.com>, Message-ID: <20060527040533.7F0A11F2BC@mail.pgehosting.com>, Hits: -1.251
    May 27 00:05:53 mail postfix/smtp[3058]: 7F0A11F2BC: to=<mike@pgesystems.com>, relay=127.0.0.1[127.0.0.1], delay=20, status=sent (250 2.6.0 Ok, id=01441-03, from MTA: 250 Ok: queued as 72B741F2BF)
    May 27 00:05:53 mail postfix/qmgr[2986]: 7F0A11F2BC: removed
    May 27 00:05:53 mail postfix/smtpd[3064]: disconnect from localhost.localdomain[127.0.0.1]
    May 27 00:05:53 mail postfix/error[3067]: 72B741F2BF: to=<mike@pgesystems.com>, relay=none, delay=0, status=bounced (User unknown in virtual alias table)
    May 27 00:05:53 mail postfix/cleanup[3057]: 80BBC1F2C0: message-id=<20060527040553.80BBC1F2C0@mail.pgehosting.com>
    May 27 00:05:53 mail postfix/qmgr[2986]: 80BBC1F2C0: from=<>, size=2545, nrcpt=1 (queue active)
    May 27 00:05:53 mail postfix/qmgr[2986]: 72B741F2BF: removed
    May 27 00:05:53 mail postfix/error[3067]: 80BBC1F2C0: to=<mike@pgesystems.com>, relay=none, delay=0, status=bounced (User unknown in virtual alias table)
    May 27 00:05:53 mail postfix/qmgr[2986]: 80BBC1F2C0: removed
    May 27 00:05:54 mail postfix/smtpd[3050]: disconnect from localhost.localdomain[127.0.0.1]
    
     
  3. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Why do you put your domains here? You must put them into the domains table in the MySQL database.

    Please comment out that line in /etc/postfix/main.cf and restart Postfix.
     
  4. mholownych

    mholownych New Member

    After following your suggestions... here is the log file..

    Code:
    May 27 09:30:19 mail postfix/postfix-script: stopping the Postfix mail system
    May 27 09:30:19 mail postfix/master[4482]: terminating on signal 15
    May 27 09:30:19 mail postfix/postfix-script: starting the Postfix mail system
    May 27 09:30:19 mail postfix/master[4701]: daemon started -- version 2.1.5
    May 27 09:30:30 mail postfix/smtpd[4769]: connect from localhost.localdomain[127.0.0.1]
    May 27 09:31:00 mail postfix/smtpd[4769]: 9C0BE1F2BD: client=localhost.localdomain[127.0.0.1]
    May 27 09:31:11 mail postfix/cleanup[4776]: 9C0BE1F2BD: message-id=<20060527133051.9C0BE1F2BD@mail.pgehosting.com>
    May 27 09:31:11 mail postfix/qmgr[4705]: 9C0BE1F2BD: from=<mike@pgehosting.com>, size=378, nrcpt=1 (queue active)
    May 27 09:31:13 mail dccproc[4784]: socket(UDP): Address family not supported by protocol
    May 27 09:31:13 mail postfix/smtpd[4786]: connect from localhost.localdomain[127.0.0.1]
    May 27 09:31:13 mail postfix/smtpd[4786]: 89C041F2C2: client=localhost.localdomain[127.0.0.1]
    May 27 09:31:13 mail postfix/cleanup[4776]: 89C041F2C2: message-id=<20060527133051.9C0BE1F2BD@mail.pgehosting.com>
    May 27 09:31:13 mail postfix/qmgr[4705]: 89C041F2C2: from=<mike@pgehosting.com>, size=853, nrcpt=1 (queue active)
    May 27 09:31:13 mail postfix/smtpd[4786]: disconnect from localhost.localdomain[127.0.0.1]
    May 27 09:31:13 mail amavis[3429]: (03429-01) Passed, <mike@pgehosting.com> -> <mike@pgesystems.com>, Message-ID: <20060527133051.9C0BE1F2BD@mail.pgehosting.com>, Hits: -1.251
    May 27 09:31:13 mail postfix/smtp[4779]: 9C0BE1F2BD: to=<mike@pgesystems.com>, relay=127.0.0.1[127.0.0.1], delay=22, status=sent (250 2.6.0 Ok, id=03429-01, from MTA: 250 Ok: queued as 89C041F2C2)
    May 27 09:31:13 mail postfix/qmgr[4705]: 9C0BE1F2BD: removed
    May 27 09:31:13 mail postfix/smtp[4787]: 89C041F2C2: to=<mike@pgesystems.com>, relay=none, delay=0, status=bounced (mail for mail.pgesystems.com loops back to myself)
    May 27 09:31:13 mail postfix/cleanup[4776]: B6EAE1F2BD: message-id=<20060527133113.B6EAE1F2BD@mail.pgehosting.com>
    May 27 09:31:13 mail postfix/qmgr[4705]: B6EAE1F2BD: from=<>, size=2573, nrcpt=1 (queue active)
    May 27 09:31:13 mail postfix/qmgr[4705]: 89C041F2C2: removed
    May 27 09:31:13 mail postfix/smtp[4787]: B6EAE1F2BD: to=<mike@pgehosting.com>, relay=none, delay=0, status=bounced (mail for mail.pgehosting.com loops back to myself)
    May 27 09:31:13 mail postfix/qmgr[4705]: B6EAE1F2BD: removed
    May 27 09:31:13 mail postfix/smtpd[4769]: disconnect from localhost.localdomain[127.0.0.1]
    
     
  5. mholownych

    mholownych New Member

    after doing some reading I have also changed the line:

    Code:
    virtual_alias_domains =
    to:

    Code:
    virtual_alias_domains = $virtual_alias_maps
    nothing has changed.. I'm still getting the mail for mail.pgehosting.com loops back to myself error.
     
  6. mholownych

    mholownych New Member

    nm... I got it...

    removed all entries in the transport table... everything is working now.

    Thank you for all your help.
     
  7. mholownych

    mholownych New Member

    on another note... how do I add or change a port for the smtp. My home provider blocks outgoing on port 25.
     
  8. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

  9. mholownych

    mholownych New Member

    The server itself is sitting in my datacenter. I am not able to send mail from home.. what I want to do is add an additional port that smtp will listen to on top of port 25 so that I can bypass the port 25 block on my home ISP.
     
  10. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

  11. mholownych

    mholownych New Member

    Thank you... everything is now working swimmingly... I will definitly recommend these howtos to everyone I know!!
     

Share This Page