Is DNS the Achilles heel in your MySQL installation?

Is DNS the Achilles heel in your MySQL installation?

PREVIOUS POST
NEXT POST

Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn’t. Don’t let it catch you off guard.

Do you really need to restrict MySQL users based on hostnames? If you don’t, you should probably disable this feature of MySQL’s authentication system. You never know when your hosting provider’s DNS (or your own for that matter) will go into the toilet. And when that happens, MySQL mysteriously stops letting users log in, and all kinds of chaos ensues. Worse, it can be kind of hard to know that this is the problem, and diagnosing adds to your downtime.

Here’s another scenario: DNS doesn’t really fail. It just gets a little bit slow. Subtle enough that you don’t really notice it, but enough to cause connection problems every now and then.

I’ve seen both scenarios recently when working with clients. Oh, and did I mention that not enabling skip_name_resolve actually leaves you open to DoS attacks, if your servers are externally accessible?

To disable two DNS lookups per authentication attempt, you just need to set skip_name_resolve in your my.cnf file and restart MySQL. But before you do that, run the following command:

Any users you see here need to be converted to use IP addresses, IP address wildcards, or ‘localhost’ or they won’t be able to log in after you disable DNS resolution.

For more information on how and why MySQL does both a forward and reverse DNS lookup on authentication attempts by default, read the MySQL manual page.

PREVIOUS POST
NEXT POST

Share this post

