Postgresql – Connecting pgAdmin3 to Postgres on Heroku

pgadminpostgresql

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.

Best Answer

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!

Related Question