Connect to Heroku Postgres from your Mac

What you need

• The Heroku CLI, signed in, or access to the app in the Heroku Dashboard.
• A database on the Essential, Advanced, Standard or Premium tier. Private and Shield databases can’t be reached directly from outside their Private or Shield space; Heroku has a separate guide for those (mutual TLS and trusted IP ranges).

Find your connection details

From the CLI (use heroku data:pg:credentials:url for Advanced databases):

heroku pg:credentials:url DATABASE -a <app>

Heroku’s example output, with the values replaced:

Connection information for default credential.
Connection info string:
  "dbname=<database> host=<host> port=5432 user=<user> password=... sslmode=require"
Connection URL:
  postgres://<user>:<password>@<host>:5432/<database>

From the Dashboard: open the database from the Datastores tab or your app’s Resources list, select the Credentials tab, click Edit on a credential, and copy the string from the URI section.

Heroku also puts the URL in your app’s config vars (DATABASE_URL, or a HEROKU_POSTGRESQL_…_URL name); heroku config | grep postgres lists them.

The values can change at any time, for example after a failover or a credential rotation, so don’t treat a copied URL as permanent.

Credentials and privileges. The default credential owns the database and sits one step below a superuser. You can create custom credentials with fewer privileges, which is a good choice for browsing production.

Connection pooling (Standard, Premium, Private and Shield tiers): attaching it with heroku pg:connection-pooling:attach DATABASE_URL --as DATABASE_CONNECTION_POOL -a <app> adds DATABASE_CONNECTION_POOL_URL, which points at PgBouncer on port 5433 in transaction mode. The pool runs on the database server, so external clients can share it.

Connection string

postgres://<user>:<password>@<host>:5432/<database>?sslmode=require

Heroku’s URLs use the postgres:// scheme, which PostgreSQL clients accept like postgresql://. See PostgreSQL connection strings.

TLS

Heroku Postgres requires TLS 1.2 or later on every connection: use sslmode=require at least.

Essential and Advanced databases use certificates issued by AWS RDS. To verify them from your Mac, download the AWS RDS global CA bundle (macOS doesn’t include it) and use sslmode=verify-full, or verify-ca if you only want the certificate checked, not the host name:

curl -O https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

Heroku’s connection article covers verification only for Essential and Advanced databases; for the classic tiers it asks for sslmode=require.

Connect with Inlet

1. Run heroku pg:credentials:url and paste the URL into Inlet: it fills in the connection form. If your project’s .env file has a DATABASE_URL, Inlet can import the connection from it. Or choose New Connection and enter the fields.
2. Under TLS, choose verify-full and select global-bundle.pem as the CA file (Essential and Advanced). On classic tiers, require.
3. Keep the password in the Keychain, or have Inlet ask every time. If Heroku rotates the credentials, Inlet’s connection window asks for the new password when the old one is refused; if the host or user changed too, paste the new URL.
4. Tag the environment. A production connection opens read-only, so the server refuses writes until you unlock it for ten minutes.

Connect on port 5432 for production, not the pool on 5433. Inlet’s read-only mode is a session setting, and through PgBouncer in transaction mode each transaction can use a different server connection; PgBouncer doesn’t reset session state in that mode either. So the setting isn’t guaranteed to hold through the pool. A custom credential with read-only privileges is the server-side way to make sure nothing gets written.

Connect from the command line

The Heroku CLI opens psql for you (it needs PostgreSQL installed locally; use heroku data:pg:psql for Advanced databases):

heroku pg:psql -a <app>

Heroku’s example output, with the database name replaced:

--> Connecting to <database>
psql (17.2 (Postgres.app), server 16.9 (Ubuntu 16.9-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)
Type "help" for help.

Or connect with the URL and certificate checks:

psql "postgres://<user>:<password>@<host>:5432/<database>?sslmode=verify-full&sslrootcert=global-bundle.pem"

Troubleshooting

• password authentication failed on a connection that used to work: the credentials changed. Fetch the URL again.
• Refused without TLS: Heroku requires it. Set sslmode=require or stricter.
• The authentication type 10 is not supported or SCRAM authentication is not supported by this driver on an Essential database: your driver doesn’t support SCRAM password authentication. Update it; Heroku has a help article on this.
• A Private or Shield database is unreachable: it can’t be reached directly from outside its space. Follow Heroku’s guide for external access to those tiers.
• Too many connections: each plan has a connection limit. Attach connection pooling for your app (Standard tier and up).