Re: pgAdmin and AWS Redshift Connection problems

On 03/08/2013 08:00 PM, Dave Page wrote:
> Hi
>
> On Fri, Mar 8, 2013 at 7:04 AM, Justin Yan <justin(dot)d(dot)yan(at)gmail(dot)com> wrote:
>> Hello!
>>
>> AWS Redshift, while proprietary, allows one to connect through any client
>> that utilizes Postgres 8.0 drivers. Approximately a week ago, I tested
>> connecting to a Redshift cluster through pgAdmin v1.12.3 [for postgres 8.0]
>> on Ubuntu through the master user which AWS provides, which has superuser
>> privileges. So:
>>
>> host = hostname which AWS provides
>> maintenance db = default db name
>> user = aws superuser
>> pw = my pw
>> port = 5439 [the port redshift uses]
>>
>> And about a week ago, this all worked. I was able to run queries, see the
>> results, the whole shebang. As of a few days ago, however, pgAdmin stopped
>> working. I would use the exact some information to connect, and it would
>> appear to be connected. However, there would be no databases [it said
>> databases(0)], and when I tried to expand the databases in the explorer, I
>> would get the error "ERROR: must be superuser to examine
>> default_tablespace". I thought, okay, that's strange, so I tried using psql
>> from the command line on Ubuntu:
>>
>> $ psql -h hostname -d dbname -p 5439 -U superuser
>>
>> and I was able to get in, run queries, create tables, all that nice stuff.
>> What I couldn't do was run the command SHOW default_tablespace; - that
>> returned the error:
>>
>> ERROR: must be superuser to examine "default_tablespace"
>>
>> I found it slightly weird, because if you run the \du command, it says:
>>
>> Role name | Attributes | Member of
>> -----------+----------------------+-----------
>> myuser | Superuser, Create DB |
>>
>> After asking around, it was chalked up to AWS tweaking the internal workings
>> to prevent users from doing certain things, so I don't think there's
>> anything pgAdmin can necessarily do about that. What I am curious about,
>> however:
>>
>> is it possible to get around this in pgAdmin? Why is it I can query the
>> databases in psql, but not pgAdmin, and is it something that can be worked
>> around in pgAdmin?
> pgAdmin queries a lot more than psql as it displays a lot more info
> whether you ask for it or not. It also needs to find out about the
> server it's running against so it knows what default values etc. to
> use in the object dialogues. I haven't checked the code, but in this
> case I suspect it's checking the default tablespace so it can build
> the reverse engineered SQL queries it displays correctly, and to
> display the correct default value in object creation dialogues.
> There's unlikely to be anything you can do about that, without hacking
> pgAdmin around.
This question was originally asked in this Stack Overflow post; I
directed the poster here and asked that they include a link to the SO
question, which is here:

http://stackoverflow.com/q/15285752/398670

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services