Thread: select * from pgadmin_users; causes error

select * from pgadmin_users; causes error

From
John Hatfield
Date:
Hi

I'm new to postgresql.

When logged in as postgres (using psql)the following query returns an error
gsmain_test=# select * from pgadmin_users;
ERROR:  pg_shadow: Permission denied.

I can select from pg_user and pg_shadow.

The reason I am trying to run the above query is I had the same error when trying to view users/groups   from pgAdmin.

The database was created using user postgres

My system
     Linux Box
    Postgresql 7.0.3-2 installed from rpms
    Redhat 7.0

     Win98 Box
    pgAdmin v7.0.5
    odbc driver 6.50.00.00
    mdac 2.6

Thanks in advance

John





Re: select * from pgadmin_users; causes error

From
Tom Lane
Date:
John Hatfield <jhatfield@g-s.com.au> writes:
> When logged in as postgres (using psql)the following query returns an error
> gsmain_test=# select * from pgadmin_users;
> ERROR:  pg_shadow: Permission denied.

pgadmin_users is a view, no?  Which user was it created by?  Views
execute with the permissions of the creating user, for reasons that
should be apparent after some thought.

            regards, tom lane

RE: select * from pgadmin_users; causes error

From
John Hatfield
Date:
Yes your right.  Thanks.

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!!

regards

John

-----Original Message-----
From:    Tom Lane
Sent:    Friday, 23 February 2001 3:01 PM
To:    jhatfield@g-s.com.au
Cc:    'PostgreSQL Admin News'
Subject:    Re: [ADMIN] select * from pgadmin_users; causes error

John Hatfield <jhatfield@g-s.com.au> writes:
> When logged in as postgres (using psql)the following query returns an
error
> gsmain_test=# select * from pgadmin_users;
> ERROR:  pg_shadow: Permission denied.

pgadmin_users is a view, no?  Which user was it created by?  Views
execute with the permissions of the creating user, for reasons that
should be apparent after some thought.

            regards, tom lane


Re: select * from pgadmin_users; causes error

From
Tom Lane
Date:
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

Re: select * from pgadmin_users; causes error

From
Tom Lane
Date:
Dave Page <dpage@vale-housing.co.uk> writes:
> 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'.

The particular case being complained of here would not be fixed by that.

            regards, tom lane

Re: select * from pgadmin_users; causes error

From
Tom Lane
Date:
Dave Page <dpage@vale-housing.co.uk> writes:
> 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'.
>>
>> The particular case being complained of here would not be
>> fixed by that.
>>

> What exactly was the problem?

The problem was that pgadmin was first started in a particular database
by a non-privileged user.  So it created the pgadmin_users view as owned
by that non-privileged user.  Then the view does not work, even for the
superuser, because its attempt to access pg_shadow is checked under the
permissions of its owner not of the invoker.

Deleting and recreating the view (to make it owned by the superuser)
will fix this problem, and I'm glad to hear that pgadmin provides a
reasonably painless way to do that.  But the average Joe isn't going to
realize what the problem is or what he has to do to fix it.  It'd be
better if the problem couldn't occur in the first place.  If you really
need a view on pg_shadow, can you postpone creating it until you are run
as superuser?

            regards, tom lane

RE: select * from pgadmin_users; causes error

From
Dave Page
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 23 February 2001 22:29
> To: Dave Page
> Cc: jhatfield@g-s.com.au; 'PostgreSQL Admin News'
> Subject: Re: [ADMIN] select * from pgadmin_users; causes error
>
>
> Dave Page <dpage@vale-housing.co.uk> writes:
> > 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'.
> >>
> >> The particular case being complained of here would not be
> >> fixed by that.
> >>
>
> > What exactly was the problem?
>
> The problem was that pgadmin was first started in a
> particular database
> by a non-privileged user.  So it created the pgadmin_users
> view as owned
> by that non-privileged user.  Then the view does not work,
> even for the
> superuser, because its attempt to access pg_shadow is checked
> under the
> permissions of its owner not of the invoker.

Hmm yes. I've just been playing with it and that is exactly the case.

> Deleting and recreating the view (to make it owned by the superuser)
> will fix this problem, and I'm glad to hear that pgadmin provides a
> reasonably painless way to do that.  But the average Joe
> isn't going to
> realize what the problem is or what he has to do to fix it.  It'd be
> better if the problem couldn't occur in the first place.  If
> you really
> need a view on pg_shadow, can you postpone creating it until
> you are run
> as superuser?

That shouldn't be a major problem, I'll look into it for the next release.

Regards,

Dave.

RE: select * from pgadmin_users; causes error

From
Dave Page
Date:

> -----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.

RE: select * from pgadmin_users; causes error

From
Dave Page
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 23 February 2001 21:07
> To: Dave Page
> Cc: jhatfield@g-s.com.au; 'PostgreSQL Admin News'
> Subject: Re: [ADMIN] select * from pgadmin_users; causes error
>
>
> Dave Page <dpage@vale-housing.co.uk> writes:
> > 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'.
>
> The particular case being complained of here would not be
> fixed by that.
>

What exactly was the problem? I must have missed an earlier part of the
thread as I don't read pgsql-admin - I barely get time to read ODBC &
Interfaces :-(

Regards, Dave.

how to reload a function

From
Jie Liang
Date:
I have 2 plpgsql defined functions, say:

create function A(....) returns ... as'
.....
'language 'plpgsql';

create function B(....) returns ... as '
declare
....
begin
  select A(..) into myvar from ....
  ....
end;
'language 'plpgsql';

If I modify function A (drop && re_create), then I have to re_create
function B though no change to function B.

Is there any way (sql stmt) let me re_load function B's defination
without using drop and create??


Thanks.




Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com



Re: how to reload a function

From
"Thomas F. O'Connell"
Date:
> If I modify function A (drop && re_create), then I have to re_create
> function B though no change to function B.
>
> Is there any way (sql stmt) let me re_load function B's defination
> without using drop and create??

i have not figured out a way to do anything like this. an additional
frustration is the postgres documentation's suggestion as a solution
to their not having implemented ALTER TABLE DROP COLUMN to do the
following (using an example table "distributors"):

    CREATE TABLE temp AS SELECT did, city FROM distributors;
    DROP TABLE distributors;
    CREATE TABLE distributors (
        did      DECIMAL(3)  DEFAULT 1,
        name     VARCHAR(40) NOT NULL
    );
    INSERT INTO distributors SELECT * FROM temp;
    DROP TABLE temp;

unfortunately, this doesn't restore any triggers on the table and
causes the function manager to complain the next time any functions
referencing this table are run.

my solution has been to write an extensive perl library with the following
functions:

regenerate_table
regenerate_function
regenerate_trigger
regenerate_view

each of these has the intelligence to regenerate any of the dependent parts
as necessary.

for instance, if i were to use regenerate_function( A ) from your example,
my library would recognize that it needed also to regenerate_function( B ).

unfortunately, in order to do this, i have found that i pretty much need to
keep my database schema on disk in the form of text files since i edit
tables, functions, triggers, and views so frequently.

i know this doesn't really answer your question, but i'm letting you know of
the workaround i came up with in the hope that anyone else who sees this
might have an even better alternative.

the short answer to your question is "no", as far as i can tell.

-tfo