Connecting psql Client Using the Cloud SQL Proxy

MySQL   |  PostgreSQL

Beta

This is a beta release of Cloud SQL for PostgreSQL. This product might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

This page describes how to connect a psql client to your Google Cloud SQL instance using the Cloud SQL Proxy, rather than over IP.

For information about connecting a psql client to a Cloud SQL instance using IP, see Connecting psql Client Using IP Addresses.

For more information about how the proxy works, see About the Cloud SQL Proxy.

Before you begin

Before you can connect a psql to a Cloud SQL instance, you must have:

• Created a Cloud SQL instance, including configuring the default user.

  For more information about creating instances, see Creating Instances.

  For more information about configuring the default user, see Configuring the default user account.

• Determined how you will connect to your instance.

  For information about the available connection options and how to choose between them, see Connection Options for External Applications.

Connecting the psql client

Connecting a psql to your Cloud SQL instance with the proxy involves the following steps:

1. Enable the Cloud SQL API
2. Install the proxy
3. Create a service account
4. Start the proxy
5. Start the psql session

1. Enable the Cloud SQL API

Enable the Cloud SQL Administration API.

Enable the API

2. Install the proxy

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
   

OS X 64-bit

1. Download the proxy:

   curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
   

2. Make the proxy executable:

   chmod +x cloud_sql_proxy
   

OS X 32-bit

1. Download the proxy:

   curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
   

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, renaming it 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, renaming it to cloud_sql_proxy.exe.

If your operating system is not included here, you can also compile the proxy from source.

3. Create a service account

When you connect using the proxy, the proxy needs to autheniticate with Google Cloud Platform. You can either use your Cloud SDK credentials, or you can provide the proxy with a path to a local key file from a service account you create (recommended for production instances). If you are using your Cloud SDK credentials, you can skip this step.

For more information about service accounts, see the Google Cloud Platform Auth Guide.

Note: To create a service account with the required permissions, the user you are logged in as must have the resourcemanager.projects.setIamPolicy permission. This permission is included in the Project Owner role, as well as the Project IAM Admin and Organization Administrator roles. If your user account does not have this permission, you must ask a user with the required permission to create the service account for you.

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 Project > Editor, but the Editor role includes permissions across Google Cloud Platform.

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

6. Change the Service account ID to a unique value that you will recognize so you can easily find this service account later if needed.
7. Click Furnish a new private key.
8. The default key type is JSON, which is the correct value to use.
9. Click Create.

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

4. Start the proxy

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

TCP sockets

1. Copy your instance connection name from the Instance details page.
2. 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.
3. 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 explicit instance specification (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

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 explicit instance specification (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. Start the client session

Now that you have installed and started the proxy, you can start a psql session using the proxy. This command can be used whenever you want to connect to your Cloud SQL instance with a psql 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? For help troubleshooting the proxy, see Troubleshooting Cloud SQL Proxy connections. Or, see our Cloud SQL Support page.

What's next

• Learn more about the proxy.
• Get help troubleshooting connection issues for the Cloud SQL Proxy.
• Learn about the two levels of access control for Cloud SQL instances.
• Create users and databases.
• Learn about connecting to your instance from your application.
• Learn about the psql Client.
• Learn about options for support.