The following bug has been logged online:
Bug reference: 5147
Logged by: Dongni
Email address: donniehan@126.com
PostgreSQL version: 8.4.0
Operating system: WindowsXP 32bit
Description: DBA can not access view
Details:
Please fyi the following test case
postgres=# create user user1;
CREATE ROLE
postgres=# create user user2;
CREATE ROLE
postgres=# set session authorization user1;
SET
postgres=> create table tb1(a int);
CREATE TABLE
postgres=> set session authorization user2;
SET
postgres=> create table tb2(b int);
CREATE TABLE
postgres=> set session authorization user1;
SET
postgres=> create view view1 as select tb1.a,tb2.b from tb1,tb2;
CREATE VIEW
postgres=> reset session authorization;
RESET
postgres=# select * from view1;
ERROR: permission denied for relation tb2
postgres=# select * from tb2;
b
---
(0 rows)
postgres=# select * from pg_user where usename='postgres'
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+------
----+-----------
postgres | 10 | t | t | t | ******** |
|
In my opinion, "postgres" as super user should be able to access any object
in the database. The document says "A database superuser bypasses all
permission checks".
But in this case, postgres can not select from the view view1. Please
confirm whether it is a bug or this behavor is by design.
