BUG #5147: DBA can not access view

"Dongni" <donniehan@126.com> 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

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@sss.pgh.pa.us>?写道：

"Dongni"?<donniehan@126.com>?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国韵网游《天下贰》

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@sss.pgh.pa.us>
Newsgroups: pgsql.bugs
Sent: Thursday, October 29, 2009 9:34 PM
Subject: Re: [BUGS] BUG #5147: DBA can not access view

"hx.li" <fly2nn@126.com> 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

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@sss.pgh.pa.us> д����Ϣ����:7536.1256911178@sss.pgh.pa.us...

"hx.li" <fly2nn@126.com> 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

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@sss.pgh.pa.us> д����Ϣ����:6863.1257132736@sss.pgh.pa.us...

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@126.com> 写入消息 news:hclr5f$2nr7$1@news.hub.org...> 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@sss.pgh.pa.us> 写入消息新闻:6863.1257132736@sss.pgh.pa.us...>> "hx.li" <fly2nn@126.com> 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@postgresql.org)>> To make changes to your subscription:>> http://www.postgresql.org/mailpref/pgsql-bugs&gt;&gt; > >

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

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

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@gmail.com>?写道：