Configuration

To set up the Audit Database connection, modify the file /etc/rstudio/audit-database.conf. The file provides documentation about using it, and you can simply uncomment any lines that are relevant to your configuration. Because the file can contain sensitive information, such as a password or access token, this file must be user read/write only (file mask 600) and should be owned by root. On startup, Posit Workbench attempts to set the permissions of the audit-database.conf file to root:root 600.

SQLite

By default, Workbench automatically creates a SQLite database under the /var/lib/rstudio-server directory. This is sufficient for single-node installations, but as stated before, it is not sufficient for load-balanced deployments.

Warning

You should never specify a SQLite directory that is on shared storage, such as NFS. Per the SQLite documentation, this can cause data corruption.

Sample configuration:

/etc/rstudio/audit-database.conf
provider=sqlite

# [optional] Directory in which the sqlite database will be written
directory=/var/lib/rstudio-server

PostgreSQL

Create the audit database

If you want to use PostgreSQL for the Audit Database, you have two options:

1. Manually create the audit database

To manually create the Audit Database, you need a PostgreSQL user with the permissions to create databases. When creating the Audit Database, use the same database name that’s specified in audit-database.conf. For example, using the default Audit Database name of audit_db, log into your PostgreSQL server and run:

CREATE DATABASE audit_db;
2. Set auto-create=1 in audit-database.conf

If auto-create=1 in audit-database.conf Workbench attempts to create the database for you. Auto creation of the Audit Database works by Workbench using the PostgreSQL connection specified for the Internal Database. As such, in order for auto-create to succeed:

  1. The Internal Database must be configured to use PostgreSQL
  2. The Internal and Audit databases must be connecting to the same PostgreSQL server
  3. The Internal Database PostgreSQL user must have permission to CREATE a new database

If any of these conditions is not satisfied, Workbench emits an error on startup and disables the Audit Database functionality. You need to either correct the errors or create the Audit Database manually.

Requirements

Do not share this database with other products or services. If running an HA setup, it is strongly recommended to run the database and Posit Workbench services on separate servers to ensure maximum availability. The minimum supported version is PostgreSQL 11. It requires a 1Ghz processor with 2GB of RAM, and 4GB of disk space.

Workbench requires PostgreSQL to be configured with either password-based authentication or SSL (TLS) certificate-based authentication. For more details on this and other PostgreSQL fundamentals, see the support article Install and Configure PostgreSQL for Posit Workbench / RStudio Server Pro and the PostgreSQL Certificate Authentication documentation.

Because the Audit Database can contain sensitive information, it is strongly recommended to configure your PostgreSQL instance to use SSL (TLS) encrypted connections. More information can be found in the Secure TCP/IP Connections with SSL section of the PostgreSQL documentation.

Sample configuration:

/etc/rstudio/audit-database.conf
# Note: when connecting to a PostgreSQL database, a default empty database called 'rstudio' must first be created!
provider=postgresql

# Specifies the host (hostname or IP address) of the database host
host=localhost
 
# Specifies the database to connect to
database=audit_db

# Specifies the TCP port where the database is listening for connections
port=5432

# Specifies the database connection username
username=rstudio

# Asks Workbench to attempt to create the database for you.
auto-create=1


# Specifies the database connection password. This may be encrypted with the secure-cookie-key.
# The encrypted password can be generated using the helper command rstudio-server encrypt-password.
# It is strongly recommended that you encrypt the password!
password=test

Sample configuration using connection-uri:

/etc/rstudio/audit-database.conf
provider=postgresql
 
# Specifies the connection URL in the form of a postgresql:// connection URL. This can be used if you need
# to set special database settings that are not available with the other parameters. If set, this parameter will
# override any other postgresql parameters that have been set, with the exception of the password. A password in 
# the URI is supported as a convenience but we strongly recommend using the separate password field, which will
# always replace any password specified in the URI.
connection-uri=postgresql://postgres-username@localhost:5432/audit-db?sslmode=verify-full&options=-csearch_path=public

Available PostgreSQL connection string parameters are documented in the official PostgreSQL documentation.

