RE: select * from pgadmin_users; causes error

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 23 February 2001 15:23
> To: jhatfield@g-s.com.au
> Cc: 'PostgreSQL Admin News'; Dave Page
> Subject: Re: [ADMIN] select * from pgadmin_users; causes error
>
>
> John Hatfield <jhatfield@g-s.com.au> writes:
> > It looks as though the views are created the first time you
> login to
> > pgAdmin.  So when I logged first as an ordinary user not as
> the postgres
> > (database superuser), the views were created with this user
> as the owner.
>
> > A trap for beginners!!
>
> Indeed.  I wonder whether pgadmin actually needs to see the password
> column.  If not, perhaps it could make a view of pg_user instead of
> pg_shadow to avoid this problem.  If it does, selecting directly from
> pg_shadow with no view seems like the most reliable way...
>
>             regards, tom lane

pgAdmin uses the view for a couple of reasons:

1) It is one of a set of views that are created and upgraded as required by
pgAdmin to simplify updating pgAdmin if the PostgreSQL system tables change
from version to version (there are loads of queries in pgAdmin, so upgrading
for a new release is far easier if I just have to update one set of views).

2) pgadmin_users includes the OID of the user tuple in pg_users which is not
in pg_shadow.

The problem in question has been fixed in the current dev code of pgAdmin
(as it has been noted as an issue by a couple of other users), soon to be
released as v7.1.0. Basically it isn't so much as the user ID that created
the views that is an issue, it's that fact that pgAdmin then didn't issue a
'GRANT ALL ON pgadmin_users TO PUBLIC'.

It may help to know that all pgAdmin created SSOs (we dubbed them Server
Side Objects 'cos it sounded good!) can be dropped and recreated using the
options on the Advanced Menu. This may be useful when preparing a finished
database for release to a customer for example.

Regards, Dave.