OHDSI Home | Forums | Wiki | Github

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


(Brendan Furey) #1

Hi

I have a SSL enabled remote PostgreSQL database and I wish to connect from the Atlas UI 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)?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

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

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

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 to without SSL, but PostgreSQL requires SSL.

Given I have set ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory, why is the DB connection failing?

Is it ever possible to pass in the contents of the SSL root certificate of my PostgreSQL as an env variable to the DatabaseConnector via the WebAPI?

Regards
Brendan


(Daniel Lima) #2

The error is Error: (...) no pg_hba.conf entry for host “xxx”, user “xxx”, database “xxx”, SSL off

So, have you allowed user xxx@xxx with an entry in pg_hba.conf?

Edit:
For example, I have added the following line to my …/pgdata/pg_hba.conf, allowing all users access all databases from 172.16.* (docker containers) using a md5 password:

host all all 172.16.0.0/12 md5


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