Connecting the psql client from Compute Engine

MySQL   |  PostgreSQL   |  SQL Server

This page describes how to use the psql client, installed on a Compute Engine instance, to connect to Cloud SQL.

You can use private IP, public IP, the Cloud SQL Proxy, or the proxy Docker image.

Before you begin

Before you can connect to your Cloud SQL instance, you must have a default database user (postgres) on the instance.

This task does not include instructions for setting up your Compute Engine instance. If you need help with creating and configuring a Compute Engine instance, see the Compute Engine documentation.

Ensure your Compute Engine VM has the proper scope to connect using the Cloud SQL Admin API.

Configure the service account to have either of the following access scopes:

• https://www.googleapis.com/auth/sqlservice.admin
• https://www.googleapis.com/auth/cloud-platform

Private IP

To connect to Cloud SQL from a Compute Engine instance using private IP, private services access must be set up for your environment and your Cloud SQL instance must be configured to use private IP. Your Compute Engine instance must be in the same region as your Cloud SQL instance, and on the network configured for a private connection. >Learn more.

1. Configure your instance to use private IP

Use the instructions in Configuring Private IP Connectivity.

2. Open a terminal connection to your Compute Engine instance.

Use the appropriate instructions, depending on the instance's operating system:

• For Linux, see Connecting to Linux Instances.
• For Windows, see Connecting to Windows Instances.

If your Compute Engine instance is running either an RHEL or a CentOS public image, SELinux might block the proxy connection. If this happens, you must configure the SELinux feature to allow the connection.

For more information about SELinux for RHEL, see the RHEL documentation. For more information about SELinux for CentOS, see the CentOS documentation.

3. Install the psql client on the Compute Engine instance, if it is not already installed.

Debian/Ubuntu

Install the psql client from the package manager:

sudo apt-get update
sudo apt-get install postgresql-client

CentOS/RHEL

Install the psql client from the package manager:

sudo yum install postgresql

openSUSE

Install the psql client from the package manager:

sudo zypper install postgresql

Other platforms

1. Download the PostgreSQL Core Distribution for your platform from the PostgreSQL Downloads page.
   The Core Distribution includes the psql client.
2. Install the PostgreSQL database, following the directions on the download page.

4. Connect with the psql client.

psql -h [CLOUD_SQL_PRIVATE_IP_ADDR] -U postgres

Public IP

To connect using public IP:

1. Add a static IPv4 IP address to the Compute Engine instance, if it does not already have one.

You cannot connect to Compute Engine using IPv6. For information about adding a static IP address, see Reserving a new static external IP address in the Compute Engine documentation.

2. Authorize the static IP address of the Compute Engine instance as a network that can connect to your Cloud SQL instance.

For more information, see Configuring access for public IP connections.

Note: Grant authorization only for static IP addresses. Ephemeral IP addresses are temporary; granting them access to your instance creates a security risk because they could be reassigned to a different Compute Engine user later.

3. Open a terminal connection to your Compute Engine instance.

Use the appropriate instructions, depending on the instance's operating system:

• For Linux, see Connecting to Linux Instances.
• For Windows, see Connecting to Windows Instances.

If your Compute Engine instance is running either an RHEL or a CentOS public image, SELinux might block the proxy connection. If this happens, you must configure the SELinux feature to allow the connection.

For more information about SELinux for RHEL, see the RHEL documentation. For more information about SELinux for CentOS, see the CentOS documentation.

4. Install the psql client on the Compute Engine instance, if it is not already installed.

Debian/Ubuntu

Install the psql client from the package manager:

sudo apt-get update
sudo apt-get install postgresql-client

CentOS/RHEL

Install the psql client from the package manager:

sudo yum install postgresql

openSUSE

Install the psql client from the package manager:

sudo zypper install postgresql

Other platforms

1. Download the PostgreSQL Core Distribution for your platform from the PostgreSQL Downloads page.
   The Core Distribution includes the psql client.
2. Install the PostgreSQL database, following the directions on the download page.

5. Connect with the psql client.

psql -h [CLOUD_SQL_PUBLIC_IP_ADDR] -U postgres

Run the following gcloud command to find the CLOUD_SQL_PUBLIC_IP_ADDR:

gcloud sql instances list

For an example of how to connect using SSL, see Connecting with SSL.

6. The psql prompt appears.

7. If you need to keep unused connections alive:

Set the TCP keepalive.

For more information, see Communicating between your instances and the Internet in the Compute Engine documentation.

Connections are kept alive automatically for instances.

Cloud SQL Proxy

To connect using the proxy from Compute Engine:

1. Enable the Cloud SQL Admin API.

Enable the API

2. Create a service account.

Note: If your Compute Engine instance has either the Full API access or Cloud SQL Admin API scope, you can skip this step; you do not need to provide a certificate file when you start the proxy.

