Thread: View permission error after upgrading from 8.4 -> 9.2
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)
Brian Hirt <bhirt@me.com> writes:
> 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
fromthe view, I get a permission denied error on one of the referenced tables. � However, I can run the view's query
directlywithout problems and I have read access to all the tables the view accesses.
Permissions checks for tables referenced by a view are done as the view's
owner. I'm suspicious that one of the relations used in your view
vcredit_info is itself a view that references developer_title, and
is owned by some other user with less privilege than you.
regards, tom lane
None of the relations used by vcredit_info are views. They are all tables. Oddly, I dropped the view and recreated it and the problem went away. Earlier I was just using create or replace view and the problem persisted. The schema was created by using pg_restore from an 8.4 custom dump. I can do another pg_restore and see if the problem is reproducible if you want.
On Aug 13, 2013, at 12:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
On Aug 13, 2013, at 12:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Brian Hirt <bhirt@me.com> writes: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.
Permissions checks for tables referenced by a view are done as the view's
owner. I'm suspicious that one of the relations used in your view
vcredit_info is itself a view that references developer_title, and
is owned by some other user with less privilege than you.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Brian Hirt <bhirt@me.com> writes:
> None of the relations used by vcredit_info are views. They are all
> tables. Oddly, I dropped the view and recreated it and the problem went
> away. Earlier I was just using create or replace view and the problem
> persisted.
Hm. I think that CREATE OR REPLACE VIEW may not change the view owner,
so the behavior would be explainable if the view's original owner did
not have permissions to read that table.
regards, tom lane