The following bug has been logged online: Bug reference: 3089 Logged by: Andrew White Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: SuSE Linux 9 Description: View/Table Creation/Ownership Bug Details:
To Whom It May Concern, I came across an interesting issue regarding views and ownership that I think may be a bug in PG. I am using PG 8.2.3 on SuSE Linux. In short: The problem I am having is that I can create a table that is owned by one role (role A), then create a view owned by another role (role B) that selects from the table I just created, grant rights to that view to role A and get an error trying to select from it (ERROR: permission denied for relation table_a SQL state: 42501) In long: Here are a set of steps one can take to reproduce what I am seeing 0) Create 2 roles as such: CREATE ROLE view_ownership_test_A NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE; CREATE ROLE view_ownership_test_B NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE; 1) Create a user and grant them view_ownership_test_A 2) Create a new database as such: CREATE DATABASE view_ownership_test WITH OWNER = view_ownership_test_A ENCODING = 'UTF8' TABLESPACE = pg_default; GRANT ALL ON DATABASE view_ownership_test TO view_ownership_test_A; GRANT ALL ON DATABASE view_ownership_test TO view_ownership_test_B; 3) Connect to view_ownership_test as SU 4) Create a table, set it's ownership to view_ownership_test_A and fill in some test data as such: create table table_A (mykey serial, myname varchar(50), myage int4); ALTER TABLE table_A OWNER TO view_ownership_test_A; GRANT ALL ON TABLE table_A TO view_ownership_test_A; insert into table_A (myname, myage) select 'Homer Simpson', 42; insert into table_A (myname, myage) select 'Peter Griffin', 43; insert into table_A (myname, myage) select 'Phillip J. Fry', 27; 5) Create a view selecting from that table as such: CREATE OR REPLACE VIEW lkup_table_A AS SELECT * FROM ONLY table_A; 6) Set ownership of this view and rights as such: ALTER TABLE lkup_table_A OWNER TO view_ownership_test_B; GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE lkup_table_A TO view_ownership_test_B; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE lkup_table_A TO view_ownership_test_A; 7) Connect to view_ownership_test as connect as someone in group view_ownership_test_A 8) Test selecting from the table created in step 4: select * from table_A; --Notice it succeeds as expected 9) Test selecting from the view created in step 5: select * from lkup_table_A; --(ERROR: permission denied for relation table_a SQL state: 42501) --Notice it fails despite that fact that by being in the role of view_ownership_test_A you own the table, have full rights to the table and have full rights to the view What I think is happening: It appears that PG allows you to create a view selecting from a table you do not have rights to. When someone who does have rights to both the table and your view uses the view it fails. I am not sure if the creation of the view should not be allowed (or warned) or if the person selecting from it should be where in the program the rights are evaluated but the current way seems to be a bug to me. Thanks, Andrew White ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster