BUG #5147: DBA can not access view

[Dongni]

The following bug has been logged online:

Bug reference:      5147
Logged by:          Dongni
Email address:      donniehan@...
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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Tom Lane-2]

"Dongni" <donniehan@...> writes:
> Description:        DBA can not access view

This is not a bug.  The view is owned by user1 and what the view can
access is determined by user1's permissions, independently of who is
calling it.

                        regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Dongni]

Hi Tom,

Thanks for you reply, but i don't quite get it.

 

"The view is owned by user1 and what the view can access is determined by user1's permissions"

 

Why? I don't get it.

On one hand, if user1 can not access tb2, he should not be able to create the view based on tb2.

On the other hand, user1 does own view1, but view1 is an independent object in database. As a super user can access tb1 and tb2. Why he can not access view1 when user1 can't and when he can that 'cause user1 can.

I don't understand the design behavior.Can you explain me more detailedly?

Thanks!

在2009-10-29 21:34:40，"Tom Lane" <tgl@...> 写道：
>"Dongni" <donniehan@...> writes:
>> Description:        DBA can not access view
>
>This is not a bug.  The view is owned by user1 and what the view can
>access is determined by user1's permissions, independently of who is
>calling it.
>
>			regards, tom lane

---

看陆川杨幂新片《琴棋书画》，品网易3D国韵网游《天下贰》

---

看陆川杨幂新片《琴棋书画》，品网易3D国韵网游《天下贰》

---

"雪见"杨幂邀你共品3D国韵网游《天下贰》，海报免费领

[fly-3]

In document£¬<SQL Commands --- grant>, it said:

It should be noted that database superusers can access all objects
regardless of object privilege settings. This is comparable to the rights of
root in a Unix system. As with root, it's unwise to operate as a superuser
except when absolutely necessary.

But Dongni's test case:

postgres=> reset session authorization;
RESET
postgres=# select * from view1; -- it is superuser, should access all
objects.
ERROR:  permission denied for relation tb2
postgres=# select * from tb2;
 b
---
(0 rows)

So I think it should not have a permission error when run "select * from
view1".

Maybe I have a misconception for superuser?

regards, hx.li

----- Original Message -----
From: "Tom Lane" <tgl@...>
Newsgroups: pgsql.bugs
Sent: Thursday, October 29, 2009 9:34 PM
Subject: Re: [BUGS] BUG #5147: DBA can not access view

> "Dongni" <donniehan@...> writes:
>> Description:        DBA can not access view
>
> This is not a bug.  The view is owned by user1 and what the view can
> access is determined by user1's permissions, independently of who is
> calling it.
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Tom Lane-2]

"hx.li" <fly2nn@...> writes:
>> This is not a bug.  The view is owned by user1 and what the view can
>> access is determined by user1's permissions, independently of who is
>> calling it.

> So I think it should not have a permission error when run "select * from
> view1".

No, that would be a bad idea.  Your proposal essentially means that it's
impossible for a superuser to give up rights when calling a setuid
function or view.  That would be a serious security hazard.

                        regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[fly-3]

Q1: Who can explain the privilage of the superuser ?

In postgresql's document£¬Part VI. Reference,SQL Commands,GRANT, it said:

It should be noted that database superusers can access all objects
regardless of object privilege settings.

Q2: Why PostgreSQL check whether the view1'sowner had peivilage for tb2 when
run "select * from view1;" ?  (Dongni's test case)

In Dongni's test case, current user is superuser when run "select * from
view1;" .
Reading the pg_class_aclmask() in aclchk.c, I found PG claim the current
object's owner(current object is view1) should have the select privilage for
table tb2. I dno't usderstant why do it so?

regards, hx.li

"Tom Lane" <tgl@...> дÈëÏûÏ¢ÐÂÎÅ:7536.1256911178@......

> No, that would be a bad idea.  Your proposal essentially means that it's
> impossible for a superuser to give up rights when calling a setuid
> function or view.  That would be a serious security hazard.
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Tom Lane-2]

"hx.li" <fly2nn@...> writes:
> In postgresql's document£¬Part VI. Reference,SQL Commands,GRANT, it said:

> It should be noted that database superusers can access all objects
> regardless of object privilege settings.

What that means in this example is that the superuser can select from
the view, even if the view's owner tries to prevent that.  However,
the view itself doesn't have any more permissions than it had before.
It would have failed for anyone, and it fails for the superuser too.

I grow weary of debating this with you.

                        regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[fly-3]

I think it is right---the superuser can select from
the view, even if the view's owner tries to prevent that---,

but maybe a good way is checking owner's privilage when creating a view as
Oracle.

