25

UPDATE: also asked on the PgAdmin-support mailing list here.

So I have an AWS Redshift cluster up and running, and I'm able to connect to it from the command line with

$ psql -h host -d database -p port -U username

I want to connect to the cluster through pgAdmin III, but after I connect to the cluster, I get an error that states:

Error: Must be superuser to view default_tablespace

Note, the fields I entered were: Name, Host, Port, Maintenance DB, Username, and Password, all of which were entered the same as with the psql command.

Now, if I connect via psql, and I check:

$ \tu

This user [the master user granted by AWS] is, in fact, a superuser. So are there any suggestions for what might be the cause of this error?

As a follow up question, when I connect to other remote dbs, I don't get this error even though I am not a superuser, so what is happening here? What about Redshift causes this error to happen when connecting via pgAdmin?

Justin
  • 1,226
  • 4
  • 18
  • 21
  • 1
    They're running a very customised PostgreSQL variant, so I won't be surprised if it isn't perfectly compatible. What does running `SHOW default_tablespace;` do in `psql` when connected to it? In this case I suspect they've bodged the internal permissions checks to hide system details. – Craig Ringer Mar 08 '13 at 03:03
  • From psql it throws the same error! I was able to connect via pgAdmin as of 3 days ago, so perhaps they've changed something since then. Is it possible to query off of the tables in Redshift via pgAdmin despite this permissions issue? I.e. something like disabling pgAdmin from attempting to read the default_tablespace? I don't really understand what I'm saying, but I'm just trying to figure out if it would be possible to hack pgAdmin around this issue. – Justin Mar 08 '13 at 03:10
  • 1
    I haven't taken a look but suspect it'd require source code level changes, but probably not hugely complicated ones; it'd just need to hide its tablespace awareness when it couldn't access the `default_tablespace` GUC. PgAdmin-III is really intended for PostgreSQL not 3rd-party variants of it, but I imagine they'd accept a patch. Try asking on the pgadmin-support mailing list. If you do, link back to this question from your post, and add a link to the archive of your post here. – Craig Ringer Mar 08 '13 at 03:42
  • Thanks Craig, I'll do some digging and try to find a solution! – Justin Mar 08 '13 at 03:51
  • 1
    You probably want to start here http://www.pgadmin.org/download/source.php and here http://www.pgadmin.org/support/list.php . – Craig Ringer Mar 08 '13 at 04:32
  • I've updated your question to link to the pgadmin-support post, and replied to your pgadmin-support post to add a link back to here like I asked you to include in the original email. – Craig Ringer Mar 08 '13 at 12:28
  • Did you see this: http://docs.aws.amazon.com/redshift/latest/gsg/before-you-begin.html –  Mar 08 '13 at 13:44
  • Craig, thanks for adding the link, I spent a while drafting the email and I forgot to include it...I'm sorry about that. a_horse, I did see the doc but I have reasons for trying to connect with pgAdmin specifically. – Justin Mar 08 '13 at 17:01

3 Answers3

17

PGAdmin 1.6.3 (from March 2007!) is working fine for me (on OS X) with Redshift.

This makes sense I guess, given that Redshift was forked from Postgres 8.0.

NOTE: by 'fine' I mean the things that I want from a GUI work OK. There are still a few quirks and error messages to be dismissed.

spazm
  • 4,399
  • 31
  • 30
Joe Harris
  • 13,671
  • 4
  • 47
  • 54
  • Does seem to be an issue with the Postgres version. Unfortunately 1.6.3 won't work for our purposes, but it's good to know what's going on. – Justin Aug 30 '13 at 21:12
3

Try SQLWorkbench. I haven't tried pgadmin, but SqlWorkbench and Postgresql jdbc connector works for me perfectly.

GG.
  • 2,835
  • 5
  • 27
  • 34
  • 1
    I have used SQLWorkbench successfully, but there are other reasons for wanting to connect with pgAdmin. – Justin Mar 08 '13 at 19:29
  • I still this [Data Grip](https://www.jetbrains.com/datagrip/) is the best db gui. It's not free, but totally worth it. – botchniaque Jul 03 '20 at 11:49
3

If all you need is a GUI way to edit a complex SQL and you don't want to copy-paste every time you make a change you can use an editing shortcut

pguser=> \e

This will open the editor of your choice (saved on your machine as $EDITOR)

To overwrite it just for the sessions's keep (if your default is vim but want to use gedit) run this:

$ EDITOR=gedit psql -h host -d database -p port -U username

Edit your SQL using \e then save and quit. It'll run your code. Then just open \e again and it'll populate your editor with previous query.

Enjoy!

Mikhail
  • 8,692
  • 8
  • 56
  • 82