OHDSI Home | Forums | Wiki | Github

WebAPI fails to connect to SSL enabled remote PostgreSQL database when generating a cohort definition


(Brendan Furey) #1

Hi

I have a SSL enabled remote PostgreSQL database and I wish to connect from Atlas to generate a cohort definition.

I am using the broadsea-webtools docker container as per https://github.com/OHDSI/Broadsea-WebTools

In my configuration

  • datasource_url is set to : jdbc:postgresql://$(datasource_hostname):$(datasource_port)/$(datasource_dbname)?user=$(datasource_username)&password=$(datasource_password)&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

  • flyway_datasource_url is set to : jdbc:postgresql://$(datasource_hostname):$(datasource_port)/$(datasource_dbname)?user=$(datasource_username)&password=$(datasource_password)&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

I use 'sslfactory=org.postgresql.ssl.NonValidatingFactory` to turn off all SSL validation.

The Atlas UI is working and I can see data sources and cohort definitions.

Using the sample data from OHDSI in a box, I attempt to generate a cohort definition on “Warfarin New Users 65 or Older at Index with Prior Atrial Fibrillation”

The command fails with the response: Error: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host “xxx”, user “xxx”, database “xxx”, SSL off

Generally this error means that the client is configured to connect without SSL, but PostgreSQL requires SSL.

Looking at the logs, I can see the following log entry

Creating new JDBC DriverManager Connection to [jdbc:postgresql://$(datasource_hostname):$(datasource_port)/$(datasource_dbname)?user=$(datasource_username)&password=$(datasource_password)]

i.e. The URL is missing the SSL parameters that were included in the datasource_url/flyway_datasource_url : ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

For other requests, I do see that the ssl params are maintained correctly. This is likely why I can see cohort definitions?

2019-06-05 08:38:11.810 INFO localhost-startStop-1 org.hibernate.annotations.common.Version - - HCANN000001: Hibernate Commons Annotations {5.0.1.Final}

2019-06-05 08:38:12.275 DEBUG localhost-startStop-1 org.springframework.jdbc.datasource.DriverManagerDataSource - - Creating new JDBC DriverManager Connection to [jdbc:postgresql://$(datasource_hostname):$(datasource_port)/$(datasource_dbname)?user=$(datasource_username)&password=$(datasource_password)&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory]

Why is the datasource_url not maintained as per the config settings?

What is slicing off the additional params that are set in the datasource url?

Related to WebAPI: Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host ... SSL off

Regards
Brendan


(Lee Evans) #2

Check the database connection strings in the SOURCE database table and verify that they have the SSL parameters you need.


(Brendan Furey) #3

Thks. Was missing the SSL Params. Adding them resolved the issue.


t