Block MySQL (port 3306) connects from the outside?



  • I run an Apache server and MariaDB backend locally.  MariaDB listens on 3306.  I don't want anyone from the inet trying to connect to the Maria server.  But I'm not sure I understand how to create a rule to prevent it.

    Clearly I need to block port 3306.  But on which interface?  Maria is listening on 3306 on the server box, but I'm guessing the listening propagates to the firewall's 3306 too, presumably on the WAN port to make it visible to the outside.  Yes?  No?  :-\

    Help?



  • Do you intend your Apache server to be visible from WAN?
    Is your MariaDB running on the same system as Apache?

    Assuming your Apache server and MariaDB are running on the same host, configure MariaDB to listen to only localhost (I'm assuming it can be configured to do that).  If they are not running on the same host, but they are both on the same "side" of your pfSense box (LAN), a block rule "from any/any to mariadbip port 3306" on WAN would work.  But you may not need that;  WAN has a default deny unless it's in response to traffic originated on LAN side.

    A simple network picture of the systems would make it easier to help.



  • As mer already said, the default WAN rule is to allow nothing in.  Unless you have specifically added a rule on WAN that allows TCP access on 3306, nothing can get there.



  • @mer:

    Do you intend your Apache server to be visible from WAN?

    No, but I have a .conf rule taking care of that - nobody but the LAN can make requests.

    Is your MariaDB running on the same system as Apache?

    Yes, everything's on one box as a convenience.

    Assuming your Apache server and MariaDB are running on the same host, configure MariaDB to listen to only localhost (I'm assuming it can be configured to do that).

    In theory, a my.ini line "bind-address=127.0.0.1" will do it, but there seems to be some question about whether it really works.

    If they are not running on the same host, but they are both on the same "side" of your pfSense box (LAN), a block rule "from any/any to mariadbip port 3306" on WAN would work.  But you may not need that;  WAN has a default deny unless it's in response to traffic originated on LAN side.

    I think that's what I'm looking for.  I couldn't figure out whether listening on 3306 qualified as "traffic originating on the LAN side", so I thought it'd be safer to have an explicit shut-out for both.

    A simple network picture of the systems would make it easier to help.

    It's not sophisticated in this instance – just the dev box and the firewall box connected via a dumb hub.  That'll change when I find time to bring up the NAS box, but for right now there's only the two boxes.



  • @KOM:

    As mer already said, the default WAN rule is to allow nothing in.  Unless you have specifically added a rule on WAN that allows TCP access on 3306, nothing can get there.

    I was just nervous because I couldn't find anything that explained whether listening on a port qualified as an invitation to come in.  It might be worth a mention in the FAQ.


  • LAYER 8 Global Moderator

    "It might be worth a mention in the FAQ."

    huh?? If you do not forward the ports on pfsense.  Then does not matter if you were listening on every single port.  This is basic firewall 101, do we really need to create a faq for this sort of stuff?



  • I was just nervous because I couldn't find anything that explained whether listening on a port qualified as an invitation to come in.

    Better safe than sorry.  Access to internal resources requires both a port forward/NAT of some type, plus a firewall rule to allow access through the firewall.  If someone inside your house is listening for someone outside, that someone outside won't be able to talk to them unless you open the front door.



  • @johnpoz:

    "It might be worth a mention in the FAQ."

    huh?? If you do not forward the ports on pfsense.  Then does not matter if you were listening on every single port.  This is basic firewall 101, do we really need to create a faq for this sort of stuff?

    Well, I didn't know it, and I made my living writing/managing software.  Not everyone is an expert in everything.

    I hope you'll agree that, in general, providing more information is better than providing less.



  • Well, I didn't know it, and I made my living writing/managing software.  Not everyone is an expert in everything.

    True, but you're kind of expected to know the basics when you're working with an expert-level tool.  Just as Visual Studio (or your IDE of choice) doesn't teach you how to program, pfSense won't guide you through everything.  Unfortunately, if you screw it up with a firewall due to inexperience or lack of knowledge, you end up leaving your ass hanging out on the Internet.  At least you're asking the right questions beforehand.  Most people new to firewalls/networking seem to play around just to get something working without understanding all the potential pitfalls and ramifications of what they did.



  • And one of the best tools for getting started is a piece of paper and pencil (or whiteboard and markers).  Draw a box that represents the firewall.  draw an arrow representing the outside world.  draw a line representing the inside world, connecting to another box (switch).  draw a few boxes for internal clients, connecting them to the switch.  That's your phyical representation of your network.

    Now stand on the firewall, start with "default deny" on all interfaces.  Nothing gets in or out unless explicitly allowed.  That is locked down, your starting point.
    Now go to the clients;  decide what traffic out of them you want to allow.  Everything?  HTTP, HTTPS only?  Then you need a rule on the firewall LAN port to allow it.  Work the traffic through the firewall, keeping in mind that the firewall keeps state.  Keeping state means that a response to your outbound traffic is allowed back in.  Repeat as needed for your traffic types, adding in any NAT or redirects from the outside as you need.

    Even if you have a single client on the LAN side going to a single WAN out of the firewall, the above exercise makes you understand the whole system and when you add clients or services, you can do it with minimal problems.


  • LAYER 8 Netgate

    And there are always experts in the field available for hire if you don't know what you're doing.

    I'm not quite sure why so many people think administering a firewall is something anyone ought to be able to do.


  • LAYER 8 Netgate

    @mer:

    Now stand on the firewall, start with "default deny" on all interfaces.  Nothing gets in or out unless explicitly allowed.  That is locked down, your starting point.

    Fixed it for you.



  • @Derelict:

    And there are always experts in the field available for hire if you don't know what you're doing.

    I'm not quite sure why so many people think administering a firewall is something anyone ought to be able to do.

    "Linksys WRT54G"  is probably why.  :o



  • @Derelict:

    @mer:

    Now stand on the firewall, start with "default deny" on all interfaces.  Nothing gets in or out unless explicitly allowed.  That is locked down, your starting point.

    Fixed it for you.

    Well one could actually "block on interface blah" which would not let anything out, then add "pass out on blah from self to any port xyz".

    pf lets you block all in and out on an interface;  of course one needs console interface to configure it then.


  • LAYER 8 Netgate

    The problem is the default configuration is to block anything IN except for the default pass rule in on LAN.  In that default configuration, pfSense is free to make OUTBOUND connections itself, such as DNS queries or OpenVPN client connections.

    Beginners start to understand things better when they figure out that rules generally allow traffic INTO pfSense, not OUT of it.  With that information they start to understand:

    1. What interface a rule should go on
    2. What the source information should be
    3. what the destination information should be

    Floating rules can be used to block outbound traffic on an interface but that is an advanced topic.



  • As for the way pfSense, I'll concede the point.  :o

    I think my method of drawing the picture helps with your 3 points.



  • @KOM:

    Well, I didn't know it, and I made my living writing/managing software.  Not everyone is an expert in everything.

    True, but you're kind of expected to know the basics when you're working with an expert-level tool.

    Actually, I did know the basics.  What I didn't know was the answer to that one rather obscure point.

    I started to wonder about it because, although I had the default block-everything on the WAN, when I'd connect to some site, commondreams.org for an actual example, I found that the firewall was letting third-party stuff through that had no permitting rule.  It was obviously piggybacking on the connection to commondreams even though I had never explicitly permitted it.  It was happening under the table.  It seemed to me that since it had a different ip address, it should have been scraped off rather than let through.

    So if there's an implicit rule that allows stuff through from a totally different ip origin, I wondered whether it would also let someone come in on 3306 if they happen to stumble across it.  I found that a much less unreasonable idea than that the firewall would let in stuff from a completely different source than the one I intentionally connected to – that really bothered (and bothers) me because it looks so Trojan-Horse-y.

    (Another example:  I'm voluntarily connected only to this forum right now, but netstat shows connections to facebook and google's snoopage site 1e100.net.  That's pretty disturbing since I definitely do not have rules with their names on!)


  • LAYER 8 Global Moderator

    "It was obviously piggybacking on the connection to commondreams even though I had never explicitly permitted it."

    Dude BS plain and simple.. So you do understand when you go to a website there are going to be lots of different urls to load say images, ads, scripts, etc. etc..  And your machine is going to make connections to them.. They are not making unsolicited connections to your machine.



  • The outbound request is from your IP, ephemeral port (a.b.c.d:XYZ), to 1.2.3.4, port 80.  Stateful firewall puts it in a table showing valid connection with a.b.c.d:XYZ.  All of these "piggybacked" traffic comes with dest addr of a.b.c.d:XYZ so they match state.

    As for the db app listening on 3306, basic rule is Listening sockets don't send unless it's in reponse to something.  That's pretty much basic networking, nothing magical about it.  Every db server that I've come across doesn't send anything unless it's in response to something, so unless there is a funky configuration thing where your db server starts up and sends unsolicited traffic out the WAN port, there is nothing to be concerned about.  Again, default deny in on WAN covers pretty much everything until you start adding pass rules.


  • LAYER 8 Global Moderator

    lets say you were talking to 1.2.3.4:80 from your private machine, and pfsense created a state from its publicIP:666 to 1.2.3.4 – even if traffic hits your publicIP:666 its out of state because its not coming from 1.2.3.4.

    I do believe that pfsense uses Port Restricted Cone NAT not only does the traffic have to be coming from 1.2.3.4 it also has to be coming from a source port of 80 since that is the state that was connected.

    Unsolicited inbound traffic to pfsense is BLOCKED out of the box.. unless you created rules to allow such traffic or its in answer to a state created by a machine going outbound the traffic is blocked.  Does not matter what inbound ports some box inside is listening on.

    Now what can get you in trouble is UPnP where boxes can open up ports, or 1:1 nat with any any sort of rules.



  • @johnpoz:

    "It was obviously piggybacking on the connection to commondreams even though I had never explicitly permitted it."

    Dude BS plain and simple.. So you do understand when you go to a website there are going to be lots of different urls to load say images, ads, scripts, etc. etc..  And your machine is going to make connections to them.. They are not making unsolicited connections to your machine.

    And generally, because of the same-origin rule, the images and "semantic sugar" (apologies to Knuth) javascript is coming from the site (CommonDreams in my example) to which I explicitly connected.

    Javascript executing at CD's control point in the browser is a guest.  It shouldn't be able to extend a cross-site invite to google or some other advert pusher or bit collector to come in and grab a port, and if it can then it seems to me that something's broken.  Since most such connections aren't secure, that'd be a great way to capture systems via man-in-the-middle attacks.  Just grab the packets, doctor them and pass them on, and bingo, your victim's site unwittingly drags the trojan horse through its gates, the gates (firewall) none the wiser.


  • Banned

    What's this thing? Another tinfoil hat thread?  ::) >:(


  • LAYER 8 Global Moderator

    Yeah dok yet another one thinking connections are piggy backing in his outbound connection and talking to his sql server..



  • @doktornotor:

    What's this thing? Another tinfoil hat thread?  ::) >:(

    Nope.  It started out as me trying to determine whether MariaDB listening on 3306 was enough to bypass the default block on the WAN port and allow some random chancer to connect from the inet.

    Since when I connect to some site A there certainly are ports being attached by unrelated sites B, C, and D in apparent defiance of the same-origin policy that is supposed to prevent cross-site scripting attacks, the question seemed like an important one.

    A partial selection from netstat -ao generated by exactly TWO intentional connects:  this site and truth-out.org:

    TCP    slowcat:2890          edge-star-shv-01-lga1.facebook.com:https  ESTABLISHED
    TCP    slowcat:2891          a23-33-44-73.deploy.static.akamaitechnologies.com:http  TI
      0
    TCP    slowcat:2892          edge-star-shv-01-lga1.facebook.com:https  ESTABLISHED
    TCP    slowcat:2893          ec2-54-88-159-215.compute-1.amazonaws.com:http  ESTABLISHE

    TCP    slowcat:2894          165.254.0.16:http      TIME_WAIT      0
    TCP    slowcat:2896          ec2-54-88-159-215.compute-1.amazonaws.com:http  TIME_WAIT
    TCP    slowcat:2897          ec2-54-88-159-215.compute-1.amazonaws.com:http  TIME_WAIT
    TCP    slowcat:2898          165.254.0.16:http      TIME_WAIT      0
    TCP    slowcat:2900          ec2-54-88-159-215.compute-1.amazonaws.com:http  TIME_WAIT
    TCP    slowcat:2901          199.16.157.105:https  ESTABLISHED    2624
    TCP    slowcat:2902          ec2-54-84-234-32.compute-1.amazonaws.com:http  ESTABLISHED
    TCP    slowcat:2903          199.16.157.105:https  ESTABLISHED    2624
    TCP    slowcat:2904          ec2-54-84-234-32.compute-1.amazonaws.com:http  TIME_WAIT
    TCP    slowcat:2905          lga15s42-in-f30.1e100.net:http  ESTABLISHED    2624
    TCP    slowcat:2906          165.254.0.11:http      ESTABLISHED    2624
    TCP    slowcat:2907          165.254.0.11:http      ESTABLISHED    2624
    TCP    slowcat:2908          lga15s42-in-f30.1e100.net:http  TIME_WAIT      0
    TCP    slowcat:2909          lga15s42-in-f30.1e100.net:http  TIME_WAIT      0
    TCP    slowcat:2910          165.254.0.11:http      TIME_WAIT      0
    TCP    slowcat:2911          165.254.0.26:http      TIME_WAIT      0
    TCP    slowcat:2912          165.254.0.11:http      TIME_WAIT      0
    TCP    slowcat:2913          lga15s42-in-f30.1e100.net:http  TIME_WAIT      0
    TCP    slowcat:2914          165.254.0.26:http      TIME_WAIT      0
    TCP    slowcat:2915          map-e.pipelane.net:http  TIME_WAIT      0
    TCP    slowcat:2916          70.42.33.241:http      TIME_WAIT      0
    TCP    slowcat:2917          ec2-52-1-141-196.compute-1.amazonaws.com:http  ESTABLISHED
    TCP    slowcat:2918          ec2-54-210-200-97.compute-1.amazonaws.com:http  ESTABLISHED

    Why isn't the default block scraping them off?  Because they came through on truthout's tuppence?  Anything, no matter what the origin, can get past the global block and establish a persistent connection even when the "official" connect is the stateless http?  That just doesn't feel appropriate to me.


  • LAYER 8 Global Moderator

    dude what part do you not understand about your machine making connections???  You think those are inbound connections to your machine??  To those random ports?  You really just don't even understand basic tcp do you??

    Oh my gawd I am being hacked

    TCP    192.168.9.100:1057    162.222.41.239:443    ESTABLISHED
      TCP    192.168.9.100:1058    209.191.165.124:5938  ESTABLISHED
      TCP    192.168.9.100:1061    216.17.8.48:443        ESTABLISHED
      TCP    192.168.9.100:1062    192.168.9.8:445        ESTABLISHED
      TCP    192.168.9.100:1068    173.194.192.125:443    ESTABLISHED
      TCP    192.168.9.100:1075    17.143.162.211:5223    ESTABLISHED
      TCP    192.168.9.100:1077    172.226.82.30:443      CLOSE_WAIT
      TCP    192.168.9.100:1126    108.161.147.131:993    ESTABLISHED
      TCP    192.168.9.100:1450    128.121.22.176:443    CLOSE_WAIT
      TCP    192.168.9.100:1458    192.241.165.183:443    CLOSE_WAIT
      TCP    192.168.9.100:1459    128.121.22.176:443    CLOSE_WAIT
      TCP    192.168.9.100:1504    192.241.165.183:443    CLOSE_WAIT
      TCP    192.168.9.100:1617    192.241.165.183:443    CLOSE_WAIT
      TCP    192.168.9.100:1626    192.241.165.183:443    CLOSE_WAIT
      TCP    192.168.9.100:1635    128.121.22.176:443    CLOSE_WAIT
      TCP    192.168.9.100:1720    128.121.22.176:443    CLOSE_WAIT
      TCP    192.168.9.100:5400    45.58.74.129:443      CLOSE_WAIT
      TCP    192.168.9.100:5835    192.241.165.183:443    ESTABLISHED
      TCP    192.168.9.100:6026    108.160.165.33:443    ESTABLISHED
      TCP    192.168.9.100:6536    107.20.249.126:443    CLOSE_WAIT
      TCP    192.168.9.100:6565    198.41.206.204:443    ESTABLISHED
      TCP    192.168.9.100:6569    17.151.226.69:443      CLOSE_WAIT
      TCP    192.168.9.100:6572    54.230.88.117:443      CLOSE_WAIT
      TCP    192.168.9.100:6599    37.48.81.86:443        ESTABLISHED
      TCP    192.168.9.100:6600    208.123.73.18:443      ESTABLISHED
      TCP    192.168.9.100:6601    208.123.73.18:443      ESTABLISHED
      TCP    192.168.9.100:6602    208.123.73.18:443      ESTABLISHED
      TCP    192.168.9.100:6604    37.48.81.86:443        ESTABLISHED

    Why don't you sniff on your machine and watch it create all the connections!!

    So for example this
    199.16.157.105

    if you hit via https like you are, its a twitter ssl cert

    The certificate is only valid for the following names: syndication.twitter.com, cdn.syndication.twitter.com, syndication-o.twitter.com, syndication.twimg.com, cdn.syndication.twimg.com, syndication-o.twimg.com

    So you think twitter connecting to you from source port of 443 to your 2903, or did your machine make that connection?? ;)  If I go to truth-out.org guess what it goes to twitter!!




  • Hmm I really tried up to follow this thread fully but I can´t! Sorry for that.

    If the Maria DB is behind NAT and no ports are opened and forwarded to the Maria DB, so they cannot
    be connected from the outside.

    And by the way placing all in one LAN is the really danger here as I see it right.
    Please create a DMZ for the Apache Webserver to get in contact with the Internet
    and place the Maria DB in the LAN what should be saved.

    It is like a bit with your PC or Laptop, on this software firewalls also ports are open
    and they connect from time to time the Internet for updates, but they are not reachable
    from the Internet because they are behind NAT!



  • @johnpoz:

    dude what part do you not understand about your machine making connections???  You think those are inbound connections to your machine??  To those random ports?  You really just don't even understand basic tcp do you??

    This is starting to become funny, in a kind of unfunny way.  You're saying that my machine is making those connects?  Spontaneously?  No outside motivation?!  Just the machine itself is taking the decision to connect?  I hope you're not, because my machine isn't that autonomous.

    What should be happening is that I call the browser's HTTP GET routine, which SYN/ACK handshakes with some site foo.org and then sends the actual page request.  Foo.org provides the page I requested, and since HTTP is stateless, drops the connect and forgets about me.  Any time I want another page, the browser has to go through the same process again.  (I did a quick check and HTTP still appears to be a stateless protocol)

    Nowhere in that process is a request directed to bar.com for a persistent connect.  My browser doesn't even know bar.com exists.  So who's doing it?  Is foo.org saying "here's the page you wanted and oh by the way here's a SYN on behalf of bar.com so please ACK/SYN it and let bar.com set up a persistent connect"?  If that's what's happening, I'd say the firewall isn't looking carefully enough at what's going on.


  • LAYER 8 Global Moderator

    Yes dude why don't you look at the what happens when you open a browser page.. yes going to truth-out.org tells your machine to go to twitter stuff and facebook stuff.  What do you not understand about this??

    Depending on the site and ads you could end up with hundreds of connections.. To who knows where..

    Your browser reads the code in a page, and follows it - if the code says hey open this up.. Guess what it tells your machine to open that up.. Pfsense just says hey your sending syn to this, rules allow it - there you go..

    You really should look at the source of the sites you go to, or open use say web developer in browser like I posted and it shows you all the stuff it does a GET for, etc..  Or just fire up wireshark on yoru machine and watch where the syn's come from.



  • @BlueKobold:

    Hmm I really tried up to follow this thread fully but I can´t! Sorry for that.

    If the Maria DB is behind NAT and no ports are opened and forwarded to the Maria DB, so they cannot be connected from the outside.

    That's how the whole thread got started – MariaDB is listening on 3306, and since the firewall box is the designated gateway, it's the 3306 on that box that's being listened on (actually, I hope that by having done bind-address=127.0.0.1 I've forced it to only play with the loopback, but that came later)

    And by the way placing all in one LAN is the really danger here as I see it right. Please create a DMZ for the Apache Webserver to get in contact with the Internet and place the Maria DB in the LAN what should be saved.

    This is a dev machine, so I don't really want Apache to talk to the inet either.  It's only supposed to serve the lan, and I think I've got the permits set for that even though their documentation for the new v2.4 scheme isn't the best.


  • LAYER 8 Global Moderator

    "MariaDB is listening on 3306, and since the firewall box is the designated gateway, it's the 3306 on that box that's being listened on"

    What???  NO pfsense is not listening on 3306 because some box behind it is??  Did you forward port 3306??



  • @johnpoz:

    What do you not understand about this??

    I don't understand why the firewall isn't interfering with such piggybacking.


  • LAYER 8 Global Moderator

    there is NO PIGGY BACKING!!!  dude you open up a web page it tells your machine to open up other stuff!  Period END of story..  What do you not understand??  Really open up that site and look at the source code, fire up wireshark and what what happens.. Where do the Syns come from..  Use something like web developer in fire fox that shows what your browser is doing where its going, what the GETS are, etc..

    so you understand what an iframe is??  So for example on your truth site

    
    <iframe src="//www.facebook.com/plugins/like.php?href=http%3A%2F%2Fwww.facebook.com%2FTruthout.org&amp;send=false&amp;layout=button_count&amp;width=120&amp;show_faces=false&amp;action=like&amp;colorscheme=light&amp;font&amp;height=21&amp;appId=174773479325394" frameborder="0" scrolling="no" width="150px" height="21px" allowtransparency="true" style="border: none; overflow: hidden; width: 150px; height: 21px;"></iframe>
    
    

    What does that tell your browser to do?  what do you think this code does

    Which is loaded from platform.twitter.com – guess your browser is going to go there as well.. So the firewall will allow it because your rules do..  There is no Piggy backing..

    If you have connections on your machine that you don not understand, then I suggest you investigate -- but it sure its not pfsense letting connections in via other connections..  Your box started the connection, or you forwarded the traffic in..  This is how a firewall works.  If your seeing connections to stuff you do not understand then why don't you investigate that - run wireshark on your machine..  What all the connections it makes when your not doing stuff ;)  You will see in my listing of netstat connections from teamviewer, connection from meraki cisco client on this machine.  dropbox checks in with home all the time..  Yes pfsense allows these connections because of your rules on your lan - which out of the box is any any!



  • That's how the whole thread got started – MariaDB is listening on 3306, and since the firewall box is the designated gateway, it's the 3306 on that box that's being listened on

    This is rubbish! Sorry but if I have a server or a program that is listening on one or more
    ports, but it is behind a lazy consumer router doing SPI/NAT, there is no was from the outside
    to connect to that program or such a server! Point.

    So if I will get your IP address, I can not do anything with it if all ports on your firewall or router are closed
    an they will be doing SPI/NAT. Only if I am opening at the WAN Interface (WAN Port) ports and forward them
    to the internal private IP address of the Maria DB host it will work that the Maria DB is able to connect from
    the outside.



  • I need to suspend this conversation awhile.  I'll be back.


  • Banned

    @MMacD:

    I'll be back.

    No need really for this conversation to continue. Negative net value in this thread.


  • LAYER 8 Global Moderator

    Yeah do a little reading on how tcp works and what a stateful firewall is and how web pages work ;)


Log in to reply