pgjdbc/pgjdbc — pull request #80 by sehrope (2013-08-23 18:58:27+00:00)
Title: Add SSL factory with remote certificate validation

This patch has two commits:

1. A new SSL socket factory (SingleCertValidationFactory)
2. A Vagrant VM and Maven project for testing the new socket factory

I've kept the commits for the two separate as the test VM could/should be cleaned up to be used for the PG-JDBC project as a whole (rather than testing this one feature). It'd make it a lot easy for new folks to work on the driver as well. It's even possible for multiple servers to be installed (or multiple VMs spun up) to allow for multiple versions of PG to test against.

We got the idea for it working on our product [JackDB](http://www.jackdb.com/) as the primary use case for our cloud version is to connect to cloud databases (ex: Heroku Postgres or Amazon RDS). Although they support connecting over SSL, the common advice online is ignore the authentication piece of the SSL handshake by using the NonValidatingFactory (which makes you vulnerable to a man in the middle attack). We looked at other options as well and none was straightforward enough, particularly for our use case of dynamically adding data sources at runtime.

This new SSL socket factory `org.postgresql.ssl.SingleCertValidatingFactory` lets you specify the expected server certificate in a number of ways. It's designed to make it as easy as possible to securely connect to a remote PostgreSQL server.

Where the certificate is loaded from is determined by the constructor arg's prefix. The following prefixes are supported:
* `file:/foo/bar/server.crt` - a file on the local filesystem
* `classpath:foo/bar/server.crt` - a resource on the local classpath
* `env:SERVER_SSL_CERT` - the String value of an environment variable accessed via System.getenv(...)
* `sys:server.ssl.cert` - the String value of a Java system property accessed via System.getProperty(...)
* `-----BEGIN CERTIFICATE-----` - the String value of the certificate itself. This let's you specify the certificate as a connection parameter at runtime without fiddling with your local environment or filesystem.

Here's sample usage of the new SSL factory:

```java
String url = "jdbc:postgresql://db.example.com:5432/mydb"
Properties info = new Properties();
info.setProperty("ssl", "true");
info.setProperty("sslfactory","org.postgresql.ssl.SingleCertValidatingFactory");
// Load the certificate from the classpath:
info.setProperty("sslfactoryarg", "classpath:ssl/pg_server.crt");
// ... set more properties, username, password, etc ...
Connection conn = DriverManager.getConnection(url, info);
```

Here's a summary of the problems with the existing options:
* NonValidatingFactory - No remote server authentication; vulnerable to a MITM attack
* LibPQFactory - Not as easy to use; expects certificates on the local filesystem; more for mimicing libpq library (reading passwords, etc)
* Default SSL Factory/Add Cert to Keystore - Adding certificates to the local keystore allows them to be properly validated but it's a real pain, especially in a dynamic environment. Also, by default host names are not validated so it's still vulnerable to a MITM attack by using *any* valid certificate signed by an accepted CA.

This new factory is both secure and much easier to use (which in turn should lead to more people actually using it, hence more security!). Also, it supports specifying either the server's SSL certificate itself or a parent signing certificate. The former allows for a form of "certificate pinning" and the latter matches up with how cloud databases are provisioned (individual SSL certificates signed by a single parent signing certificate controlled by the DBaaS provider).

The new class should not impact any existing users as it must be explicitly specified in their JDBC connection properties to use it. We've been using a variant of this class in our product [JackDB](http://www.jackdb.com/) for a while now and it's been working great. Looking forward to removing it and switching to this version in the driver itself.