Heroku Postgres Credentials
Last updated 12 September 2017
Table of Contents
Postgres manages database access permissions using the concept of roles. Heroku Postgres provides a management layer around these roles called credentials. Each credential corresponds to a different Postgres role and has a distinct set of database permissions.
Heroku Postgres credentials can be managed from data.heroku.com or from the Heroku CLI.
Heroku Postgres credentials are available only to production-class plans (Standard, Premium, Private, and Shield) on Postgres 9.6 and above. Hobby-tier plans include only the default credential, which cannot create other credentials or grant permissions.
The default credential
Every newly provisioned Heroku Postgres database includes a default credential. This credential corresponds to a permissive role that is one step below the superuser. It has the ability to:
GRANTprivileges to other credentials
SELECTon tables in the database
DELETEdata from tables
The default credential’s associated username is a randomly generated set of characters that cannot be changed or altered after the database is provisioned. This requirement preserves Heroku’s ability to define and run button apps on the platform that require the use of a Heroku Postgres database.
Creating a new credential
Creating a credential is a two-step process.
Step 1: Create the credential
You create a container for the credential with the
$ heroku pg:credentials:create postgresql-sunny-1234 --name limited_user -a sushi Creating credential limited_user... done
You must specify a name for the credential in the command’s
--name argument. The name should reflect the purpose of the credential. In the above example,
limited_user is used as the credential’s username when connecting to the database. The password for the credential is generated randomly by the Heroku Postgres orchestration system.
At this point, the credential has been created, but it doesn’t have permissions to do anything in the database besides log in to it.
Step 2: Set the credential’s privileges
Now that you’ve created a new credential, you can set privileges for it.
Use the default credential to log in to the
psql console and grant the
limited_user credential the appropriate permissions. This action follows standard Postgres conventions:
$ heroku pg:psql postgresql-sunny-1234 -a sushi --> Connecting to postgresql-sunny-1234 psql (9.6.1, server 9.6.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. sushi::CYAN=> GRANT USAGE ON SCHEMA PUBLIC TO limited_user; GRANT sushi::CYAN=> GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO limited_user; -- give access to the existing tables GRANT sushi::CYAN=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO limited_user; -- give access to future tables ALTER PRIVILEGES sushi::CYAN=> \q
In this example, the
limited_user credential has been granted permission to run read-only queries on any of the tables within the schema called
public. At this point,
limited_user can be used to log into the database and interact with the information within it.
For more information on different use cases and how to set them up, please see the Use Cases and Best Practices within this document as well as the Postgres Community Documentation.
Managing Postgres credentials with the Heroku CLI
The Heroku CLI provides several commands for managing your Postgres credentials:
pg:credentials command to list the name and state of every credential that has been created for a particular database:
$ heroku pg:credentials DATABASE_URL -a sushi Credential State ────────── ────── default active analyst active
The database in the example above has one custom credential (
analyst), along with the default credential.
Possible credential states (such as
active in the example above) are covered in Credential rotation state details.
pg:credentials:create command to create a new credential for the database:
$ heroku pg:credentials:create DATABASE_URL --name analyst -a sushi Creating credential analyst.... done
You must specify a name for the credential in the command’s
--name argument. The name should reflect the purpose of the credential. If you do not provide a name, the command fails with an error:
$ heroku pg:credentials:create DATABASE_URL -a sushi ▸ Required flag: --name NAME
When a credential is first created, it only has the
CONNECT permission. This means the credential will only be able to log into the database and will not see any table or information in Postgres. Open the Postgres console with
heroku pg:psql to grant the credential the appropriate permissions.
Remove a credential from Postgres with the
$ heroku pg:credentials:destroy DATABASE --name analyst -a sushi ▸ WARNING: Destructive action ▸ To proceed, type sushi or re-run this command with --confirm sushi > sushi Destroying credential analyst... done The credential has been destroyed within postgresql-solid-58569 and detached from all apps. Database objects owned by analyst will be assigned to the default credential
Confirmation is required to remove credentials from the database. If the credential has any attachments, it must be detached from those applications before it can be destroyed.
See the help for
heroku addons:detach for more information on how to remove a credential from a secondary application.
In some cases, you’ll need to replace the passwords associated with your credentials. In general, rotating credentials on a regular basis is a good security practice.
pg:credentials:rotate command lets you change some or all of the credentials within the database at one time. When a credential rotation occurs, Heroku Postgres notifies all affected apps and issues a connection restart to get the new credentials.
Rotating a single credential:
$ heroku pg:credentials:rotate DATABASE --name analyst -a sushi ▸ WARNING: Destructive action ▸ To proceed, type sushi or re-run this command with --confirm sushi > sushi Rotating analyst on postgresql-solid-58569... done
If you don’t provide a value for the
--name argument, the default credential is rotated:
$ heroku pg:credentials:rotate DATABASE -a sushi ▸ WARNING: Destructive action ▸ To proceed, type sushi or re-run this command with --confirm sushi > sushi Rotating default on postgresql-solid-58569... done
To rotate all credentials, pass the
--all flag into the command:
$ heroku pg:credentials:rotate DATABASE --all -a sushi ▸ WARNING: Destructive action ▸ To proceed, type sushi or re-run this command with --confirm neovintage > sushi Rotating all credentials on postgresql-solid-58569... done
Credential rotation state details
When a credential rotation is requested, applications might have open connections to the database. To let in-progress transactions finish, Heroku Postgres waits approximately 30 minutes before killing existing connections and requiring clients to use new login details.
If in-progress transactions exist during a credential rotation, Heroku Postgres provisions temporary “rotating” usernames alongside the usernames currently in use. During the rotation, the
pg:credentials command displays these usernames, which all end with
-rotating to indicate that they are temporary:
$ heroku pg:credentials:rotate DATABASE --name analyst -a sushi --confirm Rotating analyst on postgresql-solid-58569... done $ heroku pg:credentials -a sushi Credential State ──────────────────────────────────────────── ────────── default created analyst rotating
After these in-progress transactions complete and their associated connections are closed, the “rotating” username is renamed to the original username. Any connections that were created during the rotation are allowed to stop before this rename occurs.
In some cases, waiting for 30 minutes for connections to close may be too long. In those circumstances, the
--force flag could be used.
--force will immediately kill all connections to the credential and perform the rotation.
$ heroku pg:credentials:rotate DATABASE --name analyst --force -a sushi --confirm Rotating analyst on postgresql-solid-58569... done
pg:credentials:url command provides convenient access to your database’s location and login credentials so you can access it with any number of visualization tools:
$ heroku pg:credentials:url DATABASE --name analyst -a sushi Connection information for analyst credential Connection info string: "dbname=dee932clc3mg8h host=ec2-123-73-145-214.compute-1.amazonaws.com port=6212 user=analyst password=98kd8a9 sslmode=require" Connection URL: postgres://analyst:firstname.lastname@example.org:6212/dee932clc3mg8h
If you do not provide a value for the
--name argument, the connection details for the default credential are printed to stdout.
Use the pg:credentials:repair-default command to restore your database’s default credential in the event that default permissions or database object ownership are accidentally altered. This makes the default credential the owner of all objects in the database, restores default permissions, and grants the default credential admin option on all additional credentials in the database.
$ heroku pg:credentials:repair-default DATABASE -a sushi Resetting permissions and object ownership for default role to factory settings... done
Once credentials have been created within Heroku Postgres, they can be attached to apps as a config var via the
addons:attach command. Assume that new credential has been created:
$ heroku pg:credentials:create postgresql-sunny-1234 --name analyst -a sushi Creating credential analyst.... done
Now that the analyst credential has been created, it can be attached to any number of applications within Heroku. The application can be the billing application or any other application that may need access:
$ heroku addons:attach postgresql-sunny-1234 --credential analyst -a sushi Attaching analyst of postgresql-sunny-1234 to ⬢ sushi... done Setting DATABASE config vars and restarting ⬢ sushi... done, v24
If no other Heroku Postgres exist as part of the application that the database is being attached to, the credential will be assigned to the
DATABASE_URL config var. If
DATABASE_URL already exists on the application, the credential will be attached to the application using the format
If the credential on the application is not needed, the credential must be detached from the application. Assuming a credential called
analyst had been created and attached to the sushi application, to remove it, the
addons:detach command must be used in conjunction with the config var containing the credential:
$ heroku addons:detach DATABASE -a sushi Detaching DATABASE from postgresql-sunny-1234 from ⬢ sushi... done Unsetting DATABASE config vars and restarting ⬢ sushi... done, v23
Forks and followers
Follower databases on Heroku Postgres are (almost) exact copies of their corresponding primary database. As changes are made to the primary database, those changes are streamed to the follower in real time. Consequently, any credentials created on a primary database automatically cascade to the follower.
Follower database credentials match primary database credentials exactly, with the following exception:
If a primary database credential has both read and write access to a table, the corresponding follower database credential only has read access to that table.
If credential commands like create, destroy or rotate are run against a follower, an error message will be given indicating the command cannot run against the database:
$ heroku pg:credentials:create postgresql-moonlight-5678 -a sushi This operation is not supported for follower databases.
A fork is an entirely new database that contains a snapshot of the data from another existing Heroku Postgres database. Unlike a follower database, a fork does not stay up to date with the primary database and is therefore writeable.
When a fork is created, any credentials in the primary database at the time of the fork will be included in the fork. However, future modifications to primary database credentials will not be reflected in the fork.
Logging and Postgres credentials
Whenever Heroku Postgres emits logs for a user action, those logs include the username for the credential that is performing the action, unless the default credential is performing it.
Note that Heroku Postgres does not log all user actions, because doing so might negatively affect database performance. For more information on database logs that might include an associated username, see Understanding Heroku Postgres Logs.
Using the default credential
Applications that use Heroku Postgres as their data store should use the default credential when creating connections to the database. This goes for any schema modifications as well. The default credential is meant to work seamlessly with the entirety of the Heroku platform.
A common pattern for credentials within Postgres is to give a user read-only access to all of the information within the schema. This means the credential will not be able to
DELETE data or schema information within Postgres. To create a read-only credential within Heroku Postgres, it’s a two step process:
Step 1: Create the credential
Assume that we’re working with a Heroku Postgres database that has already been provisioned with an addon name of
$ heroku pg:credentials:create postgresql-sunny-1234 --name analyst -a sushi Creating credential analyst... done
Step 2: Assign the correct
Using the default credential, log into the Postgres console for the database:
$ heroku pg:psql postgresql-sunny-1234 -a sushi --> Connecting to postgresql-sunny-1234 psql (9.6.1, server 9.6.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. sushi::CYAN=> GRANT USAGE ON SCHEMA PUBLIC TO analyst; GRANT sushi::CYAN=> GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO analyst; -- give access to the existing tables GRANT sushi::CYAN=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst; -- give access to future tables ALTER PRIVILEGES
GRANT SELECT will give the
analyst role read-only access to all of the tables, view and data that exists within the public schema. If you have other schemas within Postgres, each schema will need to be named within the
GRANT command separated by commas.
Note that the read-only permissions will only apply to data, tables and objects that have been created within the database prior to issuing the
Optionally, the following
GRANT statement will allow for read-only access to data and objects created after initially creating the read-only credential:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;
This statement must be run as the default credential and each schema that exists within the database must be separated by commas.
Caveats & Considerations
- Heroku Postgres Credentials are not fully managed credentials within Postgres. This means that any objects that are created will not automatically follow any previously defined behavior.
- Credentials are required to be initially created outside of Postgres so that Heroku Postgres control plane and Heroku platform know that credentials exist.
- Irrespective of creating and removing users from Postgres, permissions for Heroku follow typical Postgres permissions and role conventions.
- Credential names are restricted to alphanumeric characters (- and _ are also supported) and can be no longer than 50 characters.