Why does mysql on ec2 bind to priviate i.p?


#1

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

Doing:

telnet localhost 3306
it says unable to connect to remote host: connectino refused

telnet x.x.x.x 3306 (ec2 private ip) it works.

The private ip isn’t even in my /etc/hosts file either which was another
source of confusion for me :slight_smile:

Is there a good reason for this? What is wrong with localhost?

server.rb has the following:

default[‘mysql’][‘bind_address’] = attribute?(‘cloud’) ?
cloud[‘local_ipv4’] : ipaddress

my.cnf gets set with this:

bind-address x.x.x.x


#2

Private IPs are used by default with most cookbooks because Amazon (and others) charge for bandwidth in and out of their data center. For a database, this could be a lot of traffic, and I believe they charge even if it’s just going right back on the public IP to another Amazon instance.

Thanks,
Matt Ray
Senior Technical Evangelist | Opscode Inc.
matt@opscode.com | (512) 731-2218
Twitter, IRC, GitHub: mattray


From: S Ahmed [sahmed1020@gmail.com]
Sent: Tuesday, November 13, 2012 10:28 PM
To: chef@lists.opscode.com
Subject: [chef] why does mysql on ec2 bind to priviate i.p?

I noticed when I used the mysql recipe it installs mysql and binds it to the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some reason it works fine using ruby.

Doing:

telnet localhost 3306
it says unable to connect to remote host: connectino refused

telnet x.x.x.x 3306 (ec2 private ip) it works.

The private ip isn’t even in my /etc/hosts file either which was another source of confusion for me :slight_smile:

Is there a good reason for this? What is wrong with localhost?

server.rb has the following:

default[‘mysql’][‘bind_address’] = attribute?(‘cloud’) ? cloud[‘local_ipv4’] : ipaddress

my.cnf gets set with this:

bind-address x.x.x.x


#3

On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#4

Hi,

I have a strange situation on a Rackspace server - in some conditions IP address for MySQL is set to public IP (which is node IP address). It looks like “cloud” attribute is not yet available when mysql bind ipaddress is evaluated.
I’m running embedded chef 10.14.4 on CentOS 6.3 on Rackspace.

It looks like some attributes are evaluated before all plugins are executed.

Does someone else experienced such issue? Should I upgrade to latest 10.6.2?

Thanks,

Vladimir Girnet
Infrastructure Engineer
Tacit Knowledge
http://www.tacitknowledge.com

On Nov 14, 2012, at 4:26 PM, Joshua Timberman wrote:

On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#5

If I set it to 127.0.0.1, does this mean I can’t access it from another ec2
server then?

