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

    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

  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

  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