Secure Google Cloud SQL Instances using Private IP: Gotchas & troubleshooting

Disclaimer: Views, thoughts, and opinions expressed in the blog belong solely to the author, and not necessarily to the author’s employer, organisation, committee or other group or individual.

Cloud SQL is Google Cloud’s fully managed relational database service for MySQL, PostgreSQL and SQL Server. This blog is for you, if you are planning to deploy Cloud SQL instances using private IP and/or intend to know more about the caveats of configuring Cloud SQL instances using Private IP. The official documentation on configuring Private IP on Cloud SQL service precisely covers most aspects, however this blog explains different scenarios one must consider while planning to deploy the Cloud SQL instance either in one or across multiple regions using Private IP. This blog also helps to frame a proactive approach on the network design which is the critical foundation for any workload.

Scope: The scope of blog is applicable to all flavours of Cloud SQL such as MySQL, PostgreSQL and SQL server because behaviour of all engines are independent of underlying network. While this blog provides artifacts of Cloud SQL MySQL, you can use concepts and scenario for Cloud SQL PostgreSQL and SQL server.

Prerequisite: As a prerequisite of this blog, you should have an understanding of how Cloud SQL service and Virtual Private Cloud (VPC) works on Google Cloud platform (GCP). If you do not have GCP access, get started here.

Assumptions: Following are the assumptions considered for this blog.

1. Intent is to deploy Cloud SQL MySQL instance in Mumbai region with HA (High Availability) configuration and cross region read replica in Delhi region.
2. Primary instances may or may not require read replica or cross region replica instances.
3. Network configuration is assumed to have only Private IP and no communication should flow using the internet. Feel free to ignore the VPC configuration section if you are already familiar with it.

A short note on conceptual thoughts

GCP manages Cloud SQL service in Google provided VPC and its respective subnet. Customer VPC and Google managed VPC are two different components when it comes to avail managed services like Cloud SQL on GCP. VPC where Google spins up and manages the Cloud SQL instance is known as Service provider VPC or subnet. When Customer creates Cloud SQL instance, Google Managed VPC and its respective subnet IP range configuration should already in place. Without this Cloud SQL create action will fail with error “Failed to create subnetwork. Couldn’t find free blocks in allocated IP ranges. Please allocate new ranges for this service provider. Help Token:…”

From this prerequisite VPC and subnet configuration, create activity will inherit IP addresses for Cloud SQL DB instances. This configuration should be done as a prerequisite for each type of DB engine (MySQL, PostgreSQL, SQL Server) of Cloud SQL per region. This is called as Private Service Access (PSA) configuration for respective Cloud SQL DB engine in each required region. For example, if the plan is to deploy the Cloud SQL instances in Mumbai and Delhi region for MySQL and PostgreSQL then 4 PSA configurations will be required. One each for MySQL and PostgreSQL for Mumbai and Delhi region. With PSA configuration in place, GCP will automatically establish VPC peering between Customer and Service provider VPC subnet. We need to make sure PSA is well established before you create a Cloud SQL instance. We will walk-through the steps to create PSA in subsequent sections. These concepts are well explained in official documentation, so excluding those content from here. I’ll highly recommend reading these documentation before proceeding forward.

PSA Configuration

Depending upon these concepts and prerequisites, it is mandatory to have free CIDR IP range in the respective region & VPC of at least “/24” CIDR range size. As of writing this blog, as per documentation, “/24” CIDR range size can accommodate no more than 50 Cloud SQL instances. Please refer here for more details on capacity of IP CIDR block size and max number of instances supported for each. Please note that if you are providing IP CIDR range, make sure it does not overlap with any other range in currently used VPC, across all its subnets. This blog will explain this scenario in greater details in subsequent sections.

Now let’s build the foundation with VPC configuration. We will do following things in sequence to make sure we have proper foundation to re-produce scenarios:

1. I’m not using “default” VPC since most organizations do not use it, as a best practice.
2. Create VPC in the Mumbai region with a subnet where we will plan to deploy the DB instance. Let’s create other subnets later as and when we will revisit the scenarios.
3. We will also ensure that all connections remain private using only Private IP addresses right from DB instance to Client VM from where we will test the connectivity to the database. So, we will also install NAT (with routers in respective regions) attached to our VPC, so that we should be able to install MySQL client. Only this part will be external but remains secure by design.

