unix socket

[brianc]

better support & documentation for connecting to PostgreSQL server via unix socket

[brianc]

I'm not really experienced w/ connecting to PostgreSQL w/ a domain socket. Been playing around with it tonight but unable to come up w/ a working solution. Anyone found the need to connect via a unix domain socket using node-postgres? If so, what did you do to get it working or what is holding you back currently? I need some examples...

[brianc]

gonna move the message discussion into the bug discussion:

from messages

brianc

Hello -

Saw you commented on the bug, but then I guess you deleted it? Also, saw I missed you in the #node.js room.

I'm not sure this works because I haven't written tests using unix sockets yet, but you can try this:

var pg = require('pg');
pg.defaults.user = 'postgres';
pg.defaults.password = '1234';
pg.defaults.port = '/postgres'; //unix domain socket instead of tcp port
pg.defaults.host = undefined;  //null out the host
pg.defaults.database = 'postgres';

pg.connect('pg://', Æ(err, client) {
  console.log(err);
  pg.end();
})

Please let me know

does this work for you?
thoughts on what the connection string should look like for unix domain socket connections
I will test this out later tonight, combine your feedback, put it in the API, and publish a new version on npm

thanks,
Brian

[brianc]

Hi!

Cool, your example works if I set port to the exact path of the socket file: pg.defaults.port='/tmp/.s.PGSQL.5432'

The directory with the socket is configured in postgres.conf as unix_socket_directory. When it's blank it means /tmp - this is the default on Arch Linux for example. On Debian the default is /var/run/postgresql. The file is usually .s.PGSQL.5432 - the suffix probably is changed when one changes the tcp port.

IMO this path could be entered explicitly in connect string - this seems a bit arcane use case so one could just do a little investigation where his socket is, we shouldn't guess it.

Quick reply to your comment why to use unix socket at all: one may encounter a fascist configuration where you can connect to the db only via such socket, no tcp no fun. I know some production databases like this myself. Not very popular setting as eg. JDBC can't connect to it, but not every db is deployed for Java stack, and well - we can do better than that :)

Btw there's some myth that unix sockets can be faster due to less OS context switches, it may be so, frankly I don't care. I just need to deal with such databases occasionally and would love to use Node with it.

Now it works and I'm happy with it as it is, but if you decide it's worth to extend your connect string syntax then I'd be happy even more :D

So how could it be hmm

pg://user@/path/to/socketfile/database
pg:///path/to/socketfile/database

So if the hostname starts with slash it is a full path with other slashes, the last portion being the database name

Sth about this seems suck, maybe too complicated?

What do you think?

[charlesreilly]

Unix sockets are nice because you can connect without a password, so you don't need passwords in your code or config files.

If you:

1. Create a database user with the same name as your unix user.
2. Create a database for that user with the same name as the user name.

then when you run "psql" it automatically logs in to this database without having to specify any connection parameters.

If you run:

psql -c "\set"

it displays its variables, specifically:

DBNAME = 'username'
USER = 'username'
HOST = '/var/run/postgresql'
PORT = '5432'

It would avoid confusion if node-postgres used the same syntax as psql, which would give:

pg.defaults.user = process.env.USER;
pg.defaults.password = null;
pg.defaults.port = '5432';
pg.defaults.host = '/var/run/postgresql';
pg.defaults.database = process.env.USER;

The connection code would distinguish between unix and tcp sockets by looking at the host (it's unix if it starts with a '/') and automatically construct the socket path ('/var/run/postgresql/.s.PGSQL.5432') from the host and port.

Of course, none of this is essential. This works fine now:

pg.defaults.user = process.env.USER;
pg.defaults.password = undefined;
pg.defaults.port = '/var/run/postgresql/.s.PGSQL.5432';
pg.defaults.host = undefined;
pg.defaults.database = process.env.USER;

[brianc]

I've put in a bit support for connecting to unix domain sockets.

Now you can do the following:

pg.connect("/tmp", function() ...

I think connecting via domain sockets by default uses the 'ident' authentication which uses the identity of the requesting process. You can still configure domain sockets + md5 authentication which would result in needing to pass more info than just the socket folder to the connect method. For this you can either set pg.defaults with the right user, password, and database or you can connect with a config object:

var config = { 
  host: "/tmp",
  port: 3333, //5432 is the default
  user: 'whatever',
  password: 'secret',
  database: 'my_db'
};
pg.connect(config, function() { ...

Note now you specify host to be the domain socket folder. I'm trying to have node-postgres follow the libpq convention of building your socket file location from the host + port values as charlesreilly mentioned.

Also, per charlesreilly's recommendation I've set the pg.defaults to be more in line with libpq defaults, and updated the wiki docs.

If you could, please look over the changes I've made. I'll bump the npm version in a few days after y'all have had a chance to look this over. I don't think changing pg.defaults breaks the api, but I wanna wait a bit just to make sure.

[wkhere]

@brianc Glad you implemented unix sockets! Will test it with spooky configurations i told you about, not instantly though, but i should do it in several days.

@charlesreilly To be exact you can decouple ident-based auth from using an unix socket. My most used postgres modus operandi is to connect to pg server via tcp from the clients on the same machine, using ident and without passwords.
Of course having direct unix socket support is also cool, but this & ident auth are different things.

[brianc]

implemented a while ago

[brianc]

implemented a while ago

[soletan]

Obviously, this currently isn't working when using native client. Using "/var/run/postgresql" in host or as a string on connecting/instantiating client the string is looked up in DNS due to being considered hostname. Next I tried undefining host and assigning socket's full pathname in port, but that's rejected due to checking portnumber for being sequence of digits.

EDIT: Tested on Ubuntu 12.04 LTS, installed as described using PPA & npm, running nodejs 0.8.8.

[soletan]

Issue is due to different processing of configuration in JS code providing native binding as it's using utils.buildLibpqConnectionString(). This method in turn is doing DNS lookups without detecting UNIX socket pathnames first.

Here is a workaround for that method in utils.js:

var getLibpgConString = function(config, callback) {
  if(typeof config == 'object') {
    var params = []
    add(params, config, 'user');
    add(params, config, 'password');
    add(params, config, 'port');
    if(config.database) {
      params.push("dbname='" + config.database + "'");
    }
    if(config.host) {
      if (!config.host.indexOf("/")) {
        params.push("host=" + config.host);
      } else {
        if(config.host != 'localhost' && config.host != '127.0.0.1') {
          //do dns lookup
          return require('dns').lookup(config.host, 4, function(err, address) {
            if(err) return callback(err, null);
            params.push("hostaddr="+address)
            callback(null, params.join(" "))
          })
        }
        params.push("hostaddr=127.0.0.1 ");
      }
    }
    callback(null, params.join(" "));
  } else {
    throw new Error("Unrecognized config type for connection");
  }
}

[luto]

The documentation still says that this is to be implemented.

[asakaev]

How I can use unix domain sockets instead of TCP to connect now?

[Lekensteyn]

@tematema Use a connection object and specify the directory to the Unix domain sockets. See above examples and the note at https://github.com/brianc/node-postgres/wiki/pg#pgdefaultshost

(just updated both pg and client wikis, these were at least one year out of date wrt host)

[jasonpolites]

The wiki has changed since the last comment here was posted, and the new docs don't seem to make any mention of using a socket path. Is there an updated reference somewhere?

[charmander]

@jasonpolites It appears to be missing from the new documentation. The option is host, anyway:

const pool = new pg.Pool({
    host: '/var/run/postgresql',
});

[jasonpolites]

Ack. Thanks!