Install and Configure PostgreSQL for Posit Workbench

Who is this article for?

System Administrators who are unfamiliar with basic installation and configuration of the PostgreSQL database engine for Posit Workbench (previously RStudio Workbench and RStudio Server Pro).

Note: The official name is "PostgreSQL" but often shortened to "Postgres".

This article provides a primer for getting started but is not intended to be a comprehensive explanation of best practices for configuring and operating Postgres in a production environment.

When is this needed?

RStudio Workbench 1.4 (and above) uses a database for internal purposes. By default, Posit uses SQLite, installing the necessary dependencies and automatically creating the database. 

When using Posit Workbench's internal load balancer, however, a PostgreSQL database must be created and configured manually by the administrator as mentioned in the Database section of the Admin Guide.

Note: Workbench can use the PostgreSQL option without load-balancing enabled, which is helpful for configuration experimentation as described in this article.

Check for PostgreSQL

From a terminal, check if PostgreSQL is already installed and display the version.

psql --version

The supported Postgres version will depend on your Posit Workbench version, but will not be older than 9.5  Please see the documentation for your version here. So if an older version is found you'll need to upgrade it or install multiple versions (both beyond the scope of this article). If the psql command isn't found, then you'll need to install PostgreSQL.

Install PostgreSQL

Although many Linux distributions include a version of PostgreSQL in their package libraries, for this article we will use the instructions on the PostgreSQL website to download the most recent version: https://www.postgresql.org/download/. Select your platform, OS version, and desired PostgreSQL version, and matching installation commands will be provided.

Note: Steps in this article were tested using PostgreSQL version 14, the most recent at time of writing, on Red Hat/Rocky Linux 8. Some details will vary on other database versions and Linux distributions.

Database Command Line Basics

After installation using recommended commands from the download page, only the "postgres" user can connect to the database engine. Connect as follows from a terminal on the same machine where you installed PostgreSQL:

sudo -i -u postgres psql

This puts you at the psql prompt. From here you can execute SQL commands against the active database and see the results, or use internal commands (often starting with a backslash).

For example, to see a list of "roles" (database user accounts in Postgres terminology), use the "\du" command:

\du

To view all databases:

\l

To exit back to your shell, use Ctrl+D, or "\q".

\q

Create Database Role (aka User) and Database

Using the default postgres role for Workbench's internal database is not recommended.

The first command below creates a new role named "rstudio", password "test", with ability to login and create databases.

Second command creates an empty database named "rstudio" owned by "rstudio". Punctuation and capitalization are significant in both commands.

sudo -i -u postgres psql -c "CREATE ROLE rstudio CREATEDB LOGIN PASSWORD 'test';"
sudo -i -u postgres psql -c "CREATE DATABASE rstudio WITH OWNER = rstudio;"

To confirm successful creation of database "rstudio" owned by "rstudio", list the databases and look for the one just created.

sudo -i -u postgres psql -c "\l"

Configure Connectivity

PostgreSQL's default security settings may require adjustment before the account just created can be used to connect to PostgreSQL on localhost. To see if this is the case, attempt to connect (enter the password used above, e.g. "test", when prompted):

psql -U rstudio -W rstudio

If this works and you are placed at the PostgreSQL prompt, proceed to next section. If you receive an error such as below, then continue this section to fix it.

psql: error: FATAL: Peer authentication failed for user "rstudio"

Connectivity is configured via the pg_hba.conf file whose location is dependent on the PostgreSQL version, and locked down and only visible and editable for the user "postgres".

To locate pg_hba.conf, you can ask the database engine.

sudo -i -u postgres psql -c 'show hba_file';

To determine which encryption to use, issue the following command:

sudo -i -u postgres psql -c 'show password_encryption';

Note the result (e.g. "scram-sha-256" or "md5". Then open pg_hba.conf in a text editor, for example:

sudo -i -u postgres vim /var/lib/pgsql/14/data/pg_hba.conf

For our scenario, where we are going to configure Posit Workbench to communicate with PostgreSQL on the same machine, the relevant line is the entry for "local all". Change it from using the method "peer" to either scram-sha-256 or md5 (as determined above). For PostgreSQL 14:

# TYPE  DATABASE        USER   ADDRESS        METHOD
local   all             all                   scram-sha-256
host    all             all    127.0.0.1/32   scram-sha-256
host    all             all    ::1/128        scram-sha-256
local   replication     all                   peer
host    replication     all    127.0.0.1/32   scram-sha-256
host    replication     all    ::1/128        scram-sha-256

Save changes then restart the database (version and OS-specific):

sudo systemctl restart postgresql-14

Try the connection again, it should now work.

psql -U rstudio -W rstudio

Configure Workbench

Note: In a production environment, you will likely want to install Posit Workbench on a different server than PostgreSQL. For this example, however, we will install them on the same server.

Install Posit Workbench on the same server as PostgreSQL, and edit the /etc/rstudio/database.conf file as follows:

provider=postgresql
database=rstudio
username=rstudio
password=test

Save, and then stop Workbench and verify the configuration:

sudo rstudio-server stop
sudo rstudio-server verify-installation

You should receive a warning about using a plain-text password (as expected); see the Admin Guide for details on how to eliminate this warning via password encryption.

Next Steps

• More background information on Posit product databases is available in Understanding the RStudio Product Databases.
• Consult the Posit Workbench Admin Guide for additional configuration choices and considerations such as password encryption.
• Consult the PostgreSQL documentation for details on configuring connectivity to PostgreSQL when it is running on a separate machine, as will be the case in a real load-balanced Posit Workbench configuration. There will be additional configuration needed to enable connection to a Postgres database, via password-based authentication, from a separate server.

Conclusion

In this article we learned basic installation and configuration of PostgreSQL for use by Posit Workbench's internal database storage.