LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-19-2011, 09:19 AM   #1
veeruk101
Member
 
Registered: Mar 2005
Distribution: Ubuntu 12.04 LTS
Posts: 249

Rep: Reputation: 16
iptables problem with mysql connection from client program


I can always connect to my local installation of mysql using the command 'mysql -u root'. However, I can only connect to it from my client program (Navicat) when iptables is disabled or uses the default rules it came with. When I apply the following rules to my iptables, I can still connect using the mysql command, but in Navicat I get the following error message: "1130 Host '192.168.0.44' is not allowed to connect to this MySQL server". The rules are:

Quote:
sudo iptables -t nat -A POSTROUTING -j MASQUERADE
sudo iptables -F FORWARD
sudo iptables -A FORWARD -i eth0 -o wlan0 -j ACCEPT
sudo bash -c "echo 1 > /proc/sys/net/ipv4/ip_forward"
This results in the following iptables status.

Code:
$ sudo service iptables status
Table: nat
Chain PREROUTING (policy ACCEPT)
num  target     prot opt source               destination         

Chain POSTROUTING (policy ACCEPT)
num  target     prot opt source               destination         
1    MASQUERADE  all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination         

Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           
3    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
5    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination
How can I change the rules I run for iptables to ensure that I can connect to MySQL from the client program? And also, why might this be a problem with the client program and not the command-line client program? I think my MySQL permissions are set up correctly because everything works when iptables is disabled or has the default rules.
 
Old 10-20-2011, 06:06 PM   #2
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,497

Rep: Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636
Do you mean you CAN connect using the mysql client:
- from localhost (the same host as running the mysql server)
- from another host (the one running Navicat)

You canNOT connect using Navicat
- from the localhost
- from another host

What is the IP of the host running mysql
What is the IP of the client running Navicat

jlinkels
 
Old 10-20-2011, 07:10 PM   #3
veeruk101
Member
 
Registered: Mar 2005
Distribution: Ubuntu 12.04 LTS
Posts: 249

Original Poster
Rep: Reputation: 16
What's weird about this problem is that there is only 1 computer involved. My Linux laptop (localhost) which has a MySQL server running on it, as well as a MySQL command-line client program as well as a GUI client program (Navicat). The internal IP of the local machine is 192.168.0.44.

Something about the way Navicat connects to MySQL causes it to stop working when my iptables is set up the way I've described above (because there's no problem when iptables is disabled or uses the default setup, before I run the iptables commands I showed in my OP.)
 
Old 10-21-2011, 08:12 AM   #4
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,497

Rep: Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636
Clear.

Try this:
mysql -u root -p localhost
mysql -u root -p 192.168.0.44

(I hope one of the two fails)

What is the IP of wlan0?

Do you see something in /var/log/mysql.log?

