Thread: BUG #3265: 8.1 -> 8.2 behviour change: View owner must have access to underlying tables

The following bug has been logged online:

Bug reference:      3265
Logged by:          Russell Smith
Email address:      mr-russ@pws.com.au
PostgreSQL version: 8.2.4
Operating system:   RHEL4
Description:        8.1 -> 8.2 behviour change: View owner must have access
to underlying tables
Details:

During the testing phase of an upgrade from 8.1.3 to 8.2.4 I discovered the
following;

If the owner of a view does not have permission to the underlying tables,
then nobody can use the view.  Not even SUPERUSERS.  This may be expected
behaviour.  The error "ERROR:  permission denied for relation t1" is
displayed when executing the "SELECT * from v1" step.  However the same SQL
executed on 8.1.3 will display the row from v1.  The SQL below was run as a
superuser.

I am of the opinion that the 8.2.4 behaviour is what is expected and the
8.1.3 behaviour is incorrect.

So the issues are really;
1. 8.1.3 has a bug that allows execution of a view with privileges other
than the view owner.
2. 8.2.4 doesn't bypass privileges for views (possibly correct)
3. 8.1 -> 8.2 Documentation doesn't reflect the behaviour change.

I was unable to test on 8.1.8 sorry.

[further irc dicussion...]

It also appears to be an issue with the way PUBLIC is implied when you
create a table in 8.1 is different from the way it's done in 8.2.

I'm a little confused after all of the investigation done so far.  So I'm
submitting what I've got and am happy to provide further clarification if I
get a clearer idea in my head of what I'm saying.  Either way, the behaviour
has changed and a doc not about it is at least required.


Begin Reproduction Script;

BEGIN;
CREATE ROLE "USER";
create table t1 (a integer);
INSERT INTO t1 VALUES (1);
CREATE view v1 as select * from t1;
ALTER TABLE v1 OWNER TO "USER";
REVOKE ALL ON t1 FROM "USER";
SELECT * from v1;
DROP VIEW v1;
DROP TABLE t1;
DROP ROLE "USER";
ROLLBACK;

"Russell Smith" <mr-russ@pws.com.au> writes:
> CREATE view v1 as select * from t1;
> ALTER TABLE v1 OWNER TO "USER";

See thread here:
http://archives.postgresql.org/pgsql-hackers/2006-04/msg01138.php
and patch here:
http://archives.postgresql.org/pgsql-committers/2006-09/msg00101.php

This was not back-patched because it seemed like too much of a behavior
change for stable branches.

            regards, tom lane

Tom Lane wrote:
> "Russell Smith" <mr-russ@pws.com.au> writes:
>
>> CREATE view v1 as select * from t1;
>> ALTER TABLE v1 OWNER TO "USER";
>>
>
> See thread here:
> http://archives.postgresql.org/pgsql-hackers/2006-04/msg01138.php
> and patch here:
> http://archives.postgresql.org/pgsql-committers/2006-09/msg00101.php
>
> This was not back-patched because it seemed like too much of a behavior
> change for stable branches.
>
Thanks,

I forgot the link between RULES and views.

But this also means that a super user is not really a super user
anymore.  Are we comfortable with this redefinition?

Russell
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

Russell Smith <mr-russ@pws.com.au> writes:
> But this also means that a super user is not really a super user
> anymore.  Are we comfortable with this redefinition?

Hm?  It has never been the case that a view would act differently for a
superuser than it would when invoked by someone else.  The bug here is
in ALTER OWNER failing to reassign the view's permissions checks
properly, not in how they are enforced later.

            regards, tom lane