Thread: 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
Re: can select contents of view but not view itself, despite indirect membership
From
Tom Lane
Date:
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
From
Kevin Field
Date:
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
From
Tom Lane
Date:
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
From
Kevin Field
Date:
> 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