Another Problem: Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP
 

Hi there!

I guess I have to consult you guys/gals out there about this problem of mine.

I have setup a new mailserver using the procedure mentioned.

After installation, I tried the configuration by sending an email to a new virtual mail account (based from: http://workaround.org/articles/ispmail-sarge/), it was on my understanding that a mailbox will be automatically created once a first mail succeeds. But, after I sent a test mail using telnet and checked whether my /home/vmail/ have a new folder with the virtual mail users account, there is none.

Here's some details on my network configuration: (xxx.edu.ph - my domain)
- I have an existing DNS, Web, FTP, Telnet, Mail Server running on Fedora 2 on a public ip - 203.177.73.xxx (A)
- On the said Server the MX and A record for mail.xxx.edu.ph are pointed to same ip (A) address.
- I setup a new mail server with a private ip of - 192.168.0.xx (b), this new mail server resides behind a Fortinet Fortigate Anti-Virus/Anti-Spam Hardware, wherein I configured its external port on a public ip - 203.177.73.yyy (B). On my new mail server I also used the same domain name, xxx.edu.ph. Then I changed the MX and A record on my DNS Server to point to the new ip address (B).
- For the virtual mail users on the new Mail server, I inserted my existing email address, me@xxx.edu.ph, there is no system user with this username. I then tried the telnet test mail but there is no 'me@xxx.edu.ph' folder on my /home/vmail.
- I checked and double checked the scripts and procedures stated on the tutorial but I don't seem to find where I could have possibly missed.

Attached herewith are some of the files I created based from the tutorial and also the mail.log:

/etc/postfix/main.cf:
# 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 = debbie.xxx.edu.ph
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination = debbie.xxx.edu.ph, localhost, localhost.localdomain
relayhost =
mynetworks = 127.0.0.0/8
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, proxy:mysql:/etc/postfix/mysql-virtual_email2email.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.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
smtpd_use_tls = yes
smtpd_tls_cert_file = /etc/postfix/smtpd.cert
smtpd_tls_key_file = /etc/postfix/smtpd.key
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


mysql-virtual_domains.cf:
user = mail_admin
password = mypassword
dbname = mail
table = domains
select_field = 'virtual'
where_field = domain
hosts = 127.0.0.1

mysql-virtual_email2email.cf:
user = mail_admin
password = mypassword
dbname = mail
table = users
select_field = email
where_field = email
hosts = 127.0.0.1

mysql-virtual_forwardings.cf:
user = mail_admin
password = mypassword
dbname = mail
table = forwardings
select_field = destination
where_field = source
hosts = 127.0.0.1

mysql-virtual_mailbox_limit.maps.cf:
user = mail_admin
password = mypassword
dbname = mail
table = users
select_field = quota
where_field = email
hosts = 127.0.0.1

mysql-virtual_mailboxes.cf:
user = mail_admin
password = mypassword
dbname = mail
table = users
select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1)
where_field = email
hosts = 127.0.0.1

mysql-virtual_transports.cf:
user = mail_admin
password = mypassword
dbname = mail
table = transport
select_field = transport
where_field = domain
hosts = 127.0.0.1


part of /var/log/mail.log
Feb 6 13:25:48 debbie postfix/qmgr[1311]: EE4F72C2A6A: from=<vonxt@hotmail.com>, size=23758, nrcpt=1 (queue active)
Feb 6 13:25:48 debbie postfix/qmgr[1311]: 33FD92C2A6B: from=<vonxt@hotmail.com>, size=23761, nrcpt=1 (queue active)
Feb 6 13:25:48 debbie postfix/qmgr[1311]: 24D0E2C2A68: from=<vonxt@hotmail.com>, size=23788, nrcpt=1 (queue active)
Feb 6 13:25:48 debbie postfix/proxymap[2464]: warning: mysql query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from users where email = 'me@xxx.edu.ph'' at line 1
Feb 6 13:25:48 debbie postfix/virtual[3080]: EE4F72C2A6A: to=<me@xxx.edu.ph>, relay=virtual, delay=15239, status=deferred (virtual_mailbox_maps: lookup me@xxx.edu.ph: Success)
Feb 6 13:25:48 debbie postfix/virtual[3080]: 33FD92C2A6B: to=<me@xxx.edu.ph>, relay=virtual, delay=15191, status=deferred (virtual_mailbox_maps: lookup me@xxx.edu.ph: Success)
Feb 6 13:25:48 debbie postfix/virtual[3080]: 24D0E2C2A68: to=<me@xxx.edu.ph>, relay=virtual, delay=15175, status=deferred (virtual_mailbox_maps: lookup me@xxx.edu.ph: Success)

