Mysql allow user to connect only from specific DNS

2
votes

I'm using amazon RDS and amazon EC2.

For amazon EC2 I've configurated load balancer, and I'm going to use autoscalling in future.

I'd like to create user in my RDS Mysql instance, that can access to mysql only from specific DNS.

For example: I've domain 'test.com' which is CNAME for loadbalancer DNS. When I'm creating user in mysql 'test-user'@'test.com' and trying to connect from my EC2 instance I have an error message: Access denined for user 'test-user'@'Private ip of my ec2 instance here'.

So I can't use ip address in mysql, because I'm going to use autoscalling, and amazon will create new instances with unknown ip, and mysql didn't resolve hostname of my instance.

Do you have any ideas?

Updated: answer for @Frédéric Henri question "do you have security group on your RDS instance ?"

Yes of course, I have security group. But it's not suitable in this case, because with security group I can allow to connect to the database only from instances inside my VPC. I need to give access to the database to another guy, but I'd like to create user which has readonly access (only select)

1
mysqlamazon-web-servicesamazon-ec2amazon-rds
do you have security group on your RDS instance ? – Frederic Henri
You are trying to logically connect two unrelated concepts. The hostname MySQL will see when authenticating a user has no relationship whatsoever to the domain name of your web site. – Michael - sqlbot

1 Answers

1
votes

One thing to note is that you can use wildcards in MySQL grants, such as test-user@'192.168.123.*'. You could grant access to the subnets that your instances launch in.

You can also use network masks, which are probably more appropriate - e.g. test-user@'192.168.123.0/255.255.255.0'. See the documentation for more information.

It appears that can also use DNS (according to the same post), but as Michael has pointed out, it would not be anything to do with the DNS name of your load balancer. I think it would have to be a DNS name that resolves to the IP address of your instance. This would only help you if you used a wildcard in your grant (e.g. test-user@'%.my-autoscaling-instances.mydomain.com' and had a DNS entry for each of your autoscaling boxes. I am not sure exactly how this mechanism works, and I'm not convinced that it's a good approach.