Virtual Users And Domains With Postfix, Courier And MySQL not delivery

Discussion in 'HOWTO-Related Questions' started by redix, Aug 7, 2013.

  1. redix

    redix New Member

    mail is not working
    I think modify is something wrong.
    /var/log/mail.log include
    --------------------------------------------------------------------------------
    Aug 7 10:58:47 sbczn postfix/smtpd[19594]: B7265CF7B: client=localhost.localdomain[127.0.0.1]
    Aug 7 10:58:47 sbczn postfix/cleanup[19598]: warning: B7265CF7B: virtual_alias_maps map lookup problem for [email protected]
    Aug 7 10:58:47 sbczn postfix/cleanup[19775]: warning: EA6CDCF7C: virtual_alias_maps map lookup problem for [email protected]
    Aug 7 10:58:47 sbczn postfix/smtpd[19611]: disconnect from localhost.localdomain[127.0.0.1]
    Aug 7 10:58:50 sbczn amavis[18153]: (18153-06-18) Negative SMTP response to data-dot (<[email protected]>): 451 4.3.0 Error: queue file write error
    Aug 7 10:58:50 sbczn amavis[18153]: (18153-06-18) (!)FWD via SMTP: <[email protected]> -> <[email protected]>,BODY=7BIT 451 4.3.0 TempFailed, id=18153-06-18, from MTA([127.0.0.1]:10025): 451 4.3.0 Error: queue file write error
    Aug 7 10:58:50 sbczn amavis[18153]: (18153-06-18) Blocked MTA-BLOCKED, <[email protected]> -> <[email protected]>, Message-ID: <[email protected]>, mail_id: H1BEEQFufYCV, Hits: -0.001, size: 305, 17906 ms
    Aug 7 10:58:51 sbczn postfix/smtp[19587]: EEFC1CF56: to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, conn_use=18, delay=152, delays=53/81/0/18, dsn=4.3.0, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.3.0 TempFailed, id=18153-06-18, from MTA([127.0.0.1]:10025): 451 4.3.0 Error: queue file write error (in reply to end of DATA command))
    Aug 7 10:58:54 sbczn postfix/cleanup[19775]: warning: 8F552CF7B: virtual_alias_maps map lookup problem for [email protected]
    Aug 7 10:58:54 sbczn postfix/smtpd[19594]: disconnect from localhost.localdomain[127.0.0.1]
    ^C
    [email protected]:~# tail -f /var/log/mail.log
    Aug 7 11:05:32 sbczn postfix/smtpd[19941]: disconnect from localhost.localdomain[127.0.0.1]
    Aug 7 11:05:32 sbczn amavis[19878]: (19878-02) Blocked MTA-BLOCKED, <[email protected]> -> <[email protected]>, Message-ID: <[email protected]>, mail_id: hRyhYmq+mKrz, Hits: -0.001, size: 305, 8245 ms
    Aug 7 11:05:32 sbczn postfix/smtp[20130]: 92BF9CF55: to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, delay=505, delays=497/0.16/0.22/8.2, dsn=4.3.0, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.3.0 TempFailed, id=19878-02, from MTA([127.0.0.1]:10025): 451 4.3.0 Error: queue file write error (in reply to end of DATA command))
    Aug 7 11:05:40 sbczn postfix/smtpd[19941]: connect from localhost.localdomain[127.0.0.1]
    Aug 7 11:05:40 sbczn postfix/smtpd[19941]: 77533CF7B: client=localhost.localdomain[127.0.0.1]
    Aug 7 11:05:40 sbczn postfix/cleanup[20143]: warning: 77533CF7B: virtual_alias_maps map lookup problem for [email protected]
    Aug 7 11:05:40 sbczn amavis[19886]: (19886-01) Negative SMTP response to data-dot (<[email protected]>): 451 4.3.0 Error: queue file write error
    Aug 7 11:05:40 sbczn amavis[19886]: (19886-01) (!)FWD via SMTP: <[email protected]> -> <[email protected]>,BODY=7BIT 451 4.3.0 TempFailed, id=19886-01, from MTA([127.0.0.1]:10025): 451 4.3.0 Error: queue file write error
    Aug 7 11:05:40 sbczn amavis[19886]: (19886-01) Blocked MTA-BLOCKED, <[email protected]> -> <[email protected]>, Message-ID: <[email protected]>, mail_id: ypcCGxcJfLPs, Hits: -0.001, size: 305, 16562 ms
    Aug 7 11:05:40 sbczn postfix/smtp[20129]: EEFC1CF56: to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, delay=562, delays=545/0.18/0.24/16, dsn=4.3.0, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.3.0 TempFailed, id=19886-01, from MTA([127.0.0.1]:10025): 451 4.3.0 Error: queue file write error (in reply to end of DATA command))
    -------------------------------------------------------------------------------

    and /postconf -n include

    ------------------------------------------------------------------------------------
    alias_database = hash:/etc/aliases
    alias_maps = hash:/etc/aliases
    append_dot_mydomain = no
    biff = no
    broken_sasl_auth_clients = yes
    config_directory = /etc/postfix
    content_filter = amavis:[127.0.0.1]:10024
    default_transport = smtp
    html_directory = /usr/share/doc/postfix/html
    inet_interfaces = all
    inet_protocols = ipv4
    mailbox_size_limit = 0
    mydestination = localhost.localdomain, localhost
    myhostname = some
    mynetworks = 127.0.0.0/8
    myorigin = /etc/mailname
    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
    readme_directory = /usr/share/doc/postfix
    receive_override_options = no_address_mappings
    recipient_delimiter = +
    relay_transport = smtp
    relayhost =
    smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
    smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
    smtpd_sasl_auth_enable = yes
    smtpd_sasl_authenticated_header = yes
    transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf
    virtual_alias_domains =
    virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf
    virtual_gid_maps = static:5000
    virtual_mailbox_base = /home/vmail
    virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
    virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
    virtual_uid_maps = static:5000
    ------------------------------------------------------------------------------------
    and master.cf
    -----------------------------------------------------------------------------------------------------------------------
    cat /etc/postfix/master.cf
    #
    # Postfix master process configuration file. For details on the format
    # of the file, see the master(5) manual page (command: "man 5 master").
    #
    # Do not forget to execute "postfix reload" after editing this file.
    #
    # ==========================================================================
    # service type private unpriv chroot wakeup maxproc command + args
    # (yes) (yes) (yes) (never) (100)
    # ==========================================================================
    smtp inet n - - - - smtpd
    #submission inet n - - - - smtpd
    # -o smtpd_tls_security_level=encrypt
    # -o smtpd_sasl_auth_enable=yes
    # -o smtpd_client_restrictions=permit_sasl_authenticated,reject
    # -o milter_macro_daemon_name=ORIGINATING
    #smtps inet n - - - - smtpd
    # -o smtpd_tls_wrappermode=yes
    # -o smtpd_sasl_auth_enable=yes
    # -o smtpd_client_restrictions=permit_sasl_authenticated,reject
    # -o milter_macro_daemon_name=ORIGINATING
    #628 inet n - - - - qmqpd
    pickup fifo n - - 60 1 pickup
    cleanup unix n - - - 0 cleanup
    qmgr fifo n - n 300 1 qmgr
    #qmgr fifo n - - 300 1 oqmgr
    tlsmgr unix - - - 1000? 1 tlsmgr
    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
    proxywrite unix - - n - 1 proxymap
    smtp unix - - - - - smtp
    # When relaying mail as backup MX, disable fallback_relay to avoid MX loops
    relay unix - - - - - smtp
    -o smtp_fallback_relay=
    # -o smtp_helo_timeout=5 -o smtp_connect_timeout=5
    showq unix n - - - - showq
    error unix - - - - - error
    retry unix - - - - - error
    discard unix - - - - - discard
    local unix - n n - - local
    virtual unix - n n - - virtual
    lmtp unix - - - - - lmtp
    anvil unix - - - - 1 anvil
    scache unix - - - - 1 scache
    #
    # ====================================================================
    # Interfaces to non-Postfix software. Be sure to examine the manual
    # pages of the non-Postfix software to find out what options it wants.
    #
    # Many of the following services use the Postfix pipe(8) delivery
    # agent. See the pipe(8) man page for information about ${recipient}
    # and other message envelope options.
    # ====================================================================
    #
    # maildrop. See the Postfix MAILDROP_README file for details.
    # Also specify in main.cf: maildrop_destination_recipient_limit=1
    #
    maildrop unix - n n - - pipe
    flags=DRhu user=vmail argv=/usr/bin/maildrop -d ${recipient}
    #
    # ====================================================================
    #
    # Recent Cyrus versions can use the existing "lmtp" master.cf entry.
    #
    # Specify in cyrus.conf:
    # lmtp cmd="lmtpd -a" listen="localhost:lmtp" proto=tcp4
    #
    # Specify in main.cf one or more of the following:
    # mailbox_transport = lmtp:inet:localhost
    # virtual_transport = lmtp:inet:localhost
    #
    # ====================================================================
    #
    # Cyrus 2.1.5 (Amos Gouaux)
    # Also specify in main.cf: cyrus_destination_recipient_limit=1
    #
    #cyrus unix - n n - - pipe
    # user=cyrus argv=/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user}
    #
    # ====================================================================
    # Old example of delivery via Cyrus.
    #
    #old-cyrus unix - n n - - pipe
    # flags=R user=cyrus argv=/cyrus/bin/deliver -e -m ${extension} ${user}
    #
    # ====================================================================
    #
    # See the Postfix UUCP_README file for configuration details.
    #
    uucp unix - n n - - pipe
    flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)
    #
    # Other external delivery methods.
    #
    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 -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}
    mailman unix - n n - - pipe
    flags=FR user=list argv=/usr/lib/mailman/bin/postfix-to-mailman.py
    ${nexthop} ${user}



    amavis unix - - - - 2 smtp
    -o smtp_data_done_timeout=1200
    -o smtp_send_xforward_command=yes

    127.0.0.1:10025 inet n - 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

    ------------------------------------------------------------------------------------------------------------------------
    and I used mysql and create database, table and so on.

    and include netstat -tap
    ------------------------------------------------------------------------------------
    netstat -tap
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 *:nfs *:* LISTEN -
    tcp 0 0 *:49447 *:* LISTEN 2316/rpc.mountd
    tcp 0 0 localhost.localdo:10024 *:* LISTEN 18137/amavisd (mast
    tcp 0 0 *:8200 *:* LISTEN 4182/minidlna
    tcp 0 0 localhost.localdo:10025 *:* LISTEN 19582/master
    tcp 0 0 localhost.localdo:mysql *:* LISTEN 20342/mysqld
    tcp 0 0 *:netbios-ssn *:* LISTEN 2268/smbd
    tcp 0 0 *:sunrpc *:* LISTEN 2147/portmap
    tcp 0 0 localhost.localdom:9877 *:* LISTEN 4043/execsrv
    tcp 0 0 *:ssh *:* LISTEN 1999/sshd
    tcp 0 0 *:ipp *:* LISTEN 2207/cupsd
    tcp 0 0 *:smtp *:* LISTEN 19582/master
    tcp 0 0 *:58425 *:* LISTEN -
    tcp 0 0 *:763 *:* LISTEN 2285/rpc.rquotad
    tcp 0 0 *:32765 *:* LISTEN 2294/rpc.statd
    tcp 0 0 *:microsoft-ds *:* LISTEN 2268/smbd
    tcp 0 52 some:ssh 192.168.0.148:49385 ESTABLISHED 15560/1
    tcp 45 0 localhost.localdo:45940 localhost.localdo:10025 CLOSE_WAIT 19886/amavisd (ch1-
    tcp 0 0 some:microsoft-ds 112.217.112.82:64886 ESTABLISHED 20206/smbd
    tcp6 0 0 [::]:imaps [::]:* LISTEN 1112/couriertcpd
    tcp6 0 0 [::]:pop3s [::]:* LISTEN 1137/couriertcpd
    tcp6 0 0 [::]:pop3 [::]:* LISTEN 1132/couriertcpd
    tcp6 0 0 [::]:imap2 [::]:* LISTEN 1094/couriertcpd
    tcp6 0 0 [::]:http-alt [::]:* LISTEN 815/apache2
    tcp6 0 0 [::]:www [::]:* LISTEN 815/apache2
    tcp6 0 0 [::]:ftp [::]:* LISTEN 4592/proftpd: (acce
    tcp6 0 0 [::]:ssh [::]:* LISTEN 1999/sshd
    -----------------------------------------------------------------------------------

    I don't konw how to fix err
     
    Last edited: Aug 7, 2013
  2. redix

    redix New Member

    add mysql and mysql-virtual_*.cf

    cat mysql-virtual_domains.cf
    user = root
    password = *
    dbname = mail
    query = select domain as virtual from domains where domain ='%s'
    #query = select domain as virtual from domains
    hosts = 127.0.0.1
    [email protected]:/etc/postfix# cat mysql-virtual_email2email.cf
    user = root
    password = *
    dbname = mail
    #query = SELECT email FROM users WHERE email='%s'
    query = SELECT email from users
    hosts = 127.0.0.1
    [email protected]:/etc/postfix# cat mysql-virtual_forwardings.cf
    user = root
    password = *
    dbname = mail
    query = select destination from forwardings where domain ='%s'
    hosts = 127.0.0.1
    [email protected]:/etc/postfix# cat mysql-virtual_mailbox_limit_maps.cf
    user = root
    password = *
    dbname = mail
    query = SELECT quota FROM users WHERE email='%s'
    #query = select quota from users
    hosts = 127.0.0.1
    [email protected]:/etc/postfix# cat mysql-virtual_transports.cf
    user = root
    password = *
    dbname = mail
    query = SELECT transport FROM transport WHERE domain='%s'
    hosts = 127.0.0.1

    ---------------------------------------------------------------------------------
    show tables;
    +----------------+
    | Tables_in_mail |
    +----------------+
    | domains |
    | forwardings |
    | transport |
    | users |
    +----------------+
    4 rows in set (0.00 sec)
    ---------------------------------------------------------------------------------
    select * from domains
    -> ;
    +----------+
    | domain |
    +----------+
    | some |
    +----------+
    1 row in set (0.02 sec)

    mysql> select * from forwardings;
    +---------------+----------------+
    | source | destination |
    +---------------+----------------+
    | [email protected] | [email protected] |
    | [email protected] | [email protected] |
    +---------------+----------------+
    2 rows in set (0.00 sec)

    mysql> select * from trasport;
    ERROR 1146 (42S02): Table 'mail.trasport' doesn't exist
    mysql> select * from transport;
    +----------+--------------------+
    | domain | transport |
    +----------+--------------------+
    | some | smtp:mail.some |
    +----------+--------------------+
    1 row in set (0.01 sec)

    mysql> select * from usersl
    -> ;
    ERROR 1146 (42S02): Table 'mail.usersl' doesn't exist
    mysql> select * from users;
    +----------------+---------------+----------+
    | email | password | quota |
    +----------------+---------------+----------+
    | [email protected] | ----- | 10485760 |
    | [email protected] | --- | 10485760 |
    | [email protected] | ---- | 10485760 |
    +----------------+---------------+----------+
    4 rows in set (0.02 sec)
     
  3. redix

    redix New Member

    uid=0 from=<root>
    postfix/cleanup[13213]: 0FD2BCF8D: message-id=<[email protected]>
    postfix/proxymap[13066]: warning: mysql query failed: Unknown column 'domain' in 'where clause'
    postfix/cleanup[13213]: warning: 0FD2BCF8D: virtual_alias_maps map lookup problem for root@----


    ------------------------------------------------------------------------------------
    cat /etc/postfix/mysql-virtual_domains.cf
    user = root
    password = ------------
    dbname = mail
    query = select domain as virtual from domains where domain ='%s'
    #query = select domain from domains where domain = '%s'
    hosts = 127.0.0.1
    ---------------------------------------------------------------------------------
    When I commit this command
    portmap /etc/postfix/mysql-virtual_domains.cf
    nothing happened.
    I think it's not cause 'mysql-virtual_domains.cf'
    any idea?
     
  4. redix

    redix New Member

    above all I solve it but still happen err

    Aug 13 12:18:36 sbczn postfix/pickup[5911]: 65BDDCDE2: uid=0 from=<root>
    Aug 13 12:18:36 sbczn postfix/cleanup[5919]: 65BDDCDE2: message-id=<[email protected]>
    Aug 13 12:18:36 sbczn postfix/qmgr[5910]: 65BDDCDE2: from=<[email protected]>, size=305, nrcpt=1 (queue active)
    Aug 13 12:18:59 sbczn postfix/smtpd[5933]: connect from localhost.localdomain[127.0.0.1]
    Aug 13 12:18:59 sbczn postfix/trivial-rewrite[5921]: warning: do not list domain somthing.domain in BOTH mydestination and virtual_mailbox_domains
    Aug 13 12:18:59 sbczn postfix/smtpd[5933]: 82C12CDE1: client=localhost.localdomain[127.0.0.1]
    Aug 13 12:18:59 sbczn postfix/cleanup[5919]: 82C12CDE1: message-id=<[email protected]>
    Aug 13 12:18:59 sbczn postfix/qmgr[5910]: 82C12CDE1: from=<[email protected]>, size=718, nrcpt=1 (queue active)
    Aug 13 12:18:59 sbczn postfix/smtpd[5933]: disconnect from localhost.localdomain[127.0.0.1]
    Aug 13 12:18:59 sbczn postfix/trivial-rewrite[5921]: warning: do not list domain somthing.domain in BOTH mydestination and virtual_mailbox_domains
    Aug 13 12:18:59 sbczn amavis[31099]: (31099-05) Passed CLEAN, <[email protected]> -> <[email protected]>, Message-ID: <[email protected]>, mail_id: rJuAUnV03CGp, Hits: -0.001, size: 305, queued_as: 82C12CDE1, 22841 ms
    Aug 13 12:18:59 sbczn postfix/smtp[5922]: 65BDDCDE2: to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, delay=24, delays=0.81/0.09/0.28/23, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=31099-05, from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as 82C12CDE1)
    Aug 13 12:18:59 sbczn postfix/qmgr[5910]: 65BDDCDE2: removed
    Aug 13 12:19:01 sbczn postfix/smtpd[5939]: warning: dict_nis_init: NIS domain name not set - NIS lookups disabled
     

Share This Page