BUG #5214: Permission troubles for views

Started by Nikolayover 16 years ago2 messagesbugs
Jump to latest
#1Nikolay
wheelly@gmail.com

The following bug has been logged online:

Bug reference: 5214
Logged by: Nikolay
Email address: wheelly@gmail.com
PostgreSQL version: 8.4.1
Operating system: Gentoo Linux
Description: Permission troubles for views
Details:

There is two users in database: user_a and user_b.
user_a is an owner of current schema.

Table "tbl_data":
ALTER TABLE "tbl_data" OWNER TO user_a;
REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE "tbl_data" FROM user_a;
GRANT REFERENCES, TRIGGER ON TABLE "tbl_data" TO user_a;
GRANT ALL ON TABLE "tbl_data" TO user_b;

Execute query as user_a:
SELECT * FROM "tbl_data"; - permission denied for relation tbl_data.
This is correct. user_a can't select from table tbl_data.

Execute query as user_b:
SELECT * FROM "tbl_data"; - returns rows from table.
This is correct. user_b can select from table tbl_data.

View "vw_data":
CREATE VIEW "vw_data" as select * from "tbl_data";
ALTER TABLE "vw_data" OWNER TO user_a;
REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE "vw_data" FROM user_a;
GRANT ALL ON TABLE "vw_data" TO user_b;

Execute query as user_a:
SELECT * FROM "vw_data"; - permission denied for relation vw_data. This is
correct.

Execute query as user_b:
SELECT * FROM "vw_data"; - permission denied for relation tbl_data. But
permissions say that user_b can select from tbl_data and from vw_data.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nikolay (#1)
Re: BUG #5214: Permission troubles for views

"Nikolay" <wheelly@gmail.com> writes:

Description: Permission troubles for views

SELECT * FROM "vw_data"; - permission denied for relation tbl_data. But
permissions say that user_b can select from tbl_data and from vw_data.

This is not a bug. The view's reference to tbl_data is controlled by
the permissions of the owner of the view. If it didn't work like that,
a view couldn't be used as a permissions gateway.

regards, tom lane