PosgreSQL to BigQuery Connection

[MacLopez]

I can't seem to connect the PostgreSQL source to BigQuery using Data Transfer Service and/or Data Stream

I already have the connection details as I have linked it directly to Looker Studio. However, it would be great if we also have it in BigQuery as possibilities are limitless. As mentioned, I already have the credentials (Username, Password, Host, Database name, Port) and the certificates and key (in .pem files). I only have the said credentials and files as the PosgreSQL source is managed by our affiliate.

Attempt 1. via Data Transfer Service

• I have tried filling out the information and the credentials but there is no way to upload the certificates. Which is why (I think) there's an error when trying to proceed or connect.

Attempt 2. via Data Stream

• I also tried creating a stream via Data Stream. Again, filled out the necessary information. We also created a connection profile where the credentials are needed but there's no option to upload the certificates?

I'm quite new to GCP and I also can't find a helpful step-by-step or how to on this topic. Please help.

[ruthseki]

Hi @MacLopez,

Welcome to Google Cloud Community!

Google's Data Transfer Service (DTS) and Datastream for PostgreSQL don't have a straightforward UI option to upload client-side SSL certificates (.pem files) required for authentication or mutual TLS (mTLS).

These services generally handle server-side SSL verification (ensuring the GCP service connects to the correct PostgreSQL server using its certificate) but often rely on other methods like username/password, IP allowlisting, or network-level security (VPC Peering, VPN, SSH Tunnels) for the actual connection and client authentication, rather than client certificate authentication directly configured within the service UI.

Since your affiliate requires these client certificates for the connection, the standard DTS/Datastream UI flows won't work out-of-the-box for your specific setup.

For workarounds, here’s a related list of cases and documentation that you may find useful.

• cannot create a connection profile for Cloud SQL Postgres with private IP address and SSL enabled
• Datastream to Postgres with only allow ssl enabled.
• Create a connection profile for PostgreSQL database
• Network connectivity options

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[MacLopez]

Apologies for the late response on this. Based on your reply, I gather that in order for me to proceed, our affiliate may need to intervene with the process as to 'not require' the client certificates for the connection?

Thanks so much!