To allow access to a Postgres DB server from any location other than “localhost,” some modifications have to be made to the Postgres server configuration files.

Postgres 8.4 comes already configured to serve the database on all IP’s. Postgres 8.2 however only serves the database on localhost by default and not on all of the IP’s (note: There is no version of OpenClinica that runs on Postgres 8.3). Regardless of whether you are using Postgres 8.2 or 8.4, to open up additional connections you will need to add an entry in the Posgres conf file so the Postgres server can allow the connection.

The following lists instructions to both (a) serve your database on all IP’s, and (b) modify Postgres’ configuration to allow additional connection.

Postgres configuration files location:

  • For Windows – C:Program FilesPostgreSQL8.4data
    • Note “Program Files” may be “Program Files x86” for your installation.
  • For Linux –  /opt/PostgreSQL/8.4/data

The two main configuration files which are both located in the data directories referenced above are “postgresql.conf” and “pg_hba.conf.”

To get Postgres 8.2 to listen on all IP’s:

  • Edit the postgresql.conf file and set the listen_address variable to “listen_addresses = ‘*'”
  • Save postgresql.conf
  • Restart Postgres (Ensure any tomcat instances pointed to the databases on the server are stopped.)
  • Postgres 8.2 is now listening on all IP addresses on the server (Reference Material)

Enable Postgres to accept additional connections:

  • Edit the pg_hba.conf file to allow connections (Reference Material).

    • The pg_hba.conf file is like an ACL (Access Control List) for the database. It only allows connections from servers that match the ACL list.

    • The file can restrict access based on IP, username and database and any combination of the above.

    • The file can also setup authentication requirements for that connection.

    • An example entry that will allow all IP’s and all usernames to connect to all databases is below.

      • host all all 0.0.0.0/0 md5

    • Most of the time the error message you get will show you what you need to add to the file. (Shown below under Error Messages)
       
  • Once you are done editing the file, restart Postgres to apply the new settings (Ensure any Tomcat instances pointed to the database(s) on the server are stopped). 
    • Alternatively, you can reload the pg_hba configuration without restarting by sending the main server process a SIGHUP signal. If you edit the file on an active system, you will need to signal the server (using pg_ctl reload or kill -HUP) to make it re-read the file. This will also load any changes to postgresql.conf.

  • The Postgres server should now allow you to connect as configured.

Error Messages:

The following error message means that the server is not running on the host. This error message could also mean that the Postgres is not listening on all IP’s–this typically only occurs with Postgres 8.2. To resolve this issue, see above section on getting Postgres 8.2 to listen on all IP’s.

psql: could not connect to server: Connection refused

        Is the server running on host “192.168.15.163” and accepting

        TCP/IP connections on port 5432?

The following error message means that the server does not have an entry in pg_hba.conf for your connection. The message helps indicate the type of entry you would need to add.

psql: FATAL:  no pg_hba.conf entry for host “192.168.15.56”, user “postgres”, database “openclinica”, SSL off

The above message is saying your IP is “192.168.15.66”, your user is “postgres” and you are trying to connect to database “openclinica.” Based on this information, your new pg_hba.conf configuration line would be the following:

 host    postgres    openclinica         192.168.15.66/32      md5