How to connect to Google CloudSQL Postgres with DBeaver
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.
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)
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
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
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.
Once you have install DBeaver, you can start a new connection and search for “Postgres”. It will prompt the right connection view to select.
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
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
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:
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:
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 :)