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

On Nov 1, 4:39 pm, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)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