BUG #13907: Restore materialized view throw permission denied

From: marian(dot)krucina(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13907: Restore materialized view throw permission denied
Date: 2016-02-02 16:14:07
Message-ID: 20160202161407.2778.24659@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13907
Logged by: Marian Krucina
Email address: marian(dot)krucina(at)gmail(dot)com
PostgreSQL version: 9.5.0
Operating system: Centos
Description:

Hi,

restore (9.4.5, 9.5.0) or pg_upgrade (9.4.5 to 9.5.0) fail on CREATE
MATERIALIZED VIEW.
This is similar to:
http://www.postgresql.org/message-id/11166.1424357659@sss.pgh.pa.us

Problem is, when view runs as user definer.
Is possible move 'CREATE MATERIALIZED VIEW' in a dump to end?

Scenario:

CREATE ROLE role1;
CREATE ROLE role2;
CREATE TABLE table1(i INT);
CREATE VIEW view1 AS SELECT * FROM table1;
ALTER TABLE table1 OWNER TO role1;
ALTER VIEW view1 OWNER TO role2;
GRANT SELECT ON table1 TO role2;
CREATE MATERIALIZED VIEW view2 AS SELECT * FROM view1;
ALTER MATERIALIZED VIEW view2 OWNER TO role2;

# pg_dump -U postgres test -f test.sql
# psql -U postgres test2 -f test.sql -1 -e
...
CREATE MATERIALIZED VIEW view2 AS
SELECT view1.i
FROM view1
WITH NO DATA;
psql:test.sql:221: ERROR: permission denied for relation table1

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-02-02 16:18:38 Re: BUG #13905: Inconsistent code modification
Previous Message dmitry-ryabov 2016-02-02 12:59:24 BUG #13906: improper hstore_to_json_loose functioning