No pg_hba.conf entry for host
This morning a colleague and I finally had the chance to update our existing Mirth configuration to use a database other than the bundled DerbyDB. While Derby worked fine for a while, we quickly reached the size limit (around 16GB) under which it was able to perform adequately. Mirth themselves recommend using a different database and package Derby, I assume, as a way to get people off the ground without ever meaning it to be used long term.
My organization's use of Mirth is still in its infancy and, while we're a Microsoft shop and use SQL Server for all of our database needs, we really didn't want to purchase a SQL server license for this purpose. We opted instead to use PostgreSQL and installed a copy on a virtual server. We ran through the default steps for setting up a Mirth database, including database user creation and execution of the postgresql table creation script bundled in the Mirth installation folder. It looked like easy sailing until we restarted the existing Mirth service and received the following message:
The Mirth Service could not be started
Contrary to the message the Mirth service did in fact restart. However, we were unable to log into Mirth despite the myriad username/password combinations we tried.
Looking at Mirth’s latest log file we found the following error:
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "myhost", user "myuser", database "mymirthdatabase", SSL off
Doing a little research we discovered that this error meant that we were hitting the correct server, however the postgresql installation was not configured to accept remote connections. I assume, but cannot say for certain, that if our mirth database and mirth application server were on the same box then we wouldn’t have had this issue.
We found that there were two files of interest in our postgresql setup.
These files are packaged in the sample folder of the postgresql installation. However, it is key to note that (on Windows at least) the files that must be edited are contained in the data folder (i.e. wherever you specified your data would be stored during the initial installation) – in our case D:\mirth_data
In the sample file the listen_addresses, port and max_connections settings were commented out. However, by default in the data folder they appeared correct, essentially listening to all available addresses on port 5432. If however, you run into this issue and the listen_addresses does not contain * or worse still, is completely commented out, then this is definitely something you’ll need to change.
listen_addresses = '*'
port = 5432
max_connections = 100
The crux of the issue, as specified in the error message above was that no entry was defined for the calling server (i.e. the Mirth application server) in our list of postgresql hosts. Our Mirth database server does not face any public networks (just our own intranet) so we were able to add the following line to allow communication from any host.
host all all 0.0.0.0/0 md5
A simple postgresql service restart and then Mirth service restart and we were back in business with our shiny new database!