BUG #12898: Failure loading materialized view with pg_restore - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      12898
Logged by:          José Pedro
Email address:      jgpedro@gmail.com
PostgreSQL version: 9.3.6
Operating system:   Ubuntu 14.04.2 LTS 64-bit
Description:

pg_restore of a materialized view fails with "permission denied for relation
<X>".

The same problem as discussed in this thread:

http://www.postgresql.org/message-id/flat/11166.1424357659@sss.pgh.pa.us#11166.1424357659@sss.pgh.pa.us

Problem identified as:

- Tables ACLs restored after materialized views creation;

- Owner of materialized view only gets permission to table after the ACLs
being restored.

As Tom Lane said
(http://www.postgresql.org/message-id/11166.1424357659@sss.pgh.pa.us):

"We need to rethink the ordering rules here.  I believe that not dumping
ACLs till late is an intentional choice to avoid corner cases with regular
tables (eg what if user has revoked INSERT on a table), but it doesn't
work so well for matviews.

One possible avenue to a fix is to also postpone the assignment of the
matview's owner, but I'm not sure that that's a great idea from a security
standpoint.

A possibly safer idea is just to put all REFRESHes after all ACL updates.
If things fail then, well, they'd have failed anyway."


I'm opening a bug report so this issue won't be forgotten ;)