222

I would like to manage my Heroku database with pgadmin client. By now, I've been doing this with psql. When I use data from heroku pg:credentials to connect de DB using pgadmin, I obtain:

An error has occurred:

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

How to achieve the connection?

Improve this question
0

6 Answers 6

Reset to default
362

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.

pgAdmin III - New Server Registration

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.

Improve this answer
6
  • 9
    As suggested in another answer, you'll need to enable SSL by choosing "SSL | SSL | require" in the connection dialog as well, or else it will still fail to connect.
    – Brad Parks
    Commented Jan 9, 2015 at 19:24
  • if you have Rails App you can access DB using github.com/igorkasyanchuk/rails_db
    – Igor Kasyanchuk
    Commented Oct 24, 2015 at 22:02
  • I am getting error on pgadmin 3 that the server you are trying to connect is not between 8.4 and 9.3 and it connects but shows a list of 159 databases with error popups all the time.
    – Abhishek
    Commented Oct 25, 2016 at 16:22
  • 3
    This just stopped working for me? Does it still work for anyone?
    – Rune Jeppesen
    Commented Aug 22, 2017 at 18:13
  • @Ced yeah me too now - Windows had decided to turn on my firewall and block that port :-/
    – Rune Jeppesen
    Commented Sep 11, 2017 at 11:25
123

After you change the Maintenance DB name as suggested by araqnid's answer above, you should also add your database to the DB restrictions field because without this you will see thousands of databases and you may not be able to find yours in the list if the list is too long.

More details here - How to hide databases that I am not allowed to access

Improve this answer
2
  • 6
    +1 and when adding the db name, be sure to quote it in single quotes since it will be used to dynamically generate an IN clause to filter the db list.
    – Glenn
    Commented Dec 2, 2015 at 0:05
  • 3
    Thank you! And as its update mentions, in pgAdmin 4, do not add quotes.
    – Ryan
    Commented Oct 26, 2020 at 18:06
105

This is for pgAdmin 4

In order to connect pgAdmin to your database (postgres instance in Heroku), do the following:

  1. Login to Heroku, and select the application in which you have the database enter image description here

  2. Select the Resources tab and then click on "Heroku Postgres Ad-on" (see below). This will open up a new tab. enter image description here

  3. Select the Settings tab and then click on "View Credentials..." (see below) enter image description here

    You will get the following information that you will use in pgAdmin:

    enter image description here

  4. Go to pgAdmin, and create a new server enter image description here

  5. In the General tab, give a useful name enter image description here

  6. In the Connection tab, fill the info you got at Heroku enter image description here

  7. In order to avoid seeing thousands of databases, you need to add your database name to DB restriction in the Advanced tab (see below) enter image description here

Improve this answer
3
  • 3
    i'm getting Unable to connect to server: FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege.
    – nassim
    Commented Apr 21, 2021 at 11:38
  • 1
    thank you so very much, i was totally missing the last bit with the advanced tab!
    – Rodrigo Merlone
    Commented Feb 13, 2022 at 20:31
  • 1
    @nassim Make sure you change the "Maintenance Database" field in the "Connection" tab to the database after the "/" in the URI from Heroku.
    – Jeremy
    Commented Apr 13, 2022 at 15:50
22

We require SSL for connections outside Heroku. Please verify whether you're forcing SSL in your client.

Answered more thoroughly here: Connecting pgAdmin3 to Postgres on Heroku

We don't allow connections to the postgres database, so be sure to set Maintenance DB to your database name, and be sure to use SSL.

Improve this answer
4
  • 2
    Do I need any kind of files/keys to force SSL? (excuse me for the ignorance)
    – Tomas Romero
    Commented Aug 2, 2012 at 2:35
  • typically graphical interfaces have a SSL checkbox somewhere. I don't use pgadmin, but based on the docs[1] it looks like there's an SSL tab in the connection config dialog. [1] pgadmin.org/docs/dev/connect.html
    – hgmnz
    Commented Aug 2, 2012 at 3:58
  • When I clicked SSL tab, it asked me for certificates and a key, and I didn't know the values for them. Although, buy setting the maintenance DB name to my heroku DB name, I could connect configurating only the 'Properties' tab, without having to specify nothing about SSL (this being done apparently automatically)
    – Tomas Romero
    Commented Aug 2, 2012 at 17:59
  • In order to change the properties, disconnect to the server first
    – Aditya Rajgor
    Commented Jan 3, 2022 at 8:10
11

Change the Maintenance Database to the name of your Database, e.g. dva70000p0090. This should work.

Improve this answer
0
0

the db password local isnt the same db password heroku. please check the heroku ip postgtres address and extrac

Improve this answer
1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Oct 21, 2022 at 8:38

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