Support connection to Postgres UNIX socket

[jtdaugherty]

I'd like to connect postgrest to my database server via a UNIX socket. I don't see a way to do this with the current command-line usage output. I could imagine accepting a Postgres connection string as in

http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-CONNSTRING

What do you think?

Thanks!

[begriffs]

We could perhaps add a new parameter for the full uri, and if that param is set then disregard the other ways of setting the host, port etc. Seems like that might be a confusing usage. Can you explain the reasons for running postgresql on a unix socket specifically?

[jtdaugherty]

Yeah, supporting both a URI and individual settings might be confusing. Another approach would be to support only URIs; a benefit of that approach is that postgrest would support more connection use cases while using a standard (to Postgres) connection specification. That also has the benefit that all the same defaulting semantics that apply to connection strings (e.g. "no database name" = "default to $USER") also apply to postgrest, making it integrate nicely alongside other tools that rely on those assumptions.

As for reasons for using a Unix socket, would it be sufficient to say that Postgres supports connecting on Unix sockets (say), so I'd like tools that talk to Postgres to support those same connection modalities? Regardless of why I'm doing it this way, I can imagine someone might have reasons to connect to Postgres in all sorts of ways using all sorts of authentication policies, and I don't want postgrest to make those policy decisions (or restrict them).

For example, one use case is testing: my testing setup needs to work on the same host for multiple users, so we make the simplifying assumption that every user has a database and Postgres role named $USER so that a connection string postgresql:/// just defaults to the database and role name equal to the user account running the tests, and that the connection occurs on the local unix socket. This also utilizes "trust" authentication since we don't want to have to manage credentials just to run the tests. This all makes it easy to have a test suite that doesn't have to change depending on who is running it.

[accelas]

UNIX domain socket can be significantly faster than TCP loopback socket. See discussion on stackoverflow.com

http://stackoverflow.com/questions/14973942/performance-tcp-loopback-connection-vs-unix-domain-socket

[diogob]

@jtdaugherty you can connect postgrest using an UNIX domain socket using the socket directory as a parameter to the --db-host parameter. Like:

postgrest -d postgres -U postgres -a postgres --db-host /tmp/

[begriffs]

@jtdaugherty did this resolve your problem?

[jtdaugherty]

@begriffs - I'm not using postgrest at the moment, but if I did need to use it then the above solution would likely work. I still think there's the bigger issue of connection settings symmetry between postgrest and Postgres tools, but this is probably a sufficient solution until that gets sorted out. Thanks!

[eric-brechemier]

I have successfully connected postgrest to PostgreSQL through its Unix socket, by setting a blank host value instead of localhost in the connection URI:

# Unix Socket Connection
postgrest postgres://user:pass@/db [flags]

instead of

# TCP/IP Connection
postgrest postgres://user:pass@localhost:port/db [flags]

Reference: http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42494

The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket connection is chosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is initiated. (...)

I can now take advantage of the peer authentication method to connect safely without a password when running postgrest as a Unix user with the same name as the database user:

# Unix Socket Connection Without a Password
sudo -u user postgrest postgres://user@/db [flags]

[begriffs]

That's clever, nice work. Care to send a PR for the docs to mention it?

[eric-brechemier]

@begriffs no problem, I'll expand the section 'Running the Server'.