Connect to Cloud SQL for PostgreSQL from Cloud Run

MySQL | PostgreSQL | SQL Server

Learn how to deploy a sample app on Cloud Run connected to a PostgreSQL instance by using the Google Cloud console and a client application.

Assuming that you complete all the steps in a timely manner, the resources created in this quickstart typically cost less than one dollar (USD).

To follow step-by-step guidance for this task directly in the Google Cloud console, click Guide me:

Guide me

Before you begin

Note: The name you use for your project must be between 4 and 30 characters. When you type the name, the form suggests a project ID, which you can edit. The project ID must be between 6 and 30 characters, with a lowercase letter as the first character. You can use a dash, lowercase letter, or digit for the remaining characters, but the last character cannot be a dash.

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Verify that you have the permissions required to complete this quickstart.
Enable the Cloud APIs necessary to run a Cloud SQL sample app on Cloud Run.
Console

Click Enable APIs to enable the APIs required for this quickstart.

Enable APIs

This enables the following APIs:
- Compute Engine API
- Cloud SQL Admin API
- Cloud Run API
- Container Registry API
- Cloud Build API
- Service Networking API
gcloud

Click the following button to open Cloud Shell, which provides command-line access to your Google Cloud resources directly from the browser. Cloud Shell can be used to run the gcloud commands presented throughout this quickstart.

Open Cloud Shell

Run the following gcloud command using Cloud Shell:
```
gcloud services enable compute.googleapis.com sqladmin.googleapis.com run.googleapis.com \
artifactregistry.googleapis.com cloudbuild.googleapis.com servicenetworking.googleapis.com
```
This command enables the following APIs:
- Compute Engine API
- Cloud SQL Admin API
- Cloud Run API
- Artifact Registry API
- Cloud Build API
- Service Networking API

Required roles

To get the permissions that you need to deploy a sample app on Cloud Run connected to a PostgreSQL instance, ask your administrator to grant you the following IAM roles on the project that you want to use:

Create or delete an Artifact Registry repository: Artifact Registry Writer (roles/artifactregistry.writer)
Create or delete an instance, database, and user: Cloud SQL Administrator role (roles/cloudsql.admin).
Create or delete an IAM service account: Service Account Administrator role (roles/iam.serviceAccountAdmin).
Create a connection:
- Service Networking Admin (roles/servicenetworking.networksAdmin))
- Service Usage Admin (roles/serviceusage.serviceUsageAdmin).
View objects and their metadata: Storage Object Viewer (roles/storage.objectViewer)
Provide permissions to administer policies on projects.: Project IAM Admin (roles/resourcemanager.projectIamAdmin).
Read and write access to all Cloud Run resources: Cloud Run Developer (roles/run.developer).
Deploy and manage Cloud Run source deployed resources: Cloud Run Source Developer (roles/run.sourceDeveloper).
To allow public access: Cloud Run Admin (roles/run.admin).
To run operations as the service account: Service Account User (roles/iam.serviceAccountUser).

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Set up Cloud SQL

Create a Cloud SQL instance

Public IP

Take the following steps to set up a Cloud SQL instance that you connect to over public IP:

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Click Create instance.
From the Create instance menu, select New instance.
Click Choose PostgreSQL.
Make sure that Enterprise Plus is selected as the Cloud SQL edition for your instance.
In the Instance ID field, enter quickstart-instance.
In the Password field, enter a password for the postgres user. Save this password for future use.
In the Choose region and zonal availability section, select Single zone.
Click the Show configuration options menu.
Expand the Machine configuration node.
From the Machine shapes region, select the 4 vCPU, 32 GB shape.
Click Create instance and then wait until the instance initializes and starts.

gcloud

Before running the command as follows, replace DB_ROOT_PASSWORD with the password of your database user.

Optionally, modify the values for the following parameters:

--database-version: The database engine type and version. If left unspecified, the API default is used. See the gcloud database versions documentation to see the current available versions.
--cpu: The number of cores desired in the machine.
--memory: Whole number value indicating how much memory is desired in the machine. A size unit should be provided (for example, 3072MB or 9GB). If no units are specified, GB is assumed.
--region: Regional location of the instance (for example asia-east1, us-east1). If left unspecified, the default us-central is used.

Run the gcloud sql instances create command to create a Cloud SQL instance.

gcloud sql instances create quickstart-instance \
--database-version=POSTGRES_14 \
 --cpu=1 \
 --memory=4GB \
 --region=us-central \
 --root-password=DB_ROOT_PASSWORD

Private IP

Take the following steps to set up a Cloud SQL instance that you connect to over private IP:

Console

Allocate an IP address range and create a private connection to configure private services access for Cloud SQL

In the Google Cloud console, go to the VPC networks page.

