Hey harbulot I really appreciate the pull request. Is it possible you provide a few test cases? I'm really reticent to accept anything without corresponding tests. TBH the main reason I have yet to implement this feature is because I initially had a hard time getting my postgreSQL server installed with ssl support and so testing for me was impossible.

Even some unit tests mocking out the streams would be a good start. If you can't, the 2nd best option would be maybe point me in the right direction for some instructions on how to configure SSL w/ postgres locally. I'll get my env set-up and add the tests. I was thinking of making the tests non-fail if they don't work so this will still run on travis-ci. I should check w/ those guys and see if they have support for SSL on their test db.

If you can't provide tests, I'll still use your implementation is a close reference. I'll implement it over again in a more test-first style, and maybe merge in your pull after the tests I write are in place.

To be honest, I'm not really familiar with Node.js, I'm not sure how to write a good test case correctly.

I tried my patch using this against a server that was already configured:

    var client = new pg.Client({
        host: 'the.host.name',
        user: 'username',
        password: 'ThePassword',
        database: 'db_name',
        ssl: true
    });
    client.connect();
    client.query("SELECT COUNT(*) FROM my_table");
    query.on('row', function(row) {
      console.log(row);
    });
    query.on('end', function() { 
      client.end();
    });

Presumably, a test-case will not involve a certificate issued by a major CA: it will need to come from a test CA or to be a self-signed certificate. For this, my patch needs a few modifications (see below).

Configuring the PostgreSQL server

Generating a test self-signed certificate

Assuming that you don't already have a test certificate, one can be generated with OpenSSL as follows:

openssl req -new -newkey rsa:2048 -keyout localhost.key -subj '/CN=localhost' -out localhost.csr
openssl rsa -in localhost.key -out localhost-nopasswd.key
openssl x509 -req -days 365 -in localhost.csr -signkey localhost-nopasswd.key -out localhost.crt

(For a proper certificate, it would be better to put the host name in the Subject Alternative Name, but for a test, this should be enough. Note that IP addresses, if used instead of a host name, must be in a Subject Alternative Name entry of IP address type, not DNS type. More on SAN generation with OpenSSL on this page: http://www.crsr.net/Notes/SSL.html)

Installing the certificate on the PosgreSQL server

In your postgresql.conf file (e.g. /etc/postgresql/8.4/main/postgresql.conf), set ssl = true. Take note of the data_directory used (e.g. /var/lib/postgresql/8.4/main).

Copy localhost.crt as server.crt in that data directory (not the main configuration directory). Copy localhost-nopasswd.key as server.key in that directory too.

Configuring the PostgreSQL users

In pg_hba.conf (where user access is normally configured), add this line, before others that would also match similar conditions:

hostssl all         test1       127.0.0.1/32        md5

(Here, test1 is the name of the test user. Of course, you can also do all this using a different source IP address and another host name than localhost if you have a remote server instead.)

You'll need to restart the server.

Just to test that you've configured it correctly using psql, add the content of localhost.crt to $HOME/.postgresql/root.crt from a test user account (only keep it for the duration of the test, though), then, try to connect using PGSSLMODE=verify-full psql -h the.host.name -U theusername.

Configuring the client with test CA certificates

The patch I've done isn't sufficient for this. I wasn't quite sure what the best way to do it was (w.r.t the rest of the API). It should be quite easy to fix, though. Essentially, a ca parameter needs to be passed in addition to the other parameters on this line (line 46 of lib / connection.js after my patch):

self.stream = tls.connect({ socket: self.stream, servername: host, rejectUnauthorized: true });

Assuming we can load the CA certificate using this:

var fs = require('fs');
var certificate = fs.readFileSync('/path/to/ca-certificate.pem', 'ascii');

Following the previous configuration steps, /path/to/localhost.crt should be used instead of /path/to/ca-certificate.pem for this test.

The idea would be to pass the content of this certificate variable somehow within this function to use:

self.stream = tls.connect({
    socket: self.stream,
    servername: host,
    rejectUnauthorized: true,
    ca: [ certificate ]
});

I would also be useful to be able to pass other parameters. Perhaps an extra optional argument to Connection.prototype.connect would be the right place:

p.connect = function(port, host, tlsParams) { ... }

(You'll also need a recent version of Node.js, by the way.)

+1 it's really important to get this stuff to work imho

I don't think this can be added into the automated test suite since travis-ci doesn't support ssl certs. I'm going to merge this in now and release a new version of node-postgres with ssl support in the native bindings. I'll circle back around to this pull request a few times in the future and get some sort of tests (at least manual ones) working locally.

This is merged and available in 0.8.3. Leaving this pull request open for more discussion.

I don't think this can be added into the automated test suite since travis-ci doesn't support ssl certs.

What is meant by this? Surely travis can run a script to install a pre-generated one if needed.

Hmm I suppose I could look at having the test suite configure the travis postgreSQL instance with the appropriate certs. That is assuming the server allows write access to the database data directory.

That is assuming the server allows write access to the database data directory.

Travis supports passwordless sudo so you can pretty much do just about anything

it doesn't work with Heroku, throws Error: DEPTH_ZERO_SELF_SIGNED_CERT

Also getting the DEPTH_ZERO_SELF_SIGNED_CERT on Heroku. Changing hard coded rejectUnauthorized: true to false fixes the issue. We just need more parameters into tls.connect. I'll try to do it if I get some time, but will post here first before starting in case someone wants to tackle this earlier.

I believe this all works as expected now - closing as fixed.