Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres

"bemanuel.pe@gmail.com" <bemanuel.pe@gmail.com> wrote:=0A=0A> tjma_dw=3D> s=
et role user_dw;=0A>=0A> tjma_dw=3D> CREATE TABLE foo_data AS SELECT i, md5=
(random()::text) FROM=0A> generate_series(1, 10) i;=0A> SELECT 10=0A> tjma_=
dw=3D> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;=0A> SELEC=
T 10=0A> tjma_dw=3D> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw;=0A> A=
LTER MATERIALIZED VIEW=0A> tjma_dw=3D> REFRESH MATERIALIZED VIEW mv_foo;=0A=
> REFRESH MATERIALIZED VIEW=0A> tjma_dw=3D> ALTER TABLE foo_data OWNER TO u=
ser_dw;=0A> ALTER TABLE=0A> tjma_dw=3D> REFRESH MATERIALIZED VIEW mv_foo;=
=0A> REFRESH MATERIALIZED VIEW=0A> tjma_dw=3D> create unique index on mv_fo=
o (i);=0A> CREATE INDEX=0A=0A> /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma=
_dw=0A=0A> tjma_dw=3D# refresh materialized view CONCURRENTLY mv_foo;=0A> E=
RROR:=A0 permission denied for relation pg_temp_432971_2=0A> CONTEXT:=A0 SQ=
L statement "DELETE FROM public.mv_foo mv WHERE ctid=0A> OPERATOR(pg_catalo=
g.=3D) ANY (SELECT diff.tid FROM pg_temp_10.pg_temp_432971_2=0A> diff WHERE=
 diff.tid IS NOT NULL AND diff.newdata IS NULL)"=0A=0AYeah, that's a bug; o=
r probably two.=A0 I can simplify the test case:=0A=0ACREATE ROLE user_dw;=
=0ASET ROLE user_dw;=0ACREATE TABLE foo_data AS SELECT i, md5(random()::tex=
t)=0A=A0 FROM generate_series(1, 10) i;=0ACREATE MATERIALIZED VIEW mv_foo A=
S SELECT * FROM foo_data;=0ACREATE UNIQUE INDEX ON mv_foo (i);=0ARESET ROLE=
;=0AREFRESH MATERIALIZED VIEW CONCURRENTLY mv_foo;=0A=0AIt is running afoul=
 of a security measure (the query to repopulate=0Adata is run as the owner =
of the materialized view, to prevent=0Aplacing trojan horses for a superuse=
r).=A0 But it seems to be=0Acreating the temporary table as the superuser, =
preventing even the=0Aowner from running the REFRESH ... CONCURRENTLY.=A0 T=
he query that is=0Abeing displayed is internal; we should probably find a w=
ay to show=0Athe statement that was run at the top level instead.=0A=0AI'll=
 look at fixing both.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprise=
db.com=0AThe Enterprise PostgreSQL Company