# PF doesn't like MySQL Connections



## shady (Jul 6, 2013)

Hey guys,

I wanted to try out FreeBSD as a system for my new project, a website, _be_cause of its flexibility and the high mentioned perfo_r_mance.

So I decided to run FreeBSD on my webserver including NGINX and PHP-FPM and on another dedicated server running with Ubuntu the MySQL server.

Everything is working without problems and for security reasons I set up a firewall with PF on the webserver using these rules:


```
TCP_SERVICES="{ 25, 80, 443, 555 }"

set skip on lo0

block in all
pass out all keep state

pass quick inet proto tcp from DBSERVER to self
pass quick inet proto tcp from self to DBSERVER

pass inet proto tcp from any to self port $TCP_SERVICES
```

My problem with PF and MySQL is now the following:

Randomly a connection between the MySQL server and the webserver is not possible with the following error: 
	
	



```
Operation not permitted
```
 (Code 2002, which means the connect has failed).

So I tried some other very basic rules for PF:


```
set skip on lo0

pass in all
pass out all keep state
```

And the interesting thing is: the problem still exists! But when I totally flush PF with loading no rules, the error doesn't appear, which seems to me very strange.

Also another user here posted the same problem, but without a solution: http://forums.freebsd.org/showthread.php?t=39010

My sysctl.conf:

```
vfs.usermount=0
vfs.read_max=128
kern.sched.slice=1
kern.bufcachepercent=90
kern.maxclusters=128000
kern.maxfiles=204800
kern.maxvnodes=200000
kern.maxfilesperproc=200000
kern.ps_arg_cache_limit=4096
kern.ipc.nmbclusters=262144
kern.ipc.somaxconn=65535
kern.ipc.maxsockets=204800
kern.ipc.maxsockbuf=16777216
security.bsd.see_other_uids=0
security.bsd.see_other_gids=0
security.bsd.conservative_signals=1
security.bsd.unprivileged_proc_debug=0
security.bsd.unprivileged_read_msgbuf=0
security.bsd.hardlink_check_uid=1
security.bsd.hardlink_check_gid=1

net.inet.tcp.mssdflt=1440
net.inet.tcp.cc.algorithm=cubic
net.inet.tcp.sendbuf_max=16777216
net.inet.tcp.recvbuf_max=16777216
net.inet.tcp.recvspace=8192
net.inet.tcp.recvbuf_inc=524288
net.inet.tcp.sendspace=262144
net.inet.tcp.sendbuf_inc=8192
net.inet.tcp.hostcache.expire=5400

net.inet.ip.ttl=126
net.inet.ip.rtexpire=2
net.inet.ip.rtminexpire=2
net.inet.ip.rtmaxcache=2048
net.inet.ip.check_interface=1
net.inet.ip.process_options=0
net.inet.ip.portrange.randomized=1
net.inet.ip.stealth=1
net.inet.ip.redirect=0
net.inet.ip.random_id=1
net.inet.ip.sourceroute=0
net.inet.ip.intr_queue_maxlen=4096
net.inet.ip.accept_sourceroute=0
net.inet.ip.forwarding=1
net.inet.ip.fastforwarding=1
net.inet.ip.portrange.first=1024
net.inet.ip.portrange.last=65500

net.inet.icmp.maskfake=0
net.inet.icmp.maskrepl=0
net.inet.icmp.bmcastecho=0
net.inet.icmp.log_redirect=0
net.inet.icmp.drop_redirect=1
net.inet.tcp.msl=5000
net.inet.tcp.ecn.enable=1
net.inet.tcp.delayed_ack=1
net.inet.tcp.drop_synfin=1
net.inet.tcp.icmp_may_rst=0
net.inet.tcp.maxtcptw=3240000
net.inet.udp.blackhole=1
net.inet.tcp.blackhole=2
net.inet.tcp.path_mtu_discovery=0
net.inet.tcp.fast_finwait2_recycle=1
net.inet.tcp.syncache.rexmtlimit=1
net.inet.tcp.syncookies=1
net.inet.tcp.nolocaltimewait=1
net.inet.tcp.local_slowstart_flightsize=32
net.inet.tcp.slowstart_flightsize=32
net.inet.tcp.ackonpush=0
net.inet.tcp.rfc1323=1
net.inet.tcp.rfc3390=0

net.local.stream.sendspace=163840
net.local.stream.recvspace=163840
```

Hope you can help me!

Thanks!


----------



## kpa (Jul 6, 2013)

Your rules may be basic but your sysctl(8) listing is far from basic. Have you verified them yourself that everything makes sense or is it something copy pasted from a guide somewhere?


----------



## shady (Jul 6, 2013)

I searched around the web for some sysctl optimizations for FreeBSD and finished my own config with the recommendations from there. You think it kills the connections? But I set high values for the limits.


----------



## kpa (Jul 6, 2013)

There's some many settings that I'm not even going to try to look if anything looks fishy. Comment them all out and see if it cures the problem. Then reapply them one by one paying very close attention to what they actually do. #occamsrazor.


----------



## quintessence (Jul 10, 2013)

Hello,

Code 2002 stands for cannot connect through socket (local MySQL server, not a remote one). You may also check is your application really tries to connect to the remote server in that time which you see this error with tcpdump. As @kpa suggested, always first try with defaults  If you have high load application with a lot of users and so on, later one by one place appropriate tuning where it is needed.

Anyway, how many are your connections to the MySQL server, I suppose you only test something now, you really not need such type of fine tuning at this moment. Don't forget also PF has own options for timeouts and limits and if you make such type of tuning via sysctl's you should also take a look in the PF's one.

For example, if you have high rate of connections between your web server and your DB server you may hit states limit which is 10000 by default.

If you hit states you may try something like:


```
TCP_SERVICES="{ 25, 80, 443, 555 }"
...
set skip on lo0
...
block in all
...
pass in quick on $ext_if inet proto tcp from any to $ext_if port $TCP_SERVICES no state
...
pass in quick on $ext_if from DBSERVER to any no state
pass out quick on $ext_if from $ext_if to DBSERVER no state
... 
pass out all
```

Check your established connections, check your states, try with defaults sysctl's, check traffic with tcpdump.

More information needed.


----------



## J65nko (Jul 10, 2013)

If your webserver needs to connect to a separate Ubuntu server running MySQL your firewall rules need to allow outgoing connections to port 3306:


```
[cmd=$]grep mysql /etc/services[/cmd]
mysql           3306/tcp   #MySQL
mysql           3306/udp   #MySQL
```


----------



## shady (Jul 11, 2013)

I just replaced my sysctl configuration with another one and it seems like the old sysctl settings had killed my connections.

It's working now without problems, but I will also try the "no state" option from @quintessence. It may be better for the future to prevent other problems.

Thanks!


----------



## CoTones (Jul 11, 2013)

Actually, stateless rules use more resources.


----------

