How does mysql determine the hostname of its clients?

Question

I'm trying to create a MySQL user which will only be allowed to connect to the MySQL database from a specific hostname.

grant all on db_name.* to 'user_name'@'appserver-lan.mydomain.com' identified by 'some_passwd'

By checking the user table on the mysql db, I can see that the user was created successfully:

use mysql; select * from user where User='user_name' and Host='appserver-lan.mydomain.com'

or

show grants for 'username'@'appserver-lan.mydomain.com'

The hostname I specified is an alias to an amazon-ec2 name, which when resolved by the AWS DNS servers results in a LAN address:

[root@db_server ~] # host appserver-lan.mydomain.com

appserver-lan.mydomain.com is an alias for ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com has address 10.xxx.xxx.xxx

The problem is that when I try to connect to the database LAN IP from this appserver-lan, I get an access denied error, although the password is correct. The weird thing here is that the hostname showed in the error is not the hostname I had specified when the user was created:

ERROR 1045 (28000): Access denied for user 'user_name'@'appserver.mydomain.com' (using password: YES)

So, my question is: how does mysql determines the client hostname? I believe it doesn't do so by a reverse DNS lookup, since I checked and it does not point to "appserver.mydomain.com" neither to "appserver-lan.mydomain.com". Additionally, the db server has no entries related to the appserver on /etc/hosts.

Summarizing, I'm pretty sure it's a hostname resolution issue, since granting privileges for host "%" or to the LAN IP works just fine.

Any ideas of what I'm missing?

Solution 1

It uses a reverse DNS lookup. It takes the IP address of the client and uses whatever PTR record is returned for that name.

In my opinion doing authentication based on the name is not very useful at all, I suggest you consider using IP addresses instead.

See this document about how Mysql uses DNS.

Solution 2

I've just had a similar problem, where the mysql server appeared to be doing reverse DNS lookups incorrectly.

The problem I had was that the server had permissions for 'user'@'1.2.3.4' as well as 'user'@'reverse.dns'. The user with just the IP address had minimal permissions, but the mysql server was using the permissions of that user rather than the one with the hostname, and returning the message "Access denied for user 'user'@'1.2.3.4'". Deleting the user with the IP address fixed the problem and forced the serer to use the other user at the hostname.

Solution 3

MySQL will be doing a reverse DNS look up on the IP address to get the host name. If you are running in AWS EC2 then you can assign an elastic IP to your server (this does not cost any extra) and then ask amazon to set up reverse DNS for the elastic IP to go to your hostname.

Also is your DB server also in EC2? Because if so it will be using the private IP address of the instance, otherwise it will be using the public ip address. It looks from you post as thou appserver-lan is the 10.XXX.XXX.XXX private ip assigned to your server, not the putlic one.

I'm not sure which IP address would be used if communicating with a different region though as I have only had servers within the same region.

Comments

[Zoredache]

Why do you think it is not reverse DNS? What happens when you do host -t PTR 10.1.2.3?

[Luis Fernando Alen]

host -t PTR 10.xxx.xxx.xxx xxx.xxx.xxx.10.in-addr.arpa domain name pointer ip-10-xxx-xxx-xxx.ec2.internal. As you can see, this is not the hostname showed in the error message (appserver.mydomain.com) and that made me think MySQL was not doing a reverse lookup.

[Zoredache]

Can you skip the IP/name authentication alltogether, instead either use a host-based firewall, or setup SSL and Cert-based authentication.

[Luis Fernando Alen]

Yes, it's on EC2 as well and at the same region. Thanks for the tip, Andy. I didn't know EC2 instances use the internal ip to communicate between them even if you specify the valid ip for the communication. That solved my problem =]