If you are connecting from Compute Engine, make sure your VM has the proper scope to connect using the Cloud SQL Admin API.

Configure the service account to have either of the following access scopes:

• https://www.googleapis.com/auth/sqlservice.admin
• https://www.googleapis.com/auth/cloud-platform

1. Go to the Service accounts page of the Google Cloud Console.

   Go to the Service accounts page

2. Select the project that contains your Cloud SQL instance.
3. Click Create service account.
4. In the Create service account dialog, provide a descriptive name for the service account.
5. For Role, select one of the following roles:
   • Cloud SQL > Cloud SQL Client
   • Cloud SQL > Cloud SQL Editor
   • Cloud SQL > Cloud SQL Admin
   Note: To create a service account with the required permissions, you must have resourcemanager.projects.setIamPolicy permission. This permission is included in the Project Owner, Project IAM Admin, and Organization Administrator roles.
   You must also have enabled the Cloud SQL Admin API. If you are using the legacy project roles (Viewer, Editor, Owner), the service account must have at least the Editor role.
6. Change the Service account ID to a unique, recognizable value.
7. Click Furnish a new private key and confirm that the key type is JSON.
8. Click Create.

   The private key file is downloaded to your machine. You can move it to another location. Keep the key file secure.

If the Compute Engine instance is in a different project than the Cloud SQL instance, ensure that its service account has the proper permissions in the project that contains the Cloud SQL instance:

1. Go to the Compute Engine instances listing in the Google Cloud Console.

   Go to the Compute Engine instances listing

2. If needed, select the project associated with the Compute Engine instance.
3. Select the Compute Engine instance to display its properties.
4. In the Compute Engine instance properties, copy the name of the service account.
5. Go to the IAM & Admin Projects page in the Google Cloud Console.

   Go to the IAM & Admin Projects page

6. Select the project that contains the Cloud SQL instance.
7. Search for the service account name.

8. If the service account is already there, and it has a role that includes the cloudsql.instances.connect permission, you can proceed to step 4.

   The Cloud SQL Client, Cloud SQL Editor and Cloud SQL Admin roles all provide the necessary permission, as do the legacy Editor and Owner project roles.

9. Otherwise, add the service account by clicking Add.

10. In the Add members dialog, provide the name of the service account and select a role that include the cloudsql.instances.connect permission (any Cloud SQL predefined role other than Viewer will work).

    Alternatively, you can use the basic Editor role by selecting Project > Editor, but the Editor role includes permissions across Google Cloud.

    If you do not see these roles, your Google Cloud user might not have the resourcemanager.projects.setIamPolicy permission. You can check your permissions by going to the IAM page in the Google Cloud Console and searching for your user id.

11. Click Add.

    You now see the service account listed with the specified role.

3. Open a terminal connection to your Compute Engine instance.

Use the appropriate instructions, depending on the instance's operating system:

• For Linux, see Connecting to Linux Instances.
• For Windows, see Connecting to Windows Instances.

If your Compute Engine instance is running either an RHEL or a CentOS public image, SELinux might block the proxy connection. If this happens, you must configure the SELinux feature to allow the connection.

For more information about SELinux for RHEL, see the RHEL documentation. For more information about SELinux for CentOS, see the CentOS documentation.

4. Install the psql client on the Compute Engine instance, if it is not already installed.

Debian/Ubuntu

Install the psql client from the package manager:

sudo apt-get update
sudo apt-get install postgresql-client

CentOS/RHEL

Install the psql client from the package manager:

sudo yum install postgresql

openSUSE

Install the psql client from the package manager:

sudo zypper install postgresql

Other platforms

1. Download the PostgreSQL Core Distribution for your platform from the PostgreSQL Downloads page.
   The Core Distribution includes the psql client.
2. Install the PostgreSQL database, following the directions on the download page.

5. Install the Cloud SQL Proxy on the Compute Engine instance.

Linux 64-bit

1. Download the proxy:

   wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
   

2. Make the proxy executable:

   chmod +x cloud_sql_proxy
   

Linux 32-bit

1. Download the proxy:

   wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
   

2. Make the proxy executable:

   chmod +x cloud_sql_proxy
   

Windows 64-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.

Windows 32-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.

Docker proxy image

For convenience, the Cloud SQL team maintains several container images that contain the Cloud SQL Proxy for use by our customers. For more information about these images, see the Cloud SQL Proxy repo on GitHub. You can pull the latest image to your local machine using Docker with the following command:

docker pull gcr.io/cloudsql-docker/gce-proxy:1.19.1

Other OS

For other operating systems not included here, you can compile the proxy from source.

6. Start the proxy.

Note: If your Compute Engine instance has either the Full API access or Cloud SQL Admin API scope, you do not need to provide a certificate file when you start the proxy.

Depending on your language and environment, you can start the proxy using TCP sockets, Unix sockets, or the Docker proxy image.

