Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > HOWTO-Related Questions

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #11  
Old 2nd July 2009, 23:35
digitalage digitalage is offline
Junior Member
 
Join Date: Jun 2009
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't know if you noticed, just to make sure I'll tell you that I used different names for the name of the host and the name of the email server, such as this:


# cat /etc/hosts
Code:
127.0.0.1       localhost.localdomain          localhost
127.0.1.1       hostname.myprovider.com        hostname
Relevant Postfix config:
# postconf -n
Code:
...
append_dot_mydomain = no
...
inet_interfaces = all
...
mydestination = server.mydomain.com, localhost, localhost.localdomain
myhostname = server.mydomain.com
mynetworks = 127.0.0.0/8
myorigin = /etc/mailname
...
relayhost =
...
# cat /etc/mailname
Code:
server.mydomain.com
Please notice the name server.mydomain.com versus hostname.myprovider.com.

server.mydomain.com is registered in my DNS - if I ping from outside, it resolves fine. hostname.myprovider.com is not registered in my DNS but it resolves because it's in my provider's DNS. There should be no reason why it wouldn't work this setup. Correct me if I'm wrong.
Reply With Quote
Sponsored Links
  #12  
Old 8th July 2009, 15:09
digitalage digitalage is offline
Junior Member
 
Join Date: Jun 2009
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default you're still with me?

Falko, you're still with me? Please, give me a sign.
I've my email down for more than one week and I desperately need to fix this.

Thank you.
Reply With Quote
  #13  
Old 9th July 2009, 16:22
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,741 Times in 2,575 Posts
Default

I have no idea what's wrong...
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #14  
Old 5th September 2009, 17:20
pilot9 pilot9 is offline
Junior Member
 
Join Date: Sep 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem with tables....

I had a very similar problem to the one laid out by digitalage with respect to log errors and some aspects of set up After fussing for several hours, I finally remembered that I was using prefixes for my Mysql tables... Not knowing how to make the prefixes explicit in the drupal-xxx-.cf files, I just added them directly to the table calls, so

....PREFIXmailfix_domains... and PREFIXmailfix_users...

solved the problem.

Hope this helps.

regards,
Erik
Reply With Quote
  #15  
Old 7th September 2009, 18:56
digitalage digitalage is offline
Junior Member
 
Join Date: Jun 2009
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you pilot9 for your response. It's now too late for me, I already reinstalled linux on that server.

However, I'd like to know more details about this issue and how to avoid it in the future. I don't understand your case. Are you nice to explain a bit more detailed? If you can, please clear up the prefixes staff, as I don't understand why and when is it write to use them. I assume you installed drupal, and after that the hell began. Am I write?
Reply With Quote
  #16  
Old 12th September 2009, 15:10
pilot9 pilot9 is offline
Junior Member
 
Join Date: Sep 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default MySql prefixes

Quote:
Originally Posted by digitalage View Post
Thank you pilot9 for your response. It's now too late for me, I already reinstalled linux on that server.

However, I'd like to know more details about this issue and how to avoid it in the future. I don't understand your case. Are you nice to explain a bit more detailed? If you can, please clear up the prefixes staff, as I don't understand why and when is it write to use them. I assume you installed drupal, and after that the hell began. Am I write?
Yes, I had installed Drupal, which gives you a nice option to use table prefixes for multisite installations in the same database. However, most recipes don't take them into consideration, so they are easy to miss. You are left with authentication errors, which are difficult to track sometimes. And as far as I know, the only more general solution to this sort of problem is keep careful track of everything! For relatively inexperienced sysadmin like myself, it is a mess and a climb up a steep learning curve... One general solution I would love to find (and have not found) is a comprehensive debugging recipe/howto, a series of steps to tackle certain kinds of very common errors (like authentication errors) where logs do not typically point to the problem directly...) When I have a little more time, I will sketch one out and maybe SPTM (smarter people than me) can flesh it out.

In my case, I had to go through my postfix (also dovecot, PAM, SASL) configuration files and add the MySQL database table prefixes accordingly. I had been working from a combination of Falko Timme's Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Lenny) recipe along with Alex Saavedra's Drupal + Postfix Integration Under Ubuntu 8.04 (Hardy) recipe and Christoph Haas's excellent tutorial Howto: ISP-style Email Server with Debian-Etch and Postfix 2.3 (also a Lenny updated version.
Basically, wherever a call was made to MySql, I needed to add table prefixes to the tables... so for example:

