Connect to a heroku database with pgadmin

Question

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?

score 360 · Accepted Answer · edited Apr 28 '13 at 15:07

360

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.

edited Apr 28 '13 at 15:07

JJD

50,076
60
203
339

answered Aug 02 '12 at 09:56

araqnid

127,052
24
157
134

9

As [suggested in another answer](http://stackoverflow.com/a/11769894/26510), 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 Jan 09 '15 at 19:24
if you have Rails App you can access DB using https://github.com/igorkasyanchuk/rails_db – Igor Kasyanchuk Oct 24 '15 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 Oct 25 '16 at 16:22
3

This just stopped working for me? Does it still work for anyone? – Rune Jeppesen Aug 22 '17 at 18:13
@Ced yeah me too now - Windows had decided to turn on my firewall and block that port :-/ – Rune Jeppesen Sep 11 '17 at 11:25
@RuneJeppesen, Heroku says they can update the server connection properties (database name/username/password) any time. – VikR May 04 '18 at 20:13

score 120 · Answer 2 · edited May 23 '17 at 12:10

120

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

edited May 23 '17 at 12:10

Community

1
1

answered Mar 10 '13 at 14:24

cm12

1,211
1
8
4

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 Dec 02 '15 at 00:05
3

Thank you! And as its update mentions, in pgAdmin 4, do not add quotes. – Ryan Oct 26 '20 at 18:06

score 97 · Answer 3 · edited Oct 07 '21 at 05:12

97

This is for pgAdmin 4

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

Login to Heroku, and select the application in which you have the database
Select the Resources tab and then click on "Heroku Postgres Ad-on" (see below). This will open up a new tab.
Select the Settings tab and then click on "View Credentials..." (see below)

You will get the following information that you will use in pgAdmin:
Go to pgAdmin, and create a new server
In the General tab, give a useful name
In the Connection tab, fill the info you got at Heroku
In order to avoid seeing thousands of databases, you need to add your database name to DB restriction in the Advanced tab (see below)

edited Oct 07 '21 at 05:12

Sabito stands with Ukraine

4,271
8
34
56

answered Jul 23 '20 at 03:48

lmiguelvargasf

63,191
45
217
228

3

i'm getting Unable to connect to server: FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege. – nassim Apr 21 '21 at 11:38
1

thank you so very much, i was totally missing the last bit with the advanced tab! – Rodrigo Merlone Feb 13 '22 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 Apr 13 '22 at 15:50

score 22 · Answer 4 · edited Oct 07 '21 at 05:11

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.

edited Oct 07 '21 at 05:11

Sabito stands with Ukraine

4,271
8
34
56

answered Aug 02 '12 at 01:35

hgmnz

13,208
4
37
41

2

Do I need any kind of files/keys to force SSL? (excuse me for the ignorance) – Tomas Romero Aug 02 '12 at 02: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] http://www.pgadmin.org/docs/dev/connect.html – hgmnz Aug 02 '12 at 03: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 Aug 02 '12 at 17:59
In order to change the properties, disconnect to the server first – Aditya Rajgor Jan 03 '22 at 08:10

score 11 · Answer 5 · edited May 09 '16 at 23:46

11

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

edited May 09 '16 at 23:46

typeoneerror

55,990
32
132
223

answered Aug 29 '12 at 18:51

ravi

111
2

score 0 · Answer 6 · answered Oct 14 '22 at 15:24

0

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

answered Oct 14 '22 at 15:24

CHarlie83CL

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](/help/how-to-answer). – Community Oct 21 '22 at 08:38

Connect to a heroku database with pgadmin

6 Answers6

This is for pgAdmin 4

Linked