Go to VPC networks
Select the default VPC network.
Select the Private service connection tab.
Select the Allocated IP ranges for services tab.
Click Allocate IP range.
For the Name of the allocated range, specify google-managed-services-default.
Select the Automatic option for IP range and specify the prefix length as 16.
Click Allocate to create the allocated range.
Select the Private connections to services tab for the default VPC network.
Click Create connection to create a private connection between your network and a service producer.
For the Assigned allocation, select google-managed-services-default.
Click Connect to create the connection.

Create an instance with private IP address and SSL enabled

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Click Create instance.
From the Create instance menu, select New instance.
Click Choose PostgreSQL.
Make sure that Enterprise Plus is selected as the Cloud SQL edition for your instance.
In the Instance ID field, enter quickstart-instance.
In the Password field, enter a password for the postgres user. Save this password for future use.
In the Choose region and zonal availability section, select Single zone.
Click the Show configuration options menu.
Expand the Machine configuration node.
From the Machine shapes region, select the 4 vCPU, 32 GB shape.
Expand the Connections node.
Clear the Public IP checkbox to create an instance only with a private IP address.
Select the Private IP checkbox.
Select the Private Service Access (PSA) checkbox.
In the VPC Network * dropdown, select default.
In the Security section, make sure that Allow only SSL connections is selected to enable SSL connections.
Click Create instance and then wait for the instance to initialize and start.

gcloud

Allocate an IP address range and create a private connection to configure private services access for Cloud SQL

Run the gcloud compute addresses create command to allocate an IP address range.

gcloud compute addresses create google-managed-services-default \
--global --purpose=VPC_PEERING --prefix-length=16 \
--description="peering range for Google" --network=default

Run the gcloud services vpc-peerings connect command to create a private connection to the allocated IP address range. Replace YOUR_PROJECT_ID with your project's project ID.

gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com \
--ranges=google-managed-services-default --network=default \
--project=YOUR_PROJECT_ID

Create an instance with private IP address and SSL enabled

Before running the command as follows, replace DB_ROOT_PASSWORD with the password of your database user.

Optionally, modify the values for the following parameters:
- --database-version: The database engine type and version. If left unspecified, the API default is used. See the gcloud database versions documentation to see the current available versions.
- --cpu: The number of cores in the machine.
- --memory: A whole number value indicating how much memory to include in the machine. A size unit can be provided (for example, 3072MB or 9GB). If no units are specified, GB is assumed.
- --region: The regional location of the instance (for example asia-east1, us-east1). If left unspecified, the default us-central1 is used. See the full list of regions.

Run the gcloud sql instances create command to create a Cloud SQL instance with a Private IP address.

gcloud sql instances create quickstart-instance \
--database-version=POSTGRES_14 \
 --cpu=1 \
 --memory=4GB \
 --region=us-central \
 --root-password=DB_ROOT_PASSWORD \
 --no-assign-ip \
--network=default

Run the gcloud sql instances patch command to enable only allow SSL connections for the instance.

gcloud sql instances patch quickstart-instance --require-ssl

Create a database

Take the following steps to create a database:

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Select quickstart-instance.
From the SQL navigation menu, select Databases.
Click Create database.

In the Database name field of the New database dialog, enter quickstart-db.
Click Create.

gcloud

Run the gcloud sql databases create command to create a database.

gcloud sql databases create quickstart-db --instance=quickstart-instance

Create a user

Take the following steps to create a user:

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
Select Users from the SQL navigation menu.
Click Add user account.
On the Add a user account to instance instance_name page, add the following information:
- In the Username field, enter quickstart-user
- In the Password field, specify a password for your database user. Make a note of this for use in a later step of this quickstart.
Click Add.

gcloud

Before running the following command, make the following replacements:

PASSWORD with a password for your database user. Make a note of this for use in a later step of this quickstart.

Run the gcloud sql users create command to create the user.

gcloud sql users create quickstart-user \
--instance=quickstart-instance \
--password=PASSWORD

Username length limits are the same for Cloud SQL as for on-premises PostgreSQL.

Deploy sample app to Cloud Run

Configure a Cloud Run service account

Configure the Compute Engine default service account used by Cloud Run so that it has the Cloud SQL Client and Storage Object Viewer roles.

Console

In the Google Cloud console, go to the IAM Service accounts page.

Go to IAM Service accounts
Click the Actions menu beside the service accound, then click Manage access.
Click Add another role, then find and select the Cloud SQL Client (roles/cloudsql.client) in the Role dropdown.
Click Add another role, then find and select the Storage Object Viewer (roles/storage.ObjectViewer) in the Role dropdown.
Click Save.

gcloud

Run the following gcloud command to get a list of your project's service accounts:
```
gcloud iam service-accounts list
```
Copy the EMAIL

Web Proxy Viewer | New URL | Original Page