How to configure PostgreSQL connectivity over SSL?
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
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.
Here is how I got it working with the Nuxeo 5.5 DM:
- 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
- Set the following in nuxeo.conf to force config regeneration:
nuxeo.force.generation=true
- Restart Nuxeo
It's not pretty, but it seems to work.
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
