Обсуждение: change owner strange behavior

In order to consolidate two users, user1 and user2, in the database I
changed the owner of all tables and views that user2 owned to be owned
by user1.  I then revoked all remaining privileges from user2 and
dropped the role.  During this process I changed both table1 and view1
(that selects from table1) to be owned by user1.  After this process,
user1 cannot select from view1 with an error saying permission denied
on table1.  user1 can run the query in the definition of the view
without a problem.  Granting select on table1 to public fixes this
problem.  Running create or replace on the view with the same view
definition fixes this problem.  Any idea what could have caused this?
It's almost as if user2 still existed in some settings for the view
and was only cleared out by the create or replace statement.

Thanks,
David

David Galkowski <david.galkowski@gmail.com> writes:
> In order to consolidate two users, user1 and user2, in the database I
> changed the owner of all tables and views that user2 owned to be owned
> by user1.  I then revoked all remaining privileges from user2 and
> dropped the role.  During this process I changed both table1 and view1
> (that selects from table1) to be owned by user1.  After this process,
> user1 cannot select from view1 with an error saying permission denied
> on table1.

Works for me ... what PG version are you dealing with?

            regards, tom lane

8.1.11

On Mon, Aug 9, 2010 at 9:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> David Galkowski <david.galkowski@gmail.com> writes:
>> In order to consolidate two users, user1 and user2, in the database I
>> changed the owner of all tables and views that user2 owned to be owned
>> by user1.  I then revoked all remaining privileges from user2 and
>> dropped the role.  During this process I changed both table1 and view1
>> (that selects from table1) to be owned by user1.  After this process,
>> user1 cannot select from view1 with an error saying permission denied
>> on table1.
>
> Works for me ... what PG version are you dealing with?
>
>                        regards, tom lane
>

David Galkowski <david.galkowski@gmail.com> writes:
> On Mon, Aug 9, 2010 at 9:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Works for me ... what PG version are you dealing with?

> 8.1.11

Oh.  This is something we fixed in 8.2 and beyond:
http://archives.postgresql.org/pgsql-committers/2006-09/msg00101.php

It was not possible to back-patch it because of this:
http://archives.postgresql.org/pgsql-hackers/2006-04/msg01140.php

            regards, tom lane