PostgreSQL#

PostgreSQL is an available database provider which is more powerful and performant than SQLite.

You must provide your own Postgres server which will likely be a separate box from your RStudio Connect server (but not required). We currently support any 9.x version greater than or equal to 9.5, as well as 10, 11, 12, and 13. Your Postgres server does not have to be dedicated to RStudio Connect, but it must have its own dedicated database.

Configure the Database.Provider as postgres for RStudio Connect to use a PostgreSQL database rather than the default SQLite database. The Postgres.URL setting is the fully-qualified connection URL for your Postgres server.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect"

The user credentials can be included in the Postgres.URL URL - remember to URL-encode any special characters in the username or password. We recommend using Postgres.Password to avoid using a bare password in your configuration file. Examples using Postgres.Password are found in the Passwords section.

The user credentials supplied in the Postgres.URL URL must be able to create and alter database tables, in addition to read/write permissions in the database referenced as the path of the URL. A blank database with the given name MUST already exist.

SSL#

RStudio Connect assumes by default that SSL is enabled on the Postgres server. If SSL is not enabled on your Postgres database, add ?sslmode=disable to the Postgres.URL.

Here is an example configuration using a Postgres database with SSL connections:

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect"

Here is an example configuration using a Postgres database without SSL connections:

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?sslmode=disable"

Schemas#

If you need to, you can tell Connect 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 URL. 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 '&'.

Here is an example configuration that specifies a schema to use, with SSL:

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?options=-csearch_path=connect_schema"

Here is an example configuration that specifies a schema to use, with SSL disabled:

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?sslmode=disable&options=-csearch_path=connect_schema"

RStudio Connect 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.

Historical Database#

Historical events generated by RStudio Connect can be written to a separate PostgreSQL database. See the Historical Information section for details.

By default, historical events will be written to the same database. Use Postgres.InstrumentationURL to use a separate database.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username:password@db.seed.co/connect"
InstrumentationURL = "postgres://username:password@db.seed.co/connect_instrumentation"

Passwords#

When your PostgreSQL connection URL requires a password, use Postgres.Password with an encrypted value to avoid credential leakage. The Property Types configuration appendix explains encrypted settings, how to encrypt secrets like passwords, and using them in your configuration file.

Note

We do not recommend embedding passwords directly in the Postgres.URL connection URL setting.

RStudio Connect uses Postgres.Password when connecting to your PostgreSQL database. Do not percent-encode your Postgres.Password value. If necessary, Connect will encode your password when it is combined with the Postgres.URL.

Note

Passwords included directly in Postgres.URL need to have special characters percent-encoded. The set of characters that must be encoded within the user information component of a URL can be found in Section 3.2.1 of RFC 3986.

Here is a sample configuration using a PostgreSQL connection URL with a username and a separate, encrypted password.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect"
Password = <encrypted, Base64-encoded password value>

The historical database connection URL can also be used with an encrypted password. Use Postgres.InstrumentationPassword to provide the password associated with the connection URL Postgres.InstrumentationURL.

Here is a sample configuration that has two separate Postgres URLs; one for the primary database and one for the historical database. Both connection URLs have separate, encrypted passwords.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect"
Password = <encrypted, Base64-encoded password value>
InstrumentationURL = "postgres://username@db.seed.co/connect_instrumentation"
InstrumentationPassword = <encrypted, Base64-encoded password value>