Below is the VPC configuration used in this blog.

Make sure to set Firewall rules as per your requirements and create NAT and router for each region, so that private VMs should be able to download required patches or packages from external networks.

Since network foundation is ready, Lets walkthrough each scenario one by one:

1. Create Cloud SQL MySQL Instance using one PSA (Private Service Access) of CIDR IP range of “/24” size in mumbai region.
2. Create Cloud SQL Instances for PostgreSQL and SQL server with Private IP configuration using existing IP range and then fix it.
3. Create a Cloud SQL instance in a region where there is no or exhausted PSA IP CIDR range.
4. Consideration on when we create Cloud SQL Read replica in the same region as primary.
5. Considerations when we create cross region read replica.

Configure and Create Cloud SQL MySQL Instance using Private IP

Let’s create a Cloud SQL MySQL instance in the Mumbai region where we already have a subnet defined under VPC. Since we do not want to use Public IP in Cloud SQL instances, we should define its private IP range using PSA configuration. Once private IP is allocated during Cloud SQL instance creation using pre-existing PSA configuration, allocated IP for Cloud SQL instance becomes static. The documentation says that PSA created with one “/24” size of CIDR range can safely accommodate 50 instances of Cloud SQL MySQL. For SQL server and PostgreSQL instances, create separate PSA configuration or IP ranges. Individual IP range is required per DB engine for each region separately. Funda of 50 instances are safe limits as each instance reserves more than one private IP for configuration like HA, maintenance etc.

Start with a PSA configuration

Go to the VPC console, select VPC and open the “Private Service Connection” tab. Under sub-tab “ALLOCATED IP RANGES FOR SERVICES”, click “ALLOCATE IP RANGE” and give IP range as shown below. You have choice choose Custom option to specify your defined range however for simplicity, Automatic option is used with CIDR range block size as 24.

Please note that the IP range is named as “psa-ip-reserved-for-cloud-sql” just to give a meaningful name. Please ignore the “description”. Likewise you can define your naming convention suitable for your organization. Please check below response, once you hit “ALLOCATE”:

With this action, GCP creates a VPC, Subnet with specified IP range and now it is time to connect Google defined VPC with Customer (your) defined VPC using VPC peering. This is automatically done in the background when connection is created next tab.

Before you connect, check how many VPC peering we have, as below which is none at the moment:

To create Private connection between service provider and customer VPC, go to “PRIVATE SERVICE CONNECTION” tab and then sub-tab “PRIVATE CONNECTIONS TO SERVICES” and choose “CREATE CONNECTION” as below in VPC console.

Choose IP range created in previous step and click “Connect”

Once created, validate “Assigned allocation” name with connection name.

Please note the IP range assigned as “10.177.255.0/24”, so any subsequent Cloud SQL instance will be assigned a private IP from this range, while creating the instance. After connection is created, VPC peering will appear which got created as part of connection creation, see below screenshot on VPC peering console:

Create CLoud SQL MySQL instance and opt for Private IP in connection configuration as shown below. Choose Mumbai as a region.

Under section “Customize your instance”, uncheck “Public IP” and select “Private IP”, choose VPC name “db-vpc” from Network drop-down and choose the PSA range option from “Allocated IP range”

Hit Create instance to start DB instance creation.

Meanwhile Cloud SQL MySQL instance is in process of getting created, let’s do a quick check — what if I try to create Cloud SQL for SQL server and PostgreSQL in the same CIDR range as Private IP configuration. Let’s do that and I get the below error and see the console with allocated IP addresses on MySQL instance and error mark on SQL Server and PostgreSQL. Please validate the Cloud SQL MySQL instance private IP in Mumbai region, if it takes the same IP from existing range and below screenshot confirms it.

Please check following error with which PostgreSQL and SQL server instance creation failed:

Failed to create subnetwork. Couldn’t find free blocks in allocated IP ranges. Please allocate new ranges for this service provider. Help Token......

