Can't connect to PostgreSQL instance proxied through GCP's Cloud SQL Proxy

sjaq commented 3 years ago

What did you do?

I'm running a Cloud SQL Proxy to connect to my GCP hosted PostgreSQL instance with IAM credentials instead of using the internal user/password authentication system (which is the preferred way when working with GCP)
Locally I can connect to this instance using psql "sslmode=disable hostaddr=127.0.0.1 dbname=[dbname] user=[username]", explicitly disabling SSL as it is terminated by Cloud SQL Proxy itself
I'm trying to set up a connection with Postico using the same hostname, database and user

What did you expect to happen?

I expected to see an warning that a connection cannot be established using SSL asking me to connect without SSL instead as I've read that Postico switches to sslmode=prefer when connecting to local instances

What actually happened?

The app hangs on "Negotiating SSL…" and finally gives the following message:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

What seems to be the issue?

Cloud SQL Proxy does not handle connections that try to use SSL properly it seems, as using psql with sslmode=prefer hangs in a similar way as Postico
It would however be resolved quite easiliy by allowing a connection to explicity disable SSL (which a lot of other PostgreSQL clients allow, and therefore work perfectly fine with this setup)

What software versions are you using?

Postico version: 1.5.17 macOS version: 10.15.7 PostgreSQL version: PostgreSQL 13.2 on x86_64-pc-linux-gnu

declanwilson-lw commented 1 year ago

1 workaround:

host: localhost database: <your-db> username: gcloud config get account password: gcloud auth print-access-token

max-winderbaum commented 1 year ago

I'd also love the ability to explicitly disable SSL - this is super frustrating for me

eriknolte commented 1 month ago

Another way to do this is with a Pre-Connect Shell Script that starts the cloud_sql_proxy in the background. Unfortunately it doesn't shutdown the proxy when you disconnect from the database.

Postico 2 database configuration:

Host: localhost
Port: 5432
Database: <database>
User: <user>
Password: <password>
Pre-Connect Shell Script:
    GOOGLE_APPLICATION_CREDENTIALS="$HOME/.credentials/<project>.json"
    export GOOGLE_APPLICATION_CREDENTIALS
    <path-to-google-cloud-tools>/cloud_sql_proxy -instances=<project>:<region>:<database>=tcp:5432 &

It would be nice if Postico had a Post-Connect or Shutdown Shell Script option that could kill cloud_sql_proxy.

jakob / Postico