SSL / TLS

Hi,

im working on a project where I need SSL connection to PostgreSQL.
Could you implement this functionality?

Thanks
Paul from Germany

[brianc]

Hey Paul,

SSL should be supported "out of the box" with the libpq bindings.

http://www.postgresql.org/docs/8.1/static/libpq-ssl.html

Would you try it out using require('pg').native and see if that works for you? If so, I'll try to reproduce the same functionality (with automatic SSL cert lookup, etc) within the pure javascript client. Also, an example hba.conf on how to require / force SSL would be nice so I can configure my test environment.

OR...you can send a pull request with the functionality yourself. ;)

[distracteddev]

I can confirm that switching to the native bindings solved the SSL problem for me. If you want to replicate the issue, create a free Heroku account and PSQL db with heroku and try connecting to it. If you don't use the native bindings, you get:

/Users/zeus/projects/freezing-day-6705/db.js:54
        if (err) throw err;
                 ^
error: no pg_hba.conf entry for host "xxx.xx.xxx.xx", user "xxxxxxxx", database "xxxxxxxx", SSL off
    at [object Object].<anonymous> (/Users/zeus/projects/freezing-day-6705/node_modules/pg/lib/connection.js:413:11)
    at [object Object].parseMessage (/Users/zeus/projects/freezing-day-6705/node_modules/pg/lib/connection.js:288:17)
    at Socket.<anonymous> (/Users/zeus/projects/freezing-day-6705/node_modules/pg/lib/connection.js:45:22)
    at Socket.emit (events.js:67:17)
    at TCP.onread (net.js:367:14)

[brianc]

yeah I really need to get SSL working with the pure javascript bindings.

[harbulot]

I haven't tried, but according to the documentation (and by looking at an existing SSL/TLS-enabled PostgreSQL connection made with libpq, via Wireshark), the connection looks as follows:

• Instead of starting with StartupMessage (0x00030000), start with SSLRequest (0x04D2162F).

• If the server replies with S (a single byte of value 53), it's willing to accept an SSL/TLS upgrade.

  N would mean it doesn't support it: libpq's sslmode=prefer would fall back on a normal StartupMessage here. (This being said, that mode is vulnerable to MITM attacks. If you want SSL/TLS for a particular server, but are willing to downgrade when the server responds it doesn't support it, you might as well not ask for it in the first place. If you expect the server to support it, it should, otherwise there's a MITM, or what you think the server configuration is is incorrect.)

• Straight after receiving this S, upgrade the current TCP connection to SSL/TLS by sending a TLS ClientHello message to initiate the handshake.

  In principle, this can be done using tls.connect and its socket parameter (socket: Establish secure connection on a given socket rather than creating a new socket. If this option is specified, host and port are ignored.). For behaviour similar to libpq's sslmode=verify-ca (or verify-full), use the ca parameter (required to protect against MITM attacks). To use client-certificate authentication, use the key/cert parameters.

  There should also be host name verification taking place to protect against MITM attacks. I'm not sure whether tls.connect does this by default. Doing so and using CA certificates would be equivalent to libpq's sslmode=verify-full, the only mode that fully protects against MITM attacks (unless the CA policies are very restricted, and allow to assume that all the certs it issues can be trusted not to impersonate each other).

• Upon completion of the handshake, and now using the resulting SSL/TLS connection more or less as if it was a plain socket, proceed with the PostgreSQL protocol as normal (send the StartupMessage).

(As a side-node, just wondering why you really need SSL working with the pure JS bindings when it should work out of the box via libpq, itself available on most platforms. I guess it can't harm to have it, though...)

[harbulot]

I've just added this pull request with initial support for what I was saying in my previous comment. I've tried it for a basic query, and it works. It might need further tweaking, though.

[brianc]

This works now.

[sidazhang]

@brianc, On AWS RDS, how does the automatic ssl look up work? Don't I need to provide where to look up certs??

[brianc]

If you're using the native bindings, they do a lot of the SSL stuff for you. http://www.postgresql.org/docs/9.3/static/libpq-ssl.html

If you're using the javascript version, you'll pass an ssl: config option to the client which will pass it on to the node socket.

[sidazhang]

Hey Brian,

I am using the native binding. I didn't realize they do all the cert checking etc for me. That's awesome! Thanks