Heroku Postgres Credentials
Last updated October 17, 2023
Table of Contents
Heroku Postgres provides a management layer around these roles called credentials. Each credential corresponds to a different Postgres role and its specific set of database privileges.
You can manage credentials from data.heroku.com or from the Heroku CLI. Credentials are available only to production plans (Standard, Premium, Private, and Shield). Essential plans only have the
default credential, which can’t create other credentials or manage permissions. Postgres credential passwords are dynamically generated, 65-byte alphanumeric character strings.
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. This credential is the owner of the database and the default
public schema. It can:
- Create new schemas and objects in the database and manage or alter any of the schemas and objects it owns.
GRANTprivileges to other credentials.
- Read statistics and monitoring data. The
defaultcredential is a member of
- Cancel or terminate other non-superuser backend processes. The
defaultcredential is a member of
- Install supported extensions.
Creating a New Credential
You can create credentials through the Heroku CLI and through data.heroku.com.
To create the credential through data.heroku.com:
- Open a Heroku Postgres database.
- Select the
- Click the
You can also create the credential with the
pg:credentials:create CLI command:
$ heroku pg:credentials:create postgresql-sunny-1234 --name limited_user -a example-app Creating credential limited_user... done
name parameter must reflect the purpose of the credential. In the example,
limited_user is the credential’s username when connecting to the database.
In both cases, the credential password is a dynamically generated, 65-byte alphanumeric character string.
Credentials created via the CLI can be used to log in to the database, but they can’t read from or write to any of your tables.
You can configure permissions for new and existing credentials through the Heroku CLI and through data.heroku.com.
To configure the credential through data.heroku.com, select one of the different permission levels below when creating the credential. Or, you can also go to the
Credentials tab, find the credential you want to configure, and select one of the permission levels. The levels are:
- No permissions: no access privileges on any table in the database
- Read-only permissions: read access on every table in the database
- Read-write permissions: read and write access on every table in the database, including the ability to delete data, and the ability to generate values from sequences
The tables also include views, materialized views, and foreign tables. The privileges configured also apply to any tables that are created in the future until the credential is reconfigured with a different set of permissions.
All users can read the system catalogs in the
pg_catalog schemas that describe the structure of the database and basic statistics. These tables and views aren’t subject to the permission levels.
None of the built-in permissions levels have access to create tables or other database objects.
For example, you can grant the
limited_user credential in the example the privileges for read-only access to the public schema. This process follows standard Postgres conventions:
$ heroku pg:psql postgresql-sunny-1234 -a example-app --> Connecting to postgresql-sunny-1234 psql (13.2, server 11.12 (Ubuntu 11.12-1.pgdg16.04+1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. example-app::CYAN=> GRANT USAGE ON SCHEMA PUBLIC TO limited_user; GRANT example-app::CYAN=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO limited_user; GRANT example-app::CYAN=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO limited_user; ALTER PRIVILEGES example-app::CYAN=> \q
In this example, the
limited_user credential has been granted the privileges to run read-only queries on any of the tables within the
public schema. At this point,
limited_user can be used to log into the database and interact with the information within it.
Breaking this down step by step:
GRANT USAGE ON SCHEMA PUBLIC TO limited_user;
This command allows the credential to look up tables and other database objects within the
GRANT SELECT ON ALL TABLES IN SCHEMA public TO limited_user;
This command allows the credential to run
SELECT on any table, view, materialized view, or foreign table in the default
public schema. If you’re using additional schemas and want to grant access to those, you can add them separated by commas:
GRANT SELECT ON ALL TABLES IN SCHEMA public, my_schema TO limited_user;
Each schema must be listed explicitly. You can’t grant access to all schemas at once. You can, however, grant access to only specific tables:
GRANT SELECT ON TABLE public.users TO limited_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO limited_user;
This command allows the credential to automatically receive
SELECT privileges on any new table, view, materialized view, or foreign table created by the default user in the
public schema. The
ALTER DEFAULT PRIVILEGES command allows you to pre-configure privileges for tables and other database objects that haven’t been created. Unlike the above
GRANT, you can also alter the default privileges for all schemas:
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO limited_user;
If you want to limit a role’s privileges, you must revoke existing access before granting more limited privileges. For example, to change the privileges of a user who previously had been granted
UPDATE on tables in the public schema, run:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM limited_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO limited_user;
Attaching Credentials to Apps
After credentials have been created within Heroku Postgres, you can attach them to apps as a config var via the
addons:attach command. Assume that a new credential has been created:
$ heroku pg:credentials:create postgresql-sunny-1234 --name analyst -a example-app Creating credential analyst.... done
Now that the
analyst credential has been created, you can attach it to any number of applications within Heroku. The application can be the billing application or any other application that needs access:
$ heroku addons:attach postgresql-sunny-1234 --credential analyst -a example-app Attaching analyst of postgresql-sunny-1234 to ⬢ example-app... done Setting DATABASE config vars and restarting ⬢ example-app... done, v24
If no other Heroku Postgres exist as part of the application that the database is being attached to, the credential is assigned to the
DATABASE_URL config var. If
DATABASE_URL already exists on the application, the credential is attached to the application using the format
If the credential on the application isn’t needed, the credential must be detached from the application. For example, to remove a credential called
analyst attached to the
example-app app, use the
addons:detach command with the config var containing the credential:
$ heroku addons:detach DATABASE -a example-app Detaching DATABASE from postgresql-sunny-1234 from ⬢ example-app... done Unsetting DATABASE config vars and restarting ⬢ example-app... done, v23
The Heroku CLI provides several commands for managing your Postgres credentials. This functionality is also available through the
Credentials tab on a Postgres database through data.heroku.com.
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 example-app Credential State ────────── ────── default active analyst active
The database in the example has one custom credential (
analyst), along with the
default credential. Possible credential states, such as
active in the example, are covered in Credential Rotation State Details.
pg:credentials:create command to create a credential for the database:
$ heroku pg:credentials:create DATABASE_URL --name analyst -a example-app Creating credential analyst.... done
--name parameter is the name of the credential within the database.
When a credential is first created, it only has the
CONNECT privilege. To learn how to configure access for a new credential, see Managing Permissions.
Remove a credential from Postgres with the
$ heroku pg:credentials:destroy DATABASE --name analyst -a example-app ▸ WARNING: Destructive action ▸ To proceed, type example-app or re-run this command with --confirm example-app > example-app 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, you must detach it from those applications before destroying it. See the Help article on
heroku addons:detach for more information on how to remove a credential from a secondary application.
In some cases, you must 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 credential passwords within the database at one time. When a credential rotation occurs, Heroku Postgres notifies all affected apps and triggers an app restart to get the new credentials. To rotate a single credential:
$ heroku pg:credentials:rotate DATABASE --name analyst -a example-app ▸ WARNING: Destructive action ▸ To proceed, type example-app or re-run this command with --confirm example-app > example-app Rotating analyst on postgresql-solid-58569... done
If you don’t provide a credential name for the
--name argument, the
default credential is rotated:
$ heroku pg:credentials:rotate DATABASE -a example-app ▸ WARNING: Destructive action ▸ To proceed, type example-app or re-run this command with --confirm example-app > example-app 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 example-app ▸ WARNING: Destructive action ▸ To proceed, type example-app or re-run this command with --confirm example-app > example-app Rotating all credentials on postgresql-solid-58569... done
Credential Rotation State Details
When a credential rotation is requested, applications can still have open connections to the database. The old credential expires as soon as there are no existing connections using it. If there are in-progress transactions, Heroku Postgres waits up to 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’re temporary:
$ heroku pg:credentials:rotate DATABASE --name analyst -a example-app --confirm Rotating analyst on postgresql-solid-58569... done $ heroku pg:credentials -a example-app Credential State ──────────────────────────────────────────── ────────── default created analyst rotating Usernames currently active for this credential: analyst-rotating active 0 connections analyst waiting for no connections to be revoked 0 connections
After these in-progress transactions are 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 can be too long. In those circumstances, you can use the
--force immediately kills all connections to the credential and performs the rotation.
$ heroku pg:credentials:rotate DATABASE --name analyst --force -a example-app --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 example-app 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 don’t provide a credential name for the
--name argument, the connection details for the
default credential are printed to stdout.
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 command 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 example-app Resetting permissions and object ownership for default role to factory settings... done
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, but followers are inherently read-only and can’t be written to by any role, even if it has write permissions. If you run the unfollow command, the follower becomes a fork, and any credentials with appropriate permissions are able to write.
If credential commands like create, destroy, or rotate are run against a follower, an error message is given indicating the command can’t run against the database:
$ heroku pg:credentials:create postgresql-moonlight-5678 -a example-app 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 doesn’t 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 are included in the fork. However, future modifications to primary database credentials aren’t reflected in the fork.
Using the Default Credential
Applications that use Heroku Postgres as their data store must 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 isn’t able to
DELETE data or make schema changes. You can create read-only users via the Heroku Data Dashboard Credentials tab, or via the CLI.
To add a read-only user through the CLI, first, create the credential. Then, using the
default credential, log in to the Postgres console for the database:
$ heroku pg:psql postgresql-sunny-1234 -a example-app --> Connecting to postgresql-sunny-1234 psql (13.2, server 11.12 (Ubuntu 11.12-1.pgdg16.04+1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. example-app::CYAN=> GRANT USAGE ON SCHEMA PUBLIC TO analyst; GRANT example-app::CYAN=> GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO analyst; -- give access to the existing tables GRANT example-app::CYAN=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst; -- give access to future tables ALTER PRIVILEGES
See Managing Permissions earlier for more information about the details of the statements and the permissions granted.
Caveats and Considerations
- Permissions configuration isn’t permanent. You can update them at any time by the
defaultcredential or any other credential that has
WITH GRANT OPTIONprivileges.
- The permissions management in data.heroku.com assumes the
defaultcredential creates all tables and other database objects. Creating objects with other credentials can mean users aren’t automatically granted the access their permissions configuration dictates.
- Credentials must be created and destroyed via the CLI or data.heroku.com instead of using
CREATE ROLEand associated DDL statements directly, but otherwise behave like standard Postgres credentials.
- Credential names are restricted to alphanumeric characters (
_are also supported) and can’t be longer than 50 characters.
- Credentials aren’t backed up by PGBackups and aren’t available when restoring from those backups.
The current maximum number of credentials Heroku Postgres supports is 60.