Brian Sutherland <brian@xxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
