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?
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.
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.
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.
I have also seen problems where IPV4 and IPV6 hostnames and IP addresses do not match, and where a host name only is used for the user permission. For example, where there is a IPV6 reverse DNS but no forward IPV6 DNS.