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

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

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

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

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

> 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