Comments (22)

  • peter Reply

    Baron,

    You could explain why DOS attacks are happening 🙂

    Indeed –skip-name-resolve should be the option you turn on for almost any production installation. If you do it early before having to deal with hostname based grants it is very easy.

    I also would note effects of host name resolve magnifies because of various bugs with host-cache in different MySQL versions which could cause more resolves when needed as well as the fact this cache size neither can be configured nor seen so typically you have no transparency about how frequently you get cache hits vs misses.

    Having some reasonable defaults on resolve timeout would also make sense.

    Another related issue I should mention – I’ve seen some operating systems building MySQL with tcp wrappers – in this case reverse lookup happens even if you have –skip-name-resolve enabled.

    May 31, 2008 at 5:29 pm
  • Baron Schwartz Reply

    Peter, right: DoS attacks can happen if the attacker makes sure their IP addresses don’t reverse lookup to anything. Then you just try to connect to the server a bunch of times 🙂

    Another thing I could have mentioned — things can potentially break if you connect from name1.com and reverse DNS says that your IP address actually maps to name2.com.

    Really, there are so many ways it can go wrong… I think the general advice is “try to build for zero dependence on DNS”.

    May 31, 2008 at 7:17 pm
  • jeffatrackaid Reply

    I find hostnames very useful for some setups. They are often easier to digest at a glance than IPs, especially if you have complicated setups or have 3rd parties that need to post to a table (very common with some payment processing vendors).

    How well does the mysql hostname cache work? I thought mysql cached hostnames internally?

    If mysql is using gethostbyname_r(), you should be able to use /etc/hosts and modify /etc/host.conf to assure /etc/hosts is read first. This is how I setup our boxes in a cluster a manage that involves 1 master server and 4 slaves. There are another 15 boxes in the cluster. We’ve some scripts to keep /etc/hosts files updated on all servers. This makes it much easier to manage than keeping track of IP addresses.

    May 31, 2008 at 8:03 pm
  • Gregory Haase Reply

    So if you store IP addresses in the grants, it doesn’t matter whether domain resolution is on or off, right?

    I’ve always strictly used IP Addresses in the grants because I can wildcard them to some degree. This can be particularly nice when you have a scale out model where you might not have a definitive number of slaves. As long as they are all on a particular subnet, you can set your host e.g. (‘192.168.20.%’). Then only machines from that block of IPs can connect as that user. If I add a new slave, I don’t have to worry about the grants.

    As far as IP Addresses being easy to digest – it’s generally a one-time, set it and forget it sort of thing. You have a network guy or you are the network guy, and he gives you a diagram or tells you what the subnets are. Machines outside our firewall generally do NOT have access to our database servers. If there’s a third party that’s going to post to our servers, there’s an application layer on top where we can do user and data validation, or if it’s a really trusted third party, then we’ll do a VPN tunnel (in which case they map to an internal IP) or similar.

    I don’t know, seems like the red flag in this article isn’t “resolving hostnames” so much as “externally accessible”.

    June 1, 2008 at 7:11 am
  • Baron Schwartz Reply

    Gregory, the emergency customer I helped yesterday would disagree about the red flag =D

    June 1, 2008 at 7:13 am
  • peter Reply

    Gregory,

    If there are not –skip-name-resolve MySQL will always resolve host name even if your grant tables only happen to contain IP addresses.

    June 1, 2008 at 8:54 pm
  • Baron Schwartz Reply

    Jeff, “How well does the mysql hostname cache work? I thought mysql cached hostnames internally?” It does, and it’s actually kind of buggy itself, plus it’s hard to manage.

    And in other news, I just got another emergency call that turned out to be DNS issues.

    June 2, 2008 at 4:18 am
  • Erik Ljungstrom Reply

    Larger installations often end up needing cryptic hostnames á la db3.cluster2.datacenter3.example.com to make sense anyways. With some planning and a cunning subnetting scheme, I think IP addresses often end up more readable than their DNS counterpart regardless.

    June 2, 2008 at 10:45 am
  • Jason Frisvold Reply

    Is there a way to verify that skip_name_resolve is set? I have it set in the my.cnf file but I’d like to verify this from within MySQL itself.

    June 3, 2008 at 8:11 am
  • Baron Schwartz Reply

    Interesting: there is no variable for this in SHOW VARIABLES. You should submit a bug report.

    June 3, 2008 at 10:47 am
    • Kumar K Shiva Reply

      mysql> show variables like ‘skip%’;
      +———————–+——-+
      | Variable_name | Value |
      +———————–+——-+
      | skip_name_resolve | OFF |

      September 14, 2016 at 1:27 am
  • Jason Frisvold Reply

    Well, at least that confirms that I’m not completely crazy. 🙂 I did check variable and global variables, but to no avail… I’ll head over and submit a report right away!

    June 3, 2008 at 11:36 am
  • Jason Frisvold Reply

    Submitted. Bug #37168

    June 3, 2008 at 11:47 am
  • Jeremy Cole Reply

    Hi Baron, All,

    I wrote about these issues in some detail about two years ago:

    http://jcole.us/blog/archives/2006/04/26/on-ips-hostnames-and-mysql/

    Regards,

    Jeremy

    August 1, 2008 at 11:52 am
  • John Marc Reply

    >Is there a way to verify that skip_name_resolve is set?

    One way is to grant access to a user using a domain name, restart mysql and look in the error log
    ex:
    080819 14:29:06 [Warning] ‘user’ entry ‘vbskin@cpvps%’ ignored in –skip-name-resolve mode.

    August 19, 2008 at 4:39 pm
  • Holger Thiel Reply

    Hello,

    Is it a good idea to disable the host-cache if you do not use DNS resolving?

    The server should be a little response faster (or not?).

    The host-cache is also used for black listing. So black listing would be disabled.

    Regards,
    Holger

    August 16, 2011 at 9:08 am
  • Baron Schwartz Reply

    I haven’t measured, but I doubt it makes a measurable difference.

    August 16, 2011 at 9:27 am
  • Thierry M. Reply

    A comment on this old post which I just came across today. The query:

    SELECT user, host FROM mysql.user
    -> WHERE host ‘localhost’ AND host RLIKE ‘[a-z]’;

    should be updated by using ‘127.0.0.1’ explicitely instead of ‘localhost’ on newer versions of MySQL. On my 5.6.19 install, it seems that MySQL is trying to resolve localhost, which it can no longer do when skip-name-resolve is ON, thus denying access to MySQL.

    September 23, 2014 at 8:50 am
  • Crystal Reply

    @Thierry M. Thank you for the tip!

    October 28, 2014 at 10:06 pm
  • Kevin K Reply

    There are two sides to this story, though. When this article was written a couple years ago, IP addresses may well have been more readable, but with IPv6, the premise is that IP addresses can be cryptic because everybody would rely on DNS. Another issue is that using IP addresses all but prevents using SSL/TLS encryption. Also, there is a security value in host-based authentication; it makes hacked credentials useless from other hosts.

    August 17, 2015 at 7:46 am
  • none Reply

    dead link – MySQL does both a forward and reverse DNS lookup on authentication attempts by default, read the MySQL manual page.

    March 10, 2016 at 7:30 am
  • Unika Infocom Reply

    OK, I’m using a high Resource Website. Do you think remote MySQL Database connection Idea is Good foe Website performance ?

    August 24, 2017 at 4:10 am

Leave a Reply