From: | Brian Hirt <bhirt(at)me(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | View permission error after upgrading from 8.4 -> 9.2 |
Date: | 2013-08-13 16:54:53 |
Message-ID: | ec980a7b-3d25-4180-8542-7c036fb1e8f7@me.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is no longer working. When selecting from the view, I get a permission denied error on one of the referenced tables. However, I can run the view's query directly without problems and I have read access to all the tables the view accesses. I'm a bit confused as to what's causing this. I'm logged in as the postgres superuser and don't have any permissions set up for the views/tables in question. Any help would be appreciated.
basement_QA=# create or replace view vcredit_info as SELECT game_credit.developer_id, credit_submission.game_id,
credit_submission.platform_id, game_credit.game_credit_title_id,
game_credit_title.developer_title_id,
( SELECT min(substr(gv.release_date::text, 1, 4)::integer) AS min
FROM game_version gv
WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS first_year,
( SELECT max(substr(gv.release_date::text, 1, 4)::integer) AS max
FROM game_version gv
WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS last_year,
developer_title.credit_title_category_id
FROM game_credit
JOIN credit_submission USING (credit_submission_id)
JOIN game_status USING (game_id, platform_id)
JOIN game_credit_title USING (game_credit_title_id)
JOIN developer_title USING (developer_title_id)
WHERE game_status.approved = 1 AND credit_submission.approved = 1;
CREATE VIEW
basement_QA=# select count(*) from vcredit_info where game_id = 30997;
ERROR: permission denied for relation developer_title
basement_QA=# select count(*) from developer_title;
count
-------
224
(1 row)
basement_QA=# select count(*) from (SELECT game_credit.developer_id, credit_submission.game_id,
basement_QA(# credit_submission.platform_id, game_credit.game_credit_title_id,
basement_QA(# game_credit_title.developer_title_id,
basement_QA(# ( SELECT min(substr(gv.release_date::text, 1, 4)::integer) AS min
basement_QA(# FROM game_version gv
basement_QA(# WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS first_year,
basement_QA(# ( SELECT max(substr(gv.release_date::text, 1, 4)::integer) AS max
basement_QA(# FROM game_version gv
basement_QA(# WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS last_year,
basement_QA(# developer_title.credit_title_category_id
basement_QA(# FROM game_credit
basement_QA(# JOIN credit_submission USING (credit_submission_id)
basement_QA(# JOIN game_status USING (game_id, platform_id)
basement_QA(# JOIN game_credit_title USING (game_credit_title_id)
basement_QA(# JOIN developer_title USING (developer_title_id)
basement_QA(# WHERE game_status.approved = 1 AND credit_submission.approved = 1) as myview where myview.game_id = 30997;
count
-------
66
(1 row)
basement_QA=# select CURRENT_USER;
current_user
--------------
postgres
(1 row)
basement_QA=# \dp vcredit_info
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+------+-------------------+--------------------------
public | vcredit_info | view | |
(1 row)
basement_QA=# \dp developer_title;
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------------+-------+-------------------+--------------------------
public | developer_title | table | |
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | François Beausoleil | 2013-08-13 17:05:52 | Difference between terminate/cancel? |
Previous Message | Jeff Janes | 2013-08-13 16:46:49 | Re: Convincing the query planner to play nice |