I added table prefixe [--PREFIX--] to the lines below. So if my prefix was "MySite" then [--PREFIX--]sometable becomes MySitesometable .

1. In /etc/postfix/sasl/smtpd.conf

sql_select: SELECT pass FROM [--PREFIX--]users WHERE mail='%u' AND status=1

Note: "users" is of course the table in the database with the passwords and so on. The database itself and the columns in the tables themselves do NOT need to be prefixed!

2. In /etc/dovecot/dovecot-sql.conf

password_query = SELECT mail AS user, pass AS password FROM [--PREFIX--]users WHERE mail='%u';

Note: "user" and "password" above are column names to retrieve your user's signin names and pws to access their email. "users" again is the table where that info is stored (and so needs to be prefixed).

3. and in /etc/pam.d/smtp

auth required pam_mysql.so user=USER passwd=PW host=127.0.0.1 db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

account sufficient pam_mysql.so user=USER passwd=PW host=127.0.0.1 db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

Note that MYDB, USER and PW are the relevant Database, the system user that accesses the database (eg "Mail" or whatever) and its password.

4. and in the Postfix configuration files in /etc/postfix/...

4a. /etc/postfix/drupal-domains.cf

query=SELECT domain_name AS domain FROM [--PREFIX--]mailfix_domains WHERE domain_name='%s'


4b. /etc/postfix/drupal-mailboxes.cf

query=SELECT CONCAT(md.domain_name, '/', LEFT(u.mail, LOCATE('@', u.mail) - 1),'/') AS maildir FROM [--PREFIX--]mailfix_domains md JOIN ([--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid) ON md.domain_id=mu.domain_id WHERE u.status=1 AND u.mail='%s'

4c. /etc/postfix/drupal-forward.cf

query=SELECT mu.forward FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s' AND LENGTH(mu.forward)>0

4d. /etc/postfix/drupal-recipient-bcc.cf

query=SELECT mu.incoming_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.incoming_bcc)>0

4e. /etc/postfix/drupal-sender-bcc.cf

query=SELECT mu.outgoing_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.outgoing_bcc)>0

4f. /etc/postfix/drupal-quota.cf

query=SELECT mu.quota FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s'


The thing about MySQL is that it is very unforgiving. One little space or an added " ` " somewhere and you are going to get something like authentication errors. I spent a lot of time combing all the MySQL calls and then going ever so carefully over the whole process of how the different parts of the server call one another before I remembered these prefixes. The silver lining was taking a few steps up the learning curve...

Pilot
Reply With Quote
  #17  
Old 13th September 2009, 11:07
digitalage digitalage is offline
Junior Member
 
Join Date: Jun 2009
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default great post

Pilot, your answer is very detailed and much appreciated. Though the approach to multi-site it's logical, I wasn't aware of how to get there. Thank you for explanation and links. I'm sure many will find this post helpful.
Reply With Quote
  #18  
Old 11th November 2009, 09:51
dipps dipps is offline
Junior Member
 
Join Date: Nov 2009
Location: A small country town
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting somewhere

Hi guys, I was having a similar problem: after learning a lot of things I got past it, so thought I'd share. This site has helped me in the past.
Nov 10 18:53:06 server2 postfix/trivial-rewrite[8681]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
Nov 10 18:53:06 server2 postfix/trivial-rewrite[8681]: fatal: mysql:/etc/postfix/mysql/virtual-alias-maps.cf(0,lock|fold_fix): table lookup problem
First thing I did was to check that the mysql queries worked from command line. I did that with postmap -q and mysql (logging onto mysql as the postfix user, same as postfix will do). This worked fine. I had a good look at it with phpmyadmin and the info seemed to be there, but still, trivial-rewrite couldn't do mysql lookups.

I read that "hosts localhost" and "hosts 127.0.0.1" worked differently in mysql, so tried both those in the query. Didn't make any difference. I tried making a connection to the socket instead... "hosts unix:/var/run/mysqld/mysqld.sock" and that failed too.

(For the record: connect to mysql on localhost, and the client makes a unix domain, socket connection, NOT a network connection. If you connect to 127.0.0.1 then it does networking. And I thought localhost and 127.0.0.1 meant the same thing )

