Re: pgAdmin and AWS Redshift Connection problems

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.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company