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)
