Search Postgresql Archives

View permission error after upgrading from 8.4 -> 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux