Borealis Isolated Postgres

Borealis Isolated Postgres add-ons can be managed with the borealis-pg-cli plugin for the Heroku CLI.

Each add-on database is securely isolated from the open internet in a virtual private cloud, which makes it impossible to access the database across the internet directly. Therefore, use borealis-pg-cli to establish a secure tunnel to an add-on database to perform ad hoc SQL queries, run database migration scripts and manage PostgreSQL extensions and database users. The following describes the use of borealis-pg-cli.

Pre-requisites

These utilities are required to proceed:

Git
Heroku CLI
psql (required only for the borealis-pg:psql command)

Instructions

Getting started

With the pre-requisites installed, simply execute the following on the command line to install the plugin:

$ heroku plugins:install borealis-pg-cli

If you notice build warnings from gyp related to cpu-features during the preceeding operation, don’t worry; this is an optional dependency that will not impact use of the plugin.

When installation is complete, execute the following to see the root documentation for the borealis-pg-cli plugin:

$ heroku help borealis-pg

You can use heroku help to see the documentation for any command or sub-command. For example, for help with the command that installs PostgreSQL extensions:

$ heroku help borealis-pg:extensions:install

Database connections

To connect securely to an add-on database using psql, use the borealis-pg:psql command. For example:

$ heroku borealis-pg:psql --app sushi

To start a persistent secure tunnel session for an add-on database, use the borealis-pg:tunnel command. For example, to connect with a dedicated personal user with read-only access to an add-on database that is attached to the example Heroku app sushi, you can execute the following:

$ heroku borealis-pg:tunnel --app sushi

Or, for a persistent secure tunnel with a dedicated personal user that has both read and write access to the same add-on database:

$ heroku borealis-pg:tunnel --app sushi --write-access

Once the session has started, you can use a tool like pgAdmin to interact with the database using the credentials provided in the borealis-pg:tunnel command’s output. Press Ctrl+C to close the tunnel and end the session.

To execute non-interactive commands (e.g. database migrations) as the Heroku app database user, use the borealis-pg:run command. For example, to execute the database migrations for a Ruby on Rails application, you can run:

$ heroku borealis-pg:run --app sushi --shell-cmd 'rake db:migrate' --write-access

Alternatively, to execute raw SQL non-interactively from a file using the same add-on database:

$ heroku borealis-pg:run --app sushi --db-cmd-file './prepopulate.sql' --write-access

By default, the borealis-pg:run command executes given commands as the Heroku app’s database user, but it can be made instead to execute its commands as a personal user (i.e. a database user role that is tied to a specific Heroku CLI user account) by specifying the --personal-user option. The borealis-pg:psql and borealis-pg:tunnel commands always run as a personal user, however. In any case, if creating tables, indexes, views or other objects as a personal user, the corresponding objects will be owned by that personal user unless/until explicitly reassigned. To reassign every object owned by the current user to the application’s read/write user, you can execute the following SQL (assuming the application read/write user’s name is app_rw_0123456789abcdef):

REASSIGN OWNED BY CURRENT_USER TO app_rw_0123456789abcdef;

PostgreSQL extensions

To install a PostgreSQL extension on an add-on database, use the borealis-pg:extensions:install command. For example, to install the PostGIS extension:

$ heroku borealis-pg:extensions:install --app sushi postgis

Removing a PostgreSQL extension is done with the borealis-pg:extensions:remove command:

$ heroku borealis-pg:extensions:remove --app sushi uuid-ossp

And to see a list of all installed PostgreSQL extensions, use the borealis-pg:extensions command:

$ heroku borealis-pg:extensions --app sushi

A list of PostgreSQL extensions that are supported by Borealis Isolated Postgres may be found here. x

Database users

The Heroku app is automatically assigned read/write and read-only database user credentials when an add-on is provisioned. And personal database user credentials are automatically created/rotated every time an authorized user executes one of the borealis-pg:psql or borealis-pg:tunnel commands (or borealis-pg:run with the --personal-user option). To list the active database users, execute the borealis-pg:users command:

$ heroku borealis-pg:users --app sushi

To seamlessly reset all of an add-on database’s user credentials, use the borealis-pg:users:reset command:

$ heroku borealis-pg:users:reset --app sushi

A full database credentials reset will generate new usernames and passwords for the Heroku app’s read/write and read-only user roles. The previous Heroku app user credentials will continue to remain valid for several minutes afterward to ensure there is an overlap between them to prevent application downtime. All personal database user roles will be deactivated, but the next time an affected user executes one of the borealis-pg:psql or borealis-pg:tunnel commands (or borealis-pg:run with the --personal-user option), their database user roles will be reactivated and new credentials will be generated. No database objects (tables, views, indexes, etc.) or table data will be lost during a full database credentials reset.

Database restore and clone

Single tenant databases allow point-in-time restoration and cloning. Point-in-time restoration is meant for disaster recovery and cloning is typically useful for creating an up-to-date copy of a production database for staging, testing, development, etc. In either case, a new add-on is created to store the restored/cloned data, leaving the original database unmodified and unaffected. Both operations are performed by the heroku borealis-pg:restore:execute command.

To determine what time range is currently supported for a point-in-time restore, use the borealis-pg:restore:capabilities command:

$ heroku borealis-pg:restore:capabilities --app sushi

To restore to a specific point in time with a different add-on plan and wait for it to finish:

$ heroku borealis-pg:restore:execute --app sushi --new-plan x2-s200-p2-r16 --restore-to-time 2023-03-06T19:31:41.109-05:00 --wait

To create an up-to-date clone attached to a different Heroku app:

$ heroku borealis-pg:restore:execute --app sushi --destination-app my-other-app

The time to clone an add-on database is typically around 10-15 minutes and does not change with the amount of data stored in the source add-on database. However, the time to complete a restore operation does scale roughly proportionally by the amount of data stored in the source add-on database.

Data integrations

Data integrations allow third party services to access an add-on database via a secure tunnel using semi-permanent SSH server and database credentials. Typical uses include extract, transform and load (ETL) services and data warehouses. To register a new data integration, provide the service’s SSH public key to the borealis-pg:integrations:register command:

$ heroku borealis-pg:integrations:register --app sushi --name my_integration1 'ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIMWSUPTT31YQ/vO2dNhtP4TAJqaFne6paL3ibYHk2Iy7'

Run the borealis-pg:integrations:remove command to deregister/remove a data integration that is no longer needed:

$ heroku borealis-pg:integrations:remove --app sushi --name my_integration1

And to see a list of registered data integrations, run the borealis-pg:integrations command:

$ heroku borealis-pg:integrations --app sushi

Updates

Under most circumstances, the Heroku CLI will periodically check for updates and keep the borealis-pg-cli plugin up to date automatically as new releases are published, but you can force it to update the plugin at any time as follows on the command line:

$ heroku plugins:update