cannot create a connection profile for Cloud SQL Postgres with private IP address and SSL enabled

[qyuand456]

Hello,

I am trying to create a data stream to sync postgres db in cloud sql and insert them to google big query. My existing database is deployed with private and pubic IPs enabled. And SSL is also enforced.

I have done the following to connect using the private IP addresses but getting the following error.

FATAL:  connection requires a valid client certificate
FATAL:  pg_hba.conf rejects connection for host "10.132.0.11" user "datastream_user" database "postgres" no encryption

 I have created a VPC peering and also set up a reverse proxy as mentioned here. 
https://cloud.google.com/datastream/docs/private-connectivity#reverse-csql-proxy

I can telnet to the postgres from the reverse proxy as well. I assume the problem is that I need to add the certificate as I have enforced SSL. 

How can i solve this problem? Help is highly appreciated .

Thanks

[ms4446]

To create a connection profile for Cloud SQL Postgres with a private IP address and SSL enabled in Google Cloud, you need to:

1. Download the Client Certificate and Private Key from Google Cloud:

   • Go to the Cloud SQL console.
   • Navigate to your PostgreSQL instance.
   • Download the Client certificate and Client private key.

2. Add the Client Certificate and Private Key to the Datastream Connection Profile:

   • In the Datastream connection profile, navigate to the Security tab.
   • Add the downloaded client certificate and private key to the Client certificate and Client private key fields, respectively.

3. Configure the Cloud SQL Instance to Allow Only SSL Connections:

   • In the Cloud SQL console, navigate to the Connections page for your instance.
   • Under the Security tab and the SSL/TLS section, select the Allow only SSL connections checkbox.

4. Configure the Reverse Proxy:

   • Ensure that the reverse proxy is set up to forward the SSL connection to the Cloud SQL instance. The exact configuration will depend on the reverse proxy software you are using.

Once you've completed these steps, you should be able to create a data stream to sync your PostgreSQL database in Cloud SQL to Google BigQuery using the private IP address of the Cloud SQL instance.

Troubleshooting the Error Message:

The error message "FATAL: connection requires a valid client certificate" indicates that Datastream is trying to connect to the Cloud SQL instance without a client certificate. This is because you've configured the Cloud SQL instance to allow only SSL connections.

To address this error:

• Ensure that you've added the correct client certificate and private key to the Datastream connection profile.
• Verify that the client certificate and private key are in the correct format.
• Confirm that the reverse proxy is correctly forwarding the SSL connection to the Cloud SQL instance.

If you continue to face issues, consider reaching out to Google Cloud Support for more in-depth troubleshooting.

[qyuand456]

Thanks @ms4446 for the reply. I am using the posgres as the source and the "Add the Client Certificate and Private Key to the Datastream Connection Profile:" is not available. I could see that it exists only in mysql.

Is there another location that I should look into or am I missing some details here.

[ms4446 · Accepted solution]

I apologize for the oversight. You're correct. As of now, Google Cloud Datastream does not support the addition of a client certificate and private key for PostgreSQL connections.

In the meantime, here are a few potential workarounds:

1. Reverse Proxy Configuration: Ensure that your reverse proxy is correctly set up to forward connections to the PostgreSQL instance. If you've enforced SSL on your PostgreSQL instance, consider temporarily disabling it to see if Datastream can connect without SSL.

2. Cloud SQL Auth Proxy: You can use the Cloud SQL Auth proxy to provide a secure connection to your Cloud SQL instance without the need for SSL/TLS certificates. The Auth proxy opens a local port on your machine, and any connection to this local port is securely forwarded to the Cloud SQL instance. This method uses IAM for authentication, ensuring a secure connection without the complexities of managing SSL certificates.

3. Custom Proxy: While it's possible to set up a custom proxy that can handle client certificates and private keys, this approach can be complex and might introduce additional maintenance overhead.

[hamma_]

When will be able to use  the  the addition of a client certificate and private key for PostgreSQL connections ? I need to connect with this setting because of company security requirement.

[jcarp]

I am also in the same boat and currently cannot use datastream because of it, would be a useful feature to have