The following bug has been logged on the website:
Bug reference: 13907
Logged by: Marian Krucina
Email address: marian.krucina@gmail.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