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
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? |