SSL certificate authentication

Posit recommends SSL certificate authentication for the PostgreSQL database. To use SSL certificate authentication, configure your PostgreSQL server for SSL connections and authentication. See the official PostgreSQL Certificate Authentication documentation for more information.

Once your certificates are set up with PostgreSQL, the connection-uri parameter in audit-database.conf needs to include sslcert, sslkey, and sslrootcert parameters.

For example:

/etc/rstudio/audit-database.conf
provider=postgresql
connection-uri=postgresql://postgres-username@your.server.address:5432/audit_db?sslcert=/etc/ssl/certs/postgresql/postgresql.crt&sslkey=/etc/ssl/certs/cacert/postgresql/postgresql.key&sslrootcert=/etc/ssl/certs/cacert/postgresql/root.crt
Common Name in the certificate must match the database username

The cn (Common Name) attribute of the certificate is compared to the requested database user name in audit-database.conf, and if they match, the login is allowed.

If they don’t match, you can specify a map in pg_ident.conf to map cns and usernames. For example:

pg_ident.conf
posit-workbench your-common-name   postgres-username

Then, you can update pg_hba.conf to reference the map created above:

pg_hba.conf
hostssl all            all            0.0.0.0/0                cert  map=posit-workbench

For more information, see the PostgreSQL User Name Maps documentation.

Environment variables

For better integration with external secret management tools, set the PostgreSQL password in an environment variable instead:

WORKBENCH_POSTGRES_PASSWORD=test

# Or, to read the password from a file on startup:
WORKBENCH_POSTGRES_PASSWORD_FILE=/run/secrets/test

Environment variables take precedence over any settings in the audit-database.conf file, and WORKBENCH_POSTGRES_PASSWORD takes precedence over WORKBENCH_POSTGRES_PASSWORD_FILE when both are set.

Note

These environment variables are used for both the Internal Database and the Audit Database if both are using PostgreSQL. Using a different username and password for each database with environment variables is not currently supported.

For more information on setting environment variables, see Core Administrative Tasks.

Schemas

If needed, you can tell Workbench to restrict itself to keeping its tables within a specific schema. You control this by giving PostgreSQL a search path as part of the URL by adding options=-csearch_path=<schema-name> to the connection-uri. If it is the only item you’re adding, separate it from the rest of the URL with ‘?’ (just like the sslmode item above). Otherwise, separate it from other items with ‘&’.

For example:

/etc/rstudio/audit-database.conf
provider=postgresql
connection-uri=postgresql://postgres-username@localhost:5432/audit_db?sslmode=verify-full&options=-csearch_path=rstudio_schema

Posit Workbench refuses to start when given a schema that does not already exist. The schema must be owned by the connecting user or by a group that contains the connecting user.

Note

Technically, configuring Workbench to use schemas like this could allow sharing a single PostgreSQL database with two different schemas, one for the internal database and the audit database. This configuration is not recommended or supported for performance reasons. The internal and audit databases should be two distinct databases.

PostgreSQL account

When configuring your PostgreSQL database for use with Workbench, ensure that you do not use the default postgres user account that comes with a standard installation. This is to ensure that your database is secure. If using password authentication, always ensure that whichever account is being used to access the database contains a strong password. Do not use an account that has no password.

PostgreSQL connection testing and troubleshooting

Once the settings have been added to /etc/rstudio/database.conf, use the sudo rstudio-server verify-installation command to test connectivity and quickly view errors and warnings.

Connection pool

Posit Workbench creates a pool of connections to the Audit Database at startup. The size of this pool defaults to the number of logical CPUs on the host running Posit Workbench, up to six. It is not generally recommended that you adjust the size of this pool manually unless you need to address a specific problem, such as exceeding a connection limit on the database or experiencing delays in Workbench caused by unavailable connections.

When load balancing, make sure the database can support enough connections for all servers.

If you do need to adjust the size of the pool, you can do so by setting pool-size in audit-database.conf as follows:

/etc/rstudio/audit-database.conf
pool-size=5
Back to top