Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running a Heroku Postgres database (Database) in assosciation 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 occured:

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.

share|improve this question
    
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 '12 at 10:24
add comment

2 Answers

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

share|improve this answer
add comment

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!

share|improve this answer
    
Awesome answer! –  araqnid Nov 16 '12 at 11:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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