Thread: BUG #5146: After granting all select privelges on view to user, who still can not select the view

The following bug has been logged online:

Bug reference:      5146
Logged by:          xqzhang
Email address:      zxq230104@163.com
PostgreSQL version: 8.3.3
Operating system:   linux 32
Description:        After granting all select privelges on view to user, who
still can not select the view
Details:

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

TEST=# CREATE USER u1;
CREATE ROLE
TEST=# CREATE USER u2;
CREATE ROLE
TEST=# CREATE USER u3;
CREATE ROLE
TEST=# CREATE USER u4;
CREATE ROLE
TEST=# SET SESSION AUTHORIZATION u1;
SET
TEST=> CREATE TABLE t1(a INT);
CREATE TABLE
TEST=>
TEST=> SET SESSION AUTHORIZATION u2;
SET
TEST=> CREATE VIEW v2 AS
TEST-> SELECT * FROM t1;
CREATE VIEW
TEST=>
TEST=> SET SESSION AUTHORIZATION u3;
SET
TEST=> CREATE VIEW v3 AS
TEST-> SELECT * FROM v2;
CREATE VIEW
TEST=>
TEST=> SET SESSION AUTHORIZATION u4;
SET
TEST=> SELECT * FROM v3;
ERROR:  permission denied for relation v3
TEST=>
TEST=> RESET SESSION AUTHORIZATION;
RESET
TEST=# GRANT ALL ON t1 to u4;
GRANT
TEST=# GRANT ALL ON v2 to u4;
GRANT
TEST=# GRANT ALL ON v3 to u4;
GRANT
TEST=# SET SESSION AUTHORIZATION u4;
SET
TEST=> SELECT * FROM v3;
ERROR:  permission denied for relation v2
TEST=>

"xqzhang" <zxq230104@163.com> writes:
> Description:        After granting all select privelges on view to user, who
> still can not select the view

You have a fundamental misconception about how that works.  The
privilege checks for each view are applied with respect to the owner
of that view, not the current session user.  So for example u3 needs
access to v2, not u4.

            regards, tom lane