I edited /etc/postfix/master.cf so it would NOT chroot trivial-rewrite. Amazingly... postfix did its first mysql lookup, using hosts unix:

But I wanted to put trivial-rewrite back in its chroot, so wanted the network connection to 127.0.0.1. About this time, I found that even command-line connections to 127.0.0.1 failed:
# mysql --host=127.0.0.1 -u root -p
(connection refused)
So it wasn't just postfix having trouble. "netstat -nap" showed mysql listening on 127.0.0.1:3306, as wanted. I turned on mysql's logging, and could see that it wasn't getting any requests: just sitting there idle while postfix, mysql client etc asked stuff and were refused.

I revisited the privileges tables in mysql - made sure that the postfix user could connect from host 127.0.0.1. I'm not sure I did that exactly right so I won't post the details yet. Still mysql lookups failed at 127.0.0.1.

I had already checked that iptables firewall was allowing access to port 3306.

About this time, a google result sent me to read README.Debian. (A bit embarrassing that I hadn't already, but there ya go.) The actual file on Lenny was /usr/share/doc/mysql-server-5.0/README.Debian.gz and it gave me the puzzle piece I needed:
If your connection is aborted immediately see if "mysqld: all" or similar is in /etc/hosts.allow and read hosts_access(5).
Sigh... I added mysqld: 127.0.0.1 to hosts.allow and it all came good. The mail.log started complaining about the SQL syntax, which was really good news at the time, that was something I could fix!

I'm not finished yet still have things to fix on this postfix/mysql/etc mailserver, but this was a step forwards. Hope it helps someone.
Reply With Quote
  #19  
Old 11th November 2009, 18:15
digitalage digitalage is offline
Junior Member
 
Join Date: Jun 2009
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
About this time, a google result sent me to read README.Debian. (A bit embarrassing that I hadn't already, but there ya go.) The actual file on Lenny was /usr/share/doc/mysql-server-5.0/README.Debian.gz and it gave me the puzzle piece I needed:

If your connection is aborted immediately see if "mysqld: all" or similar is in /etc/hosts.allow and read hosts_access(5).

Sigh... I added mysqld: 127.0.0.1 to hosts.allow and it all came good. The mail.log started complaining about the SQL syntax, which was really good news at the time, that was something I could fix!
Dipps, thank you for your post.

As far as I'm aware of, it makes sense to have a rule in /etc/hosts.allow when there is a rule in /etc/hosts.deny which blocks everything (ie: "all: all") or part of it ("mysql: all"). For the beginner, the explanation: first rule denies everything, in which case we need a rule in /etc/hosts.allow to allow mysql, the second blocks mysql from the public IP, in which case wee need a rule in /etc/hosts.allow to override this for the specific part we need mysql access (local - 127.0.0.1, or public_ip).

I find this link useful for those who need more informations about how hosts.deny/hosts.allow works:
http://linux.about.com/od/commands/l...l5_hostsal.htm.

In short, this is what we need to read from the link above:
Quote:
The access control software consults two files. The search stops at the first match:

* Access will be granted when a (daemon,client) pair matches an entry in the /etc/hosts.allow file.

* Otherwise, access will be denied when a (daemon,client) pair matches an entry in the /etc/hosts.deny file.

* Otherwise, access will be granted.
Reply With Quote
  #20  
Old 13th March 2013, 19:02
reason8 reason8 is offline
Senior Member
 
Join Date: Aug 2008
Posts: 141
Thanks: 2
Thanked 0 Times in 0 Posts
 
Default

Issue resolved.

The update added "proxy" to the beginning of the mysql instances.
mysql:/etc/postfix/mysql-virtual_transports.cf

Email started flowing shortly thereafter.

Why was this proxy enabled? What is it's reason?
__________________
Running:

ISPConfig 3
openSUSE Linux 11.3
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
enable mod rewrite for the ISPconfig apache web server blocker General 3 18th April 2009 16:14
Rewrite Rules tristanlee85 General 2 30th January 2009 05:27
Postfix virtual users jacknetgb HOWTO-Related Questions 7 10th February 2008 08:50
gallerie and rewrite fpl Installation/Configuration 20 14th February 2007 15:21
Drupal and Apache Rewrite Rules andre Server Operation 10 4th May 2006 21:04


All times are GMT +2. The time now is 20:32.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.