TCP sockets

1. Copy your instance connection name from the Instance details page.

   For example: myproject:myregion:myinstance.

2. If the instance has both public and private IP configured, and you want the proxy to use the private IP address, you must provide the following option when you start the proxy:

   -ip_address_types=PRIVATE

3. If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
4. Start the proxy.

   Some possible proxy invocation strings:

   • Using Cloud SDK authentication:

     ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432
     

     The specified port must not already be in use, for example, by a local database server.
   • Using a service account and explicitly including the name of the instance connection (recommended for production environments):

     ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 \
                       -credential_file=<PATH_TO_KEY_FILE> &
     

   For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.

Unix sockets

Note: The proxy currently does not support Unix sockets on Windows.

1. If you are using explicit instance specification, copy your instance connection name from the Instance details page.
2. Create the directory where the proxy sockets will live:

   sudo mkdir /cloudsql; sudo chmod 777 /cloudsql

3. If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
4. Open a new terminal window and start the proxy.

   Some possible proxy invocation strings:

   • Using a service account and explicitly including the name of the instance connection (recommended for production environments):

     ./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \
                       -credential_file=<PATH_TO_KEY_FILE> &

   • Using Cloud SDK authentication and automatic instance discovery:

     ./cloud_sql_proxy -dir=/cloudsql &

   It is best to start the proxy in its own terminal so you can monitor its output without it mixing with the output from other programs.

   For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.

5. When using a unix socket to connect to Cloud SQL using the Cloud SQL Proxy, make sure the socket filename's length does not surpass the system's limit. It depends on the system, but it's usually between 91-108 characters. On Linux, the length is usually defined as 108, and you can use the following command to check:
   cat /usr/include/linux/un.h | grep "define UNIX_PATH_MAX"

Docker proxy image

To run the Cloud SQL Proxy in a Docker container, use the Proxy Docker image available from the Google Container Registry.

You can start the proxy using either TCP sockets or Unix sockets, with the commands shown below.

Note: The proxy doesn't support listening on Unix domain sockets on Windows.

Depending on your language and environment, you can start the proxy using either TCP sockets or Unix sockets. Unix sockets are not supported for applications written in the Java programming language or for the Windows environment.

Using TCP sockets

docker run -d \\
  -v PATH_TO_KEY_FILE:/config \\
  -p 127.0.0.1:5432:5432 \\
  gcr.io/cloudsql-docker/gce-proxy:1.19.1 /cloud_sql_proxy \\
  -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:5432 -credential_file=/config

If you are using the credentials provided by your Compute Engine instance, do not include the credential_file parameter and the -v PATH_TO_KEY_FILE:/config line.

Always specify 127.0.0.1 prefix in -p so that the proxy is not exposed outside the local host. The "0.0.0.0" in the instances parameter is required to make the port accessible from outside of the Docker container.

Using Unix sockets

docker run -d -v /cloudsql:/cloudsql \\
  -v PATH_TO_KEY_FILE:/config \\
  gcr.io/cloudsql-docker/gce-proxy:1.19.1 /cloud_sql_proxy -dir=/cloudsql \\
  -instances=INSTANCE_CONNECTION_NAME -credential_file=/config

If you are using the credentials provided by your Compute Engine instance, do not include the credential_file parameter and the -v PATH_TO_KEY_FILE:/config line.

If you are using a container optimized image, use a writeable directory in place of /cloudsql, for example:

-v /mnt/stateful_partition/cloudsql:/cloudsql

You can specify more than one instance, separated by commas. You can also use Compute Engine metadata to dynamically determine the instances to connect to. Learn more about the proxy parameters.

7. Start the psql session.

The connection string you use depends on whether you started the proxy using a TCP socket or a UNIX socket or Docker.

TCP sockets

1. Start the psql client:

   psql "host=127.0.0.1 sslmode=disable dbname=<DB_NAME> user=<USER_NAME>"
   

   Even though the sslmode parameter is set to disable, the proxy does provide an encrypted connection.

   When you connect using TCP sockets, the proxy is accessed through 127.0.0.1.

2. If prompted, enter the password.
3. The psql prompt appears.

Using Unix sockets

1. Start the psql client:

   psql "sslmode=disable host=/cloudsql/<INSTANCE_CONNECTION_NAME> user=<USERNAME>"
   

   Even though the sslmode parameter is set to disable, the proxy does provide an encrypted connection.

2. Enter the password.
3. The psql prompt appears.

Need help? For help troubleshooting the proxy, see Troubleshooting Cloud SQL Proxy connections. Or, see our Cloud SQL Support page.

What's next

• Get help troubleshooting connection issues for the Cloud SQL Proxy.
• Create users and databases.
• Learn more about private IP.
• Learn about options for connecting to your instance from your application.
• Learn about the psql client.
• Learn about options for support.