Not enforcing SSL on CloudSQL, really !

When creating a MySQL CloudSQL instance, SSL connections are not enforced by default and you get below in the Connections tab of the Google Cloud Platform console.  Is this a problem ?  Some people might think no, but I do not agree with them.  And if I am writing this post, you can probably guess that there is a lot to say about this subject.  Read on for the details.

When creating a MySQL CloudSQL instance, it is accessible by default on a public IP address (there is also the option to use a private IP, but this post is about the public IP configuration).  As written above and by default, unsecured connections are allowed to connect to a newly created instance.  I claim that this default setting is dangerous and that you should quickly press the Allow only SSL connections button after creating an instance.  Before explaining why, I first need to present another configuration option of CloudSQL.

This other configuration option is& Authorized networks.  It is shown below with other Connectivity settings.  This option whitelists public IP addresses to be able to connect to the instance.  So by default, no IP addresses are allowed to connect to a newly created CloudSQL instance.  This looks safe, even with SSL not enforced, but this safety is an illusion, and I will come back to that later in this post.

One way to connect to the CloudSQL instance is to whitelist some IP addresses and connect from there.  This would be risky if unsecured connections are allowed because a miss configuration of the client would result in an unencrypted connection to the database.  This unencrypted connection would expose the credentials, the statements (including INSERTed, UPDATEd and DELETEd data), and the data returned by queries.  So if you whitelist IP addresses and IMHO, you should enforce SSL (if you do not agree, please share your reasoning in the comments below, but after reading the rest of this post).  But are there alternatives to whitelisting for connecting to a CloudSQL instance ?

Another method for connecting to a CloudSQL instance is to use CloudSQL Proxy.  The idea is to run a proxy software provided by Google to manage the interface to CloudSQL and to access the database via this proxy.  It is possible to connect to this proxy using a UNIX socket or a TCP connection.  It is also possible to use this proxy without SSL in the case of a legacy application that does not support encrypted connections (but make sure to run the proxy on the same host as the client or on a secure network is you do this).  Below is an example of running CloudSQL Proxy on my laptop.

$ $ cloud_sql_proxy -instances=my-project:us-central1:test-jfg-no-ssl=tcp:3306
2019/05/07 18:35:03 Rlimits for file descriptors set to {&{8500 9223372036854775807}}
2019/05/07 18:35:04 Listening on 127.0.0.1:3306 for my-project:us-central1:test-jfg-no-ssl
2019/05/07 18:35:04 Ready for new connections

And connecting to this proxy with the MySQL Client...

$ mysql -h 127.0.0.1 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 171
Server version: 5.7.14-google-log (Google)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

And the connection above results in below in the logs of CloudSQL Proxy.

2019/05/07 18:36:11 New connection for "my-project:us-central1:test-jfg-no-ssl"

So back to the initial question of this post, is the infrastructure below secure ?

• a CloudSQL instance not enforcing SSL,
• without any whitelisted IP address,
• and with all application connections using CloudSQL Proxy.

Some people could claim that above is secure, I claim it is not.  A simplistic argument is the following: just press the Allow only SSL connections button and let's stop wasting time discussing this subject !  After all, enforcing SSL has no drawbacks if it is always used...  A more complete argumentation is that I believe this infrastructure is at risk of someone thinking the connection is secure when it is not.  The opposite side of the argument could claim that no unsecured connections can be established to the database because no IP addresses are whitelisted, but this is forgetting another way to connect to the database.

Another way to connect to a CloudSQL instance is to use the gcloud sql connect command.  An example is below.

$ gcloud sql connect --project my-project --user=root test-jfg-no-ssl
Whitelisting your IP for incoming connection for 5 minutes...done.                                                           
Connecting to database with SQL user [root].Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 5.7.14-google-log (Google)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

But wait, what is in red above: Whitelisting your IP for incoming connection for 5 minutes...  We also have the following in the Connections tab of the Google Cloud Platform console (I have hidden my public IP address for obvious reasons).

The gcloud command whitelisted my public IP address

So the gcloud command whitelisted my public IP address.  And I think we agreed above that a CloudSQL instance with whitelisted IP addresses should always have SSL enforced.  So the hypothesis that all the connections to our instance being done via CloudSQL Proxy is not valid anymore.  But is this a problem ?  After all, this whitelisting is only done for 5 minutes, right !  But what if someone (not you obviously, because you would not do that) would think of using SequelPro to connect to CloudSQL during those 5 minutes ?  He could, in a hurry, enter the host/user/password as below, and press the Connect button.  What is wrong ?  The Connect using SSL checkbox was not ticked, so this connection is not encrypted !

SequelPro is not using SSL by default

So SequelPro is not using SSL by default, and this, combined with not enforcing SSL connection, and with using the gcloud command for its 5-minute whitelisting, can result in an unencrypted connection to the database.  A similar problem can happen with old version of MySQL Workbench: the screenshots below are from 5.2.47, and guess which option should be selected but is easily forgotten... the Use SSL if available tick-box !

Recent versions of MySQL Workbench have saner default (below is from version 6.3.10), but are you sure that Use SSL if available will always result in a secure connection ?  Is this bullet-proof to an SSL Stripping Attack ?  Will it revert to non-SSL connections when the server certificate expires ?  I would not put the security of my data to those unanswered questions.

If you want to be as secure as possible, make sure you enforce SSL connections on your CloudSQL instances.  This will sadly prevent you from connecting with the gcloud command (it does not manage temporary client certificates), so you will need to learn to use CloudSQL Proxy (or use a client certificate and whitelisting), but this is part of the cost of being secure.

Make sure you enforce SSL connections
to your CloudSQL instances

One last thing, when SSL was not enforced on my CloudSQL instance, and when connecting to my instance using the gcloud command, I have below as an output of ps (look at the full command that is executed by gcloud).  Is this always using SSL... I think not.  Finding situations where this is not doing an encrypted connection to the database is left as an exercise to the reader (I can think of at least two).

$ ps aux | grep mysq[l]
my-user [...] mysql -h 35.238.75.176 -u root -p