Connecting psql Client Using the Cloud SQL Proxy Docker Image

This page describes how to connect a psql client to your Google Cloud SQL instance, from a client machine running Linux or Compute Engine Linux instance, using the Cloud SQL Proxy Docker image.

Before you begin

You must have:

  • Installed the gcloud command-line tool. Learn more.
  • Authorized the gcloud tool. Learn more.
  • Set the default project for the gcloud tool. Learn more.
  • Configured a database user on your Cloud SQL instance. Learn more.

Connecting a psql client using the proxy Docker image

To connect using the proxy Docker image:

  1. Enable the Cloud SQL Administration API.

    Enable the API

  2. If you are using a Compute Engine instance, prepare the instance:
    1. Display the Compute Engine instance properties:
      gcloud compute instances describe [GCE_INSTANCE_NAME]
    2. Verify the scopes enabled on the instance.

      Authenticating using scopes requires both of the following scopes:

      • https://www.googleapis.com/auth/sqlservice.admin
      • https://www.googleapis.com/auth/devstorage.read_write

      Alternatively, the https://www.googleapis.com/auth/cloud-platform scope enables all Google Cloud Platform APIs.

      Note: If your Compute Engine instance does not have the proper scopes, you must either create a new Compute Engine instance or use a service account as described later in this task. You cannot add scopes to an existing Compute Engine instance.
    3. Open a terminal connection to the instance, using the instructions at Connecting to Linux Instances.
  3. Install the psql client on the Compute Engine instance or client machine, 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 MySQL client from the package manager:

    sudo yum install postgresql
    

    openSUSE

    Install the MySQL 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. If needed, install the Docker client:
    curl https://get.docker.com | sh
    sudo usermod -aG docker $USER
    

    If you are using a container-optimized Compute Engine instance, it already has the Docker client installed.

  5. Install the latest version of the Proxy Docker image from the Google Container Registry.
    docker pull gcr.io/cloudsql-docker/gce-proxy
  6. Determine the location of your local certificate files.
    The following table provides some common locations:
    OSCertificate location
    Debian/Ubuntu/Gentoo/etc/ssl/certs/ca-certificates.crt
    Fedora/RHEL/etc/pki/tls/certs/ca-bundle.crt
    OpenSUSE/etc/ssl/ca-bundle.pem
    OpenELEC/etc/pki/tls/cacert.pem
  7. If you are running the Proxy Docker image on a local machine (not a Compute Engine instance), or your Compute Engine instance does not have the proper scopes, create a Google Cloud Platform service account.

    You provide the path to the key for the service account to the proxy for authentication.

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

      Go to the Service accounts page

    2. If needed, 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 Cloud SQL > Cloud SQL Client.

      Alternatively, you can use the primitive Editor role by selecting Roles > Editor, but the Editor role includes permissions across Google Cloud Platform.

    6. Change the Service account ID to a shorter value if needed.
    7. Click Furnish a new private key.
    8. The default key type is JSON, the correct value.
    9. Click Create.

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

  8. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  9. Select the instance to open its Instance details page and copy the Instance connection name.
  10. Start the proxy.

    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.

    TCP sockets

    docker run -d -v /cloudsql:/cloudsql \
      -v [LOCAL_CERTIFICATE_FILE_PATH]:[LOCAL_CERTIFICATE_FILE_PATH] \
      -p 127.0.0.1:5432:5432 \
      gcr.io/cloudsql-docker/gce-proxy /cloud_sql_proxy \
      -instances=[INSTANCE_CONNECTION_NAME]=tcp:0.0.0.0:5432 -credential_file=[CLOUD_KEY_FILE_PATH]
    

    If you are using a Compute Engine instance, do not include the credential_file parameter.

    Always specify 127.0.0.1 prefix in -p so that the proxy is not exposed outside the local host.

    Unix sockets

    docker run -d -v /cloudsql:/cloudsql \
      -v [LOCAL_CERTIFICATE_FILE_PATH]:[LOCAL_CERTIFICATE_FILE_PATH] \
      gcr.io/cloudsql-docker/gce-proxy /cloud_sql_proxy -dir=/cloudsql \
      -instances=[INSTANCE_CONNECTION_NAME] -credential_file=[CLOUD_KEY_FILE_PATH]
    

    If you are using a Compute Engine instance, do not include the credential_file parameter.

    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.

  11. Start the client:

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

    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. Enter the password.
    3. You should see the psql prompt.

    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. You should see the psql prompt.

Need help? See our Cloud SQL Support page.

What's next

Send feedback about...

Cloud SQL for PostgreSQL