--If you can also help me on the error given by postfix/proxymap (log #4)

okay, i seemed to see the problem just now...

whew, it was on my nth time checking and re-checking all the scripts and configuration files, it seemed to be working as of now...

Okay, I can now receive emails from the outside world. My problem now is I can't send emails to the outside world.

At present I'm testing sending emails using Mozilla Thunderbird and this error occurs when I send the email:

"An error occured while sending mail. The mail server responded: <soyski_it2004@yahoo.com>: Relay access denied. Please verify that your email address is correct in your Mail preferences and try again."

Before, I have nothing on the mysql-virtual_transports.cf. When the error occured, I added the following entries on the said configuration file:
xxx.edu.ph :
* smtp:[203.177.73.xxx] "the ip of the original mail server"

Then I restarted postfix: /etc/init.d/postfix restart

I again tried sending an email but the same error occurs.

I think this problem is similar to Rocky's problem.

One more thing, can you possibly give a suggestion as to what webmail program I could use for this type of mail server, and if there is a tutorial document wherein I can refer to?

Thanks in advance.

You must enable something like "Server requires authentication." in your email client.



You can use any webmail program, such as Squirrelmail, Uebimiau, Horde, ...

I did try it, but still there is an error. I guess the problem is that I configured my SMTP server IP (on the mail client) the public IP of the Anti-Virus/Anti-Spam, hardware where the mail server is connected. But when I tried the same mail client on another PC which is also connected in parallel with the mail server I don't encounter a problem at everything works out fine.


Thanks, I used Squirrelmail. All the while I thought it wouldn't work on virtual users, but it did.

Now, here's another query:
Is there a tool or any tutorial, wherein I can populate my users database on the new mail server from the existing mail users and their passwords from the old mail server (running on Fedora 2 and sendmail and not using virtual users)? And also, how can I transfer their mails from the old server to the new server. It would really be neat that when I transfer to the new mail server, it will be transparent to my users, as if no upgrades or nothing had happened.

Thanks in advance.

And did you forward port 25 from that antivirus/antispam hardware to your SMTP server? Haven't you tried the IP address of your SMTP server itself?




There are so many different mail server setups, they all differ very much or at least slightly, so I don't think there's a tool... This is manual work...

Yes, forwarding was done on the hardware. The SMTP server's IP is a private IP, 192.168.*.* and not a public IP. The configuration on the antivirus/antispam hardware is to have all SMTP traffic forwarded to that private IP.





Well, I guess I don't have any choice. I'll try doing manually transferring. For the users, I'll just make a text file and upload it to the mysql database and table and just give them a default password for the meantime. My problem will be the transferring of their emails.

If it's working on another computer, then it must be somekind of misconfiguration in your email client.
For example, you configure Outlook for SMTP-AUTH like this: http://mail.cs.uiuc.edu/relay/outlook-config.html

Thanks again, falko.

At this point, all is fine, specially when I installed squirrelmail as my webmail apps. So my email users can read their emails thru the corporate webmail.

Now my next move is to import all the email users and create a temporary password for each from my old mail server to the new one. Then my next problem will be their old emails.

The configuration on the link you gave is somewhat the same as what I did on the two computers, the only difference is that I inputted the IP not the FQDN as what the sample config illustrates. I'll send you a network config diagram thru PM by tomorrow of my whole set-up.

Okay, the server is running and most of the important emails were already migrated from the old mail server.

I now, change the IP address from the Class "C" private ip (192.168.0.xxx) to a Class "A" private ip (10.0.0.xxx), I also changed the ip's on the resolv.conf and /etc/hosts files. I transferred physically the server from my workarea to the server room, and upon testing, email doesn't work. I tried "telnet localhost 25" and tried sending test email but to no avail.

Are there any other files, which I need to reconfigure?