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)
