Thread: BUG #5147: DBA can not access view

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.

"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

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


> "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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

"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

SGkgVG9tLApUaGFua3MgZm9yIHlvdSByZXBseSwgYnV0IGkgZG9uJ3QgcXVp
dGUgZ2V0IGl0LgogCiJUaGUgdmlldyBpcyBvd25lZCBieSB1c2VyMSBhbmQg
d2hhdCB0aGUgdmlldyBjYW4gYWNjZXNzIGlzIGRldGVybWluZWQgYnkgdXNl
cjEncyBwZXJtaXNzaW9ucyIKIApXaHk/IEkgZG9uJ3QgZ2V0IGl0LiAKT24g
b25lIGhhbmQsIGlmIHVzZXIxIGNhbiBub3QgYWNjZXNzIHRiMiwgaGUgc2hv
dWxkIG5vdCBiZSBhYmxlIHRvIGNyZWF0ZSB0aGUgdmlldyBiYXNlZCBvbiB0
YjIuCk9uIHRoZSBvdGhlciBoYW5kLCB1c2VyMSBkb2VzIG93biB2aWV3MSwg
YnV0IHZpZXcxIGlzIGFuIGluZGVwZW5kZW50IG9iamVjdCBpbiBkYXRhYmFz
ZS4gQXMgYSBzdXBlciB1c2VyIGNhbiBhY2Nlc3MgdGIxIGFuZCB0YjIuIFdo
eSBoZSBjYW4gbm90IGFjY2VzcyB2aWV3MSB3aGVuIHVzZXIxIGNhbid0IGFu
ZCB3aGVuIGhlIGNhbiB0aGF0ICdjYXVzZSB1c2VyMSBjYW4uCkkgZG9uJ3Qg
dW5kZXJzdGFuZCB0aGUgZGVzaWduIGJlaGF2aW9yLkNhbiB5b3UgZXhwbGFp
biBtZSBtb3JlIGRldGFpbGVkbHk/ClRoYW5rcyEgCgoKCgrU2jIwMDktMTAt
Mjk/MjE6MzQ6NDCjrCJUb20/TGFuZSI/PHRnbEBzc3MucGdoLnBhLnVzPj/Q
tLXAo7oKPiJEb25nbmkiPzxkb25uaWVoYW5AMTI2LmNvbT4/d3JpdGVzOgo+
Pj9EZXNjcmlwdGlvbjo/Pz8/Pz8/P0RCQT9jYW4/bm90P2FjY2Vzcz92aWV3
Cj4KPlRoaXM/aXM/bm90P2E/YnVnLj8/VGhlP3ZpZXc/aXM/b3duZWQ/Ynk/
dXNlcjE/YW5kP3doYXQ/dGhlP3ZpZXc/Y2FuCj5hY2Nlc3M/aXM/ZGV0ZXJt
aW5lZD9ieT91c2VyMSdzP3Blcm1pc3Npb25zLD9pbmRlcGVuZGVudGx5P29m
P3dobz9pcwo+Y2FsbGluZz9pdC4KPgo+CQkJcmVnYXJkcyw/dG9tP2xhbmUK
CgoKCr+0wr20qNHuw93QwsasobbH2cblyum7raG3o6zGt8340tczRLn61M/N
+NPOobbM7M/Ct6GhtwoKCr+0wr20qNHuw93QwsasobbH2cblyum7raG3o6zG
t8340tczRLn61M/N+NPOobbM7M/Ct6Ghtw==

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...
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

"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...
> "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
>

Hi Tom,

I agree with Hxli. It may be a good way to add permissions check when creat=
e 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 confus=
ed by these words. So it maybe necessary to check the user's permissions wh=
en he create the object.

Regards
-Dongni
"hx.li" <fly2nn@126.com> =D0=B4=C8=EB=CF=FB=CF=A2 news:hclr5f$2nr7$1@news.h=
ub.org...> I think it is right---the superuser can select from> the view, e=
ven if the view's owner tries to prevent that---,> > but maybe a good way i=
s 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> =D0=B4=C8=EB=CF=FB=CF=A2=D0=C2=CE=
=C5:6863.1257132736@sss.pgh.pa.us...>> "hx.li" <fly2nn@126.com> writes:>>> =
In postgresql's document=A3=ACPart VI. Reference,SQL Commands,GRANT, it sai=
d:>>>>> It should be noted that database superusers can access all objects>=
>> regardless of object privilege settings.>>>> What that means in this exa=
mple is that the superuser can select from>> the view, even if the view's o=
wner tries to prevent that.  However,>> the view itself doesn't have any mo=
re permissions than it had before.>> It would have failed for anyone, and i=
t fails for the superuser too.>>>> I grow weary of debating this with you.>=
>>> regards, tom lane>>>> -- >> Sent via pgsql-bugs mailing list (pgsql-bug=
s@postgresql.org)>> To make changes to your subscription:>> http://www.post=
gresql.org/mailpref/pgsql-bugs>> > >=

2009/11/2 donniehan <donniehan@126.com>:
> Hi Tom,
>
> I agree with Hxli. It may be a good way to add permissions check when cre=
ate
> the view.
>
> I also find=A02 pieces of words in the document about the owner of the=A0=
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=A0user1 should has=
 all
> privileges of view1, but user1 can not select=A0from view1, I am very con=
fused
> by these words.=A0So it maybe necessary to check the user's permissions w=
hen
> 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

