Re: Failure loading materialized view with pg_restore

Brian Sutherland <brian(at)vanguardistas(dot)net> writes:
> On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote:
>> User "nobody" does not have permission to read table x, so the REFRESH
>> fails, because the view's query executes as the view's owner.

> If you grant select permission for the user nobody on x, pg_restore
> still fails even though a REFRESH succeeds:

Oooh. Yeah: the problem is that pg_dump dumps the REFRESH before it dumps
the ACLs for the tables:

--
-- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE x (
y integer
);

ALTER TABLE x OWNER TO postgres;

--
-- Name: myview; Type: MATERIALIZED VIEW; Schema: public; Owner: nobody; Tablespace:
--

CREATE MATERIALIZED VIEW myview AS
SELECT x.y
FROM x
WITH NO DATA;

ALTER TABLE myview OWNER TO nobody;

--
-- Data for Name: x; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY x (y) FROM stdin;
\.

--
-- Name: myview; Type: MATERIALIZED VIEW DATA; Schema: public; Owner: nobody
--

REFRESH MATERIALIZED VIEW myview;

--
-- Name: x; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE x FROM PUBLIC;
REVOKE ALL ON TABLE x FROM postgres;
GRANT ALL ON TABLE x TO postgres;
GRANT SELECT ON TABLE x TO nobody;

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.

regards, tom lane