In case you intend to use CloudSQL to host your application’s databases in GCE, then there will be a need for you to have Google Cloud Compute Engine SQL Proxy installed and working. This will allow you to connect to your Databases securely and beautifully. The Cloud SQL Auth proxy provides secure access to your instances without a need for Authorized networks or for configuring SSL. It works by having a local client running in the local environment. Your application communicates with the Cloud SQL Auth proxy running locally with the standard database protocol used by your database.
Benefits of Cloud SQL Auth proxy
- Secure connections: The proxy automatically encrypts traffic to and from the database using TLS with a 128-bit AES cipher.
- IAM database authentication
- Easier connection authorization
Prerequisites
For this to work till the end, we need to have the following:
- Google Cloud authentication credentials. You will have to create a service account credential file (JSON) specifically for the Cloud SQL Auth proxy. It will be explicitly and permanently linked to the Cloud SQL Auth proxy as long as it is running.
- A valid database user account and password for your instance.
Step 1: Install Cloud SQL Auth proxy
To begin the installation process, we will first download the Cloud SQL Auth proxy:
cd ~
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
Step 2: Make it executable
After you have fetched the file of interest, we will have to make the Cloud SQL Auth proxy executable as follows.
chmod +x cloud_sql_proxyStep 3: Create SystemD service
At this point, we can use the application by executing it as follows
./cloud_sql_proxy -versionEven though you can use the Cloud Compute Engine SQL Proxy as it is now, we can do better. We can create a systemd service so that we can have it easy to start, stop , enable or disable the service. It will afford us a lot os convenience and we can manage it just like we are used to managing other daemons and applications within your servers. We can do this, so let us get to it right away.
First, add cloud_sql_proxy, the executable, to PATH as follows
sudo cp ~/cloud_sql_proxy /usr/local/binThen let us create a systemd service file thus. You can call the service a name that works for you:
$ sudo vim /lib/systemd/system/proxy.service
[Install]
WantedBy=multi-user.target
[Unit]
Description=Google Cloud Compute Engine SQL Proxy
Requires=networking.service
After=networking.service
[Service]
Type=simple
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud_sql_proxy -instances=your_gcp_project:region_of_instace:cloudsql_instance_name=tcp:3307 -credential_file=/var/credential.json
Restart=always
StandardOutput=journal
User=root
The “credential.json” file is the service account we need having the requisite permissions to access CloudSQL. Replace the following with your details:
- your_gcp_project
- region_of_instace e.g us-central1
- cloudsql_instance_name
- port e.g 3307 or something else not used in the server.
Do a daemon reload so that the new file can be read and loaded
sudo systemctl daemon-reloadThen start and enable the proxy
sudo systemctl start proxy
sudo systemctl enable proxy
Check its status to confirm that everything is okay
$ systemctl status proxy
● proxy.service - Google Cloud Compute Engine SQL Proxy
Loaded: loaded (/lib/systemd/system/proxy.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2022-02-02 13:52:01 UTC; 21h ago
Main PID: 9411 (cloud_sql_proxy)
Tasks: 10 (limit: 4915)
Memory: 10.3M
CGroup: /system.slice/proxy.service
└─9411 /usr/local/bin/cloud_sql_proxy -instancesThis is the part that you smile!
Step 4: Connecting to your instance via the Auth Proxy
When you connect to your instance using the Cloud SQL Auth proxy, you provide a user account that is used to log in to the instance. You can use any database user account for this purpose. An example of connecting to a MySQL instance is as follows via the CLI.
mysql -u your_user —-host=127.0.0.1 —-port=3307 -p your_database
In this example, we are connecting to port 3307 as we configured our “proxy.service” to listen from. It is also connecting to localhost. So the Google Cloud Auth SQL Proxy will receive the command and route it all the way to your GCP CloudSQL instance.
However, because the Cloud SQL Auth proxy always connects from a hostname that cannot be accessed except by the Cloud SQL Auth proxy, you can create a user account that can be used only by the Cloud SQL Auth proxy. The advantage of doing this is that you can specify this account without a password without compromising the security of your instance or your data.
To create a user account for Cloud SQL Auth proxy connections, specify the hostname as ‘cloudsqlproxy~[IP_ADDRESS]‘. You can also use the IP address wildcard, which would result in ‘cloudsqlproxy~%‘.
You can do it as follows using “gcloud” command:
gcloud sql users create user \
--host=cloudsqlproxy~24.123.4.142 \
--instance=cloudsql-instance \
--password=passwordOr
gcloud sql users create user \
--host=cloudsqlproxy~% \
--instance=cloudsql_instance_name \
--password=your_passwordAfter that, you can now connect to the Auth proxy from your application and it will authenticate against the new user without any qualms. And that is how we will end our guide today.
References
Concluding Remarks
We hope that the information provided will be helpful and any improvements we can make are always welcome. Thank you for reading through and we continue to appreciate your enormous support that we continue to receive.
Other guides for you:
