How to configure PostgreSQL connectivity over SSL?

[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

[davenz_]

Hi Ben, thanks heaps for replying - I'll give that a shot and see

[remillet_]

Here is how I got it working with the Nuxeo 5.5 DM:

1. Appended the following text to the nuxeo.db.name property value in the nuxeo.conf file:

?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&foo=true

For example:

nuxeo.db.name=nuxeo?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&foo=true

2. Set the following in nuxeo.conf to force config regeneration:

nuxeo.force.generation=true

3. Restart Nuxeo

It's not pretty, but it seems to work.

[Amir_Buzo]

For me it worked well. change in nuxeo.conf nuxeo.db.name=nuxeo?ssl=true And then add certificate to java where you run nuxeo server.

C:\Program Files\Java\jdk1.8.0_40\jre\lib\security>keytool -import -trustcacerts -keystore cacerts -storepass changeit -noprompt -alias postgresql -file C:\open ssl\bin\client\postgresql.crt

Configuration of the pg_hba.conf should be:

hostssl         nuxeo           nuxeo           127.0.0.1/32       md5
hostssl    all             all             ::1/128                 md5

In the file postgresql.conf

ssl = on				# (change requires restart)
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
					# (change requires restart)
ssl_renegotiation_limit = 0		# amount of data between renegotiations
ssl_key_file = 'server.key'		# (change requires restart)

Use openssl for generating certificates.

It works fine for me in windows and it should not be a problem at all in linux.