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

[Kevin Field <kevinjamesfield@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general