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

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

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.