10

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.

1
  • pgadmin.org/docs/1.8/connect.html says: The maintenance DB field is used to specify the initial database that pgAdmin connects to, and that will be expected to have the pgAgent schema and adminpack objects installed (both optional). On PostgreSQL 8.1 and above, the maintenance DB is normally called ‘postgres’ - and that is which pgAdmin tried to connect to. Provide the password of postgres user.
    – dezso
    Aug 2, 2012 at 10:24

3 Answers 3

6

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!

1
  • but this opned the 1368 databases. even if i have added Maintenance DB as my db name
    – Sunil Garg
    Dec 20, 2017 at 18:48
3

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

2

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.

1
  • now heroku pg:credentials:url
    – pixelearth
    Sep 28, 2022 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.