View Single Post
  #2  
Old 21st July 2006, 00:44
wwinfrey wwinfrey is offline
Junior Member
 
Join Date: Jul 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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'
?
Reply With Quote