PostgREST on Google Cloud SQL: unix socket URI format?

Question

Any of you with experience with PostgREST and Cloud SQL ?

I have my SQL instance ready with open access (0.0.0.0/0) and I can access it with local PostGREST using the Cloud proxy app.

Now I want to run Postgrest from an instance of the same project but I can't find an URI format for Postgrest that supports Cloud SQL format, as Google SQL Cloud uses only unix sockets like /cloudsql/INSTANCE_CONNECTION_NAME

Config 1

db-uri = "postgres://postgres:password@/unix(/cloudsql/INSTANCE_CONNECTION_NAME)/mydatabase"
db-schema = "api"
jwt-secret = "OOcJ7VoSY1mXqod4MKtb9WCCwt9erJkRQ2tzYmLb4Xe="
db-anon-role = "web_anon"
server-port=3000

Returns {"details":"could not translate host name \"unix(\" to address: Unknown host\n","code":"","message":"Database connection error"}

Config 2

db-uri = "postgres://postgres:password@/mydatabase?unix_socket=/cloudsql/INSTANCE_CONNECTION_NAME"
db-schema = "api"
jwt-secret = "OOcJ7VoSY1mXqod4MKtb9WCCwt9erJkRQ2tzYmLb4Xe="
db-anon-role = "web_anon"
server-port=3000

The parser rejects the question mark {"details":"invalid URI query parameter: \"unix_socket\"\n","code":"","message":"Database connection error"}

Config 3

db-uri = "postgres://postgres:password@/mydatabase"
db-schema = "api"
jwt-secret = "OOcJ7VoSY1mXqod4MKtb9WCCwt9erJkRQ2tzYmLb4Xe="
db-anon-role = "web_anon"
server-port=3000
server-unix-socket= "/cloudsql/INSTANCE_CONNECTION_NAME"

server-unix-socket appears to only take socket lock file path. Feeding it /cloudsql/INSTANCE_CONNECTION_NAME tries to delete file as in `postgrest.exe: /cloudsql/INSTANCE_CONNECTION_NAME: DeleteFile "/cloudsql/INSTANCE_CONNECTION_NAME": invalid argument t (The filename, directory name, or volume label syntax is incorrect.)

Documentation

Cloud SQL Doc

• https://cloud.google.com/sql/docs/mysql/connect-run

PostgREST

• http://postgrest.org/en/v6.0/configuration.html
• https://github.com/PostgREST/postgrest/issues/1186
• https://github.com/PostgREST/postgrest/issues/169

Environment

• PostgreSQL version:11
• PostgREST version: 6.0.2
• Operating system: Win10 and Alpine

Ladd

Did you tried:postgres//<db_user>:<db_pass>@/<db_name>?unix_sock=/cloudsql/<cloud_sql_instance_name>/.s.PGSQL.5432, or maybe host instead of unix_sock – Mablemabry

Try it like: db-uri = postgres:///user@/dbname(no password). Also, server-unix-socket is only a webserver setting, not a pg connection setting. I suggest trying the connection string with psql <db-uri> first. If the db-uri is right, it will also work with PostgREST. – Clientage

did you get this working @j-dumont? I tried steve chavez approach, but that defaults to /var/postgresql/.s.PGSQL.5432 but I can't find a way to customise the socket location as the location is hardcoded by cloudrun. – Dawkins

Answer 1

First you have to add the Cloud SQL connection to the Cloud Run instance: https://cloud.google.com/sql/docs/postgres/connect-run#configuring

After that, the DB connection will be available in the service on a Unix domain socket at path /cloudsql/<cloud_sql_instance_connection_name> and you can set the PGRST_DB_URI environment variable to reflect that.

Here's the correct format: postgres://<pg_user>:<pg_pass>@/<db_name>?host=/cloudsql/<cloud_sql_instance_connection_name>

e.g. postgres://postgres:postgres@/postgres?host=/cloudsql/project-id:zone-id-1:sql-instance

Roofdeck

I think that would double the costs – Lyudmila

@Lyudmila Why is that? – Roofdeck

I think it was little misunderstanding, I thought that your solution is to run external cloud run instance that will do the proxy! Folder /cloudsql is generated in cloud run and cloud function so no need to add instances! My fault! – Lyudmila

Answer 2

According with Connecting with CloudSQL, the example is:

# postgres+pg8000://<db_user>:<db_pass>@/<db_name>?unix_sock=/cloudsql//.s.PGSQL.5432

Then you can try with (Just as @marian.vladoi mentioned):

db-uri = "postgres://postgres:password@/mydatabase?unix_socket=/cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432"

Keep in mind that the connection name should include:

ProjectID:Region:DatabaseName

For example: myproject:myregion:myinstance

Anyway, you can find here more options to connect from external applications and from within Google Cloud.

Boohoo

Bro thanks! it works for me. Take in account that if password contains special caracters you need to scape december.com/html/spec/esccodes.html – Heartthrob

Answer 3

I tried many variations but couldn't get it to work out of the box, however I'll post this workaround.

FWIW I was able to use an alternate socket location with postgrest locally, but then when trying to use the cloudsql location it doesn't seem to interpret it right - perhaps the colons in the socket path are throwing it off?

In any case as @Steve_Chávez mentions, this approach does work db-uri = postgres:///user:password@/dbname and defaults to the postgrest default socket location (/run/postgresql/.s.PGSQL.5432). So in the docker entrypoint we can symlink this location to the actual socket injected by Cloud Run.

First, add the following to the Dockerfile (above USER 1000):

RUN mkdir -p /run/postgresql/ && chown postgrest:postgrest /run/postgresql/

Then add an executable file at /etc/entrypoint.bash containing:

set -eEux pipefail

CLOUDSQL_INSTANCE_NAME=${CLOUDSQL_INSTANCE_NAME:-PROJECT_REGION_INSTANCE_NAME}
POSTGRES_SOCKET_LOCATION=/run/postgresql

ln -s /cloudsql/${CLOUDSQL_INSTANCE_NAME}/.s.PGSQL.5432 ${POSTGRES_SOCKET_LOCATION}/.s.PGSQL.5432 
postgrest /etc/postgrest.conf

Change the Dockefile entrypoint to CMD /etc/entrypoint.sh. Then add CLOUDSQL_INSTANCE_NAME as an env var in cloud run. The PGRST_DB_URI env var is like so postgres://authenticator:password@/postgres

An alternative approach if you don't like this, would be to connect via serverless vpc connector.

Dawkins

Answer 4

I struggled with this too.

I end up doing a one-liner for DB-URI env variable

host=/cloudsql/project-id:zone:instance-id user=user port=5432 dbname=dbname password=password

However, I have postgrest running on cloud run that lets you specify the instance connection name via

INSTANCE_CONNECTION_NAME=/cloudsql/project-id:zone:instance-id

Maybe you can host it there and you end up doing it serverless Im not sure where are you running it currently.

https://cloud.google.com/sql/docs/mysql/connect-run

Customs