PostgreSQL crosstab query not using view owner permission when queried from other users
1
vote
1
answer
91
views
I'd like some help with regards to creating a view in PostgreSQL using the
crosstab
function.
I've been trying to create a view as follows:
create or replace view csv_data as with answers as(
select * from crosstab('select a.column1, q.column2, a.column3 from answer a
join question q on a.question_id = q.id order by 1,2')
as final_result (column1 bpchar, question_1 varchar(500), question_2 varchar(500), question_3 varchar(500)))
select q.name as "Column1", a.question_1 as "Question1", a.question_2 as "Question_2", a.question_3 as "Question_3"
from answers a join evaluation e on e.id = a.eid
join questionnaire q on q.id = e.qid
where q.active = true
order by q.name;
The query above has been slightly modified in order to be in this post, but it does not change the main point in question.
The point being, while trying to perform a select on the view with another user, whose privileges are restricted to a SELECT on such view, I was denied access to the table answer
inside the crosstab
query. What bothers me is that, the ownership chaining is woking perfectly well in all other cases, only in the query within the crosstab
function it does not chain privileges and tries to perform a select using the executing user privileges instead of the view owner.
I'd like to know whether it is possible to solve this issue while still using the crosstab
. If not, is there any other way?
Asked by lsimonetii
(21 rep)
Jun 13, 2024, 04:12 PM
Last activity: Jun 17, 2024, 07:26 PM
Last activity: Jun 17, 2024, 07:26 PM