SGkgUm9iZXJ0LApPa2F5IGkgZ2V0IHdoYXQgeW91IG1lYW4sIHlvdSBjYW4g
ZmluaXNoIHRoaXMgdGhyZWFkLiBUaGFua3MgZm9yIHJlcGx5IGFueXdheSEK
UmVnYXJkcwotRG9uZ25pCgoKCgrU2jIwMDktMTEtMDI/MjI6NTY6MDOjrCJS
b2JlcnQ/SGFhcyI/PHJvYmVydG1oYWFzQGdtYWlsLmNvbT4/0LS1wKO6Cj4y
MDA5LzExLzI/ZG9ubmllaGFuPzxkb25uaWVoYW5AMTI2LmNvbT46Cj4+P0hp
P1RvbSwKPj4KPj4/ST9hZ3JlZT93aXRoP0h4bGkuP0l0P21heT9iZT9hP2dv
b2Q/d2F5P3RvP2FkZD9wZXJtaXNzaW9ucz9jaGVjaz93aGVuP2NyZWF0ZQo+
Pj90aGU/dmlldy4KPj4KPj4/ST9hbHNvP2ZpbmQ/Mj9waWVjZXM/b2Y/d29y
ZHM/aW4/dGhlP2RvY3VtZW50P2Fib3V0P3RoZT9vd25lcj9vZj90aGU/b2Jq
ZWN0Lgo+Pgo+Pj8iQnk/ZGVmYXVsdCw/b25seT90aGU/b3duZXI/b2Y/YW4/
b2JqZWN0P2Nhbj9kbz9hbnl0aGluZz93aXRoP3RoZT9vYmplY3QuIgo+Pgo+
Pj8iLi4uLmFzP3RoZT9vd25lcj9oYXM/YWxsP3ByaXZpbGVnZXM/Ynk/ZGVm
YXVsdC4iCj4+Cj4+P0luP215P2Nhc2UsP2FzP3RoZT92aWV3MT9pcz9hbHJl
YWR5P293bmVkP2J5P3VzZXIxLD9zbz91c2VyMT9zaG91bGQ/aGFzP2FsbAo+
Pj9wcml2aWxlZ2VzP29mP3ZpZXcxLD9idXQ/dXNlcjE/Y2FuP25vdD9zZWxl
Y3Q/ZnJvbT92aWV3MSw/ST9hbT92ZXJ5P2NvbmZ1c2VkCj4+P2J5P3RoZXNl
P3dvcmRzLj9Tbz9pdD9tYXliZT9uZWNlc3Nhcnk/dG8/Y2hlY2s/dGhlP3Vz
ZXIncz9wZXJtaXNzaW9ucz93aGVuCj4+P2hlP2NyZWF0ZT90aGU/b2JqZWN0
Lgo+Cj5HdXlzLD90aGlzP2lzP3ByZXR0eT9zdHJhaWdodGZvcndhcmQuPz9U
aGU/cGVybWlzc2lvbnM/b24/dGhlP3ZpZXcKPmRldGVybWluZT93aG8/Y2Fu
P2FjY2Vzcz9pdC4/P1RoZT9wZXJtaXNzaW9ucz9vZj90aGU/dmlldz9vd25l
cgo+ZGV0ZXJtaW5lP3doYXQ/dGhlP3ZpZXc/Y2FuP2FjY2Vzcy4/P1RoZT93
YXk/dG8/dGhpbms/YWJvdXQ/dGhpcz9tYXkKPmJlP3RoYXQ/YT92aWV3P2Fj
dHM/YT9iaXQ/bGlrZT9hP3NldHVpZD9wcm9ncmFtP3VuZGVyP1VOSVg6P2E/
cmVndWxhcgo+dXNlcj9jYW4/Z2Fpbj9zdXBlcnVzZXI/cHJpdmlsZWdlczs/
YT9zdXBlcnVzZXI/Y2FuP2dpdmU/dGhlbT91cC4KPgo+VGhpcz9tYXk/b3I/
bWF5P25vdD9tYWtlP3NlbnNlP3RvP3lvdT9hbmQ/aXQ/bWF5P29yP21heT9u
b3Q/YmU/d2hhdAo+eW91P3dhbnQsP2J1dD9pdCdzP05PVD9BP0JVRy4/P0l0
J3M/ZG9uZT90aGF0P3dheT9vbj9wdXJwb3NlLD9pdCdzCj53ZWxsLWRvY3Vt
ZW50ZWQsP2FuZD9pdCdzP2JlZW4/dGhhdD93YXk/Zm9yP2E/bG9uZz90aW1l
Lj8/SWY/eW91P3dhbnQKPnNvbWU/ZXhwbGFuYXRpb24/b2Y/V0hZP2lzP3Ro
YXQ/d2F5P2FuZD93aGF0P2l0P21pZ2h0P2JlP3VzZWZ1bD9mb3IsCj5zdGFy
dD9ieT9yZWFkaW5nP3RoZT9kb2N1bWVudGF0aW9uP2FuZD90aGVuP2lmP3lv
dT9oYXZlP3F1ZXN0aW9ucyw/YXNrCj5vbj90aGU/YXBwcm9wcmlhdGU/bWFp
bGluZz9saXN0LD9tYXliZT9wZ3NxbC1nZW5lcmFsP29yP3Bnc3FsLW5vdmlj
ZS4KPgo+Li4uUm9iZXJ0Cg==