BUG #3089: View/Table Creation/Ownership Bug

The following bug has been logged online:

Bug reference:      3089
Logged by:          Andrew White
Email address:      whitea@imsweb.com
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