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