Connecting DBeaver to Cloud SQL

Published in

Google Cloud - Community

6 min readSep 27, 2019

I like to manage my data and databases through DBeaver, and I don’t seem to be alone. So in this short blog post we will connect DBeaver to Google Cloud SQL (PostgreSQL) in two ( and a half ) different ways. First we will look at the recommended way of using the local Cloud SQL Proxy and secondly we will look at how-to hook up DBeaver directly to Cloud SQL using a secure connection.

I will assume that you have created a Cloud SQL instance as described here.

If you want to run the Cloud SQL Proxy in a container you can jump directly to that section

Installing the Cloud SQL Proxy

First we need to make sure that the Cloud SQL API is enabled.

Secondly we need to obtain the Cloud SQL Proxy. I am on a Mac so I just download the binary and make it executable like so.

$ curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64$ chmod +x cloud_sql_proxy

Instructions for how to install the proxy on other operating systems can be found here.

In order to run the proxy we need to get the instance name from the developer console. Go to the Cloud SQL instance page and click the instance name to open its Instance details page. Under Connect to this instance, note the Instance connection name.

For the command line aficionados there’s always:

$ gcloud sql instances describe <INSTANCE_NAME> | grep connectionName

Start the proxy in its own terminal so you can monitor its output. Replace <INSTANCE_CONNECTION_NAME> with the instance connection name you copied in the previous step.

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

Optional: Running the Cloud SQL proxy in a container

I’ll assume that you have Docker installed on your machine

Download the Cloud SQL Proxy Docker image from the Google Container Registry.

$ docker pull gcr.io/cloudsql-docker/gce-proxy:1.14

Then we need to create a Service Account.

1. Go to the Service accounts page of the Google Cloud Platform Console.
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
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, easily 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.
You provide the path to the key file as “PATH_TO_KEY_FILE” when you start the proxy.

After we have configured the Service Account it’s time to start the proxy.

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
2. Select the instance to open its Instance details page and copy the Instance connection name.
For example: myinstance:us-central1:myproject.
3. 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.

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

Connecting DBeaver to the Cloud SQL Proxy

This step is the same regardless of if you are running the Cloud SQL Proxy in a container or not.

When we have the proxy running, connecting DBeaver is straightforward. Click Database > New Database Connection > PostgreSQL > Next

You’ll then get to a connection form where you specify host, port, database, user, and password. Leave the defaults and add the password for the postgres user. Also click Show all databases

Then you can click the Test Connection button and you should see:

Click OK twice and you are up and running.

Connecting DBeaver to Cloud SQL through SSL

So as stated earlier we really recommend you to use the Cloud SQL SQL proxy to connect DBeaver to Cloud SQL but if you are for some reason unable to do so we’ll go through how to connect DBeaver to a public IP address via SSL.

Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
1. Click the instance name to open its Instance details page.
2. Select the Connections tab.
3. Select the Public IP checkbox.
4. Click Add network.
5. In the Network field, enter the IP address or address range you want to allow connections from. Use CIDR notation.
6. Optionally, enter a name for this entry.
7. Click Done.
8. Click Save to update the instance.

On the same page make sure you click the Allow only SSL connections

Configuring SSL/TLS

Download the new server certificate information:

1.Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
2. Click the instance name to open its Instance details page.
3. Select the CONNECTIONS tab.
4.Scroll down to the Configure SSL server certificates section.
5. Click Create new certificate.
6. Scroll down to Download SSL server certificates section.
7. Click Download.
The server certificate information, encoded as a PEM file, is displayed and can be downloaded to your local environment.

Managing your client certificates

Creating a new client certificate

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
2. Click the instance name to open its Instance details page.
3. Select the CONNECTIONS tab.
4. Scroll down to the Configure SSL client certificates section.
5. Click Create new certificate.
6. In the Create a client certificate dialog box, give the certificate a name unique for this.
7. Click Create.
8. In the first section of the New SSL certificate created dialog box, click Download client-key.pem to download the private key to a file named client-key.pem.
9. In the second section, click Download client-cert.pem to download the client certificate to a file named client-cert.pem.
10. In the third section, click Download server-ca.pem to download the server certificate to a file named server-ca.pem.
11. Click Close.

At this point, you have:

A server certificate saved as server-ca.pem
A client public key certificate saved as client-cert.pem
A client private key saved as client-key.pem

In order to get this to work we need to restrict the permissions for the private key

$ chmod 0600 client-key.pem

Because DBeaver is a Java application, we will need to transform our key file to the *.pk8 format using an OpenSSL command like the one shown below.

$ openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client.root.pk8 -nocrypt

Now your connection form should look something like this:

And the SSL tab something like this:

At this point I hope you have DBeaver up and running against Cloud SQL. Feel free to ping me if there is something that doesn’t work.

Where to go next?