Connecting to Postgres using private IP

1
votes

When creating my Postgres Cloud SQL instance I specified that would like to connect to it using private IP and chose my default network.

My VM sits in the same default network.

Now, I follow instructions as described here https://cloud.google.com/sql/docs/postgres/connect-compute-engine and try executing 

psql -h [CLOUD_SQL_PRIVATE_IP_ADDR] -U postgres

from my VM, but get this error:

psql: could not connect to server: Connection timed out Is the server running on host "CLOUD_SQL_PRIVATE_IP_ADDR" and accepting TCP/IP connections on port 5432?

Anything I am under-looking?

P.S. My Service Networking API (whatever that is) is enabled.

2
google-cloud-platformgoogle-cloud-sql
Do you use Cloud SQL to create Postgres or just install postgres in your own instance ? - howie
@howie Cloud SQL - Dennis
As per the documentation, the PostgreSQL instance and the Google Compute Engine instance have to be in the same region. Is your GCE instance in the same region as your PostgreSQL? - dhauptman
I'm having the same problem. I checked the Cloud SQL "Private IP" box in the auto-setup, it seemed to create the "Private Service Networking," ip range allocation, VPN Peering, and everything else successfully, but I still couldn't connect to the Cloud SQL instance, ping its private IP, etc. from the VM instance. I also checked to make sure I could connect to the Cloud SQL instance via a Public IP, and as long as I added it to the VM's public IP to the accept list in Cloud SQL, that worked just fine. Still digging, will update here if I figure it out. - Brad Folkens
Are you connecting from a Docker container or just directly in the OS on the VM instance? - Brad Folkens

2 Answers

2
votes

If you have ssh to a VM in the same network you can connect to Cloud SQL using cloud SQL proxy:

  1. Open the ssh window (VM-instances in Computer engine and click on ssh), then download the proxy file with:
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
  1. Execute, in the ssh shell
chmod +x cloud_sql_proxy

  1. Create a service account with role Cloud SQL Client and create an api key. Download the json key in your local computer.

  2. In the ssh vm shell click on the wheel and "upload", and upload the key file

    3. 5. 
./cloud_sql_proxy -instances=<Instance connection name>=tcp:5432 -credential_file=<name of the json file>

where "Instance connection name" can be found in SQL-Overview -> Connect to this instance

  1. Finally
psql "host=127.0.0.1 port=5432 sslmode=disable user=<your-user-name> dbname=<your-db-name>"

On the other hand, if you want to connect to cloud sql from your local computer and the cloud sql instance does not have a public ip you have to connect through a bastion host configuration.

https://cloud.google.com/solutions/connecting-securely

1
votes

According to this document connect via private ip, you need to setup following item:

  • You must have enabled the Service Networking API for your project. If you are using shared VPC , you also need to enable this API for the host project.
  • Enabling APIs requires the servicemanagement.services.bind IAM permission.
  • Establishing private services access requires the Network Administrator IAM role.
  • After private services access is established for your network, you do not need the Network Administrator role to configure an instance to use private IP.