Thread: 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