postgresql unix socket

[ctxnop]

I'm trying to use unix socket with postgresql without any success. The libpq documentation says that if I provide an host starting with a / unix sockets are used.

My unix socket file for postgresql is /var/run/postgresql/.s.PGSQL.5432, so here is my database.yml

development:
  dialect: postgres
  database: mydatabase
  user: myuser
  password: none
  host: '/var/run/postgresql/.s.PGSQL.5432'

But here is the result:

$ buffalo db drop
v4.11.2

[POP] 2019/07/17 12:11:36 info - drop mydatabase (postgres://myuser:none@/var/run/postgresql/.s.PGSQL:5432/mydatabase?sslmode=disable)
couldn't drop database mydatabase: error dropping PostgreSQL database mydatabase: pq: no pg_hba.conf entry for host "::1", user "myuser", database "var/run/postgresql/.s.PGSQL.5432:5432/postgres", SSL off

As you can see, it tries to use IPv6 connection instead of the unix socket connection. Also the detected database name is garbage.

I tried some path for host omitting the port (.s.PGSQL), the complete filename (keeping only the directory) and even prefixing with "unix:/" (which end up with a lookup failure for the domain 'unix', which is understandable).

[ctxnop]

I tracked the issue through layers and find that the issue is on both sides of pop and pq.

On the pq side, they use the official go url parser (/usr/lib/go/src/net/url/url.go), which fails to correctly parse the URL. When it see the host starting with a /, it assume that there is no specified host and consider it as the resource path.

Nothing surprising here, this is exactly why the postgresql documentation state that you can escape the host using %2F instead of /. Unfortunately, pq does not support as of today: https://github.com/lib/pq/issues/796

To bypass the issue, you have to use either the connection string syntax (host=/var/run/postgresql user=user password=password port=5432 dbname=database - See: https://godoc.org/github.com/lib/pq) Or to specify the host as a query option (postgresl://user:password@:5432/database?host=/var/run/postgres).

And it's here where the issue come back on the pop side: the URL given to pq is not the one from the database.yml, it's generated. So you can't work around the issue by tweaking yourself the URL.

I patched the urlWithoutDb function inside dialect_postgresql.go:

func (p *postgresql) urlWithoutDb() string {
    c := p.ConnectionDetails
    // https://github.com/gobuffalo/buffalo/issues/836
    // If the db is not precised, postgresql takes the username as the database to connect on.
    // To avoid a connection problem if the user db is not here, we use the default "postgres"
    // db, just like the other client tools do.
    s := "postgres://%s:%s@:%s/postgres?host=%s&%s"
    return fmt.Sprintf(s, c.User, c.Password, c.Port, c.Host, c.OptionsString(""))
}

And now it works fine if my database.yml is this:

development:
  url: 'postgresql://none@:5432/mydatabase?host=/var/run/postgresql'

Still not working the classic yaml definition.

[stop5]

Postgresql understand also an other format that circumvent the problem with Unixsockets:

---
development:
    dialect: postgresql
    url: 'host=/var/run/postgresql database=DATABASE'

Both host and database need to be specified to be recognized by pq. The post is irrelevant, but i haven't tested multiple PostgreSQL-Servers on one machine.

[ctxnop]

If you read carefully my second post you'll see that I already stated that this other format exist, but does not work because pop parse the connection string and rebuild it, using the url format which cause problem.

[bscott]

@stanislas-m @markbates Was this fixed?, Google Cloud Run only supports Unix Sockets to connect to Cloud SQL Postgres. Been trying to figure out the proper connection string to pass to DATABASE_URL.

https://cloud.google.com/sql/docs/postgres/connect-run?hl=en_US https://golangtesting.com/posts/gobuffalo-app-engine

[vrubiolo]

Just a note to say that I am interested in the answer too. Thanks for the update!

[stanislas-m]

I'm not sure @bscott. I'll try to check this week-end.

[bscott]

Thank you!

[cameron-martin]

@ctxnop can this change be sent as a PR?

[zepatrik]

Just for anyone looking into this, the postgres driver now is jackc/pgx, so previous findings and workarounds might not hold anymore.

[ctxnop]

I'm not working with golang anymore, feel free to do whatever you want with my proposal if it still applies.

[aleksandrhorkavyi]

It could help

.env

DATABASE_URL="user=postgres password=pass dbname=some_name host=/cloudsql/project-id:europe-west1:production-instance"

database.yml example file

production:
   dialect: postgres
   url: {{envOr "DATABASE_URL" ""}}