How do you specify DB_URI postgres DB connection string to instance running in Google Sql cloud?

Question

Here's my scenario. I have set up an instance of Postgres DB running in the Google SQL cloud. It's up and running and if I whitelist my local IP, I can connect directly with no issue.

I then have deployed a docker container (postGrest) which is a web server that connects to the postgres DB. When I configured this on Google Cloud Run, it did have a drop-down option where I could specify DB connectivity and it says that, behind the scenes, it configures Cloud SQL Proxy for this connection.

The container allows environment variables to be passed in to specify which server, etc. One required parameter is the DB_URI to the postgred instance. When running locally, it looks like this:

postgres://authenticator:mysecretpassword@localhost:5432/testdb

When I tried to configure this on the cloud version I tried using the IP 127.0.0.1 (The google cloud SQL proxy documentation says this is how you connect via the proxy). This didn't work.

I then tried using the public-ip assigned to the postgres DB....this didn't work either.

Does anyone know how to specify the correct connection string using this DB_URI format?

Morbid

Answer 1

Cloud Run does not support connecting to Cloud SQL using IP addresses. This means 127.0.0.1 will not work. Cloud Run uses Unix Sockets. You must use a connection string.

The Cloud SQL Proxy connection string looks like this:

myprojectid:region:myinstanceid

You can get the instanceid from the Cloud SQL Instance Details page in the console.

You will also need to add permissions to your Cloud Run service account to access Cloud SQL. You will need at least Cloud SQL Client.

Darksome

Thanks, that was the issue, I found this document on postgres connection strings very helpful as well : postgresql.org/docs/9.3/libpq-connect.html – Morbid

Answer 2

I am just going to add this as an answer rather than a comment since it's easier for readability and perhaps helping other users. Please don't feel encouraged to change the accepted answer.

By following the documentation provided by the OP, the final pattern for the URI became:

# Breaking lines for improved readability

POSTGRESS_URI=postgresql:///dbname
  ?host=/cloudsql/myprojectid:region:myinstanceid
  &user=username
  &password=password
  &sslmode=disable

* dont forget to prefix the unix socket path with /cloudsql/

Any parameters can be used normally as in the example of sslmode.

Also, be aware that two important things are mentioned in the Cloud SQL documentation:

1. Note: The PostgreSQL standard requires a .s.PGSQL.5432 suffix in the socket path. Some libraries apply this suffix automatically, but others require you to specify the socket path as follows: /cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432.

In my case, the program I am using already adds as a suffix .s.PGSQL.5432, so I didn't need to add it to my URI.

2. Warning: Linux based operating systems have a maximum socket path length of 107 characters. If the total length of the path exceeds this length, you will not be able to connect with a socket from Cloud Run (fully managed).

Grappa

you are a saint. Thanks for adding this. I was pulling my hair out until I found this solution. – Koon

connecting in a scala app, here's my conn string that works: jdbc:postgresql:///my-db-name?host=/cloudsql/my-project-id:us-west1:my-postgres-instance-name (note that I provide the id/pw args via my db api and not through the conn string, and it works). – Koon

I wasn't able to connect from App Engine until I enabled sqladmin.googleapis.com – Falla

Answer 3

It seems that some postgres client libraries don't support putting user and password parameters in the URL query params. I had problems with pq for Node.js and would get the error "no PostgreSQL user name specified in startup packet".

An alternative way of writing the connection string is as follows:

Breaking lines for readability:

postgres://db_user:db_password@
    %2Fcloudsql%2Fproj_name%3Aus-central1%3Asql_instance_name
    /db_name&sslmode=disable

It's like a normal TCP connection string, but you put the path to the Unix socket as the host, encoding the / and : characters. If the first character of the hostname is /, then the hostname will be treated as a filesystem path.

Glazing