Cloudsql-proxy: CloudSQLProxy attempts to connect to my Postgres Database on port 3307

[shicholas]

Bug Description

When using the CloudSQLProxy to connect to the Private IP of my PostgreSQL 11 Cloud SQL instances, the proxy attempts to reach the PostgreSQL instance on port 3307, instead of port 5432.

Example code (or command)

On my Mac OSX device, I started ./cloud_sql_proxy version 1.16 as follows:

./cloud_sql_proxy -ip_address_types=PRIVATE -instances=neon-law-development:us-west4:staging=tcp:5432 -verbose

In another terminal, I start a node process, which has a POSTGRES_URL of postgres://postgres:<PASSWORD>@127.0.0.1:5432/<DB_NAME>.

A connection is established (see stack traces below), however, it ends up timing out.

Stacktrace

Any relevant stacktrace here. Be sure to filter sensitive information.

Here are the logs from our CloudSQL instance:

2020/05/14 14:51:15 Rlimits for file descriptors set to {&{8500 9223372036854775807}}
2020/05/14 14:51:17 Listening on 127.0.0.1:5432 for neon-law-development:us-west4:staging
2020/05/14 14:51:17 Ready for new connections
2020/05/14 14:54:50 New connection for "<PROJECT-ID>:us-west4:staging"
2020/05/14 14:56:06 couldn't connect to "<PROJECT-ID>:us-west4:staging": dial tcp 172.20.0.3:3307: connect: operation timed out

In the last line, 172.20.0.3 is the Private IP of our database inside the VPC. Using the MySQL port 3307 threw me off. I've been able to connect to CloudSQL databases using public IPs and port 5432.

How to reproduce

1. Create a Postgres 11 Cloud SQL Database in a VPC
2. Repeat the steps above in the Example Code section.

Environment

1. OS type and version: OS X 10.15.4
2. Cloud SQL Proxy version (./cloud_sql_proxy -version): 1.16

[kurtisvg]

Hi @shicholas,

The Cloud SQL proxy always uses 3307 (MySQL is actually 3306) when connecting to your Cloud SQL port, regardless of engine type. This is because the client proxy actually connects to a server side proxy, as shown in this diagram from the "About the Proxy" page:

[shicholas]

Thanks, @kurtisvg.

Can you please tell me what the last two lines of my stack trace above mean?

2020/05/14 14:54:50 New connection for "<PROJECT-ID>:us-west4:staging"
2020/05/14 14:56:06 couldn't connect to "<PROJECT-ID>:us-west4:staging": dial tcp 172.20.0.3:3307: connect: operation timed out

I feel a connection to the database was initially successful, then it dropped? I received an ECONNRESET error from my Node app, which tells me the initial TCP handshake was successful.

I will look into the networking and authentication of my service account used for the proxy server.

[Carrotman42]

The "New connection" log means there was an incoming (local) connection to
the Proxy, and the "couldn't connect" message means the Proxy couldn't
connect to the remote database. The ECONNRESET as seen in your app is a
symptom of the Proxy closing the local connection after the remote
connection failed.

That IP address looks like a private ip. Do you have private IP enabled for
your database? If so (and if there is no public IP) then you'll only be
able to contact your database when the host running the Proxy is on the
same private network.

On Thu, May 14, 2020, 6:00 PM nick shook [e-mail] wrote:

Thanks, @kurtisvg https://github.com/kurtisvg.

Can you please tell me what the last two lines of my stack trace above
mean?

2020/05/14 14:54:50 New connection for ":us-west4:staging"
2020/05/14 14:56:06 couldn't connect to ":us-west4:staging": dial tcp 172.20.0.3:3307: connect: operation timed out

I feel a connection to the database was initially successful, then it
dropped? I received an ECONNRESET error from my Node app, which tells me
the initial TCP handshake was successful.

I will look into the networking and authentication of my service account
used for the proxy server.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/395#issuecomment-628947510,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAELF34PKVWNCUUYAI3MD6LRRSAYZANCNFSM4NBB5PFA
.

[shicholas]

Thanks, @Carrotman42 for this explanation.

I've now understood my error to be a networking one outside the scope of this library - in my case, it was because I created a new VPC and did not create the necessary firewalls for intra-VPC communication.

[VGerris]

