CSF not allowing "Remote MySQL" IP's through anymore.

[SoftDux]

Some time back, someone shared a script which could allow "Remote MySQL" IP's through.

Here's the script:

Code: Select all

mysql mysql -e "select Host,User from user where Host!='localhost' group by Host;" | awk {'print "tcp:in:d=3306:s=" $1 "\t# " $2'} | sed "s/\%//g" | egrep "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" | grep -v "127.0.0.1" | sort | uniq

Running the script in SSH gets a list of IP's, and if I allow an IP manually through it works fine.
i.e. here's the list of IP's which the code above generated:

Code: Select all

tcp:in:d=3306:s=154.66.196.44   # aaamaree
tcp:in:d=3306:s=154.66.196.69   # aaamaree
tcp:in:d=3306:s=155.254.31.50   # bonzai
tcp:in:d=3306:s=165.144.190.249 # cwayyorg
tcp:in:d=3306:s=192.168.128.219 # aaamotor
tcp:in:d=3306:s=196.34.136.111  # aaamotor
tcp:in:d=3306:s=196.34.19.36    # cmggssof
tcp:in:d=3306:s=197.96.136.124  # cpses_zanCCjCP32
tcp:in:d=3306:s=199.83.209.221  # aaamotor

These IP's have been added by various users in their cPanel. But the IP's don't get added to the firewall allow list:

Code: Select all

 csf -L | grep 165.144.190.249

Adding the IP manually through SSH works fine though:

Code: Select all

 csf -a tcp:in:d=3306:s=165.144.190.249   # aaamaree
Adding tcp:in:d=3306:s=165.144.190.249 to csf.allow and iptables ACCEPT...
ACCEPT  tcp opt -- in !lo out *  165.144.190.249  -> 0.0.0.0/0  tcp dpt:3306
root@mercury:[~]$ csf -L | grep 165.144.190.249
1        0     0 ACCEPT     tcp  --  !lo    *       165.144.190.249        0.0.0.0/0           tcp dpt:3306

As soon as I did that I could connect to the MySQL server from a remote location.

This used to work some time ago, though not anymore.

Code: Select all

root@mercury:[~]$ csf -v
csf: v9.24 (cPanel)

root@mercury:[~]$ uname -a
Linux mercury.x.x 2.6.32-673.8.1.lve1.4.3.el6.x86_64 #1 SMP Wed Feb 10 08:57:30 EST 2016 x86_64 x86_64 x86_64 GNU/Linux

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1736
Server version: 5.5.52-cll MySQL Community Server (GPL)

[sparek]

You have to restart csf after adding lines to /etc/csf/csf.allow

Code: Select all

csf -r

Probably the best way to do this is to utilize Include in the /etc/csf/csf.allow

Code: Select all

echo "Include /etc/csf/mysql.allow" >>/etc/csf/csf.allow
touch /etc/csf/mysql.allow

This tells csf to include IP address structures in the /etc/csf/mysql.allow file into the allow system.

Then change the MySQL IP address grabber:

Code: Select all

mysql mysql -e "select Host,User from user where Host!='localhost' group by Host;" | awk {'print "tcp|in|d=3306|s=" $1 "\t# " $2'} | sed "s/\%//g" | egrep "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" | grep -v "127.0.0.1" | sort | uniq >/etc/csf/mysql.allow
csf -r

Note, I have changed the separators from : to | which has been the preferred method for some time now.

Note you are now redirecting this output into /etc/csf/mysql.allow meaning it can be overwritten over and over again without affecting your /etc/csf/csf.allow file.

[SoftDux]

Thanx,

Your code doesn't quite work as expected. If I manually add my IP address to /etc/csf/mysql.allow, I can connec to the port 3306, but with the "MySQL IP grabber" script you provided, in the /etc/csf/csf.allow, it doesn't automatically grab the IP's from the "Remote MySQL" list in cPanel.

It's asif /etc/csf/csf.allow doesn't execute scripts anymore.

[sparek]

Huh? /etc/csf/csf.allow doesn't execute anything. csf reads it when you restart it.

What specifically is not working?

What does

Code: Select all

mysql mysql -e "select Host,User from user where Host!='localhost' group by Host;" | awk {'print "tcp|in|d=3306|s=" $1 "\t# " $2'} | sed "s/\%//g" | egrep "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" | grep -v "127.0.0.1" | sort | uniq

show?

Either this code piece is outputting IPs from the mysql.user table or it is not.

[SoftDux]

Huh? /etc/csf/csf.allow doesn't execute anything. csf reads it when you restart it.

What specifically is not working?

What does

Code: Select all

mysql mysql -e "select Host,User from user where Host!='localhost' group by Host;" | awk {'print "tcp|in|d=3306|s=" $1 "\t# " $2'} | sed "s/\%//g" | egrep "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" | grep -v "127.0.0.1" | sort | uniq

show?

Either this code piece is outputting IPs from the mysql.user table or it is not.

As I said initially, when that piece of code was added /etc/csf/csf.allow, it was executed as a script, and the IP's were automatically allowed through the firewall as soon as the client typed his IP address in the "Remote MySQL' screen in cPanel.

Now it doesn't do it anymore, so I guess the devs took the scripting feature away from /etc/csf/csf.allow

[sparek]

Yea, you'll need to get someone from ConfigServers involved with this.

As far as I know, /etc/csf/csf.allow has never allowed anything to be executed inside of it. I'm not really sure what you are doing. But there appears to be a misunderstanding some where.

[SoftDux]

Yea, you'll need to get someone from ConfigServers involved with this.

As far as I know, /etc/csf/csf.allow has never allowed anything to be executed inside of it. I'm not really sure what you are doing. But there appears to be a misunderstanding some where.

Well, I did initially post it in the "Report bugs" forum but see it was moved here. And if you did what I did, when it worked, you would have understood