It would be better not to create a view if a user cann`t access a table.

regards, hx.li

"Tom Lane" <tgl@...> дÈëÏûÏ¢ÐÂÎÅ:6863.1257132736@......

> "hx.li" <fly2nn@...> writes:
>> In postgresql's document£¬Part VI. Reference,SQL Commands,GRANT, it said:
>
>> It should be noted that database superusers can access all objects
>> regardless of object privilege settings.
>
> What that means in this example is that the superuser can select from
> the view, even if the view's owner tries to prevent that.  However,
> the view itself doesn't have any more permissions than it had before.
> It would have failed for anyone, and it fails for the superuser too.
>
> I grow weary of debating this with you.
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Dongni]

Hi Tom,

I agree with Hxli. It may be a good way to add permissions check when create the view.

I also find 2 pieces of words in the document about the owner of the object.

"By default, only the owner of an object can do anything with the object."

"....as the owner has all privileges by default."

In my case, as the view1 is already owned by user1, so user1 should has all privileges of view1, but user1 can not select from view1, I am very confused by these words. So it maybe necessary to check the user's permissions when he create the object.

Regards

-Dongni

"hx.li" <fly2nn@...> 写入消息 news:hclr5f$2nr7$1@......

> I think it is right---the superuser can select from
> the view, even if the view's owner tries to prevent that---,
> 
> but maybe a good way is checking owner's privilage when creating a view as 
> Oracle.
> 
> It would be better not to create a view if a user cann`t access a table.
> 
> regards, hx.li
> 
> "Tom Lane" <tgl@...> 写入消息新闻:6863.1257132736@......
>> "hx.li" <fly2nn@...> writes:
>>> In postgresql's document，Part VI. Reference,SQL Commands,GRANT, it said:
>>
>>> It should be noted that database superusers can access all objects
>>> regardless of object privilege settings.
>>
>> What that means in this example is that the superuser can select from
>> the view, even if the view's owner tries to prevent that.  However,
>> the view itself doesn't have any more permissions than it had before.
>> It would have failed for anyone, and it fails for the superuser too.
>>
>> I grow weary of debating this with you.
>>
>> regards, tom lane
>>
>> -- 
>> Sent via pgsql-bugs mailing list (pgsql-bugs@...)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>> 
> 
>

[Robert Haas]

2009/11/2 donniehan <donniehan@...>:

> Hi Tom,
>
> I agree with Hxli. It may be a good way to add permissions check when create
> the view.
>
> I also find 2 pieces of words in the document about the owner of the object.
>
> "By default, only the owner of an object can do anything with the object."
>
> "....as the owner has all privileges by default."
>
> In my case, as the view1 is already owned by user1, so user1 should has all
> privileges of view1, but user1 can not select from view1, I am very confused
> by these words. So it maybe necessary to check the user's permissions when
> he create the object.

Guys, this is pretty straightforward.  The permissions on the view
determine who can access it.  The permissions of the view owner
determine what the view can access.  The way to think about this may
be that a view acts a bit like a setuid program under UNIX: a regular
user can gain superuser privileges; a superuser can give them up.

This may or may not make sense to you and it may or may not be what
you want, but it's NOT A BUG.  It's done that way on purpose, it's
well-documented, and it's been that way for a long time.  If you want
some explanation of WHY is that way and what it might be useful for,
start by reading the documentation and then if you have questions, ask
on the appropriate mailing list, maybe pgsql-general or pgsql-novice.

...Robert

--
Sent via pgsql-bugs mailing list (pgsql-bugs@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Dongni]

Hi Robert,

Okay i get what you mean, you can finish this thread. Thanks for reply anyway!

Regards

-Dongni

在2009-11-02 22:56:03，"Robert Haas" <robertmhaas@...> 写道：
>2009/11/2 donniehan <donniehan@...>:
>> Hi Tom,
>>
>> I agree with Hxli. It may be a good way to add permissions check when create
>> the view.
>>
>> I also find 2 pieces of words in the document about the owner of the object.
>>
>> "By default, only the owner of an object can do anything with the object."
>>
>> "....as the owner has all privileges by default."
>>
>> In my case, as the view1 is already owned by user1, so user1 should has all
>> privileges of view1, but user1 can not select from view1, I am very confused
>> by these words. So it maybe necessary to check the user's permissions when
>> he create the object.
>
>Guys, this is pretty straightforward.  The permissions on the view
>determine who can access it.  The permissions of the view owner
>determine what the view can access.  The way to think about this may
>be that a view acts a bit like a setuid program under UNIX: a regular
>user can gain superuser privileges; a superuser can give them up.
>
>This may or may not make sense to you and it may or may not be what
>you want, but it's NOT A BUG.  It's done that way on purpose, it's
>well-documented, and it's been that way for a long time.  If you want
>some explanation of WHY is that way and what it might be useful for,
>start by reading the documentation and then if you have questions, ask
>on the appropriate mailing list, maybe pgsql-general or pgsql-novice.
>
>...Robert

---

网易历六年耗亿资打造，3D国韵网游《天下贰》，免费领光盘