Support Unix Domain Sockets for Postgres

[mehcode]

//cc @xcthulhu

[bikeshedder]

I just wanted to take sqlx for a test ride and found this feature missing. Is there any chance for a release including this feature soon? I almost exclusively use databases with UDS.

[dcardamo]

Likewise I would like this feature too. On GCP Cloud Run you can only connect to postgres using their cloud sql proxy which provides a UDS method:
https://cloud.google.com/sql/docs/postgres/connect-run

[mehcode]

We'll get a patch release out in the next couple days. A couple things that landed on master require a bit of testing before we can push a build out.

[dylanjt]

@mehcode I am also attempting to deploy my app to GCP Cloud Run and hitting a roadblock trying to connect via UDS.

[mehcode]

@dylanjt 0.3.5 should support UDS. What issue are you having?

[dcardamo]

Its been a few weeks since I've looked at it. I couldn't find an URL format that it would allow connecting with last time and couldn't find anything documented in the sqlx repo.

I read through the code such as sqlx-core/src/url.rs where there are some tests for connecting but none UDS. Also read through sqlx-core/src/postgres/connection.rs and there are some good comments there but none applicable to UDS.

My plan was to look into this again when 0.4.0 came out since I saw there were some changes in master that may improve this. I remember seeing some API where you can skip an URL and just provide the various field values for the connection.

It would be awesome if it were documented or there was a test case for UDS. Also very possible I missed it.

Once this works I'd be happy to add documentation on how to get this working in GCP cloud run.

[mehcode]

Its been a few weeks since I've looked at it. I couldn't find an URL format that it would allow connecting with last time and couldn't find anything documented in the sqlx repo.

Sorry for the lack of docs on it. It's actually just as specified in libpq (or it should be).

See: https://www.postgresql.org/docs/9.3/libpq-connect.html#AEN39692

postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

Definitely not pretty though. You're correct. On master we have PgConnectOptions that is a builder for a connection that has a simple .socket("/var/lib/postgresql") which will have it use UDS.

---

It's a bit complicated to test this in CI. I haven't had the time to figure it out.

[bikeshedder]

I personally prefer the key word syntax as it doesn't need any %2F escaping and looks way more sane:

postgresql:///dbname?host=/run/postgresql

Btw. two major linux distributions (Ubuntu and Fedora) nowadays put the UDS under /run/postgresql instead of /tmp. I've never seen the socket under /var/lib/postgresql to be honest.

[dylanjt]

@mehcode got it, I'm starting to think this is a GCP Cloud Run issue and not related to sqlx. I hope you don't mind if I put my current status here regardless to rule out sqlx; I'm pulling my hair out on this.

I was able to make this format to respond, as in I got an error about incorrect username/password:

postgresql:///DB_NAME?host=/cloudsql/GCP_CONNECTION_NAME

So when I add the username:password parameters,

postgresql://DB_USERNAME:DB_PASSWORD@/DB_NAME?host=/cloudsql/GCP_CONNECTION_NAME

I get an error UrlParse(EmptyHost). So I add localhost to try and appease (based on a comment I saw in a PR for this feature):

postgresql://DB_USERNAME:DB_PASSWORD@localhost/DB_NAME?host=/cloudsql/GCP_CONNECTION_NAME

And I get a new error (this one takes some time to fail): PoolTimedOut(None)

I have also tried manually appending /.s.PGSQL.5432 to the end of all the above.

[dcardamo]

I spent a bit of time and wasn't able to get it to work with cloud run 'cloud sql proxy'. I did get further using the percent encode where I encoded ':' in the path (which is part of the google postgres connection instance name) with %3A. But it just timed out.

Good news though, google now offers VPC for cloud run. So you can setup private IP access to POSTGRES and then serverlesss VPC connectors as documented here: https://cloud.google.com/sql/docs/postgres/connect-run. Then you can tell sqlx to connect using a standard port 5432 TCP connection.

I'm successfully running on google cloud run with sqlx this way.

[bikeshedder]

@dylanjt Why didn't you use key words all the way?

postgresql:///DB_NAME?host=/cloudsql/GCP_CONNECTION_NAME&user=DB_USERNAME&password=DB_PASSWORD

Everything can be encoded as parameter key words:

You can even put the dbname as key word in the URL:

postgresql:///?dbname=DB_NAME&host=/cloudsql/GCP_CONNECTION_NAME&user=DB_USERNAME&password=DB_PASSWORD

[mehcode]

Can you open a new issue about "GCP Cloud Run" ? If you can connect via psql but not SQLx it is something we should try and figure out what's going on and attempt to fix.

[dylanjt]

@bikeshedder oddly enough this resulted in an authenticate error from PG: incorrect password for user postgres error; odd because &user= specified a different username, but at least is looks like it connected to something. I tried both of the formats you showed and double / sanity-checked a few times.

I ended up taking the private IP route @dcardamo. This honestly feels cleaner, is more familiar to me, and was quick to setup. It certainly is a viable option in probably most GCP Cloud Run scenarios.

@mehcode I would love to do this, I'm new to Rust but am diving in and am eager to contribute especially to awesome libraries like this that I actively use and follow. Once I stabilize my setup some more, I will loop back and dive into this issue again.

[dakom]

(deleted comment here, moved to new issue: #500)

[juroberttyb]

As a reference for anyone who stumbles upon this, as suggested by @dakom in #500, one solution could be the use of PgConnectOptions.

This successfully connects a Cloud Run instance to Cloud SQL using a public IP in my case.

let opts = PgConnectOptions::new()
    .host("127.0.0.1")
    .port(5432)
    .username(USER_NAME)
    .password(DB_PASSWORD)
    .database(DB_NAME)
    .socket(INSTANCE_CONNECTION_NAME);

let pool = PgPool::connect_with(opts).await?;