I think this is far from obvious. The 'usual' cloud proxy used by cloud shell in the browser and to do port forwards with kubectl, transparently route private IPs : when I port forward to a service I can connect to that locally on the forwarded port.
In that line of thinking I would expect cloud proxy to do the same - why otherwise do I need a proxy?
If I am in the same network I can directly connect with a client, so I don't need a proxy.

When I use the option that say use cloud sdk, I need to have my project set too to have it find the database, so it all lines up.
I see no reason why the address should be any different from the others in the Kubernetes cluster so I find it hard to believe what you state below is correct.

The "New connection" log means there was an incoming (local) connection to the Proxy, and the "couldn't connect" message means the Proxy couldn't connect to the remote database. The ECONNRESET as seen in your app is a symptom of the Proxy closing the local connection after the remote connection failed. That IP address looks like a private ip. Do you have private IP enabled for your database? If so (and if there is no public IP) then you'll only be able to contact your database when the host running the Proxy is on the same private network.
…
On Thu, May 14, 2020, 6:00 PM nick shook @.*> wrote: Thanks, @kurtisvg https://github.com/kurtisvg. Can you please tell me what the last two lines of my stack trace above mean? 2020/05/14 14:54:50 New connection for ":us-west4:staging" 2020/05/14 14:56:06 couldn't connect to ":us-west4:staging": dial tcp 172.20.0.3:3307: connect: operation timed out I feel a connection to the database was initially successful, then it dropped? I received an ECONNRESET error from my Node app, which tells me the initial TCP handshake was successful. I will look into the networking and authentication of my service account used for the proxy server. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#395 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAELF34PKVWNCUUYAI3MD6LRRSAYZANCNFSM4NBB5PFA .

[kurtisvg]

Hi @VGerris,

This is a closed issue, so if you have a specific issue you'd like to see discussed it would be best to open a new issue with more information. However, I can answer some of your questions quickly:

why otherwise do I need a proxy? If I am in the same network I can directly connect with a client, so I don't need a proxy.

Most people are familiar with proxies from connecting to VPNs, so it's not totally uncommon to mistakenly think that proxies only provide access to new networks. Proxies can provide many different types of benefits, including things like modifying requests, authorization, improving security, and more.

In the README, the Cloud SQL proxy is described as follows:

The Cloud SQL Proxy allows a user with the appropriate permissions to connect to a Second Generation Cloud SQL database without having to deal with IP whitelisting or SSL certificates manually. It works by opening unix/tcp sockets on the local machine and proxying connections to the associated Cloud SQL instances when the sockets are used.

So it provides 2 main benefits: IAM connection-level authentication, and strong, convient encryption when connecting to a Cloud SQL instance.

When I use the option that say use cloud sdk, I need to have my project set too to have it find the database

So I'm not sure I fully understand the scenario you are describing. If you are talking about the gcloud sdk, the gcloud sql connect command uses either a temporary Authorized IP to connect, or the very same Cloud SQL proxy located in this repo (depending on the version of gcloud). Since Cloud Shell doesn't have VPC access, you won't be able to connect without a Public IP.

Hope this helps.

[ss153g]

@kurtisvg what's the solution to this problem? I'm connecting from my local machine (not google shell) and want to connect to MySQL instance with only Private IP. I'm following instructions from https://cloud.google.com/sql/docs/mysql/sql-proxy

I'm assuming the issue is with the firewall setting as described on the page:
_If you have an outbound firewall policy, make sure it allows connections to port 3307 on the target machine._

I'm using App Engine Standard - can you reference or provide more information how to establish connection? Thanks!

[kurtisvg]

@ss153g Not sure what what problem you are referring to since this one has been answered and you haven't provided specific information. Generally it's more useful to open a new issue with complete details rather than comment on one marked as closed.

I'd suggested checking out the following links:
https://cloud.google.com/sql/docs/mysql/connect-app-engine-standard
https://cloud.google.com/sql/docs/mysql/private-ip#application_environment_requirements

You stated your using GAE-Standard, but also mentioned using the proxy. For both situations, your environment (GAE or the local machine) needs to have access to the VPC. For App Engine, you need to follow the instructions on the first link. For an off-GCP machine, the process is very heavy to get access to a VCP - I would recommend just using Public IP instead.