Learn how to - Install Google Cloud SQL Proxy on Debian 11 / Debian 10

Felix Williams Cloud Debian GCP How to Learn January 28, 2023

Learn how to – Install Google Cloud SQL Proxy on Debian 11 / Debian 10.

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_proxy

Step 3: Create SystemD service

At this point, we can use the application by executing it as follows

./cloud_sql_proxy -version

Even 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/bin

Then 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-reload

Then 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 -instances

This 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=password

gcloud sql users create user            
--host=cloudsqlproxy~% 
--instance=cloudsql_instance_name 
--password=your_password

After 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

Google Cloud Docs

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:

Cart

Learn how to – Install Google Cloud SQL Proxy on Debian 11 / Debian 10