How to connect to Google CloudSQL Postgres with DBeaver

Introduction

Recently I have received many requests from our data teamto export all kinds of data from a CloudSQL database. Since I don’t want to become a bottleneck, it’s best to provide them a shared READ-ONLY account. In this way they can play with the data themselves freely with a SQL client.

Prerequisite

I am assuming that you have:

  • A running CloudSQL instance (Postgres) with a public IP
  • Permission on GCP to edit the database instance
  • A database user account that have permission to create new users (Optional unless you need to create new accounts)

Step One

Follow the instructions described in the documentation to download the certificates and allow only SSL/TSL access for security purpose: https://cloud.google.com/sql/docs/postgres/configure-ssl-instance

After this step you should have 3 files on hand:

  • 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

Important:

For the client-key.pem , run the following command to restrict the permission. Otherwise you will get an error complain about the permission on the private key is too wide.

chmod 0600 client-key.pem

Step Two

Add your own public IP to the “authorized network” section in the GCP CloudSQL console (see the connections tab. It’s on the same page where you download the certificates). You can find your IP by search “what’s my IP” in Google.

Step Three

Once you have install DBeaver, you can start a new connection and search for “Postgres”. It will prompt the right connection view to select.

Select the connection view

On the connection view, enter the following configurations:

  • Database instance public IP, which can be found on the overview of your instance.
  • Database name you want to connect to.
  • Database username and password
Main configuration

Now switch to the SSL tab at the right side, select use SSL, supply the certificates and set the SSL mode to “verify-ca”.

One caveat: we need to convert the SSL certificate to .pk8 format since DBeaver is a Java application. Use the converted file in the SSL cerificate key section.

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

Test your connection and it should succeed like a breeze :)

I hope this helps you connect to CloudSQL using DBeaver.

Last step (Optional)

As I mentioned at the beginning, I want to create a READ-ONLY account for our data team. To do this in DBeaver is really easy. Just click on the “Roles” section of the connected database and select “Create New Role”. It will prompt you to create a new role:

Create New Role

After it’s created, you can assign the appropriate table permissions to the role. Since it’s read-only, the “SELECT” option should be enough:

Assign permissions

Once all of these are done, you can save all the certificates (including the converted one) and the database access credentials to a shared vault or secret manager so that the users can get their read-only access. Be sure to consult with your own IT/Security team on the best practice on shared account :)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ice Panda

Ice Panda

Software engineer during the day and write at night.