Troubleshooting Postgres Connection Issues

Many users of Posit Workbench & Posit Connect like to migrate to a PostgreSQL database, as opposed to the default SQLite3 database. Doing so can introduce some new challenges to your infrastructure.

Technically, PostgreSQL is outside of the bounds of Posit support. However, there are some pointers that we can provide, as this is a very common implementation of the Posit Teams suite.

Allow access to the database

By default, a standard PostgreSQL installation will block all inbound connections at the operating system, regardless of your firewall settings. You will need to allow access by modifying your pg_hba.conf configuration file. You will need to add a line that looks something like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

This allows all users access to all databases on the localhost. Different organizations will have different security protocols, so it's best to reach out to your organization's security team to ensure that you have implemented the correct settings. More information on the configuration options can be found here:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Then, restart PostgreSQL for these changes to take effect.

From there, it's important to ensure that you have allowed the necessary inbound firewall rules for your PostgreSQL server. By default, PostgreSQL listens on port 5432. 

Verify database access

Now is a good time to verify access to the PostgreSQL database. Prior to making any configuration changes to Posit products, verify access from your Posit servers command line, using the pg_isready utility as below: 
 

pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>                      

0 - if the server is accepting connections normally, 
  1 - if the server is rejecting connections (for example during startup), 
  2 - if there was no response to the connection attempt, and 
  3 - if no attempt was made (for example due to invalid parameters).