Configuration
In order to set up a database connection, modify the file /etc/rstudio/database.conf. The file contains documentation about how to use it, and you can simply uncomment any lines that are relevant to your configuration. Note that 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 start up, Posit Workbench will attempt to set the permissions of the database.conf file to root:root 600.
SQLite
By default, Posit Workbench creates a SQLite database for you automatically under the /var/lib/rstudio-server directory. For single-node installations, this is sufficient, but as stated before, will not be sufficient for load balanced deployments.
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/database.conf
provider=sqlite
# Directory in which the sqlite database will be written
directory=/var/lib/rstudio-serverPostgreSQL
If you wish to use PostgreSQL, you must create an empty database for Posit Workbench to connect to. You must not share this database with other products or services. If running an HA setup, it is strongly recommended to run the database on a separate server than the Posit Workbench services 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.
Posit 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 internal 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
# 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=rstudio
# Specifies the TCP port where the database is listening for connections
port=5432
# Specifies the database connection username
username=rstudio
# 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=testSample configuration using connection-uri:
/etc/rstudio/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://rstudio@localhost:5432/rstudio?sslmode=verify-full&options=-csearch_path=publicAvailable PostgreSQL connection string parameters are documented in the official PostgreSQL documentation.
SSL Certificate Authentication
SSL certificate authentication for the PostgreSQL database can be used with Posit Workbench, and is the recommended auth method to use. To use certificate authentication you must configure your PostgresSQL server for SSL connections and authentication. See the official PostgreSQL Certificate Authentication documentation for more information.
Once your certificates are set up with postgres, the connection-uri parameter in database.conf will need to include sslcert, sslkey, and sslrootcert parameters.
For example:
/etc/rstudio/database.conf
provider=postgresql
connection-uri=postgresql://postgres-username@your.server.address:5432/rstudio?sslcert=/etc/ssl/certs/postgresql/postgresql.crt&sslkey=/etc/ssl/certs/cacert/postgresql/postgresql.key&sslrootcert=/etc/ssl/certs/cacert/postgresql/root.crtThe cn (Common Name) attribute of the certificate will be compared to the requested database user name in database.conf, and if they match the login will be 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-usernameYou can then update pg_hba.conf to reference the map created above:
pg_hba.conf
hostssl all all 0.0.0.0/0 cert map=posit-workbenchFor more information, see the PostgreSQL User Name Maps documentation.
Schemas
If you need to, you can tell Posit 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’s 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:
provider=postgresql
connection-uri=postgresql://postgres@localhost:5432/rstudio?sslmode=verify-full&options=-csearch_path=rstudio_schemaPosit Workbench will refuse 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.
PostgreSQL account
When setting up your PostgreSQL database for use with Posit 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, aways ensure that whichever account that is used to access the database contains a strong password - do not use an account that has no password! You should also ensure that only one PostgreSQL user has access to the Posit Workbench database for maximum security.
PostgreSQL connection testing and troubleshooting
Once the settings have been entered in /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 will create a pool of connections to the database at startup. The size of this pool defaults to the number of logical CPUs on the host running Posit Workbench, up to 6. 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 Posit 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 database.conf as follows:
/etc/rstudio/database.conf
pool-size=5