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

From: Kevin Field <kevinjamesfield(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: can select contents of view but not view itself, despite indirect membership
Date: 2010-11-01 18:28:01
Message-ID: 33a5e2a6-e5d0-44d8-ab09-ae4b4dc582c5@g25g2000yqn.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan Tripathy 2010-11-01 18:38:49 Re: JDBC Transactions
Previous Message hubert depesz lubaczewski 2010-11-01 18:27:48 Re: Why so many xlogs?