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, [email protected], there is no system user with this username. I then tried the telnet test mail but there is no '[email protected]' 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=<[email protected]>, size=23758, nrcpt=1 (queue active)
Feb 6 13:25:48 debbie postfix/qmgr[1311]: 33FD92C2A6B: from=<[email protected]>, size=23761, nrcpt=1 (queue active)
Feb 6 13:25:48 debbie postfix/qmgr[1311]: 24D0E2C2A68: from=<[email protected]>, 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 = '[email protected]'' at line 1
Feb 6 13:25:48 debbie postfix/virtual[3080]: EE4F72C2A6A: to=<[email protected]>, relay=virtual, delay=15239, status=deferred (virtual_mailbox_maps: lookup [email protected]: Success)
Feb 6 13:25:48 debbie postfix/virtual[3080]: 33FD92C2A6B: to=<[email protected]>, relay=virtual, delay=15191, status=deferred (virtual_mailbox_maps: lookup [email protected]: Success)
Feb 6 13:25:48 debbie postfix/virtual[3080]: 24D0E2C2A68: to=<[email protected]>, relay=virtual, delay=15175, status=deferred (virtual_mailbox_maps: lookup [email protected]: 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: <[email protected]>: 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?

 

What's in the mail log?

 

Here's the latest mail log I get, when I tested sending email...

Feb 20 17:30:07 debbie imaplogin: Connection, ip=[::ffff:203.177.xx.xx]
Feb 20 17:30:07 debbie imaplogin: LOGIN, [email protected], ip=[::ffff:203.177.xx.xx], protocol=IMAP
Feb 20 17:31:03 debbie postfix/proxymap[4514]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server during query
Feb 20 17:31:03 debbie postfix/proxymap[4514]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server during query
Feb 20 17:31:03 debbie postfix/trivial-rewrite[4695]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
Feb 20 17:31:03 debbie postfix/trivial-rewrite[4696]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
Feb 20 17:31:03 debbie postfix/trivial-rewrite[4697]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
Feb 20 17:31:03 debbie postfix/trivial-rewrite[4698]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
Feb 20 17:31:03 debbie postfix/trivial-rewrite[4699]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
Feb 20 17:31:04 debbie postfix/smtpd[2297]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[2297]: warning: problem talking to service rewrite: Success
Feb 20 17:31:04 debbie postfix/smtpd[3299]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[3299]: warning: problem talking to service rewrite: Connection reset by peer
Feb 20 17:31:04 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 4695 exit status 1
Feb 20 17:31:04 debbie postfix/master[1359]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
Feb 20 17:31:04 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 4696 exit status 1
Feb 20 17:31:04 debbie postfix/smtpd[3301]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[3301]: warning: problem talking to service rewrite: Success
Feb 20 17:31:04 debbie postfix/smtpd[4341]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[4341]: warning: problem talking to service rewrite: Connection reset by peer
Feb 20 17:31:04 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 4697 exit status 1
Feb 20 17:31:04 debbie postfix/smtpd[4523]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[4523]: warning: problem talking to service rewrite: Success
Feb 20 17:31:04 debbie postfix/smtpd[4537]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[4537]: warning: problem talking to service rewrite: Connection reset by peer
Feb 20 17:31:04 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 4698 exit status 1
Feb 20 17:31:04 debbie postfix/smtpd[3179]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[3179]: warning: problem talking to service rewrite: Success
Feb 20 17:31:04 debbie postfix/smtpd[2359]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[2359]: warning: problem talking to service rewrite: Connection reset by peer
Feb 20 17:31:04 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 4699 exit status 1
Feb 20 17:31:04 debbie postfix/qmgr[4642]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/qmgr[4642]: warning: problem talking to service rewrite: Success
Feb 20 17:31:04 debbie postfix/smtpd[4644]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 20 17:31:04 debbie postfix/smtpd[4644]: warning: problem talking to service rewrite: Connection reset by peer

 

Is MySQL running? Please post the output of

Code:

netstat -tap

Also, compare your configuration files with the ones from the tutorial. Make sure the format (white spaces, etc.) is the same.

 

Here's the output of 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 *:tcpmux                *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:imaps                 *:*                     LISTEN     1128/couriertcpd    
tcp        0      0 *:20034                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:32771                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:pop3s                 *:*                     LISTEN     1151/couriertcpd    
tcp        0      0 *:32772                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:40421                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:32773                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:32774                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 localhost.localdo:10024 *:*                     LISTEN     14454/amavisd (mast 
tcp        0      0 *:31337                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 localhost.localdo:10025 *:*                     LISTEN     1359/master         
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     1223/mysqld         
tcp        0      0 *:ircd                  *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:systat                *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:5742                  *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:pop3                  *:*                     LISTEN     1137/couriertcpd    
tcp        0      0 *:finger                *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:netstat               *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:imap2                 *:*                     LISTEN     1114/couriertcpd    
tcp        0      0 *:sunrpc                *:*                     LISTEN     874/portmap         
tcp        0      0 *:54320                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:sieve                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:www                   *:*                     LISTEN     1479/apache         
tcp        0      0 *:27665                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:auth                  *:*                     LISTEN     1166/inetd          
tcp        0      0 *:ingreslock            *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:ftp                   *:*                     LISTEN     1372/pure-ftpd (SER 
tcp        0      0 *:ssh                   *:*                     LISTEN     1389/sshd           
tcp        0      0 *:nntp                  *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:socks                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:728                   *:*                     LISTEN     1394/rpc.statd      
tcp        0      0 *:12345                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:smtp                  *:*                     LISTEN     1359/master         
tcp        0      0 *:12346                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:635                   *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:49724                 *:*                     LISTEN     1516/portsentry     
tcp        0      0 *:uucp                  *:*                     LISTEN     1516/portsentry     
tcp        0      0 localhost.localdo:mysql localhost.localdo:37300 TIME_WAIT  -                   
tcp        0     52 192.168.0.3:ssh         192.168.1.9:1110        ESTABLISHED11585/0             
tcp        0      0 localhost.localdo:mysql localhost.localdo:37299 TIME_WAIT  -                   

Yes, all configuration files are the same. As I have mentioned before, when I was still using a test ip of 192.168.0.2, it was running fine, but when I put the server on a production environment with a class 'a' ip of 10.0.0.2, the mail system doesn't work.

 

yes that was the first file i changed, then restarted the network. here are the files i have so far edited.

/etc/network/interfaces

Code:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto eth0
#iface eth0 inet static
#	address 192.168.0.2
#	netmask 255.255.255.0
#	network 192.168.0.0
#	broadcast 192.168.0.255
#	gateway 192.168.0.100
iface eth0 inet static
	address 10.0.0.2
	netmask 255.255.255.240
	network 10.10.0.0
	broadcase 10.0.0.15
	gateway 10.0.0.1

/etc/resolv.conf

Code:

search
nameserver 10.0.0.1
nameserver 203.177.255.10
nameserver 203.177.3.100

/etc/hosts

Code:

127.0.0.1	localhost.localdomain	localhost	debbie
10.0.0.2	debbie.cpu.***.**	debbie

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

 

You have a typo there, it should be

Code:

broadcas[B][COLOR="Red"]t[/COLOR][/B] 10.0.0.15

 

aargh.... thanks falko... okay now i corrected the typo and restarted actually the server in order to restart all services.... then i tested telnet, here's an output of the telnet....

Code:

debbie:~# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
220 debbie.cpu.edu.ph ESMTP Postfix (Debian/GNU)
mail from:<[email protected]>
^]
telnet> quit
Connection closed.
debbie:~#

I have to disconnect because it just stop after entering the "mail from" sentence.

here's the mail log:

Code:

Feb 22 17:21:50 debbie postfix/qmgr[1369]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 22 17:21:50 debbie postfix/qmgr[1369]: warning: problem talking to service rewrite: Success
Feb 22 17:21:50 debbie postfix/cleanup[1370]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 22 17:21:50 debbie postfix/cleanup[1370]: warning: problem talking to service rewrite: Connection reset by peer
Feb 22 17:21:50 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 2328 exit status 1
Feb 22 17:21:50 debbie postfix/master[1359]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
Feb 22 17:21:50 debbie postfix/smtpd[2207]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 22 17:21:50 debbie postfix/smtpd[2207]: warning: problem talking to service rewrite: Success
Feb 22 17:21:50 debbie postfix/smtpd[2255]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 22 17:21:50 debbie postfix/smtpd[2255]: warning: problem talking to service rewrite: Connection reset by peer
Feb 22 17:21:50 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 2329 exit status 1
Feb 22 17:21:50 debbie postfix/smtpd[2306]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
Feb 22 17:21:50 debbie postfix/smtpd[2306]: warning: problem talking to service rewrite: Success
Feb 22 17:21:50 debbie postfix/master[1359]: warning: process /usr/lib/postfix/trivial-rewrite pid 2330 exit status 1
Feb 22 17:22:12 debbie postfix/qmgr[1369]: fatal: watchdog timeout
Feb 22 17:22:13 debbie postfix/master[1359]: warning: process /usr/lib/postfix/qmgr pid 1369 exit status 1
Feb 22 17:22:13 debbie postfix/master[1359]: warning: /usr/lib/postfix/qmgr: bad command startup -- throttling
Feb 22 17:22:23 debbie postfix/pickup[1368]: fatal: watchdog timeout
Feb 22 17:22:24 debbie postfix/master[1359]: warning: process /usr/lib/postfix/pickup pid 1368 exit status 1
Feb 22 17:22:25 debbie postfix/pickup[2331]: CD4CA7BC160: uid=110 from=<logcheck>

 

Maybe you should consider redoing the Postfix configuration again, as close as possible to the tutorial... You seem to have some problems there...

 

Thanks, falko. Actually I've been considering to re-do everything, from scratch using the class 'a' private ip instead of the test ip (class 'c'), but I was just curious why it didn't work when i just changed the ip's. Anyway, I'll give you some updates on the outcome.

BTW, can you PM to me on how & what I can possibly give to help for this 'how to' site keep on running.