GCP
How to connect to Google Cloud SQL using a service account in Python?
Google Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer relational databases on the Google Cloud Platform. With Cloud SQL, you can connect to your database from anywhere in the world using a secure connection.
In this article, we will show you how to connect to Google Cloud SQL using Python, with best practices about security and maintainability in mind.
Requirements
- A Google Cloud Platform project with a valid Billing Account assigned to it (as Cloud SQL is not a service in Google Cloud's Free Tier)
- A Cloud SQL instance in that project
- Python 3 with pip
Choosing between the Public and Private connections
Depending on the setup of your Cloud SQL instance, and your networking security and access management requirements, you have to make a choice on what way of connecting to the instance is enabled, which will determine how exactly your Python application will get connected to the instance.
You can have both a public and private connection enabled on your instance, though in reality you probably only want to worry about managing one of the two.
Public
Having Public enabled means that the Cloud SQL instance gets a Public IP address with a block-by-default IP whitelist. You can either whitelist a specific IP address, or an IP range. It is also possible to not whitelist any particular IP address and instead use the Cloud SQL Auth proxy.
Pros
- Connectivity from anywhere in the world
- You can use the Cloud SQL Auth proxy to secure the connection regardless of the language you're using and its ways of connecting to SQL databases
Cons
- Security auditors really don't like seeing any kind of "Public" IP addresses on traditional databases
Private
Pros
- You only have private connectivity to the instance, so the Google Cloud Compute VMs and/or other services connecting to it do not need to have internet connectivity
- Possibly 0 networking egress charges if all networking coming into the instance originate from the same zone
- Peace of mind if only Private is enabled as connectivity is only enabled from within the same private network (VPC, plus VPN if present)
Cons
- There are additional steps required for the initial setup; "Private services access" has to be enabled in your project
- The "Private services access" creates a VPC Peering connection between your VPC and the VPC that Google creates and manages and places the actual Cloud SQL instance in. This means that the Cloud SQL instance isn't in your project directly - but that itself is not the "con".
- The "con" is that the VPC Peerings are not transitive, meaning that if you have another VPC Peering and want to communicate with the Cloud SQL instance through the other peering, you won't be able to, unless you add some sort of an active proxy in the project in the middle.
- Another "con" is that you really want to have a custom VPC setup at that point, as changing VPC setups is almost impossible once something is deployed in them. The "default" VPC in each GCP Project is littered with default subnets, which take up precious IP ranges.
- The VPC that is peered takes up atleast a whole /24 IP range block
- Cannot be disabled once enabled
Option 1: Auth proxy
Note: This approach will only provide a secure direct tunnel between your application and the Cloud SQL instance, and the database connection itself is still handled by the programmer.
Note: You do not need to perform these steps if you are using some Google Cloud services, such as Cloud Run, App Engine, and Cloud Functions, as there's an option within these services to facilitate this without a more manual setup.
Creating a Service Account
In your project, create a Service Account. You can call it whatever you want, and you have to give it the "Cloud SQL Client" role.
To achieve this, follow these steps:
- Log in to your Google Cloud Platform account and navigate to the "IAM & Admin" page
- On the left-hand side, click on "Service accounts"
- Click on the "Create service account" button
- Give your service account a name and description
- Click on the "Create" button
- Select the "Cloud SQL Client" role from the list of roles on the next page
- Click on the "Continue" button
- On the next page, click on "Done"
Creating and handling Service Account credentials
You’ll need to download the credentials once you create the service account. To do this, take the following steps:
- Click the three dots next to the service account and select the "Create key" option
- On the next page, select the "JSON" option and click the "Create" button. This will download the credentials file to your local machine
Treat this file with as you would any other sensitive information: Do not share it with anyone who shouldn't be allowed to access the project, and store it securely.
Install the proxy itself
Choose one of the links in the Google Cloud documentation to get the actual binary for your platform. You also have to follow the step to make the binary executable, which on Unix-like systems is the chmod +x cloud-sql-proxy command.
If you are going to be using this for local development, just make sure the cloud-sql-proxy program is running whenever you're in the development zone.
If you are going to be using this on a more serious platform, such as a server, consider using your system's daemon manager to daemonize the proxy.
(Advanced) For Systemd on Linux, first move the proxy binary into /usr/local/bin/cloud-sql-proxy, and then create a unit file for it:
# cloud-sql-proxy.service
[Install]
WantedBy=multi-user.target
[Unit]
Description=cloud-sql-proxy
Requires=networking.service
After=networking.service
[Service]
Type=simple
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud-sql-proxy
-instances=REPLACE_WITH_INSTANCE_CONNECTION_ID=tcp:5432
-credential_file=/PATH/TO/CREDENTIALS.JSON -dir=/tmp
Restart=always
StandardOutput=journal
User=root
And finally enable and start it:
sudo systemctl enable --now cloud-sql-proxy
Option 2: Cloud SQL Python Connector approach
Note: This approach will only provide a secure tunnel between your application and the Cloud SQL instance, and the database connection itself is still handled by the programmer.
Note: You do not need to perform these steps if you are using some Google Cloud services, such as Cloud Run, App Engine, and Cloud Functions, as there's an option within these services to facilitate a proxy like in Option 1, effectively removing the need for the Python Connector.
Google provides a Python package called Cloud SQL Python Connector, which is basically like the Auth proxy built directly into python with Google's Cloud SDK, and it is available on pypi.org and can therefore be installed by just running:
pip3 install --upgrade cloud-sql-python-connector
Note: if you have Python installed without Pip, try running the following commands:
python3 -m venv .venv
source .venv/bin/activate
The exact form of both commands may be different based on your project's preferred venv directory name and on what shell you have running in your terminal.
This package wraps specific database connection drivers (pymysql, pg8000, asyncpg and pytds and effectively also SQLAlchemy at the time of writing this article), so you have to use one of these when using this package.
Database driver setup
The last step is to implement your favorite database's Python driver in your source code. This is the part where the exact implementation is more up to you within the possibilities of Python and the libraries in question.
While this part is probably not the one you need any assistance with if you are this article's target audience, we'll go over the most important parts using MySQL and the PyMySQL driver as examples. We won't however go over the exact implementation of these drivers into all the possible web frameworks or whatever else you could be running, as there are just too many possible combinations, but there definitely are other articles for each one.
When using the Auth proxy - which has to run at the same time as the Python app:
import pymysql.cursors
# The host is always 'localhost', as the proxy runs locally.
# The credentials and database name are of course up to you.
connection = pymysql.connect(
host="localhost",
user="user",
password="passwd",
database="db",
cursorclass=pymysql.cursors.DictCursor
)
When using the Cloud SQL Python Connector, which will run the equivalent of pymysql.connect() for you in the background:
from google.cloud.sql.connector import Connector
import sqlalchemy
connector = Connector()
# Replace "project:region:instance" with the connection
# ID you'll see in Google Cloud SQL web console
def getconn() -> pymysql.connections.Connection:
conn: pymysql.connections.Connection = connector.connect(
"project:region:instance",
"pymysql",
user="user",
password="passwd",
db="db"
)
return conn
pool = sqlalchemy.create_engine(
"mysql+pymysql://",
creator=getconn,
)
Conclusion
Following the steps outlined in this tutorial, you can easily connect your Python app to your Google Cloud SQL instance using either the Authentication Proxy or the Cloud SQL Python Connector using an app-specific Service Account. With a secure connection, you can manage your relational databases on the Google Cloud Platform anywhere.
Do you want to learn more tech hacks and find out more about the cloud? Contact us for a free consultation.
Source:
https://cloud.google.com/sql/docs/mysql/sql-proxy
https://cloud.google.com/sql/docs/postgres/sql-proxy
https://cloud.google.com/sql/docs/sqlserver/sql-proxy
https://cloud.google.com/vpc/network-pricing#egress-within-gcp
https://cloud.google.com/compute/docs/regions-zones#identifying_a_region_or_zone
https://cloud.google.com/vpc/docs/private-services-access
https://cloud.google.com/vpc/docs/vpc-peering
https://cloud.google.com/sql/docs/mysql/sql-proxy#install
https://cloud.google.com/sql/docs/postgres/sql-proxy#install
https://cloud.google.com/sql/docs/mysql/sqlserver#install
https://pypi.org/project/cloud-sql-python-connector/
https://pypi.org/project/pymysql/
https://pypi.org/project/pg8000/
https://pypi.org/project/asyncpg/
