Connecting pgAdmin3 to Postgres on Heroku

Question

I'm running a Heroku Postgres database (Database) in association with a Django app. The docs that refer to the add-on are here. I thought this would be a worthwhile question for the community because Heroku alerted its users today that it will be migrating everything off the shared database add-on that used to be default.

I'm having some trouble connecting pgAdmin3 to the Database.

As per the instructions in the link above, I grab the Database's credentials with the following command:

$ heroku pg:credentials HEROKU_POSTGRESQL_OLIVE

I'm using a homebrew installation of Postgresql on Mac OSX.

In the meantime, I have:

• switched SSL on in my pg_postgresql.conf;
• added the setting: listen_addresses = '*'
• and added host all all 0.0.0.0/0 md5
• and restarted my postgres server.

Then I go to pgAdmin3 and select File > Add Server.

I enter the credentials provided by Heroku as follows (first is Heroku credential name, second is pgAdmin field name):

In the "Properties" Tab:

• dbname into Name;
• host into Host;
• port into Port;
• I leave the pgAdmin field for "Maintenance Database" blank;
• user into Username;
• password into Password;

In the SSL Tab:

• Im the field for SSL, I select "require" (as instructed by Heroku).

I hit enter, and pgAdmin thinks, then throws an error as follows:

An error has occurred:

Error connecting to the server: FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.

Not sure what to do at this point. Would appreciate any pointers.

Answer 1

Thanks to @araqnid on Stack Overflow for the answer here.

Quoted Verbatim from there:

Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to. The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true in your case.

This worked for me!

Answer 2

FATAL: permission denied for database "postgres" indicates that it is trying to connect to the postgres database. We indeed don't grant such privileges. You can connect to your own database though, that should be fine. You can prove that by connecting via the psql CLI.

It seems that you are setting heroku's dbname to something called Name in pgadmin. That seems to be the "connection name" for what you are creating there (something arbitrary to refer to it later), but isn't there a place where you can set the actual database name, so that it does not default to postgres?

I'm not a pgadmin user, but just searching around found docs[1] that specify the following:

The maintenance DB field is used to specify the initial database that pgAdmin connects to.

Could you try that?

[1] http://www.pgadmin.org/docs/dev/connect.html

Answer 3

Run the following command to get the database credentials from Heroku:

heroku pg:credentials DATABASE_URL

Then you can use a GUI tool like PG Commander or PGAdmin to connect to the db.