Обсуждение: can select contents of view but not view itself, despite indirect membership

Поиск
Список
Период
Сортировка

can select contents of view but not view itself, despite indirect membership

От
Kevin Field
Дата:
Hi everyone,

I'm having a strange issue on PostgreSQL 9.0.1 on Windows Server 2003
SP2.

I connect as a superuser and then SET SESSION AUTHORIZATION to user
"X" who is a member of group role "extranet_user" which inherits
membership from group role "user".  "X", "extranet_user", and even
"user" are all INHERIT.

I have the following view:

CREATE OR REPLACE VIEW page_startup AS
 SELECT contact.name, contact.nickname, COALESCE(
        CASE
            WHEN has_table_privilege('mandate'::text, 'select'::text)
THEN ( SELECT false AS bool
               FROM mandate
       NATURAL JOIN task
         WHERE task.waiting_for = "session_user"()::text::integer AND
task.deadline < now()
        LIMIT 1)
            ELSE NULL::boolean
        END, true) AS no_mandates
   FROM contact
  WHERE contact.id = "session_user"()::text::integer;

GRANT SELECT ON TABLE page_startup TO "user";


If I run this:

set session authorization "X";
select pg_has_role('user','member')

I get 't' as a result.  Also, if I run this (just copying the
definition of the view):


set session authorization "X";
 SELECT contact.name, contact.nickname, COALESCE(
        CASE
            WHEN has_table_privilege('mandate'::text, 'select'::text)
THEN ( SELECT false AS bool
               FROM mandate
       NATURAL JOIN task
         WHERE task.waiting_for = "session_user"()::text::integer AND
task.deadline < now()
        LIMIT 1)
            ELSE NULL::boolean
        END, true) AS no_mandates
   FROM contact
  WHERE contact.id = "session_user"()::text::integer;


I get the single row of data I'm looking for.

However, if I try to use the view instead of copying its definition:


set session authorization "X";
select * from page_startup


I get the following:

ERROR:  permission denied for relation page_startup

********** Error **********

ERROR: permission denied for relation page_startup
SQL state: 42501



Strange, no?  Anybody have any ideas why this might be?

Thanks,
Kev

Re: can select contents of view but not view itself, despite indirect membership

От
Tom Lane
Дата:
Kevin Field <kevinjamesfield@gmail.com> writes:
> Strange, no?  Anybody have any ideas why this might be?

Worksforme:

regression=# create group "user";
CREATE ROLE
regression=# create group extranet_user in group "user";
CREATE ROLE
regression=# create user x in group extranet_user;
CREATE ROLE
regression=# create view page_startup as select ...
CREATE VIEW
regression=# GRANT SELECT ON TABLE page_startup TO "user";
GRANT
regression=# set session authorization x;
SET
regression=> select * from page_startup;
[ works ]

I'm a bit suspicious of naming a group "user".  I wonder whether you
outsmarted yourself somewhere along the line by failing to double-quote
that name, so that the command ended up doing something else than you
thought.

            regards, tom lane

Re: can select contents of view but not view itself, despite indirect membership

От
Kevin Field
Дата:
On Nov 1, 4:39 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Kevin Field <kevinjamesfi...@gmail.com> writes:
> > Strange, no?  Anybody have any ideas why this might be?
>
> Worksforme:
>
> regression=# create group "user";
> CREATE ROLE
> regression=# create group extranet_user in group "user";
> CREATE ROLE
> regression=# create user x in group extranet_user;
> CREATE ROLE
> regression=# create view page_startup as select ...
> CREATE VIEW
> regression=# GRANT SELECT ON TABLE page_startup TO "user";
> GRANT
> regression=# set session authorization x;
> SET
> regression=> select * from page_startup;
> [ works ]
>
> I'm a bit suspicious of naming a group "user".  I wonder whether you
> outsmarted yourself somewhere along the line by failing to double-quote
> that name, so that the command ended up doing something else than you
> thought.
>
>                         regards, tom lane

Good point about the naming (I was a bit wary of it myself but hadn't
thought of the right thing yet); however, as you can see, the view
grant is quoted, and also pgAdminIII shows this:

GRANT "user" TO extranet_user;

My guess is that it has something to do with the join to the table
'mandate'.  If your view definition includes a CASE WHEN... that would
potentially (but never actually, since it tests for permission first)
select from a table that you don't have permission to select
from...does it still work for you?  (I'll try to build a generic
example tomorrow to limit it to this specific test.)

Thanks,
Kev

Re: can select contents of view but not view itself, despite indirect membership

От
Tom Lane
Дата:
Kevin Field <kevinjamesfield@gmail.com> writes:
> My guess is that it has something to do with the join to the table
> 'mandate'.

I was wondering about that too, but the error message is pretty clear
about which table it's complaining about.

Please see if you can put together a self-contained example.

            regards, tom lane

Re: can select contents of view but not view itself, despite indirect membership

От
Kevin Field
Дата:
> My guess is that it has something to do with the join to the table
> 'mandate'.  If your view definition includes a CASE WHEN... that would

My new guess is something to do with permissions being cached
somewhere, because this morning when I tried it (SET followed by
SELECT from page_startup) from pgAdminIII, it worked.  I had shut down
pgAdminIII before leaving last night.  Gah!

Oh well, at least it's working now.  I guess I should remember to
always try restarting everything...

Kev