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 ;)