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.
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.
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.
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!
Hi @MacLopez Thanks for sharing your situation , this is actually a fairly common question when working with externally managed PostgreSQL databases that require secure connections using certificates (.pem files).
What’s happening:
The BigQuery Data Transfer Service (DTS) currently does not support certificate-based authentication for PostgreSQL. It only allows basic user/password authentication, with no option to configure custom TLS or upload .pem files.
While DataStream does offer more secure connection options, support for PostgreSQL is somewhat limited , especially if your server enforces certificate-based authentication. Uploading .pem files directly through the UI isn’t currently supported, which can be a challenge for environments with strict security requirements (which seems to be your case).
Possible solutions:
Option 1: Use an intermediate layer like a bastion host or Cloud SQL Auth Proxy
One workaround is to deploy a small GCE VM or a bastion host that can securely connect to your PostgreSQL using the .pem certificate. From there, you can set up an ETL job to move data into BigQuery.
This approach works, but it does require managing infrastructure and custom scripts.
Option 2: Use an ETL platform that supports secure PostgreSQL connections
A simpler approach may be using a platform like Windsor.ai, which supports connecting to externally managed PostgreSQL instances — even those requiring .pem-based authentication — and automatically syncing the data to BigQuery.
It also supports incremental updates and reduces the need for manual config or infrastructure.
Final recommendation:
If you need to keep the secure .pem connection and want to avoid building additional infrastructure, I’d recommend looking into an ETL platform that natively supports secure PostgreSQL connections.
If you’d rather stick with DataStream, you could also consider setting up a VM with secure access to the database and use Dataflow to push the data into BigQuery. Hope this helps!
BigQuery Data Transfer Service and DataStream don’t support uploading SSL certificates, which your PostgreSQL source requires. That’s why the connection fails even if you have all the credentials.
There are multiple options you can try:
- Use a ELT/ETL tool like Windsor.ai Or Airbyte
- Supports PostgreSQL to BigQuery
- Easier setup, handles SSL, no coding required
- A custom script on a GCP VM
- You can upload your .pem certs there and control the connection
- Load data to BigQuery manually or on a schedule
- Advanced: Set up a bastion host with reverse SSH tunneling if the DB is behind a firewall
Since you’re new to GCP, I’d recommend starting with Windsor.ai as it offers a simple no-code way to integrate data.
Connecting a managed PostgreSQL source to BigQuery can be tricky when certificates are involved because neither Data Transfer Service nor Datastream currently allow direct upload of .pem files for authentication. Both tools assume the source is publicly accessible or using supported SSL configurations.
Some practical approaches:
1- VPN / Private IP + Cloud SQL Auth – If the PostgreSQL instance can be accessed via a private network or Cloud SQL, Datastream can connect using username/password with SSL enabled in the connection profile.
2- Self-managed replication – Set up a small VM or Cloud Function to extract data using your .pem certificates and load it incrementally into BigQuery using the BigQuery API.
3- Use an intermediate ETL/ELT layer – Tools like Windsor.ai, Fivetran, or Airbyte can handle SSL certificates and securely stream PostgreSQL data into BigQuery without manual certificate management.
The key is that BigQuery’s native transfer tools have limitations around SSL cert uploads, so an ETL/ELT layer or a controlled extraction process often simplifies things while keeping it secure.