Cloudsql-proxy: Postgres proxy wants to connect to port 3307 (but should be 5432)?

[metachris]

I want to connect to a Cloud SQL Postgres instance in a private network.

So I fire up the proxy like described in the tutorials (https://cloud.google.com/sql/docs/postgres/sql-proxy):

./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:5432

Now I try to connect my Postgres client, but after 30 seconds I receive the following error:

2019/04/14 17:36:59 couldn't connect to "[INSTANCE_CONNECTION_NAME]": dial tcp [CORRECT_SQL_INSTANCE_IP]:3307: connect: operation timed out

Should it not connect to postgres port 5432 rather than 3307? I found no documentation about this. Any advice would be much appreciated :)

[Carrotman42]

The proxy (between this proxy client and the server side code) works over
3307, to help differentiate the proxy protocol (postgres-over-tls) from
just the regular postgres protocol. It is expected.

I do not know why the connection to the backend is timing out. Do you have
a firewall between you and the database that might be blocking some ports?

[metachris]

Thanks for the clarification :+1: (Edit: no firewall here, but i'll look into more details on the networking part)

[bdimcheff]

@metachris did you ever figure out what was up? I'm getting the same timeouts when I've been trying to figure out how I can connect to cloudsql from outside of the VPC, but I suspect that you have to do this from a machine that's actually on the VPC.

[kurtisvg]

Hey bdimcheff@ -- in order to connect via Private IP, you'll have to be on the VPC. You can read more about the environment requirements here.

If you want to connect from an external network, you'll need to use Public IP.

[TonyWu5]

@kurtisvg isn't this the whole point of using the cloud sql proxy, to connect to the db from outside of the VPC?

[Carrotman42]

@TonyWu5 Original author here. At least historically, the whole point of using the proxy is to avoid having to manually set up SSL and firewall rules. Note that it was created even before Private IP was supported in Google's cloud platform, as far as I remember.

[ckmaresca]

This basically means that connecting to a GCP Postgress SQL that uses a private IP (which is the most sensible thing from a security standpoint) using Cloud SQL Proxy is broken and there is no way to specify the correct port.

Is someone going to actually fix this or is Cloud SQL Proxy for Postgres permenantly broken if you don't use a public IP? If that is the case, can someone update the docs to make sure you specify that Cloud SQL Proxy doesn't work with private IPs. Specifically https://cloud.google.com/sql/docs/postgres/connect-admin-proxy#private-ip

All this seems extremely badly thought out as pretty much every single other GCP service that has an IP uses private IPs - VPC or no VPC. So if you want your Cloud SQL instance only to be accessible to GCP resources, why expose it via a public IP?

I've used Cloud SQL Proxy in the past specifically to connect from a GCP VM -> Cloud SQL - this is a non-issue for MySQL as it works fine, but Postgress is totally broken in this model since it is trying to talk to an unsupported non-standard port.

[kurtisvg]

This basically means that connecting to a GCP Postgress SQL that uses a private IP (which is the most sensible thing from a security standpoint) using Cloud SQL Proxy is broken and there is no way to specify the correct port.

@ckmaresca This is absolutely not what it means in any way, shape, or form. The Cloud SQL proxy is perfectly capable of connecting to Postgres instances (or SQL Server instances) via public or private IP.

If you check out the "About the proxy" page, you'll see the details of how the proxy works:

The Cloud SQL Proxy provides secure access to your instances without the need for Authorized networks or for configuring SSL.
...
The proxy does not provide a new connectivity path; it relies on existing IP connectivity. To connect to a Cloud SQL instance using private IP, the proxy must be on a resource with access to the same VPC network as the instance.

Additionally:

While the proxy can listen on any port, it only creates outgoing connections to your Cloud SQL instance on port 3307. If you have an outbound firewall policy, make sure it allows connections to port 3307 on your Cloud SQL instance's IP.

The following diagram shows how the proxy connects to Cloud SQL:

As you can see, 3307 is a non-standard port for ALL the database types, because there is a server side proxy that listens on a different port than the engine itself. There is nothing stoping the proxy from working with Cloud SQL for Postgres.