Fix the error — To fix this, create Individual IP range for each DB engine in Mumbai region following the same procedure, how we created it earlier for MySQL instances. We should finally see 3 entries each for DB engines in VPC Private Service Connect configuration as below where I created 2 more IP ranges with meaningful naming convention which indicates one is for PostgreSQL and other one is for SQL Server. Validate IP range and see in column these range are still not part of VPC peering:

To add above two new ranges of IP CIDR as part of PCA configuration and VPC peering, goto “PRIVATE CONNECTION TO SERVICES” tab and click the link on “servicenetworking-googleapis-com” to update new IP range as below and click UPDATE.

This step will assign new IP range to the existing PCA, validate IP range as part of service networking as below:

Also validate in “ALLOCATED IP RANGES FOR SERVICES” tab as below:

Next, validate these configuration by creating first Cloud SQL PostgreSQL selecting below configuration followed Cloud SQL SQL Server in subsequent screenshot:

For Cloud SQL SQLserver

After DB instance creation is completed, validate Private IP allocated based on how we allocated IPs while creating these instances.

How to handle if existing PSA Private IP CIDR range is reaching to it limits?

If the existing PSA Private IP CIDR range is reaching its limits, then create DB Instance using additional Private IP CIDR range in the respective region, in our case the region is Mumbai. Let’s see below how to add additional Private IP range. As similar to step before add new IP range and update to service networking as shown in below screenshots for the VPC in “Private Service Connection” tab:

Then add this IP range to existing service networking as below:

After Update, this will appear in part of service networking:

Also Validate in Allocated IP range tab as below:

If you get an error while adding or removing IP range in existing service networking then use “Force” option, Learn more or use force option from command line to remove or add IP ranges.

gcloud services vpc-peerings update \
service=servicenetworking.googleapis.com \
-ranges=OLD_RESERVED_RANGE_NAME,NEW_RESERVED_RANGE_NAME \
-network=VPC_NETWORK \
-project=PROJECT_ID \
-force

Now, test above configuration by creating a Cloud SQL MySQL DB instance with following network connect option in Mumbai region:

Validate the assigned IP in below screenshot on list of created DB instances:

There are few more troubleshooting scenario, you should keep in mind:

1. With the same region as primary, read replica will inherit private IP from primary instance. This scenario requires no action from PSA configuration point of view.
2. When you add a cross region replica, make sure the read replica region should have Private service access (PSA) configured with an empty private IP range or intended region should already have the same type of DB instance with Private IP with additional room in IP CIDR range. For example, as show in below screenshot, I’ve added one more IP range and I will create a cross region replica in Delhi region, so it has picked up new allocated IP for it.

Cross region read replica created as below and IP is assigned from newly created PSA IP CIDR range:

3. You may encounter the error “Failed to create subnetwork. Couldn’t find free blocks in allocated IP ranges. Please allocate new ranges for this service provider. ” while creating DB instances, in any of following situations:

a) On Private IP option only, creating DB instance or cross region read replica in a region without PSA configuration.
b) Private Service Connect IP range is exhausted or if its IP range overlapped with another existing part of the VPC network, check you may have one or more subnets with overlapped IP range.

Tips and tricks

Next, below are the way you can track it effciently.

1. Use below gcloud command to list Peering route in each region:

gcloud compute networks peerings list-routes servicenetworking-googleapis-com — network=db-vpc — region=asia-south1 — direction=INCOMING

2. You can also use Cloud Inventory API or console to track the same using IAM console and choose “Asset Inventory”.

Then choose one of the IP range to see it detailed metadata. Refer this blog to check how to create data pipeline to do more with it.

3. Use below gcloud command to list the IP ranges used for PSA configurations:

gcloud compute addresses list — global — filter=”purpose=VPC_PEERING”

Cost considerations: If you do not use Google Cloud free tier or your usage goes beyond free tier, you will incur cost on spinning up cloud SQL instances, NAT routers and Network egress charges, please estimate using GCP pricing calculator.

Call to action: Tryout these scenarios to understand the Private Service Access behavior which is mandatory to understand before you spin up or plan for production Cloud SQL instances using Private IP. Refer to the GCP community guideline page to get more help.

Cheers!!