The problem is during deployment, I don’t know the private i.p in advance
and during deployment all my database configuration information has
’localhost’ in it. I understand what you guys are saying and it does make
sense, I just have to modify my deployment process to first pull in the
private ip’s for each service (I should be doing this anyhow as eventually
not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.comwrote:

On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#6

The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than an array, so it’s impossible to listen on a limited subset of local addresses. You may decide to have mysql listen on all bound IP addresses, or one and only one bound IP address, but you many not listen a subset of all bound IP addresses (unless you bring local firewalls into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding to 0.0.0.0 and allowing connections from any host is an unacceptable default. This leaves an unfortunate dilemma: Either bind to the private IP address and disallow traffic from localhost, or bind to localhost and only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours of frustration as people try to figure out why the database isn’t accessible from their external servers out of the box. However, since it’s possible for a node in EC2 to access its own private IP address and resolve it to a hostname, the default that arguably solves the most problems and creates the least frustration is to bind to the private IP address.

Many people get around this problem by using unix sockets for communication to mysql if they’re running the app and database on the same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t support unix sockets. The solution for you is either to override the cookbook’s default bind_address to 127.0.0.1, or to use a template to get the hostname into your application’s config file instead of localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s inconvenient for many use cases. You’ll need to override the cookbook’s default attribute back to localhost, or address the database via the hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed <sahmed1020@gmail.commailto:sahmed1020@gmail.com>
Reply-To: "chef@lists.opscode.commailto:chef@lists.opscode.com" <chef@lists.opscode.commailto:chef@lists.opscode.com>
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.commailto:chef@lists.opscode.com" <chef@lists.opscode.commailto:chef@lists.opscode.com>
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another ec2 server then?

The problem is during deployment, I don’t know the private i.p in advance and during deployment all my database configuration information has ‘localhost’ in it. I understand what you guys are saying and it does make sense, I just have to modify my deployment process to first pull in the private ip’s for each service (I should be doing this anyhow as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman <joshua@opscode.commailto:joshua@opscode.com> wrote:
On 11/13/12 9:28 PM, “S Ahmed” <sahmed1020@gmail.commailto:sahmed1020@gmail.com> wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#7

More information on the mysql bind-address server parameter:

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address

Thanks,
–Charles

From: S Ahmed <sahmed1020@gmail.commailto:sahmed1020@gmail.com>
Reply-To: "chef@lists.opscode.commailto:chef@lists.opscode.com" <chef@lists.opscode.commailto:chef@lists.opscode.com>
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.commailto:chef@lists.opscode.com" <chef@lists.opscode.commailto:chef@lists.opscode.com>
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another ec2 server then?

The problem is during deployment, I don’t know the private i.p in advance and during deployment all my database configuration information has ‘localhost’ in it. I understand what you guys are saying and it does make sense, I just have to modify my deployment process to first pull in the private ip’s for each service (I should be doing this anyhow as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman <joshua@opscode.commailto:joshua@opscode.com> wrote:
On 11/13/12 9:28 PM, “S Ahmed” <sahmed1020@gmail.commailto:sahmed1020@gmail.com> wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#8

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group doesn’t
have 3306 open anyhow, so you wouldn’t be mooning the entire world per say,
only your other instances (which I believe by default all your other
instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.comwrote:

The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than an
array, so it’s impossible to listen on a limited subset of local addresses.
You may decide to have mysql listen on all bound IP addresses, or one and
only one bound IP address, but you many not listen a subset of all bound IP
addresses (unless you bring local firewalls into the picture, but that’s
another discussion).

When running in an inherently insecure environment such as EC2, binding
to 0.0.0.0 and allowing connections from any host is an unacceptable
default. This leaves an unfortunate dilemma: Either bind to the private IP
address and disallow traffic from localhost, or bind to localhost and only
allow traffic from self.

If the default is to bind to localhost, this will cause countless hours
of frustration as people try to figure out why the database isn’t
accessible from their external servers out of the box. However, since it’s
possible for a node in EC2 to access its own private IP address and resolve
it to a hostname, the default that arguably solves the most problems and
creates the least frustration is to bind to the private IP address.

Many people get around this problem by using unix sockets for
communication to mysql if they’re running the app and database on the same
node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t support
unix sockets. The solution for you is either to override the cookbook’s
default bind_address to 127.0.0.1, or to use a template to get the hostname
into your application’s config file instead of localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s
inconvenient for many use cases. You’ll need to override the cookbook’s
default attribute back to localhost, or address the database via the
hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another
ec2 server then?

The problem is during deployment, I don’t know the private i.p in
advance and during deployment all my database configuration information has
’localhost’ in it. I understand what you guys are saying and it does make
sense, I just have to modify my deployment process to first pull in the
private ip’s for each service (I should be doing this anyhow as eventually
not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.comwrote:

On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#9

Your other instances cannot access each other unless one of their security groups is allowed inbound access. The default SG is configured this way.

There is a lot of FUD about security and EC2. Remember that what you have is a software based firewall controlling ingress to your box. Your packets share wires with other packets, even if you can’t see them.

If you think there are issues with AWS security groups then by all means add iptables to your hosts and limit access that way.

Also, if you have that level of security requirements you should probably be using ssl to protect your data in transit. http://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 10:26 AM, S Ahmed wrote:

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group doesn’t have 3306 open anyhow, so you wouldn’t be mooning the entire world per say, only your other instances (which I believe by default all your other instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.com wrote:
The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than an array, so it’s impossible to listen on a limited subset of local addresses. You may decide to have mysql listen on all bound IP addresses, or one and only one bound IP address, but you many not listen a subset of all bound IP addresses (unless you bring local firewalls into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding to 0.0.0.0 and allowing connections from any host is an unacceptable default. This leaves an unfortunate dilemma: Either bind to the private IP address and disallow traffic from localhost, or bind to localhost and only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours of frustration as people try to figure out why the database isn’t accessible from their external servers out of the box. However, since it’s possible for a node in EC2 to access its own private IP address and resolve it to a hostname, the default that arguably solves the most problems and creates the least frustration is to bind to the private IP address.

Many people get around this problem by using unix sockets for communication to mysql if they’re running the app and database on the same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t support unix sockets. The solution for you is either to override the cookbook’s default bind_address to 127.0.0.1, or to use a template to get the hostname into your application’s config file instead of localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s inconvenient for many use cases. You’ll need to override the cookbook’s default attribute back to localhost, or address the database via the hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another ec2 server then?

The problem is during deployment, I don’t know the private i.p in advance and during deployment all my database configuration information has ‘localhost’ in it. I understand what you guys are saying and it does make sense, I just have to modify my deployment process to first pull in the private ip’s for each service (I should be doing this anyhow as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.com wrote:
On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#10

On Wed, Nov 14, 2012 at 11:33 AM, S Ahmed sahmed1020@gmail.com wrote:

The problem is during deployment, I don’t know the private i.p in advance
and during deployment all my database configuration information has
’localhost’ in it. I understand what you guys are saying and it does make
sense, I just have to modify my deployment process to first pull in the
private ip’s for each service (I should be doing this anyhow as eventually
not everything is going to fit on a single instance!)

You should know what interface you want to bind to at least, e.g. eth0
or eth1. There’s bits of ruby for getting the IP of a specific
interface here: http://tickets.opscode.com/browse/OHAI-88

Bryan


#11

I’m certainly not trying to spread any FUD about security and EC2.
Security groups are great, I’m a huge fan! Use them!

That said, you might be amazed at how many times Helpful Friendly Junior
Sysadmin has just added every machine to the default security group, and
then enabled 3306 TCP. This is clearly insane behavior.

But the point of a sane default is to keep things operating in a
predictable, reliable, & secure fashion, even when people do insane
things. In this case, compounding potential user insanity by default
binding mysql to 0.0.0.0 would be the most predictable behavior, but also
the least reliable or safe.

To my mind, defaulting to reliability and safety over predictability in
this case is the most sane course of action. Thus my voicing support for
the current default setting in the mysql cookbook.

Thanks,
–Charles


On 11/14/12 11:09 AM, “Jeffrey Hulten” jeffh@automatedlabs.com wrote:

Your other instances cannot access each other unless one of their
security groups is allowed inbound access. The default SG is configured
this way.

There is a lot of FUD about security and EC2. Remember that what you have
is a software based firewall controlling ingress to your box. Your
packets share wires with other packets, even if you can’t see them.

If you think there are issues with AWS security groups then by all means
add iptables to your hosts and limit access that way.

Also, if you have that level of security requirements you should probably
be using ssl to protect your data in transit.
http://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 10:26 AM, S Ahmed wrote:

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group
doesn’t have 3306 open anyhow, so you wouldn’t be mooning the entire
world per say, only your other instances (which I believe by default all
your other instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.com
wrote:
The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than
an array, so it’s impossible to listen on a limited subset of local
addresses. You may decide to have mysql listen on all bound IP
addresses, or one and only one bound IP address, but you many not listen
a subset of all bound IP addresses (unless you bring local firewalls
into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding
to 0.0.0.0 and allowing connections from any host is an unacceptable
default. This leaves an unfortunate dilemma: Either bind to the private
IP address and disallow traffic from localhost, or bind to localhost and
only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours
of frustration as people try to figure out why the database isn’t
accessible from their external servers out of the box. However, since
it’s possible for a node in EC2 to access its own private IP address and
resolve it to a hostname, the default that arguably solves the most
problems and creates the least frustration is to bind to the private IP
address.

Many people get around this problem by using unix sockets for
communication to mysql if they’re running the app and database on the
same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t
support unix sockets. The solution for you is either to override the
cookbook’s default bind_address to 127.0.0.1, or to use a template to
get the hostname into your application’s config file instead of
localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s
inconvenient for many use cases. You’ll need to override the cookbook’s
default attribute back to localhost, or address the database via the
hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another
ec2 server then?

The problem is during deployment, I don’t know the private i.p in
advance and during deployment all my database configuration information
has ‘localhost’ in it. I understand what you guys are saying and it
does make sense, I just have to modify my deployment process to first
pull in the private ip’s for each service (I should be doing this anyhow
as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.com
wrote:
On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it
to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for
some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the
[private]
network so that other systems like application servers can connect to
it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your
database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or
on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#12

Charles,

I agree, it’s better to be safe than sorry.

On Wed, Nov 14, 2012 at 3:36 PM, Charles Johnson charles@opscode.comwrote:

I’m certainly not trying to spread any FUD about security and EC2.
Security groups are great, I’m a huge fan! Use them!

That said, you might be amazed at how many times Helpful Friendly Junior
Sysadmin has just added every machine to the default security group, and
then enabled 3306 TCP. This is clearly insane behavior.

But the point of a sane default is to keep things operating in a
predictable, reliable, & secure fashion, even when people do insane
things. In this case, compounding potential user insanity by default
binding mysql to 0.0.0.0 would be the most predictable behavior, but also
the least reliable or safe.

To my mind, defaulting to reliability and safety over predictability in
this case is the most sane course of action. Thus my voicing support for
the current default setting in the mysql cookbook.

Thanks,
–Charles


On 11/14/12 11:09 AM, “Jeffrey Hulten” jeffh@automatedlabs.com wrote:

Your other instances cannot access each other unless one of their
security groups is allowed inbound access. The default SG is configured
this way.

There is a lot of FUD about security and EC2. Remember that what you have
is a software based firewall controlling ingress to your box. Your
packets share wires with other packets, even if you can’t see them.

If you think there are issues with AWS security groups then by all means
add iptables to your hosts and limit access that way.

Also, if you have that level of security requirements you should probably
be using ssl to protect your data in transit.
http://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 10:26 AM, S Ahmed wrote:

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group
doesn’t have 3306 open anyhow, so you wouldn’t be mooning the entire
world per say, only your other instances (which I believe by default all
your other instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.com
wrote:
The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than
an array, so it’s impossible to listen on a limited subset of local
addresses. You may decide to have mysql listen on all bound IP
addresses, or one and only one bound IP address, but you many not listen
a subset of all bound IP addresses (unless you bring local firewalls
into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding
to 0.0.0.0 and allowing connections from any host is an unacceptable
default. This leaves an unfortunate dilemma: Either bind to the private
IP address and disallow traffic from localhost, or bind to localhost and
only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours
of frustration as people try to figure out why the database isn’t
accessible from their external servers out of the box. However, since
it’s possible for a node in EC2 to access its own private IP address and
resolve it to a hostname, the default that arguably solves the most
problems and creates the least frustration is to bind to the private IP
address.

Many people get around this problem by using unix sockets for
communication to mysql if they’re running the app and database on the
same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t
support unix sockets. The solution for you is either to override the
cookbook’s default bind_address to 127.0.0.1, or to use a template to
get the hostname into your application’s config file instead of
localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s
inconvenient for many use cases. You’ll need to override the cookbook’s
default attribute back to localhost, or address the database via the
hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another
ec2 server then?

The problem is during deployment, I don’t know the private i.p in
advance and during deployment all my database configuration information
has ‘localhost’ in it. I understand what you guys are saying and it
does make sense, I just have to modify my deployment process to first
pull in the private ip’s for each service (I should be doing this anyhow
as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.com
wrote:
On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it
to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for
some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the
[private]
network so that other systems like application servers can connect to
it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your
database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or
on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#13

Without going too off topic, I usually don’t use default for that very reason. Also having monitoring rules to watch the settings of your security groups (and vpcs and elbs…) is a good plan.

I should really add to the AWS cookbook to support converging SGs…


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 12:36 PM, Charles Johnson wrote:

I’m certainly not trying to spread any FUD about security and EC2.
Security groups are great, I’m a huge fan! Use them!

That said, you might be amazed at how many times Helpful Friendly Junior
Sysadmin has just added every machine to the default security group, and
then enabled 3306 TCP. This is clearly insane behavior.

But the point of a sane default is to keep things operating in a
predictable, reliable, & secure fashion, even when people do insane
things. In this case, compounding potential user insanity by default
binding mysql to 0.0.0.0 would be the most predictable behavior, but also
the least reliable or safe.

To my mind, defaulting to reliability and safety over predictability in
this case is the most sane course of action. Thus my voicing support for
the current default setting in the mysql cookbook.

Thanks,
–Charles


On 11/14/12 11:09 AM, “Jeffrey Hulten” jeffh@automatedlabs.com wrote:

Your other instances cannot access each other unless one of their
security groups is allowed inbound access. The default SG is configured
this way.

There is a lot of FUD about security and EC2. Remember that what you have
is a software based firewall controlling ingress to your box. Your
packets share wires with other packets, even if you can’t see them.

If you think there are issues with AWS security groups then by all means
add iptables to your hosts and limit access that way.

Also, if you have that level of security requirements you should probably
be using ssl to protect your data in transit.
http://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 10:26 AM, S Ahmed wrote:

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group
doesn’t have 3306 open anyhow, so you wouldn’t be mooning the entire
world per say, only your other instances (which I believe by default all
your other instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.com
wrote:
The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than
an array, so it’s impossible to listen on a limited subset of local
addresses. You may decide to have mysql listen on all bound IP
addresses, or one and only one bound IP address, but you many not listen
a subset of all bound IP addresses (unless you bring local firewalls
into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding
to 0.0.0.0 and allowing connections from any host is an unacceptable
default. This leaves an unfortunate dilemma: Either bind to the private
IP address and disallow traffic from localhost, or bind to localhost and
only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours
of frustration as people try to figure out why the database isn’t
accessible from their external servers out of the box. However, since
it’s possible for a node in EC2 to access its own private IP address and
resolve it to a hostname, the default that arguably solves the most
problems and creates the least frustration is to bind to the private IP
address.

Many people get around this problem by using unix sockets for
communication to mysql if they’re running the app and database on the
same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t
support unix sockets. The solution for you is either to override the
cookbook’s default bind_address to 127.0.0.1, or to use a template to
get the hostname into your application’s config file instead of
localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s
inconvenient for many use cases. You’ll need to override the cookbook’s
default attribute back to localhost, or address the database via the
hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another
ec2 server then?

The problem is during deployment, I don’t know the private i.p in
advance and during deployment all my database configuration information
has ‘localhost’ in it. I understand what you guys are saying and it
does make sense, I just have to modify my deployment process to first
pull in the private ip’s for each service (I should be doing this anyhow
as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.com
wrote:
On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it
to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for
some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the
[private]
network so that other systems like application servers can connect to
it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your
database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or
on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.


#14

To me it feels like configuring the security group itself is outside the scope of a cookbook running on arbitrary nodes. On a system that is deemed the centralize place where configuration for general infrastructure level changes, maybe. But Aws has that - cloud formation.

I’m just wary of adding too much scope to the Aws cookbook from a maintenance perspective. This is similar to determining where to draw the line in the knife-ec2 plugin. It could easily become a replacement for all the ec2 API tools. But should it?

On Nov 14, 2012, at 17:30, “Jeffrey Hulten” jeffh@automatedlabs.com wrote:

Without going too off topic, I usually don’t use default for that very reason. Also having monitoring rules to watch the settings of your security groups (and vpcs and elbs…) is a good plan.

I should really add to the AWS cookbook to support converging SGs…


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 12:36 PM, Charles Johnson wrote:

I’m certainly not trying to spread any FUD about security and EC2.
Security groups are great, I’m a huge fan! Use them!

That said, you might be amazed at how many times Helpful Friendly Junior
Sysadmin has just added every machine to the default security group, and
then enabled 3306 TCP. This is clearly insane behavior.

But the point of a sane default is to keep things operating in a
predictable, reliable, & secure fashion, even when people do insane
things. In this case, compounding potential user insanity by default
binding mysql to 0.0.0.0 would be the most predictable behavior, but also
the least reliable or safe.

To my mind, defaulting to reliability and safety over predictability in
this case is the most sane course of action. Thus my voicing support for
the current default setting in the mysql cookbook.

Thanks,
–Charles


On 11/14/12 11:09 AM, “Jeffrey Hulten” jeffh@automatedlabs.com wrote:

Your other instances cannot access each other unless one of their
security groups is allowed inbound access. The default SG is configured
this way.

There is a lot of FUD about security and EC2. Remember that what you have
is a software based firewall controlling ingress to your box. Your
packets share wires with other packets, even if you can’t see them.

If you think there are issues with AWS security groups then by all means
add iptables to your hosts and limit access that way.

Also, if you have that level of security requirements you should probably
be using ssl to protect your data in transit.
http://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Jeffrey Hulten
Principal Consultant at Automated Labs, LLC
jeffh@automatedlabs.com 206-853-5216
Skype: jeffhulten

On Nov 14, 2012, at 10:26 AM, S Ahmed wrote:

Thanks, I’ll look into this further so it sinks in :slight_smile:

BTW, why is ec2 inherently not secure? The default security group
doesn’t have 3306 open anyhow, so you wouldn’t be mooning the entire
world per say, only your other instances (which I believe by default all
your other instances can access).

On Wed, Nov 14, 2012 at 12:04 PM, Charles Johnson charles@opscode.com
wrote:
The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than
an array, so it’s impossible to listen on a limited subset of local
addresses. You may decide to have mysql listen on all bound IP
addresses, or one and only one bound IP address, but you many not listen
a subset of all bound IP addresses (unless you bring local firewalls
into the picture, but that’s another discussion).

When running in an inherently insecure environment such as EC2, binding
to 0.0.0.0 and allowing connections from any host is an unacceptable
default. This leaves an unfortunate dilemma: Either bind to the private
IP address and disallow traffic from localhost, or bind to localhost and
only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours
of frustration as people try to figure out why the database isn’t
accessible from their external servers out of the box. However, since
it’s possible for a node in EC2 to access its own private IP address and
resolve it to a hostname, the default that arguably solves the most
problems and creates the least frustration is to bind to the private IP
address.

Many people get around this problem by using unix sockets for
communication to mysql if they’re running the app and database on the
same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn’t
support unix sockets. The solution for you is either to override the
cookbook’s default bind_address to 127.0.0.1, or to use a template to
get the hostname into your application’s config file instead of
localhost.

tl;dr: The default behavior of the cookbook is sane, even though it’s
inconvenient for many use cases. You’ll need to override the cookbook’s
default attribute back to localhost, or address the database via the
hostname instead of localhost.

Thanks,
–Charles

From: S Ahmed sahmed1020@gmail.com
Reply-To: "chef@lists.opscode.com" chef@lists.opscode.com
Date: Wednesday, November 14, 2012 8:33 AM
To: "chef@lists.opscode.com" chef@lists.opscode.com
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can’t access it from another
ec2 server then?

The problem is during deployment, I don’t know the private i.p in
advance and during deployment all my database configuration information
has ‘localhost’ in it. I understand what you guys are saying and it
does make sense, I just have to modify my deployment process to first
pull in the private ip’s for each service (I should be doing this anyhow
as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman joshua@opscode.com
wrote:
On 11/13/12 9:28 PM, “S Ahmed” sahmed1020@gmail.com wrote:

I noticed when I used the mysql recipe it installs mysql and binds it
to
the private i.p on ec2, why is that?

My application can’t connect as it uses ‘localhost’ (jdbc), but for
some
reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the
[private]
network so that other systems like application servers can connect to
it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your
database
server accessible on the external network is bad :).

You can set the attribute node['mysql']['bind_address'] in a role or
on
the node itself, to use loopback.

For example, in a role:

default_attributes(
“mysql” => {
“bind_address” => “127.0.0.1”
}
)

On the node itself would be the JSON equivalent of the above.