Also, find my.cnf (most likely /etc/my.cnf and see what the bind-address is.

I suspect that somehow the Navicat client gets the source address of the other interface (you are routing between eth0 and wlan0) and this source address is used for attempting to connect with mysql.

jlinkels
 
1 members found this post helpful.
Old 10-21-2011, 01:33 PM   #5
veeruk101
Member
 
Registered: Mar 2005
Distribution: Ubuntu 12.04 LTS
Posts: 249

Original Poster
Rep: Reputation: 16
Very interesting, you're right. I don't know how to solve it though - here's some more information. /etc/my.cnf doesn't have much in it, no bind-address. /var/log/mysql.log has the following after the errors show up:

Quote:
111021 19:12:55 [Warning] IP address '192.168.1.22' could not be resolved: Name or service not known
111021 19:25:24 [Warning] IP address '192.168.1.13' could not be resolved: Name or service not known
When I plugged my laptop directly into the modem eth0 was 192.168.1.13 and wlan0 was 192.168.1.22. Now here's what's interesting. Connecting to 'localhost' always works, but whether I entered 192.168.1.22 or 192.168.1.13, the error message was always with '192.168.1.13'. I'm new to this and to be honest I don't know what the implications of 'routing' between the 2 networks are. (All I know is I needed to do so in order for a device directly connected to my computer with a crossover cable to access the internet.)

Quote:
$ mysql -u root -h 192.168.1.22
ERROR 1130 (HY000): Host '192.168.1.13' is not allowed to connect to this MySQL server
$ mysql -u root -h 192.168.1.13
ERROR 1130 (HY000): Host '192.168.1.13' is not allowed to connect to this MySQL server
$ mysql -u root -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g.
But when I unplug from the router and only have wireless, wlan0 is still 192.168.1.22, and then I get the error message showing 192.168.1.22:

Quote:
$ mysql -u root -h 192.168.1.22
ERROR 1130 (HY000): Host '192.168.1.22' is not allowed to connect to this MySQL server
$ mysql -u root -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g.
What I'm hoping to know first of all is how to resolve this, but also if someone could explain on a conceptual level what's going on here so that I understand, it would help my learning process as I'm still relatively new to all this, particularly the networking side of things...

Last edited by veeruk101; 10-21-2011 at 01:37 PM.
 
Old 10-21-2011, 02:10 PM   #6
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,497

Rep: Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636
I can't follow exactly what you say about eth0 plugging directly into the modem or not. This doesn't match your previous post telling that your laptop had IP 192.168.0.44.

Let me assume your eth0 has a fixed address on 192.168.0.44 and your wlan0 is 192.168.1.13. The device connected to your laptop must be 192.168.0.12.

(Now when you started talking about device connected to your laptop I see that I answered a post of yours before on routing. )

IPtables does masquerading, that means any address not found on the local subnet is being translated to the address of wlan0 and then sent onto the internet. What comes back to wlan0 is catched by IPtables, translated back to 192.168.0.nnn and sent to the device it was originating from.

What most likely happens is that Navicat uses an IP address which is translated first to 192.168.1.xxx before it connects to MySQL.

If you configure Navicat, what address of MYSql do you enter? A name or IP?


111021 19:12:55 [Warning] IP address '192.168.1.22' could not be resolved: Name or service not known

Is this the error which is logged when you start Navicat?

Can you make Navicat go to localhost instead?

Some crude experiments do them one by one, in this sequence:

Change the masquerade statement to this:
Code:
sudo iptables -t nat -F #flush old rules
sudo iptables -t nat -A POSTROUTING -p tcp !--dport 3306 -j MASQUERADE
(After this UDP might stop working correctly, like DNS. If so, we'll solve that later)

Furthermore if MySQL complains about reverse DNS, try to add this to /etc/hosts:
Code:
192.168.1.13       hostname.of.yourlaptop
The 192.168.1.13 must be the wlan0 ip address.

Add this line to /etc/my.cnf:
Code:
# skip-networking <<< comment out this line if it is present
bind-address= <IP of your wlan0>
Or alternatively
Code:
# skip-networking <<< comment out this line if it is present
bind-address= <IP of your eth0>
I am not too fond of setting the bind address to wlan0 as it might impose a security risk. Be aware of that.

Let's see what happens.

jlinkels

Last edited by jlinkels; 10-21-2011 at 02:13 PM.
 
1 members found this post helpful.
Old 10-21-2011, 06:44 PM   #7
veeruk101
Member
 
Registered: Mar 2005
Distribution: Ubuntu 12.04 LTS
Posts: 249

Original Poster
Rep: Reputation: 16
Your first suggestion worked - when I enter the following commands, I can connect to Navicat but the device connected to my laptop can no longer connect to the internet, so the routing seems to have been broken. However, there didn't seem to be any DNS problems (meaning I can still ping google.com from my laptop) or anything similar that you warned could appear. And MySQL doesn't seem to complain about reverse DNS. Thanks for your help, it seems to be getting closer to a solution where I can connect to Navicat and the device can connect to the internet.

Quote:
sudo iptables -t nat -F
sudo iptables -t nat -A POSTROUTING -p tcp ! --dport 3306 -j MASQUERADE
sudo iptables -F FORWARD
sudo iptables -A FORWARD -i eth0 -o wlan0 -j ACCEPT
sudo bash -c "echo 1 > /proc/sys/net/ipv4/ip_forward"
And when I enter the following commands that I was using originally, the device connected to my laptop can connect to the internet, but I can't connect to Navicat:

Quote:
sudo iptables -t nat -F
sudo iptables -t nat -A POSTROUTING -j MASQUERADE
sudo iptables -F FORWARD
sudo iptables -A FORWARD -i eth0 -o wlan0 -j ACCEPT
sudo bash -c "echo 1 > /proc/sys/net/ipv4/ip_forward"
(In Navicat, I enter 'localhost' as the host to connect to, rather than using an IP address or anything else.)

Last edited by veeruk101; 10-21-2011 at 06:47 PM.
 
Old 10-22-2011, 08:47 AM   #8
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,497

Rep: Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636
Quote:
Originally Posted by veeruk101 View Post
Your first suggestion worked -
That isn't much use, if it breaks routing, is it?

Quote:
Originally Posted by veeruk101 View Post
when I enter the following commands, I can connect to Navicat but the device connected to my laptop can no longer connect to the internet
Can you confirm that Navicat connects to MySQL and that "I can connect to Navicat" is an incorrect expression? If the expression is correct, what/how connects to Navicat?

Does the "device" play any role in the MYSql/Navicat setup or you just want to connect the device and use internet thru that laptop?

Can you restore the IPTables situation where the device can connect to internet and try the bind-address suggestions in the my.cnf file?

jlinkels
 
Old 10-24-2011, 06:47 AM   #9
veeruk101
Member
 
Registered: Mar 2005
Distribution: Ubuntu 12.04 LTS
Posts: 249

Original Poster
Rep: Reputation: 16
You're right, I meant to say 'Navicat connects to MySQL' rather than 'I can connect to Navicat'. The device doesn't play any role in the MySQL/Navicat setup. I restored the iptables setting where the device can connect to the internet, but the bind-address suggestions didn't work.

However, the following did - adding a record to mysql.user with host set to '192.168.%', user set to 'root', and everything else the same as the record with host set to 'localhost' and 'user' set to root. Now I don't know whether this is the right way to go about solving this problem, because it would seem this would just be a symptom of some other misconfiguration on my part. But it works... I'm stumped.

Perhaps in my other thread if I were to explore the option you suggested of using the 'route' command to make the device access the internet, then I wouldn't need to mess with iptables and then I could likely remove this new record from the mysql.user table.
 
Old 10-24-2011, 07:00 AM   #10
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,497

Rep: Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636Reputation: 636
I have to admit I totally forgot about the permissions in MySQL itself. Maybe because I allow connections from all hosts, and I use the bind-address to control the networks from which connections are allowed.

There should be no reason why Navicat presents itself with a different IP address than localhost. But since it does there is nothing wrong with allowing it to connect to MySQL using that IP address. Just make sure the 3306 port is not allowed to pass through in your internet modem.

Good error finding.

jlinkels
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
iptables rule to accept all connection from a program f14f21 Linux - Networking 2 10-29-2010 12:27 PM
connection refused in my client server program jamesbon Programming 1 10-23-2010 10:28 PM
MySQL odbc connection remote client keirvt Linux - Server 4 07-29-2010 11:28 PM
MySQL client/server connection problem crashsystems Linux - Server 2 02-06-2007 12:05 PM
Impossible to use autotools to compile a MySQL client program? rylan76 Linux - Software 0 10-08-2005 08:34 AM


All times are GMT -5. The time now is 05:44 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration