Database#

Posit Package Manager supports multiple database options. Currently, the supported databases are SQLite and PostgreSQL.

Customize the Database.Provider property with a database scheme appropriate for your organization. Refer to the Database section in the appendix for details.

SQLite#

SQLite is the default database provider.

Package Manager will use a SQLite database if the Database.Provider setting has a value of sqlite or if Provider is not present in the configuration file.

Here is a partial configuration that chooses to use SQLite.

[Database]
Provider = sqlite

You can also specify the directory to store the SQLite file on your file system. This can be done by specifying SQLite.Dir in the configuration file.

[SQLite]
Dir = /mnt/rstudio-pm/sqlite

If this field is not specified, it will default to {Server.DataDir}/db. This location must exist on local storage.

PostgreSQL#

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

You must provide your own PostgreSQL server, which will likely be a separate server from your Package Manager server (but not required). Package Manager integrates with all currently supported PostgreSQL versions. Your PostgreSQL server does not have to be dedicated to Package Manager, but it must have its own dedicated database.

To use PostgreSQL instead of the default SQLite database, configure the Database.Provider option with Database.Provider = postgres, and the Postgres.URL setting with a fully-qualified connection URL.

[Database]
Provider = postgres

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

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 PostgreSQL Passwords section.

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

Usage Data#

Using a separate PostgreSQL database for usage data#

[Database]
Provider = postgres

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

Using a single database with a separate schema for usage data#

[Database]
Provider = postgres

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

SSL#

Package Manager uses the sslmode=prefer Postgres option by default. For more information on the available sslmode Postgres options, see the documentation.

To update the sslmode, append the ?sslmode= query option to the connection string, for example:

[Database]
Provider = postgres

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

Schemas#

You can configure Package Manager to use a specific PostgreSQL schema by providing a search path in the URL. 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 &.

Package Manager 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.

Using PostgreSQL with a search path#

[Database]
Provider = postgres

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

PostgreSQL passwords#

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

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

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

[Postgres]
URL = "postgres://username@db.seed.co/rstudio_pm"
Password = <base64 encrypted password value>

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

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

[Postgres]
URL = "postgres://username@db.seed.co/rstudio_pm"
Password = <base64 encrypted password value>
UsageDataURL = "postgres://username@db.seed.co/rstudio_pm_usage"
UsageDataPassword = <base64 encrypted password value>

PostgreSQL SSL certificate authentication#

SSL certificate authentication for the PostgreSQL database can be used with Package Manager. 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 PostgreSQL, the connection URL needs to include sslcert, sslkey, and sslrootcert parameters.

[Postgres]
URL = "postgres://username@db.seed.co/rstudio_pm?sslcert=/etc/ssl/certs/postgresql/postgresql.crt&sslkey=/etc/ssl/certs/cacert/postgresql/postgresql.key&sslrootcert=/etc/ssl/certs/cacert/postgresql/root.crt"

posit-package-manager your-common-name   postgres-username

hostssl all            all            0.0.0.0/0                cert  map=posit-package-manager

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

Usage data#

Package Manager operates with two sets of tables by default. The primary table set stores essential information for running the service, including the configuration of repositories, sources, and packages. The secondary table set captures usage data, such as the number of times a package was downloaded.

For SQLite implementations, two databases are automatically created in the configured directory. For PostgreSQL, you can either utilize a single Postgres.URL for both purposes or specify two separate databases using Postgres.URL for the primary data and Postgres.UsageDataURL for the usage data.

To disable usage data entirely, set the Server.UsageDataEnabled option to false. When disabled, no usage data will be collected or displayed. Additionally, you can adjust the retention period for usage data with the Server.UsageDataRetention setting, which defaults to 365 days. Note that increasing this duration may lead to greater disk space usage for the usage data database and could slightly degrade performance over time on servers with high activity.