How to connect Postgres managed database as a datasource in Google Data Studio

Question

Hello, I want to connect Postgres managed database to google data studio but I get an error while trying to establish a connection. any guidance will be helpful.

Rajkishore J · Accepted Answer

Hi

Please follow the below steps to connect Postgres Managed database as the data source for google data studio

Downloaded the CA certificate for your DigitalOcean instance from the console ( this will be a Server certificate)

https://docs.digitalocean.com/products/databases/postgresql/how-to/connect/

Generate client public/private keys pairs

https://support.google.com/a/answer/6342198?hl=en

Make sure you have OpenSSL installed on your operating system. OpenSSL will be used to generate client-side public/private key pairs.

openssl genrsa -out rsaprivkey.pem 2048
openssl rsa -in rsaprivkey.pem -pubout -outform DER -out rsapubkey.der
openssl pkcs8 -topk8 -inform PEM -outform DER -in rsaprivkey.pem -out rsaprivkey.der -nocrypt

To connect your DO PostgreSQL as a data source in Data Studio

https://support.google.com/datastudio/answer/7288010?hl=en&ref_topic=7332343#zippy=%2Cin-this-article

Select “Data Sources” on the left followed by the blue (+) on the bottom right corner to add a new data source. Please follow the next set of prompts to agree to google terms and conditions.
Select “PostgreSQL” as the data source type on the left and proceed to “Authenticate” DigitalOcean PostgreSQL. Follow the next set of steps to authenticate with your Google account.
Under the “Basic” settings, input the values for host, port, database, username, and password. (Note: these values can be obtained from the DigitalOcean console)
Remember to enable SSL for over-the-wire encryption of data and enter the cert and key files.
Finally, click “Authenticate” to authenticate with the DO PostgreSQL database server.
After authentication with the database, you can proceed to query data from it