postgresql ssl connection

[Shuhua Wu]

Hi,

I am setting-up a new XNAT installation where postgresql requires ssl
connection.
It seems only the JDBC 3 driver supports ssl while xnat uses
postgresql-7.4.214.jdbc2.jar. Am I right?

What should I do? To replace the JDBC 2 driver with JDBC 3 driver?

Thanks,
Shuhua

[Timothy Olsen]

Shuhua,

You can give it a try. We haven't tested XNAT with anything other than that driver. However, some other users have played around with different versions.

If you get some odd error messages, pass them on.

Tim

[Shuhua Wu]

Tim,

It didn't succeed. What I did was:
1. Edit mave.xml by replacing all postgresql-7.4.214.jdbc2.jar with
postgresql-8.2-504.jdbc3.jar
2. Edit build.properties to change as:
xdat.project.db.connection.string=jdbc:postgresql://mydbhost/xnatpilot?
sslfactory=org.postgresql.ssl.NonValidatingFactory&ssl=true
3. Run setup.sh. It passed the DB connection test. But error occured
when generating InstanceSettings.xml as follows:
xdat:auto-generate:
xdat:createGeneratedFiles:
[java] SETTINGS LOCATION: /ictsxnat/xnat/xnat/deployments/
xnatpilot/
[java] java.lang.RuntimeException: Unable to load DOM document:/
ictsxnat/xnat/xnat/deployments/xnatpilot/InstanceSettings.xml
[java] The reference to entity "ssl" must end with the ';'
delimiter.
[java] at org.nrg.xft.utils.XMLUtils.GetDOM(XMLUtils.java:
415)
[java] at
org.nrg.xft.schema.XFTManager.<init>(XFTManager.java:220)
[java] at org.nrg.xft.schema.XFTManager.init(XFTManager.java:
97)
[java] at org.nrg.xft.XFT.init(XFT.java:96)
[java] at org.nrg.xdat.XDAT.init(XDAT.java:93)
[java] at org.nrg.xdat.XDAT.init(XDAT.java:81)
[java] at org.nrg.xdat.XDATTool.<init>(XDATTool.java:66)
[java] at
org.nrg.xft.commandPrompt.CommandPromptTool._process(CommandPromptTool.java:
401)
[java] at
org.nrg.xft.commandPrompt.CommandPromptTool.run(CommandPromptTool.java:
323)
[java] at
org.nrg.xft.commandPrompt.CommandPromptTool.<init>(CommandPromptTool.java:
151)
[java] at
GenerateAllCreateFiles.<init>(GenerateAllCreateFiles.java:24)
[java] at
GenerateAllCreateFiles.main(GenerateAllCreateFiles.java:28)

BUILD FAILED
File...... /ictsxnat/xnat/xnat/maven.xml
Element... java
Line...... 780
Column.... 92


The InstanceSettings.xml has been generated. But the Url attribute is
not valid as follows:
<Databases>
<Database Type="POSTGRESQL" Id="xnatpilot"
Driver="org.postgresql.Driver" Url="jdbc:postgresql://
database.icts.uiowa.edu/xnatpilot?sslfac
tory=org.postgresql.ssl.NonValidatingFactory&ssl=true"
User="xnatpilot" Pass="xnat2008" MaxConnections="10"/>
</Databases>

It looks the sign & is not valid. Could it be doable to replace & with
&amp; in Java program?

Shuhua

[Timothy Olsen]

Technically the problem is that the instance settings xml file is invalid. All '&' characters in an xml should be escaped.

Out of curiosity, have you tried escaping it in the build.properties file?

jdbc:postgresql://mydbhost/xnatpilot?sslfactory=org.postgresql.ssl.NonValidatingFactory&amp;ssl=true

We'll need a more elegant solution, but this may give a temporary fix.

Tim
-----Original Message-----
From: xnat_di...@googlegroups.com [mailto:xnat_di...@googlegroups.com] On Behalf Of Shuhua Wu

[Shuhua Wu]

I tried. It didn't pass the DB connection test in the maven.xml

Unable to connected to 'jdbc:postgresql://mydbhost/xnatpilot?
sslfactory=org.postgresql.ssl.NonValidatingFactory&amp;ssl=true'
Please resolve the PostgreSQL issue (specified below).

BUILD FAILED
File...... /ictsxnat/xnat/xnat/maven.xml
Element... ant:TestDB
Line...... 1087
Column.... 177
FATAL: no pg_hba.conf entry for host "mydbhost", user "xnatpilot",
database "xnatpilot", SSL off


Shuhua

[Timothy Olsen]

So the DB test needs the un-escaped version, but the InstanceSettings.xml needs the escaped version.

We may be able to fix this in the Maven code.

Return your build.properties file to the un-escaped version.
jdbc:postgresql://mydbhost/xnatpilot?sslfactory=org.postgresql.ssl.NonValidatingFactory&ssl=true

Then try the Maven.xml I attached. Notice you'll have to make your driver name modifications. (You may just be able to do a diff on the two files).

Warning, I was unable to test this (as I don't have a postgresql running over ssl). So, you may need to tweak it a bit.

Tim

-----Original Message-----
From: xnat_di...@googlegroups.com [mailto:xnat_di...@googlegroups.com] On Behalf Of Shuhua Wu
Sent: Friday, November 07, 2008 1:26 PM
To: xnat_discussion
Subject: Re: postgresql ssl connection

[Shuhua Wu]

It worked. Thanks.