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;