Securely connecting to Cloud SQL using the MySQL Client
Part of Working with Cloud SQL
Tutorial
- Getting the IP address of the instance
- Connecting to the “insecure” instance
- Connecting to the “secure” instance
- Final words
- References
In this second tutorial on “Working with Cloud SQL,” I will explain how to securely use the MySQL Client to connect to a Cloud SQL instance. In the previous tutorial of this series, I showed you how to set up a Cloud SQL instance that allows only SSL connections. In this video, I will show you how to connect securely, even when the instance is not explicitly disallowing insecure connections.
I’ve already set up two Cloud SQL instances for this video: “cloud-sql-insecure” and “cloud-sql-secure.” The “insecure” instance allows unencrypted connections, while the “secure” server will deny access if traffic is not encrypted. I recommend checking out this series’ previous tutorial or video to learn more about how I configured these Cloud SQL instances.
Getting the IP address of the instance
When connecting to a Cloud SQL instance using the MySQL client, you need to provide the IP address of that instance. We can get this IP address through the Google Cloud Console, as shown in the previous video, but in this video, I wanted to show you a different approach using the Google Cloud command-line tool “gcloud.”
If you don’t have the “gcloud” command line tools installed yet, head over to the installation guide in the Google Cloud Documentation to get them installed.
Note that I’m using “iTerm2” as my command-line application of choice, but everything we go through should also work in the standard “Terminal” application that comes pre-installed with macOS. For Linux, things are very similar to macOS, and many distributions come with a command-line application pre-installed. If you are a Windows user, I recommend using the Windows Subsystem for Linux, WSL, as things might work differently on the standard Windows command line or in Powershell.
For this tutorial, I will assume you understand how the gcloud command-line tool works. I will create a more detailed introduction to the Google Cloud command-line tools in the future. With that said, let‘s open up a command-line window to get our instance‘s IP address. We will use the gcloud sql instances describe command with a “--format” flag for this purpose.
gcloud sql instances describe cloud-sql-insecure \
--project <project-id> \
--format='value(ipAddresses.filter("type:PRIMARY").extract("ipAddress").flatten())'
Using the “--format” flag, we indicate to gcloud that we want to take the output of the “describe” command and extract the primary IP address from the list of IP addresses it returns. We use the “value” format function to indicate that we don’t want additional formatting to happen but want the raw value outputted to the terminal. Press ENTER to execute the command.
You should now see the IP address displayed in your terminal window. We are now ready to connect to our instance.
Connecting to the “insecure” instance
Now that we have the IP address of the cloud-sql-insecure instance, let’s show that unencrypted connections are allowed by running a MySQL command in which we explicitly disable SSL.
mysql -h <ip-address> -u root -p --ssl-mode=DISABLED
After pressing ENTER to execute the command, you will see that a MySQL session starts, and we can execute MySQL commands.
Let’s verify that our connection is not encrypted. Run the status; command in your command-line application. This command should return output similar to the one I printed below.
--------------
mysql Ver 8.0.33 for macos13.3 on x86_64 (Homebrew)
Connection id: 2172
Current database:
Current user: root@
SSL: Not in use
Current pager: less
…
As you can see, the system indicates that SSL is “Not in use.” Type exit; to stop the MySQL session.
Without an SSL mode specified, recent versions of the MySQL client use “PREFERRED” mode, which tries to establish an encrypted connection but falls back to unencrypted connections if you did not configure SSL on the server. We can validate this by removing the “--ssl-mode” flag.
mysql -h <ip-address> -u root -p
We can run the status; command to validate that the MySQL client encrypts data in transit.
--------------
mysql Ver 8.0.33 for macos13.3 on x86_64 (Homebrew)
Connection id: 2172
Current database:
Current user: root@
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: less
…
If you have installed a recent version of the MySQL client, you should see the value “Cipher in use…” next to “SSL.” This value informs us that the connection is indeed encrypted. Exit the MySQL session by running the exit; command.
We can make the MySQL Client more secure by enforcing an SSL connection. The “REQUIRED” SSL mode causes a connection attempt to fail if the connection can’t be encrypted. Because Cloud SQL always has SSL enabled, this setting will not have any immediate impact. It doesn’t harm to use it, though, as security is always best applied in layers, and you should never assume that the other party in a connection will assure a secure connection.
Even though we‘ve been working to establish encrypted connections, there is still a chance that a Man-in-the-Middle attack will occur when we try to connect. Such an attack can happen because we are not validating the server’s identity. We should download and use the server’s certificate to validate that the server we connect to is the actual server we configured in Google Cloud. Let’s download the server’s certificate using the gcloud command.
gcloud sql instances describe cloud-sql-insecure \
--project <project-id> \
--format='value(serverCaCert.cert)' > server-ca.pem
This command gets the certificate using gcloud and stores it in a file called “server-ca.pem”. If you prefer not to use the command-line, you can also download this file from the Google Cloud Console.
Now let’s try to connect to our MySQL instance again, but this time let’s add the flag “--ssl-ca=server-ca.pem”. Adding this flag will switch the SSL Mode to “VERIFY_CA,” informing the MySQL client that we want to validate the server’s certificate against our “server-ca.pem” certificate.
mysql -h <ip-address> -u root -p --ssl-ca=server-ca.pem
As you can see, we can still connect to our instance. Let’s again run the status; command to validate that the MySQL Client encrypts our connection.
--------------
mysql Ver 8.0.33 for macos13.3 on x86_64 (Homebrew)
Connection id: 2172
Current database:
Current user: root@
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: less
…
The command output shows that SSL is still enabled.
Let’s now move on and experiment a bit with our “cloud-sql-secure” instance, which has “Allow only SSL connections” enabled.
Connecting to the “secure” instance
As with the “insecure” instance, we must get the IP address before connecting. We can use gcloud sql instances describe to get it.
gcloud sql instances describe cloud-sql-insecure \
--project <project-id> \
--format='value(ipAddresses.filter("type:PRIMARY").extract("ipAddress").flatten())'
We can copy the value returned and use it in the following command to connect to the instance. First, let’s connect with SSL mode set to “DISABLED.”
mysql -h <ip-address> -u root -p --ssl-mode=DISABLED
You should see an error message informing you that the server denies us access. This error message is what we would expect, given that the server has “Allow only SSL connections” enabled.
ERROR 1045 (28000): Access denied for user 'root'@'<ip-address>' (using password: YES)
In the previous tutorial, I explained that enabling the “Allow only SSL connections” flag enforces SSL and enables Mutual TLS or mTLS. We can validate this by connecting to the server with SSL Mode set to “REQUIRED.”
mysql -h <ip-address> -u root -p --ssl-mode=REQUIRED
Running this command, still gives us an “access denied” error.
ERROR 1045 (28000): Access denied for user 'root'@'<ip-address>' (using password: YES)
Mutual TLS enables an additional layer of security by requiring every client to authenticate by using a client certificate, a client key, and a username and password.
Let's generate a client certificate and client key using the gcloud command-line tool.
gcloud sql ssl client-certs create test-client client-key.pem \
--instance cloud-sql-secure \
--project <project-id>
The “ssl client-certs create” gcloud command creates a “client-key.pem” private key file in our working directory.
Now we need to get the client certificate. We can get this certificate using another gcloud command:
gcloud sql ssl client-certs describe test-client \
--instance cloud-sql-secure \
--project <project-id> \
--format='value(cert)' > client-cert.pem
If all went well, you should have a “client-key.pem” and a “client-cert.pem” file in your working directory. The final puzzle piece is the server’s public key, which we can get using the same command we used to download the server certificate for the “insecure” instance.
gcloud sql instances describe cloud-sql-secure \
--project <project-id> \
--format='value(serverCaCert.cert)' > server-ca.pem
Now that we have our public key, the certificate, and the private key, let’s try to connect again to our Cloud SQL instance. In this case, we will have to specify two additional flags in addition to the “--ssl-mode” flag: “--ssl-cert” and “--ssl-key.”
mysql -h <ip-address> -u root -p \
--ssl-cert=client-cert.pem \
--ssl-key=client-key.pem \
--ssl-mode=REQUIRED
When running this command, we can successfully connect to the server. As explained before, we can still add a layer of security on top by setting the SSL mode to “VERIFY_CA” or by directly specifying the “--ssl-ca” flag so that we validate the server's certificate from the client side as well.
The final MySQL command looks like this:
mysql -h <ip-address> -u root -p \
--ssl-cert=client-cert.pem \
--ssl-key=client-key.pem \
--ssl-mode=VERIFY_CA \
--ssl-ca=server-ca.pem
After executing this final command, you can see that connectivity to the server can still be established. Our connection is now adequately secured from both the client and server side. We can take other steps to harden our instance further, but we are on the right path.
Final words
The steps and configuration we went through in today’s tutorial are similar to how you would work with a programming language-specific driver.
We will simplify the connection process in future tutorials using the Cloud SQL Proxy. As you will see, the Cloud SQL Proxy will add another layer of security on top of what we already have while also taking care of all the certificate management you must do when connecting directly through the MySQL Client or a programming language-specific MySQL driver. We will also explore the best techniques for connecting to Cloud SQL from Python and the JVM.