Heroku Postgres Credentials

Last updated 17 August 2018

Postgres manages database access using the concept of roles. Roles can be granted (and have revoked) specific privileges that define what they can do when connected to the database.

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.

Credentials can be managed from data.heroku.com or from the Heroku CLI. Credentials are available only to production-class plans (Standard, Premium, Private, and Shield) on Postgres 9.6 and above. Ineligible plans include only the default credential, which cannot create other credentials or manage 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:

• GRANT privileges to other credentials
• Run SELECT on tables in the database
• INSERT new data
• UPDATE data
• DELETE data from tables
• On Postgres 10, view some usage statistics and monitoring data

Creating a new credential

You can create new credentials through both the Heroku CLI and through data.heroku.com.

To create the credential through data.heroku.com, select the Credentials tab and click the Create Credential button. You will be able to configure an initial set of database privileges for the role (see Managing Permissions below).

You can also create the credential with the pg:credentials:create CLI command:

$ heroku pg:credentials:create postgresql-sunny-1234 --name limited_user -a sushi
Creating credential limited_user... done

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.

In both cases, the password for the credential is generated randomly by Heroku.

Credentials created via the CLI can be used to log in to the database, but it cannot read from or write to any of your tables.

Managing permissions

You can configure permissions for new and existing credentials through both the Heroku CLI and the data.heroku.com.

To configure the credential through data.heroku.com, either select one of the different access levels below when creating the credential, or 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, plus the ability to generate values from sequences

Note that “table” above also includes views, materialized views, and foreign tables. The privileges configured also apply to any tables that may be created in the future (until the credential is reconfigured with a different set of permissions).

Note also that all users are able to read the system catalogs in the information_schema and pg_catalog schemas that describe the structure of the database and basic statistics—these tables and views are not subject to the above permissions.

None of the built-in permissions levels have access to create new tables or other database objects.

To configure privileges via the CLI, use the default credential to log in to the psql console, and run standard Postgres GRANT, REVOKE, and ALTER DEFAULT PRIVILEGES commands.

For example, you can grant the limited_user credential from above the privileges for read-only access to the public schema. This 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;
GRANT
sushi::CYAN=> ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO limited_user;
ALTER PRIVILEGES
sushi::CYAN=> \q

In this example, the limited_user credential has been granted the privileges necessary 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.

Breaking this down step-by-step:

GRANT USAGE ON SCHEMA PUBLIC TO limited_user;

Allow the credential to look up tables and other database objects within the schema

GRANT SELECT ON ALL TABLES IN SCHEMA public TO limited_user;

Allow the credential to run SELECT on any table, view, materialized view, or foreign table in the schema “public” (the default schema). If you are using additional schemas and want to grant access to those, too, 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 cannot 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;

And finally:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO limited_user;

Allow the credential to automatically receive SELECT privileges on any new table, view, materialized view, or foreign table created by the default user in the schema “public”. The ALTER DEFAULT PRIVILEGES command allows you to pre-configure privileges for tables and other database objects that are yet to be 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;

Make sure that if you want to limit a role’s privileges, you revoke existing access before granting the more limited privileges. For example, to change the privileges of a user who previously had been granted SELECT and 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;

For more information on how to configure credentials, please see the Postgres Community Documentation on roles, privileges, and the GRANT, REVOKE, and ALTER DEFAULT PRIVILEGES commands.

Attaching credentials to apps

addons:attach

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 HEROKU_POSTGRESQL_[COLOR]_URL.

addons:detach

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

Managing credentials

The Heroku CLI provides several commands for managing your Postgres credentials. This functionality is also available through the Credentials tab on the data.heroku.com.

pg:credentials

Use the 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

Use the 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

When a credential is first created, it only has the CONNECT privilege. See Managing permissions to learn how to configure access for a new credential.

pg:credentials:destroy

Remove a credential from Postgres with the pg:credentials:destroy command:

$ 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.

pg:credentials:rotate

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. The 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. 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
       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 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.

Force rotation

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

The 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:98kd8a9@ec2-123-73-145-214.compute-1.amazonaws.com:6212/dee932clc3mg8h

If you do not provide a value for the --name argument, the connection details for the default credential are printed to stdout.

pg:credentials:repair-default

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

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 readonly and cannot be written to by any role, even if it has write permissions. If you run the unfollow command, the follower will become a fork and any credentials with appropriate permissions will be able to write.

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.

Best practices

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.

Read-only users

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 INSERT, UPDATE or 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 as above. Then, 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

See Managing permissions above for more information about the details of the statements and the permissions granted.

Caveats and considerations

• Permissions configuration is not permanent–they can be updated at any time by the default credential or any other credential that has WITH GRANT OPTION privileges.
• The permissions management in data.heroku.com assumes the default credential creates all tables and other database objects; creating objects with other credentials may mean users are not 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 ROLE and associated DDL statements directly, but otherwise behave like standard Postgres credentials.
• Credential names are restricted to alphanumeric characters (- and _ are also supported) and can be no longer than 50 characters.