node-postgres: Native driver can't connect to UNIX socket with connection string

I think connectionString should work the same as if you pass it to psql directly; e.g. these two CLIs:

$ psql foo
$ psql "postgres:///foo"

both connect to database foo using unix socket (and peer auth). This is how pg should work, but it doesn’t work like this on Linux currently unless you set the PGHOST='/var/run/postgresql' envvar I mentioned above.

Peer auth and socket connection should always be used if no hostname is specified. If you specify no hostname but you override username/password you may get an error due to peer auth failure:

$ psql postgres://username:password@/foo
psql: FATAL:  Peer authentication failed for user "username"

If you specify your peer-auth username and absolutely any password (or no password) it still works due to peer auth overriding password:

$ psql postgres://benjie:anything@/foo
psql (11.6 (Ubuntu 11.6-1.pgdg18.04+1))
Type "help" for help.

[foo] # 

---

If you specify a hostname then it should use TCP sockets to connect. I’m not sure if localhost should be treated differently, but for me on Linux psql fails to connect:

$ psql postgres://localhost/foo
Password for user benjie: 
psql: FATAL:  password authentication failed for user "benjie"
FATAL:  password authentication failed for user "benjie"

I have a feeling this still used peer auth on Mac, but I don’t have a mac handy to test.

---

TL;DR: If a hostname is specified, use TCP. If no hostname is specified, use a domain socket.

This affects how peer authentication works when using a connection string. The connection string postgres:///dbname or simply dbname works fine with PostgreSQL on Mac; but when using it on Linux it causes the errors outlined above:

benjie$ node
> pg = require('pg')
[...snip...]
> pool = new pg.Pool({connectionString: 'pggql_test'})
[...snip...]
> pool.connect().then(console.log, console.error)
[...snip...]
{ error: password authentication failed for user "benjie"
    at Connection.parseE (/home/benjie/Dev/graphile-engine/postgraphile/node_modules/pg/lib/connection.js:604:11)
    at Connection.parseMessage (/home/benjie/Dev/graphile-engine/postgraphile/node_modules/pg/lib/connection.js:401:19)
    at Socket.<anonymous> (/home/benjie/Dev/graphile-engine/postgraphile/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:198:13)
    at Socket.EventEmitter.emit (domain.js:466:23)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 102,
  severity: 'FATAL',
  code: '28P01',
  [...snip the undefineds...]
  file: 'auth.c',
  line: '336',
  routine: 'auth_failed' }

To make peer auth work in the same way on Linux as it was previously working on Mac, I set this in my bash profile:

export PGHOST='/var/run/postgresql'

I’m guessing that a fix to this issue would be a breaking change, but maybe we could opt into the new behaviour via a flag in the v7 branch before potentially making it the default behaviour in v8? I’m open to sending a PR, with some guidance.

• the PostgreSQL source default is /tmp
• many popular distributions of libpq change it to /run/postgresql
• finding out which one libpq would use if pg-native were installed is probably too complicated (amounting to always requiring libpq)
• being able to put a connection string libpq would accept into pg and knowing it’ll do the same thing is a very nice goal, I think
• full autodetecting doesn’t sound good

How about picking a default between /run/postgresql and /tmp, and if the connection fails because the socket isn’t there, checking the other directory and suggesting it in the error message?

Error: could not connect to server: No such file or directory

Is the server running locally and accepting connections on Unix domain socket “/run/postgresql/.s.PGSQL.5432”?

Note: /tmp/.s.PGSQL.5432 exists. To connect to this socket, set the environment variable PGHOST=/tmp or add { host: “/tmp” } to pg’s configuration. <link to details on node-postgres.com>

not only listen on different ports (i.e. socket names) but also use different temp folders (/tmp vs /run/postgresql)

It would be the same port, otherwise there wouldn’t be an issue (since the socket file is named based on the port). listen_addresses would probably need to be different. This has happened to me, but containers were involved 😃

I think /run/postgresql already covers the majority of users, but you’re right, there might not be significant downsides to falling back.

Now, how do we detect which platforms should use it as a default…? And/or should we also fall back to localhost TCP after all the socket directories have been checked?

That’s certainly a factor, @charmander. I’m glad you’re thinking about these things! I feel like having two different instances of PostgreSQL that not only listen on different ports (i.e. socket names) but also use different temp folders (/tmp vs /run/postgresql) is quite unlikely, as you say, but I can’t really put quantitative values on that unlikeliness. That said, I think auto-detecting for the majority of users beats making everyone learn the intricacies of where libpq puts things; and if you have multiple postgresql versions installed on your machine and they use different locations you’re probably already better equipped to debug this issue since you’re presumably already a very capable user to have gotten into this situation in the first place?

I’m definitely open to a PR for this. I’m not sure exactly the fix y’all are looking for after reading the comments. Does it work as expected w/ the pure JS implementation? Fwiw I never use a connection string to connect - always environment variables splatted into an actual config object passed to a pool/client constructor so I haven’t run into this myself.

@charmander Ok, but I spotted differences in behavior of pg module and psql without args, on Debian… but does not matter, libpq behaves same way as psql