How to configure PostgreSQL connectivity over SSL?

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

davenz_

Hi All,

I'm attempting to configure a Nuxeo 5.6 VM to access an external PostgreSQL 9.1 database over SSL. Non-encrypted connections work fine, but I'm stumped as to how to tell Nuxeo to use SSL.

First, in my PostgreSQL database, I've generated an SSL certificate and key pair, performed the necessary PostgreSQL configuration to enable SSL, and made the appropriate entries in my pg_hba.conf file, for example:

hostssl         nuxeo           nuxeo           127.0.0.1/32       md5
hostssl         nuxeo           nuxeo           192.168.1.1/32       md5

I've tested that SSL is working by connecting to the database from localhost:

# psql -h 127.0.0.1 -U nuxeo nuxeo
Password for user nuxeo: 
psql (9.1.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

nuxeo=> 

I've copied the PostgreSQL server certificate to the Nuxeo 5.6 VM and imported it into the system Java keystore:

nuxeo@nuxeovm:/usr/lib/jvm/java-7-openjdk-i386/jre/lib/security$ keytool -list -alias "postgresql91" -keystore ./cacerts
Enter keystore password:  
postgresql91, Sep 9, 2013, trustedCertEntry, 
Certificate fingerprint (SHA1):

The $64,000 question is - how does one now tell Nuxeo to use encrypted connections? I've tried using the advanced settings at "Admin Center -> Setup -> Advanced setup" to specify SSL in my JDBC URL as follows:

nuxeo.db.jdbc.url  jdbc:postgresql://${nuxeo.db.host}:${nuxeo.db.port}/${nuxeo.db.name}?ssl=true

But on restarting the Nuxeo server I get this in a stacktrace when Nuxeo clearly can't connect to the database over SSL (and looks instead for an unencrypted connection):

Caused by: org.nuxeo.ecm.core.storage.StorageException: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.1", user "nuxeo", database "nuxeo", SSL off

Can someone please share with me how to enable encrypted database connections for Nuxeo?

Cheers, Dave

ben_

Hi,

For me it works with a default PostgreSQL 9.1.9 (ubuntu) with ssl enable.

I just had to change the template file templates/postgresql/nuxeo.defaults to update the jdbc url:

nuxeo.db.jdbc.url=jdbc:postgresql://${nuxeo.db.host}:${nuxeo.db.port}/${nuxeo.db.name}?ssl=true

I can check with tcpdump that the communication is now ssl encrypted.

Is the following command working for you ?

psql -h 192.168.1.1 -U nuxeo

Regards

ben