Scala: How can I establish secure connection to PostgreSQL server?

Question

I want to connect securely to my PostgreSQL server programmatically in Scala using a JDBC-based library. Furthemore, I want to store all the SSL certificate and key files in the formats generated by openssl (i.e., not the KeyStore format generated by the native Java libraries).

How can I do this?

I am using:

• PostgreSQL 9.3
• JVM 1.6
• Scala 2.10.3

Answer 1

Extend the org.postgresql.ssl.WrappedFactory class that is part of the PostgreSQL JDBC Driver, then pass the name of your extension class as the argument to the setSslfactory() method of an appropriate subclass of BaseDataSource.

Example

Assuming you're using a self-signed certificate as the PostgreSQL server's certificate (N.B., the server's certificate is in the file whose name is specified as the value of configuration parameter ssl_cert_file), and

Assuming the PostgreSQL login role you will use to establish the connection is named database_user:

Use the PostgreSQL server's certificate to sign a client certificate having common name (CN) attribute database_user. Then, with the server's certificate, client's certificate, and client's private key each in openssl PEM format on the client machine, the following will enable secure, bilaterally-authenticated connections to the PostgreSQL server:

package mypackage

import org.postgresql.ssl.WrappedFactory

import org.bouncycastle.util.io.pem.PemReader

import java.io.{FileInputStream,FileReader}
import javax.net.ssl.{SSLContext,KeyManagerFactory,TrustManagerFactory}
import java.security.{KeyStore,KeyFactory,SecureRandom}
import java.security.cert.CertificateFactory
import java.security.spec.PKCS8EncodedKeySpec

object SocketFactory {
  final val PRIVATE_KEY_FILENAME = "private.key"
  final val CLIENT_CERT_FILENAME = "client.crt"
  final val SERVER_CERT_FILENAME = "/home/me/.ssl/postgres_server.crt"

  def certFromFile(fileName: String) =
    CertificateFactory getInstance "X.509" generateCertificate {
      new FileInputStream(fileName)
    }
}

class SocketFactory extends WrappedFactory {
  import SocketFactory._

  val sslContext = SSLContext getInstance "TLS"

  sslContext init (
    { // key managers: Array[KeyManager] 
      val kmf = KeyManagerFactory getInstance "SunX509"
      kmf init ({
        val keyStore = KeyStore getInstance KeyStore.getDefaultType
        keyStore load (null, null)
        keyStore setKeyEntry ( "",
          KeyFactory getInstance "RSA" generatePrivate (
            new PKCS8EncodedKeySpec (
              new PemReader (
                new FileReader(PRIVATE_KEY_FILENAME)
              ).readPemObject.getContent
            )
          ),
          Array[Char](), // no password for this entry
          Array(certFromFile(CLIENT_CERT_FILENAME))
        )
        keyStore
      }, Array[Char]())
      kmf.getKeyManagers
    },
    { // trust managers: Array[TrustManager]
      val tmf = TrustManagerFactory getInstance "PKIX"
      tmf init {
        val keyStore = KeyStore getInstance KeyStore.getDefaultType
        keyStore load (null, null)
        keyStore setCertificateEntry ("", certFromFile(SERVER_CERT_FILENAME))
        keyStore
      }            
      tmf.getTrustManagers
    },
    SecureRandom getInstance "SHA1PRNG"
  )

  _factory = sslContext.getSocketFactory

}

Using the foregoing, you can get a Java DataSource something like this:

def dataSource = {
  val ds = new PGSimpleDataSource
  ds.setDatabaseName("my_database")
  ds.setServerName("myHost.com")
  ds.setUser("database_user")
  ds.setSsl(true)
  ds.setSslfactory("mypackage.SocketFactory")
  ds
}

Notes:

1. This code catches no exceptions, nor closes Streams/Readers. Modify as desired.
2. This code does not validate the client certificate against the server certificate, nor the private key against the client certificate (beyond the connection attempt). Beware of related errors, and modify as desired.
3. The underlying Java methods often accept null as meaningful arguments. If you notice strange behavior, moke sure you aren't accidentally passing a null method parameter somewhere.
4. The foregoing uses RSA key-pairs. More recent versions of openssl can use elliptical curve encryption.

5. To achieve forward secrecy, consider specifying appropriate cipher suites in your PostgreSQL configuration, e.g.:

   ssl_ciphers = 'DHE-RSA-AES256-SHA:DHE-RSA-CAMELLIA256-SHA'

Other Files

The foregoing example uses the following library dependencies in the build.sbt file:

libraryDependencies ++= Seq(
  "org.bouncycastle"   % "bcpkix-jdk15on" % "1.50",
  "org.postgresql"     % "postgresql"   % "9.3-1100-jdbc4"
)

Your pg_hba.conf file will need an appropriate entry, something like:

hostssl my_database database_user <client.ip.number>/32 trust clientcert=1